Cameron.Turner
New member
- Local time
- Today, 18:19
- Joined
- Oct 6, 2009
- Messages
- 5
To the hive mind....
I am importing a named range from hundreds of excel workbooks in specific file locations into an Access Database. The code below works fine except when someone in the business has the excel file open. The import function doesn't work because the file is READ ONLY. When the code is finished, all the excel workbooks that I couldn't import from are open. I tried some code to make it open all files as READ ONLY, but as there are hundreds, I ran out of memory and my computer cried.
Is there a way to ignore the Excel READ ONLY lock and import anyway? Any help you can give would be appreciated!
Function ImportExcelFiles()
DoCmd.SetWarnings False
DoCmd.RunSQL ("Delete * FROM Prod_Mod_Data")
DoCmd.SetWarnings True
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean
blnHasFieldNames = True
strTable = "Prod_Mod_Data"
Set xlObj = CreateObject("Excel.Application")
strPath = "C:\Example\"
strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, , strTable, strPathFile, blnHasFieldNames, "Data_Extract"
strFile = Dir()
Loop
MsgBox "Files successfully Imported"
End Function
I am importing a named range from hundreds of excel workbooks in specific file locations into an Access Database. The code below works fine except when someone in the business has the excel file open. The import function doesn't work because the file is READ ONLY. When the code is finished, all the excel workbooks that I couldn't import from are open. I tried some code to make it open all files as READ ONLY, but as there are hundreds, I ran out of memory and my computer cried.
Is there a way to ignore the Excel READ ONLY lock and import anyway? Any help you can give would be appreciated!
Function ImportExcelFiles()
DoCmd.SetWarnings False
DoCmd.RunSQL ("Delete * FROM Prod_Mod_Data")
DoCmd.SetWarnings True
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean
blnHasFieldNames = True
strTable = "Prod_Mod_Data"
Set xlObj = CreateObject("Excel.Application")
strPath = "C:\Example\"
strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, , strTable, strPathFile, blnHasFieldNames, "Data_Extract"
strFile = Dir()
Loop
MsgBox "Files successfully Imported"
End Function
. Also, there are just too many sheets (circa 30 each week) being added. Linking them will take too much of my time each week.