• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 488
  • Last Modified:

Import thousands of worksheet columns into Access table

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")
objAccess.OpenCurrentDatabase "C:\SomeFolder\data.accdb"

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
Next

Open in new window

Does anyone know a faster or more effecient way to get this done?

(Excel and Access 2007)
0
thomasd04
Asked:
thomasd04
  • 3
  • 2
1 Solution
 
HainKurtSr. System AnalystCommented:
what about putting all rows into one worksheet with worksheetname in another column
then load all data to db in one shot!
0
 
thomasd04Author Commented:
Hi HainKurt. I'm not quite sure what you are suggestion. Could you clarify a bit more? Right now the spreadsheet is being created by an external application and we are unable to modify how it outputs to Excel. Now that it's been outputted in this format, I need to find a way to get it into a database.
0
 
HainKurtSr. System AnalystCommented:
what I say is, instead of loading each worksheet into db one by one,
create a new excel file with one worksheet
then with vba code, collect all data from worksheets and put into the new excel file (add a new column to store worksheetname)
then load this worksheet into db in one shot...
0
 
thomasd04Author Commented:
ahhhh...okay. That makes sense. Could you help me with the code? I'm not good with VB, it took me forever to write the little code I posted above.
0
 
HainKurtSr. System AnalystCommented:
like this


strFileName = "C:\SomeFile\File.xlsx"
strTargetFileName = "C:\SomeFile\output.xlsx"

Set objWorkbook = objExcel.Workbooks.Open(strFileName)
Set objWorkbookNew = objExcel.Workbooks.Open(strTargetFileName)
Set colWorksheets = objWorkbook.Worksheets

For Each objWorksheet in colWorksheets 
    Set objRange = objWorksheet.UsedRange 
    strWorksheetName = objWorksheet.Name & "!" & objRange.Address(False, False) 
     ... copy data into objWorkbookNew, with one worksheet with name say data, with e3xtra column = strWorksheetName ...
Next 

objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
        "Vulnerability", strTargetFileName, True, "data"

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now