Results 1 to 6 of 6
  1. #1
    Sck is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    82

    Import from mult sheets in Excel to mult tables in Access

    I have an Excel file with 8 different sheets. Each sheet is related to a specific table in the database. I need to bring the data in via VBA. I made saved imports for each sheet thinking I would be able to use them (like you can with the TransferText) but I do not see a way to call the import name in the TransferSpreadsheet method.

    The code needs to be set up so the user can select the Excel file then the 8 imports will run. The amount of data on each sheet will vary every time so I don't think named ranges would be a practical way of handling it. Just for a bit more background, this process sets up a new backend database, links to the new db, then the spreadsheet import will bring in user developed startup data (ie user table, labor codes table,....)

    Am I just missing something in the TransferSpreadsheet method?




    In Summary: I need to provide the Excel file (path and name) then run an import for each of the 8 sheets into 8 different tables.

    Any help is greatly appreciated.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why is user entering 'startup' data into a spreadsheet and not into the new database via forms?

    Run TransferSpreadsheet 8 times. Can specify the sheet name but must include a range, like:
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tablename", "path\filename.xlsx", , "Sheet1!A1:Z2000"

    Would think named ranges would be ideal for this, assuming you built workbook for this purpose and therefore know the range names, and users add rows and columns within the range.

    Just use the range name in place of "Sheet1!A1:Z2000".
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Sck is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    82
    The users work with the data in Excel (export from cost system is excel format) so it is easier to just upload directly. I got it working with the Transferspreadsheet

    Thanks

  4. #4
    keiths22 is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2012
    Posts
    1
    hi, i'm new to this forum....

    would you mind sharing how you were able to get your issue working utilizing the Transferspreadsheet option? to clarify, were you able to import an excel file allowing the user to browse for the excel file that needs to be imported?...that is what i'm attempting to do

    thanks

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Sck is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    82
    Quote Originally Posted by keiths22 View Post
    hi, i'm new to this forum....

    would you mind sharing how you were able to get your issue working utilizing the Transferspreadsheet option? to clarify, were you able to import an excel file allowing the user to browse for the excel file that needs to be imported?...that is what i'm attempting to do

    thanks
    I added a call to ahtCommonFileOpenSave to get the file location and name instead of hard coding name and location. A Google search on ahtCommonFileOpenSave will show you several examples of how to use it.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 08-25-2012, 06:11 PM
  2. Replies: 7
    Last Post: 08-24-2012, 04:08 PM
  3. Send mult records to word via VBA
    By claven123 in forum Programming
    Replies: 7
    Last Post: 01-06-2011, 10:52 PM
  4. Import excel sheets to access.
    By calexandru in forum Import/Export Data
    Replies: 0
    Last Post: 08-19-2009, 09:44 AM
  5. Replies: 0
    Last Post: 04-29-2009, 04:27 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums