[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel - Append Multiple Worksheets into One and Export to Access

Posted on 2012-08-31
13
Medium Priority
?
253 Views
Last Modified: 2013-08-06
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).
0
Comment
Question by:ahs_expert
12 Comments
 
LVL 40

Expert Comment

by:als315
ID: 38354640
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?
0
 

Author Comment

by:ahs_expert
ID: 38356215
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.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38356417
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

0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:ahs_expert
ID: 38356447
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?
0
 
LVL 29

Accepted Solution

by:
IrogSinta earned 1400 total points
ID: 38356454
This code will import all 30 sheets into 1 table.  Here's what it would look like for Jun.  You just need to change the Filename and, if you want, the name of the table to import into.
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:="Jun " & i & "!"
    Next
End Sub

Open in new window

0
 

Author Comment

by:ahs_expert
ID: 38356526
Range:="Jun " & i & "!" - what does this mean? Can you please elaborate on it
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38356549
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.
0
 

Author Comment

by:ahs_expert
ID: 38356584
Thanks for the elaboration. What about Spreadsheet Type and Table Name?
0
 

Author Comment

by:ahs_expert
ID: 38356587
Also once imported, do the macros run faster in access versus excel?
0
 

Author Comment

by:ahs_expert
ID: 38356590
Lastly once it imports all the sheets can I get it to auto append them all into one table?
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38356593
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.
0
 
LVL 24

Expert Comment

by:Tracy
ID: 39385210
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

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.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

834 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