Hi Rich,
I don't know of a sample database offhand, though possibly there's one
on Roger Carlson's site
http://www.rogersaccesslibrary.com/
But basically all you need to is
1) create a new module in your database.
2) paste the code from the Access Web (from the line marked Code Start
to the line marked Code End) into the module and save it (call it
something like vbOpenFile.
3) to make it work, put a commandbutton on a form. Name the button
something like cmdOpenFile. Set the button's Click property to [Event
Procedure], and click the adjacent [...] button. This fires up the VBE
editor and inserts the skeleton code:
Private Sub cmdOpenFile_Click()
End Sub
4) Now paste the following (which is copied from the same Access Web
page) into the skeleton:
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)
The statement beginning "strInputFileName" displays the File Open dialog
and puts the path and name of the selected file into that variable. So
the next thing is to use this name to import the data, which needs one
more VBA statement, something like this (but you'll have to adjust the
table name etc.)
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, "MyTable", strInputFileName, True
Thanks John for the response, but I'll be honest with you,
I know very little about VB. I was researching this issue
a couple of days ago and ran accross this link but was
confused as to where to drop the (moodule?) and how to
call on that code in my database. Do you know if there is
a sample database (other than Northwind) that might
contain a similiar process. If I could see it, I could
probably copy it.
-----Original Message-----
Hi Rich,
Use VBA for this rather than a macro.
There's code at
http://www.mvps.org/access/api/api0001.htm to display
the Windows File Open dialog and get the file name; you can then pass
this to DoCmd.TransferSpreadsheet.
I need to automate the import process. I created a macro
and referenced the TransferSpreadsheet. Is there a way I
can pass the file name as a parameter. I want to create a
form button that by pressing will bring up the file browse
dialog box where the user can choose the EXCEL file name
to import. The table in which the file will be imported to
is always the same, and the format of the file is always
the same.
Thanks in advance for any help given!
Rich
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
