Import Spreadsheet with variable name 2

[0212]

Hi, all! I have a form with three command buttons. The second two preview reports. The first will automatically import a spreadsheet to Access and append to a table. My problem is how do I make the file name in the TransferSpreadsheet Method variable? I would like the system to request a file name or allow browsing to the specific file and then insert the file name in "stFileName" (See below). Thanks for your help!


Option Compare Database

Private Sub Command3_Click()
Dim stFileName As New Worksheet
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"Assignment Check", "stFileName", True
End Sub

 

[HaworthBantam]

Have a look at the FileDialogue box in help.

I don't have any code handy, but using this your user(s) should be able to browse to the file (whatever it's name) to be imported. You can then determine the name of the file selected, and import the data.

Hope this helps.

 

[HaworthBantam]

Opps, I meant "FileDialog".

 

[0212]

Thanks, HayworthBantam! However, I used the following code from Help and received the following error message:
"Compile Error: User-defined type not defined" Any ideas? This refers to "Dim dlgOpen As FileDialog".

Thanks

Private Sub Command3_Click()
Dim dlgOpen As FileDialog

Set dlgOpen = Application.FileDialog( _
FileDialogType:=msoFileDialogOpen)

With dlgOpen
.AllowMultiSelect = False
.Show
End With

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"Assignment Check", "strSaveFileName", True
End Function

 

[HaworthBantam]

Ok, this should be pretty straight forward, you'll need to set a reference to the "Microsoft Office 11.0 Object Library".

Choose "Tools" from the menu bar and then "References". A References dialog box will appear. Scroll down the list until you come across the library above and put a check in the box on its left.

Click "OK", save it and compile the project, and you should (fingers crossed) find that it will work.

 

[0212]

Thanks, again, HaworthBantam! However, now I get the follwoing error message in the following Line:

"The microsoft Jet Database Engine could not find object 'C:\...dlgOpn.XLS' (Runtime error '3011')"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"Assignment Check", "dlgOpen", True

Thanks for any help you can give!

 

[PWise]

fname=dlgOpen.SelectedItems(1)

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"Assignment Check", fname,True

 

[0212]

Thank you, pwise!!!!! It works. I really appreciate it!!