Access2002: Transfer Spreadsheet - Import without specific filename

[Wozza]

I am hoping the above is possible, but I don't know how to achieve this.
I am quite fluent in Access generally, but not in VBA.
I have created a macro, to import an Excel spreadsheet from a specific location and with a specific filename, converted this macro to a module, but cannot work out how to edit this so that a Dialogue Box appears, for me to then choose which Excel file to import.
Hope I haven't garbled too much....
Warren

 

[JGillespie]

in your module code you should see an entry like:

DoCmd.TransferSpreadsheet

one of the parameters of this method is FileName - the path and file you want to import - just pass the correct path to the file you wish to import

Jamie Gillespie
j-gillespie@s-cheshire.ac.uk

 

[SkipVought]

Warren,

There is the GetOpenFilename method in Excel Object Library -- displays a window to SELECT the file from the directory tree.



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Molby]

You can set the filename to be the result of an inputbox, which means you can type whatever spreadsheet name you want, and it will open the corresponding file.

Something like:

yourfilename$ = Inputbox("Please enter file name&quot

yourfilename$ then becomes the path as Jamie says.

 

[Wozza]

Thanks for your input so far.
A couple of things;
The 'InputBox' appears, just as you described, but upon entering the file location and filename the following error appears;
The Microsoft Jet database engine could not find the object '\\{details path to my own personal network drive}\0.XLS'. Make sure the object exists abd that you spell its name and path correctly.

Within Access, Tools & Options > General, this is my default database folder. I have tried removing this reference, but it keeps reappearing.
Can I create a workbook in this location to get around this?


Also, rather than an 'InputBox,' is it possible to use something where you can browse?

 

[SkipVought]

Helloooooooooo!

GetOpenFilename as suggested

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[JGillespie]

need to look at using common open dialog.

see help files for info

Jamie Gillespie
j-gillespie@s-cheshire.ac.uk