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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 401
  • Last Modified:

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
0
mickeyshelley1
Asked:
mickeyshelley1
  • 2
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
0
 
Rey Obrero (Capricorn1)Commented:
0
 
mickeyshelley1Author Commented:
Worked great, saved me a headache, my police officers cause me enough headaches cap..lol
0
 
Rey Obrero (Capricorn1)Commented:
glad to help!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now