I have a Excell Spreadsheet that has over 5,000 worksheets. Each worksheet has the same exact number of column and column names; and each worksheet has at least one row of data. I'm currently using the following VB code to import but it is extremely, extremely slow:
Const acImport = 0
Const acSpreadsheetTypeExcel9 = 8
Set objAccess = CreateObject("Access.Application")
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
strFileName = "C:\SomeFile\File.xlsx"
Set objWorkbook = objExcel.Workbooks.Open(strFileName)
Set colWorksheets = objWorkbook.Worksheets
For Each objWorksheet in colWorksheets
Set objRange = objWorksheet.UsedRange
strWorksheetName = objWorksheet.Name & "!" & objRange.Address(False, False)
objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"Vulnerability", strFileName, True, strWorksheetName
Does anyone know a faster or more effecient way to get this done?
(Excel and Access 2007)