returning a user selected file path and file name

[fishcakes]

I am trying to write a code to import a table from
another Access database, with a prompt for the user to browse
for the location of the database. My code to import the table works
fine on its own, if I include the path and filename:

Public Sub importTemperature()

DoCmd.DeleteObject acTable, "Loggers_newrecs"
'to delete the table that was last imported into the database

DoCmd.TransferDatabase acImport, "Microsoft Access", _
"D:\ThermoSuite.mdb", acTable, "Loggers", "Loggers_newrecs"
'imports the table "Loggers" from the database "ThermoSuite.mdb" on the
"D:\" drive

End Sub

How can I modify this to include a prompr for the user to select the path
for the database file?
The database name "ThermoSuite" will always be the same, but the location
will change
and we may need to browse through the network to find it.

I tried using GetOpenFile (after loading the code from
http://www.mvps.org/access/api/api0001.htm into a separate
module), but I can't work out how to modify it to return the name of the
path and file name. The script below
returns an error "Could not find file 'path:\info\strFileName' ", presumably
because GetOpenFile is just
opening the file and not actually returning the path information.

Public Sub importTemperature()
DoCmd.DeleteObject acTable, "Loggers_newrecs"
Dim strFileName As String
strFileName = GetOpenFile()
DoCmd.TransferDatabase acImport, "Microsoft Access", _
"strFileName", acTable, "Loggers", "Loggers_newrecs"
End Sub

I also had a look at FileDialog but couldn't figure out how to get the
syntax to work.

Could someone help me with this code? I am new to VBA and would appreciate
any help I can get
Thanks,
Fishcakes.

 

[Douglas J. Steele]

You want the value of what was returned from the API call, not the name of
the variable. Get rid of the quotes around strFileName:

DoCmd.TransferDatabase acImport, "Microsoft Access", _
strFileName, acTable, "Loggers", "Loggers_newrecs"

 

[fishcakes]

thanks Doug,
it works fine now! I really appreciate the help.
Cheers,
Fishcakes.