asked on

Import multiple pages from Excel file into Access Table

I have an excel file that contains 71 pages, each page contains approx 50-60 rows of data. I need to import all the rows from each page into a single new access table.
Access 2003 and Excel 2003. I would like to do this from a button on an access form. The location of the excel file is C:\Reports\monthly.xls the location of the access database is C:\Database_Reports. The first row will be the header on the first page, each page has a header and is formatted the same

Scott McDaniel (EE MVE )

EE works much better when you provide us with the code that you've done so far, and not when you post your project requirements. All that's missing from this is "I expect this by close of business tomorrow".

Off my soapbox now ...

How do you import them now? Are you using the built-in functionality of Access to do this, or are you using something like TransferSpreadsheet?

Here is one way to do it:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "YourTableName", "Path to your spreadsheet", True, "1!"

If it ALWAYS has 71 tabs:

Dim i As Integer

For i = 1 to 71
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "YourTableName", "Path to your spreadsheet", True, cstr(i) & "!"
Next i

See this link:
http://msdn.microsoft.com/en-us/library/office/ff844793.aspx

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mickeyshelley1

ASKER

Worked great, saved me a headache, my police officers cause me enough headaches cap..lol

Rey Obrero (Capricorn1)

glad to help!