Solved

msaccess VBA import file path - must it be physical path?

Posted on 2007-03-29
8
676 Views
Last Modified: 2012-05-05
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 ?
0
Comment
Question by:tom_burkhardt
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 34

Assisted Solution

by:jefftwilley
jefftwilley earned 50 total points
ID: 18818335
>>Access searches the MyDocuments folder for the Excel file.

What makes you say that?

When Access is installed, you are asked what to use for a default file location. This is of course as you've already found, not the same as the "relative" path.

To answer your question directly, yes...you can use the relative path, but you will need to determine what the setting is for your App. OTherwise, use the DB's current directory.
J
0
 
LVL 26

Accepted Solution

by:
jerryb30 earned 300 total points
ID: 18818336
it probably does work, but you expect to see it where  it isn't.
".\MyFile.xls"
will put it in current working path, which may not be database path.

add this to top of code:
Dim strpath As String
strpath = Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\") )
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblMyTable", strpath & "myfile.xls" ,true


0
 
LVL 26

Expert Comment

by:jerryb30
ID: 18818346
debug.print curdir will give default location Jeff mentioned.
0
 

Author Comment

by:tom_burkhardt
ID: 18818533
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.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 26

Expert Comment

by:jerryb30
ID: 18818619
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.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18818712
Thanks Jerry, good to be back! and...Glad it's sorted out Tom. Love these quick and informative ones :o)
J
0
 
LVL 1

Expert Comment

by:genaughton
ID: 18818715
>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
0
 

Author Comment

by:tom_burkhardt
ID: 18820872
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.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Correct ordering 16 44
Aceess 2016 - Variable Data Fields? 12 43
Replace special characters in Access VBA 8 23
Max per month 3 14
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now