msaccess VBA import file path - must it be physical path?
Hi all, here's a straighforward question.
I've got a form in Access 2000, with a command button, on a Windows machine.
Click the buttton and an Excel spreadsheet is imported.
My question: Do I HAVE to use a physical path? Can I not use a relative path?
When I use no path at all -only the name of the Excel file-, and simply place the Excel file in the same directory as the Access file, Access searches the MyDocuments folder for the Excel file.
Right now the code to import the spreadsheet is:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblMyTable", "C:\data\MyFile.xls", True
Why doesn't this work:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblMyTable", "MyFile.xls", True ?
I've got a form in Access 2000, with a command button, on a Windows machine.
Click the buttton and an Excel spreadsheet is imported.
My question: Do I HAVE to use a physical path? Can I not use a relative path?
When I use no path at all -only the name of the Excel file-, and simply place the Excel file in the same directory as the Access file, Access searches the MyDocuments folder for the Excel file.
Right now the code to import the spreadsheet is:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblMyTable", "C:\data\MyFile.xls", True
Why doesn't this work:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblMyTable", "MyFile.xls", True ?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
jerryb30
debug.print curdir will give default location Jeff mentioned.
ASKER
thanks to both of you for the quick response!
Jeff, when I saw your answer of course I did a "Doh!" forehead slap.
JerryB30, thanks for the code, much appreciated, and it works beautifully. Now I have to look up InStrRev(). That's a new one to me, I've only been exposed to InStr().
I increased the pointage to give Jeff 50 for making me think, and JerryB an extra 50 because he gave me a working code example I hadn't asked for.
Jeff, when I saw your answer of course I did a "Doh!" forehead slap.
JerryB30, thanks for the code, much appreciated, and it works beautifully. Now I have to look up InStrRev(). That's a new one to me, I've only been exposed to InStr().
I increased the pointage to give Jeff 50 for making me think, and JerryB an extra 50 because he gave me a working code example I hadn't asked for.
instrrev:New since Access 2000. Especially useful for extracting a full path when given a path\filename.
I personally change my default location in Tools-Options-General to ".\"
Then I can easily invoke it when doing imports, exports. But being able to find currentdb path dynamically is more flexible.
Thanks.
Welcome back Jeff.
I personally change my default location in Tools-Options-General to ".\"
Then I can easily invoke it when doing imports, exports. But being able to find currentdb path dynamically is more flexible.
Thanks.
Welcome back Jeff.
Thanks Jerry, good to be back! and...Glad it's sorted out Tom. Love these quick and informative ones :o)
J
J
>When I use no path at all -only the name of the Excel file-, and simply place the >Excel file in the same directory as the Access file, Access searches the >MyDocuments folder for the Excel file.
I assume it's using the value from the Tools\Options menu, under the General tab -- the "Default database folder:" setting.
When I'm designing forms where I'm dealing with outside files, I use an unbound textbox (i.e., txtFileLocation) that you can populate via a command button with the GetOpenFile routine attached to it's OnClick event. That code's available at my SECOND favorite Access site:
http://www.mvps.org/access/api/api0001.htm
Then you use:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblMyTable", Me!txtFileLocation, True
Regards,
JN
I assume it's using the value from the Tools\Options menu, under the General tab -- the "Default database folder:" setting.
When I'm designing forms where I'm dealing with outside files, I use an unbound textbox (i.e., txtFileLocation) that you can populate via a command button with the GetOpenFile routine attached to it's OnClick event. That code's available at my SECOND favorite Access site:
http://www.mvps.org/access/api/api0001.htm
Then you use:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblMyTable", Me!txtFileLocation, True
Regards,
JN
ASKER
holy smokes, you guys ... just checked my email and saw all these notifications from EE. Thank you so much not only for your help, but the generous spirit it was given in! Very much appreciated. Always love learning something new. I've been self-taught in VBA, VBScript/ASP, Access and MySQL since an early retirement a few years ago, and there's a real satisfaction when the old grey cells light up in understanding a new concept (rare yet satisfying).
I like that suggestion to make my default location in Tools-Options-General to ".\" They say the sign of true genius is the ability to see the obvious! That had simply never occured to me.
And genaughton, thanks, I've bookmarked that site. The more resources the better, and I'm never ashamed to ask for help.
Thanks again, all.
I like that suggestion to make my default location in Tools-Options-General to ".\" They say the sign of true genius is the ability to see the obvious! That had simply never occured to me.
And genaughton, thanks, I've bookmarked that site. The more resources the better, and I'm never ashamed to ask for help.
Thanks again, all.