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!"
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!"
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.
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 ?
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.
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 tableIt automatically opens the excel file, how can we do it without opening the excel file ?
sorry this is like the code
Yes we have path and file variable, sh is the sheet name.Code:DoCmd.TransferSpreadsheet acImport, , "table", pathvariable + file_variable, True, "sh!"
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.Why does it matter if the excel file is opened in order to copy the data to an access table
I feel like the program or code can't control (like close it again) the one that opens while loading.
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.
could I see your code ?
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.
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.
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.
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 ?
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......