Dialog Box to Select Import File

[Guest]

I'm using the TransferSpreadsheet method to import an Excel sheet into Access.
DoCmd.TransferSpreadsheet acImport, 5, "TempDHU", "\\Path\Master.xls", -1

Instead of naming a specific Excel workbook to import (Master.xls), is there
a way to get a dialog box (like the Import File dialog) so the user can
select the folder and file to import?
Thanks.

 

[Guest]

Hi,

for a nice example check the webpage www.mvps.org and especially
http://www.mvps.org/access/api/api0001.htm there you will find a nice
example which works in Access 95 and later versions.

Regards,
Bernd

 

[Guest]

Wow! Thanks for the help but that's way over MY head.
Any suggestions on how I learn to read that stuff?
I tried some cut and paste and some changes but couldn't get anything to work.
Thanks anyway.

 

[Guest]

Hi,

If you know how to do it, it is quite simple.

1. copy the whole code with the blue background - without amendments - into
an own module.

2. Add the following lines to a Function in another module

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)

And to import the file use the DoCmd.TransferSpreadsheet command. In your
example it would be
DoCmd.TransferSpreadsheet acImport, 5, "TempDHU", strInputFileName, -1

(Instead of the '-1' I prefer 'True')


HTH
Bernd

 

[Guest]

You also might want to try:

DoCmd.RunCommand acCmdImport