Results 1 to 14 of 14
  1. #1
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419

    VBA to load xlsx into ACCESS without opening it

    How to load excel sheet into access table without open excel file ?
    Excel file is supposedly save onto a folder.



    This code below loads the excel file. Thanks !



    Code:
    DoCmd.TransferSpreadsheet acImport, , "table, file, true, "sh!"

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    What is the issue?
    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
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    When you import excel sheet that is saved on a directory/folder into access table. It automatically opens the excel file, how can we do it without opening the excel file ?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Doesn't open for me. But I am not referencing a range. Do you really need to?

    Why do you have a solo quote mark in front of table? Are table and file variables in your code?
    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.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    It automatically opens the excel file, how can we do it without opening the excel file ?
    this is a bit like how do you find out what is in a box without opening the lid. Why does it matter if the excel file is opened in order to copy the data to an access table

  6. #6
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    sorry this is like the code

    Code:
    DoCmd.TransferSpreadsheet acImport, , "table", pathvariable + file_variable, True, "sh!"
    Yes we have path and file variable, sh is the sheet name.

  7. #7
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    Why does it matter if the excel file is opened in order to copy the data to an access table
    Because the program is also opening up the same file and loading is also opening up the file. Two of the same file opens with one as read only.
    I feel like the program or code can't control (like close it again) the one that opens while loading.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    table is name of destination table?

    I did another test with sheet name in the Range reference and "table" as table name. Still works and Excel does not open. Cannot replicate the issue.
    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.

  9. #9
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    could I see your code ?

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    DoCmd.TransferSpreadsheet acImport, , "Test", "C:\Condos.xlsx", True, "Order"

    Does your range name actually have ! character?
    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.

  11. #11
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    Quote Originally Posted by June7 View Post
    DoCmd.TransferSpreadsheet acImport, , "Test", "C:\Condos.xlsx", True, "Order"

    Does your range name actually have ! character?
    No - it doesn't have ! character. Need to put there as that is the sheet name.

    It looks like your code might have an error, Order should be "Order!"
    I tried your code without ! and it doesn't work.

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Sorry, error in earlier post, range name is Order. I thought you were importing a range. So I just tested:

    Docmd.TransferSpreadsheet acImport, , "Test", "C:\Condos.xlsx", , "Sheet2!"

    Again, import successful and workbook does not open.
    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.

  13. #13
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    Ok - the reason why it opens up is because I have the program open the file, make changes, then save it (not close it), then import it with that code.
    Try that in the same module and your import file will open as read only.
    How can we fix or solve this ?

  14. #14
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The first person that opens an Excel spreadsheet has read/write privileges. All of the people that then open the spreadsheet have only read access (while the SS remains open).

    IFAIK, there is no way to change this behavior. Had a user that would walk away for the night and leave a spreadsheet open. Other users needed to edit the spreadsheet, so I had to write an inactivity timer routine. After 30 min of inactivity, the workbook was closed, not saving any changes, allowing other users to open the SS to make changes.


    So just close Excel after saving, then import......

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

Similar Threads

  1. Replies: 3
    Last Post: 01-31-2016, 06:14 PM
  2. Access Macro/VBA to load up a PDF
    By robimi in forum Access
    Replies: 1
    Last Post: 01-23-2015, 11:01 AM
  3. Replies: 7
    Last Post: 09-21-2014, 11:32 AM
  4. Open Excel .xlsx file from Access
    By Bigmix in forum Programming
    Replies: 6
    Last Post: 02-28-2012, 09:55 AM
  5. Replies: 0
    Last Post: 11-17-2009, 02:35 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