Link to home
Start Free TrialLog in
Avatar of ahs_expert
ahs_expert

asked on

Excel - Append Multiple Worksheets into One and Export to Access

I have about 30 worksheets in the workbook (each sheet represents 1 day of revenues). I need to consolidate them all into one and export them to Microsoft Access. Whats the fastest way of doing this without having the file size to be very large (at the moment the workbook is about 150+ MB).
Avatar of als315
als315
Flag of Russian Federation image

It is not a problem to link all sheets to Access DB and with 30 append queries or with one union query or with some strings of VBA code add records to Access table. What is preferrable?
Avatar of ahs_expert
ahs_expert

ASKER

How do I link the sheets? And how do I make 30 append queries? Whats the fastest, most efficient, least demanding method?

All the data is currently stored in Excel and is super annoying because the file size is huugee and super slow. I run macros on it and it crashes. Please advise the best solution based on your suggestions.
If your sheets are numbered such as Day1, Day2, Day3 or Sheet1, Sheet2, Sheet3, you can use a For..Next loop along with the TransferSpreadsheet method for this.

Sub ImportSheets()
    Dim i As Byte
    
    For i = 1 To 30
        DoCmd.TransferSpreadsheet TransferType:=acImport, _
        SpreadsheetType:=10, _
        TableName:="tbl_ImportedFromExcel", _
        FileName:="C:\NameOfFile.xls", _
        HasFieldNames:=True, _
        Range:="Sheet" & i & "!"
    Next
End Sub

Open in new window

They are labelled by month, e.g Jun 1, Jun 2, Jun 3, Jun 4.

What would the code look like for that?

Can I not combine all the worksheets into one and then export or it is better to fetch each one individually from excel through access?
ASKER CERTIFIED SOLUTION
Avatar of IrogSinta
IrogSinta
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Range:="Jun " & i & "!" - what does this mean? Can you please elaborate on it
Range specifies what you want to import.  If you say Range:="Jun 6!", that specific sheet will be the one imported.  "i" is a variable that goes from 1 to to 30.  So with each pass, each sheet from Jun 1 to Jun 30 are imported into the one table.
Thanks for the elaboration. What about Spreadsheet Type and Table Name?
Also once imported, do the macros run faster in access versus excel?
Lastly once it imports all the sheets can I get it to auto append them all into one table?
Table Name is the name you want to give your table in Access.  Spreadsheet Type is the type of spreadsheet.  You can check out the Help file for the TransferSpreadsheet method to find out about all these parameters.

I'm not sure what you mean by the macros running faster.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.