Select file to Import

[Rod]

Hello,

I have a simple table, tblWholesaler, with Address, Address2, City, State,
Zip, Zip4, Phone, First, Middle, last; all are of text length 50 except:zip
of length 5, Zip4 of length 4, phone of length 10.

I will be receiving Excel files which have no specific naming convention, so
I will need to select the files from a dialog box which I need to appear
asking for the filename. The Excel files will all have the same
structure/column headings. I'm looking to:
1) Select the file name
2) Select the tab name in the file
3) Import all of the data in the selected file into tblWholesaler, but
skipping the column headers
4) Once that file is imported ask for another file to append to
tblWholesaler until canceled.

Thanks Much!

 

[John Nurick]

Hi Rod,

The standard Spreadsheet Import Wizard includes items (1) to (3).

If you want (4) as well you'll need to write VBA code to do it.

(1) is best done with the code at
http://www.mvps.org/access/api/api0001.htm, which uses the standard
File|Open dialog.

For (2) the most elegant approach I know is Jamie Collins's GetWSNames()
function. If you search these newsgroups (e.g. at Google Groups) for
collins getwsnames
you'll find it.

For (3) use DoCmd.TransferSpreadsheet.

And for (4) enclose the whole thing in a loop that terminates when the
user clicks Cancel in the File|Open dialog.