User-supplied filename for TransferSpreadsheet option

[Melanie O]

What is the code for a user to specify the path and filename that will be
imported into Access from an Excel spreadsheet using the TransferSpreadsheet
method?

Example:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_test1",
strLocation, False, "A1:B20"

I would like the strLocation variable to equal the value of the text box on
a form that the user enters (ideally a path name: "C:\Survey.xls").

Thanks,
Melanie

 

[Douglas J. Steele]

What you've got is exactly what you need. Is your question about how to
allow the user to specify the path and filename (i.e.: how to populate
strLocation)?

Take a look at http://www.mvps.org/access/api/api0001.htm at "The Access
Web" for complete code to invoke the standard Windows File Open dialog.

 

[visdev1]

Is this what you want?

strLocation = Me.Text0.Value

 

[Melanie O]

Yes, that is what I was looking for. For some reason, it didn't work when I
tried it. Thanks for the help!

Melanie

 

[Dorci]

Douglas,
I checked out the web site you recommended, and I think I'm ALMOST there!
The Open File dialog box opens, allowing me to navigate and select the file I
want. Then nothing happens. How do I get my code to USE the file I
selected? I am attempting to import the selected file to an existing table
called "xTemp" (for now). Thanks in advance for your help!

Here is the code:
DoCmd.TransferSpreadsheet acImport, , "xTemp", GetFile(), True

Here is the GetFile function that I copied from the site:
Function GetFile()
Dim strFilter As String
Dim strInputFileName As String
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
End Function

 

[Douglas J. Steele]

Something like:

strLocation = GetFile()

If Len(strLocation) > 0 Then
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, "tbl_test1", _
strLocation, False, "A1:B20"
End If

 

[Dorci]

Got it! Thanks!

Something like:

strLocation = GetFile()

If Len(strLocation) > 0 Then
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, "tbl_test1", _
strLocation, False, "A1:B20"
End If