Import multiple worksheets
With my service report, all of the worksheets are named to correspond to the tables I want to import into, but the filename will not be consistent. Therefore I believe I'll have to do this in VBA. Although I have VBA experience in Excel, I don't know how to do it in Access. Would someone be able to point me in the right direction
Thank you for taking the time to report an issue.
What's wrong... Please write below.
now I am looking to build a VBA in Access 2007 to import all the data into one table at one shot so I can write queries and perform the reporting activities.
What I would like to happen is user run a macro to prompt to select a file from any given file path. After they select the file it should ask them or direct them into choosing what table should data be appended to.
User would then choose/select table and data would start to import. I would like the importing to either be visible or have the user be notifiedwhen import is completed.
I could use the "Run Command" -> "Import", but I'm trying to automate this process to keep the user interaction down.
2. Import the excel file (2 worksheets) into 2 table in access. The worksheets first row is a header row.
This is easy to do using the "Import" command to bring up the Import Wizard, but since I want to stay away from the wizard, I believe that I should use the "Transfer Spreadsheet" command". The problem I run into here, is that I'm required to put the filename and path into the "File Name" property.
I am not sure how to set this up since I want to user to browse for the file.
Should I use VBA or can I do this with macros? Are there better commands to use or a better method to do this?
She has an Excel workbook, with approx. 60 worksheets. Each worksheet is a different cost center; the columns are the same for each worksheet. She needs to combine them all into one spreadsheet.
My initial thoughts were to import the workbook into Access, and then use a Union Query to combine each worksheet. But then I started thinking that isn't there a limit to how many tables you can Union in a Union Query? If so, she would need, what, about 10 Union queries, to get all of the worksheets combined? Is there any other method that could be used to combine these tables in a more expedient way
I can import the first sheet, but when I try to import the second sheet Access tells me there is an error. It doesn't tell me what the error is though, so I can't go about trying to fix it.
If there is a way to do this, I would like to import all the sheets at once.
I am on Access 2010 and the code is as follows:-
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "12 Monthly Day 6 5R", MyPath & "\Day 6 Delivery.xls", False, "5 Region"
I want to export query "12 Monthly Day 6 5R"
into file "Day 6 Delivery.xls"
and overwrite worksheet "5 Region
These files all have different names but will always be found in the same location.
C:\Documents and Settings\computer\Desktop\F FILES
I have little VBA knowledge and am wondering if I can import these files into an existing Access table tbl_Table via a macro?
A number of problems:
1. It does not import directly into the existing table, instead it creates a new table tbl_Table , I'd like to import directly into the existing table without using append or similar.
2. I cannot get it to import ALL files in the folder.
3. I'd like to import the first 7 characters of the filename into a field (KEY).