Import Excel file using Dialog Box

[k1ngcharles]

How can I import an Excel file on a daily basis that may have a different name? I want to use a dialog box so I can select the file.

The format of the file will be the same, but the name changes on a daily basis.

 

[The_Doc_Man]

Do this in two steps.

Step one is to automatically import a spreadsheet with a fixed name. (Do this until you get it right.) Search this forum for topics such as "Importing a Spreadsheet" and "Excel Object" - the latter being a way to import spreadsheets via VBA so you can control how you do the actual import at a fairly low level.

Step two requires you to search the forum for topics such as "Common Dialog Control" and "OpenFile" dialog box. This will allow you to set up a file picker using the standard "Open" dialog box that lets you browse folders and select a file. There is a little bit of VBA staring you in the face, but not much.

 

[cchaalan]

DialogBox Import

Hi ,
I created a Button w/a click event w/VB code to open a dialog box to choose a file to import into an Access Database. It works on the original Access database. Now I want to use the same code in another Access Database, & just change the Destination of the table that will be populated with the imported data. I did all that, but it won't work. I am hoping you can take a quick look at my code & tell me if I am missing anything.

Private Sub BtnSelect_Click()

Dim dlg As FileDialog ' I get a Compile Error: User-defined type not defined
Set dlg = Application.FileDialog(msoFileDialogFilePicker)
With dlg
.Title = "Select the Excel file to import"
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Excel Files", "*.xls", 1
.Filters.Add "All Files", "*.*", 2
If .Show = -1 Then
StrFileName = .SelectedItems(1)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "T_FTORN", StrFileName, True
Else
Exit Sub
End If
End With

End Sub

 

[DJkarl]

Hi ,
I created a Button w/a click event w/VB code to open a dialog box to choose a file to import into an Access Database. It works on the original Access database. Now I want to use the same code in another Access Database, & just change the Destination of the table that will be populated with the imported data. I did all that, but it won't work. I am hoping you can take a quick look at my code & tell me if I am missing anything.

Private Sub BtnSelect_Click()

Dim dlg As FileDialog ' I get a Compile Error: User-defined type not defined
Set dlg = Application.FileDialog(msoFileDialogFilePicker)
With dlg
.Title = "Select the Excel file to import"
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Excel Files", "*.xls", 1
.Filters.Add "All Files", "*.*", 2
If .Show = -1 Then
StrFileName = .SelectedItems(1)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "T_FTORN", StrFileName, True
Else
Exit Sub
End If
End With

End Sub

Check your references, I believe the FileDialog object is part of the Office references which are not automatically added when a new database is created.