Topics Search

Import multiple worksheets

Import multiple worksheets
Views: 90
I have a service report in Excel format that is divided into 8 separate worksheets, and I would like to be able to import all of them at the same time into a database I'm developing. Until now I have been doing it manually in the File -> Get External Data -> Import, but this is a very laborious process that has an added risk of operator error. I've seen that it's possible to run a macro that would import worksheets, but in writing the macro Access needs to know the filename ahead of time.

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
Sponsored Links:

More topics

 

Importing data from spreadsheet

Preview
I have multiple workbooks and within that there will be multiple worksheets. Number of workbooks and worksheets in that vary but the columns remain same. all put together there will be around 600K - 650K.

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.
 

How do i import multiple excel spreed sheets into Access?

Preview
All worksheets in Excel file can have a various number of worksheets, but all data in each worksheet has same headers. I need all data to go into a user selected table that already exists in Access.

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.
 

Users import from Excel to Access

Preview
I'm trying to create a macro that will do the following.

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?
 

Combine multiple tables into 1

Preview
I'm asking this on behalf of a friend, so I have not seen the data myself.

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
 

Importing Data From Excel

Preview
I need to import data from several different worksheets within the same Excel workbook into an Access database. I can't figure out how to import more than one sheet at a time. All the sheets have the column headings. 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
 

Importing Data From Excel

Preview
I need to import data from several different worksheets within the same Excel workbook into an Access database. I can't figure out how to import more than one sheet at a time. All the sheets have the column headings.

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.
 

Export Queries to Named Excel Worksheets

Preview
I am trying to export multiple queries into existing worksheets within the same spreadsheet, but instead of the export overwriting within the existing worksheets, it is creating additional worksheets with the same name plus a "1" suffix. I could rename the queries to the worksheet names, but would prefer to leave the query naming conventions and just specify the name of the worksheet that I want to export to

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
 

Importing Excel 2007 spreadsheet into Access 2002

Preview
I have an Access 2002 database which a number of users use to import worksheets from excel. Most of my users are using excel 2003 but a few have migrated to excel 2007 to move beyond the row limitation. The GUI for my db uses the docmd.transferspreadsheet command to import the excel tables. I get an error when trying to import an excel 2007 table. Any comment on modifing the vba code to allow import of the excel 2007 data.
 

DoCmd.TransferSpreadsheet not overwriting worksheet

Preview
The database I inherited has a series of queries that output the final results to a format template, overwriting the data on each worksheet that has the same name as the query. I've added two additional worksheets with the appropriate names, but for some reason the new worksheets are not being overwritten like the other ones in the workbook. Instead, the worksheets are being added at the end with the correct names but appended with "1" at the end as if the script recognizes that the worksheets are there but cannot overwrite them for some reason. The section of the relevant code is as follows...
 

Import multiple txt files and filename?

Preview
I have a question regarding import of multiple Text files.

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).