Here is some code I have for importing an excel binary into my DB as a table. It is supposed to allow the user to choose the file location to import from. I need it to replace, not append to the existing table of the same name. After the user makes a selection, it temporarily renames the existing table for safety, then imports the file under the correct name. Lastly it deletes the original table that was renamed. The run-around is so that if there is a hang-up I don't end up losing the original.
I want to filter the File Dialog box so that the user can only choose files of the correct type. The line that is supposed to do that hangs up though. If I comment it out, everything else runs smoothly. Here's what I have:
Code:
Option Explicit
Public Sub Import()
Dim dlgSlct As FileDialog
Dim strInputFileName As String
Set dlgSlct = Application.FileDialog(msoFileDialogFilePicker)
With dlgSlct
.InitialFileName = CurrentProject.Path
.InitialView = msoFileDialogViewDetails
.Filters = "Excel Files (*.xlsb)"
.AllowMultiSelect = False
.Title = "Choose a file to import"
End With
dlgSlct.Show
If dlgSlct.SelectedItems.Count > 0 Then
strInputFileName = dlgSlct.SelectedItems(1)
DoCmd.Rename "Original Table1", acTable, "Table1"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Table1", _
strInputFileName, True, "Table1"
DoCmd.DeleteObject acTable, "Original Table1"
MsgBox "Import complete"
Else
MsgBox "Data was not imported"
End If
End Sub
The module is called through a command button on a form which is coded with the following:
Code:
Private Sub Command33_Click()
On Error GoTo Command33_Click_Err
Run "Import"
Command33_Click_Exit:
Exit Sub
Command33_Click_Err:
MsgBox Error$
Resume Command33_Click_Exit
End Sub
Any thoughts on this?