Solved

Import thousands of worksheet columns into Access table

Posted on 2011-03-16
5
470 Views
Last Modified: 2012-05-11
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
Comment
Question by:thomasd04
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 35150083
what about putting all rows into one worksheet with worksheetname in another column
then load all data to db in one shot!
0
 
LVL 3

Author Comment

by:thomasd04
ID: 35150795
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
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 35151168
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
 
LVL 3

Author Comment

by:thomasd04
ID: 35151194
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
 
LVL 53

Accepted Solution

by:
Huseyin KAHRAMAN earned 500 total points
ID: 35151205
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

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question