In my DB, I have a Module called "modImportFiles" which consists of several sub-procedures that utilize the "DoCmd.TransferSpreadsheet acImport" function to import a .XLSX spreadsheet into a specified table in the DB.
The .xlsx files are located in various folders on a network drive - though 95% of the folder path is identical for the files.
Thus, I just recently updated the module to declare a bunch of variables at the Module Level instead of declaring them in each Sub-Procedure since they are used across all Sub-Procedures in this Module.
What's strange is that when I step through a couple of the sub-procedures, I've noticed that the specified ".xlsx" spreadsheet is now opening up (as Read Only) in the database after the DoCmd.TransferSpreadsheet line is finished.
The spreadsheet is obviously supposed to be imported into the database, but I'm not sure why Access is now opening the spreadsheet as well. I didn't change anything other than declaring the variables at the module level.
In theory it should just be importing the file into the specified table, correct? Not also opening the file up
Example - these are the module level variables that I'm declaring
Code:
Option Compare Database
Dim strPriorMonth, strPriorMonthName, strYear, strMonthYear, strMontYear15 As String
Dim strFilePath As String
Dim strFileLoc As String
Dim strFileName As String
Dim strImportTable As String
Dim strDataOwnerEDS As String
Code:
strPriorMonth = (DateSerial(Year(Date), Month(Date), 1)) - 1
strPriorMonthName = Format(strPriorMonth, "MMMM")
strYear = Year(strPriorMonth)
strMonthYear = strPriorMonthName & Space(1) & strYear
strMontYear15 = strPriorMonthName & Space(1) & "15" & Space(1) & strYear
strDataOwnerEDS = "bsmith"
strImportTable = "tblbsmith_Import"
strFilePath = "\\corpnt01\global_sh\PROJECTS\Security Program\Access Reviews\" & strMonthYear & "\Reports\" & strDataOwnerEDS & "\"
strFileName = strDataOwnerEDS & "_" & strMonthYear & ".xlsx"
strFileLoc = strFilePathEDS & strFileName
strFileLocAlt = strFilePathEDSAlt & strFileName
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDelete_Current_Records"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, strImportTable, strFileLoc, True, "Owner_Users!"