Import and convert Data

[Guest]

I have a button on a form that would allow me to browse and pick a file.
Once I pick the file I have another button that would let me import the file
into the spread sheet but I am get this Error 424 - object required.

Can someone help with this?
Code for Browse
Private Sub cmdBrowse_Click()
'Requires reference to Microsoft Office 11.0 Object Library.
Dim fDialog As Office.FileDialog
Dim varFile As Variant
Dim PicFn As String
'Clear listbox contents.
'Me.FileList.RowSource = ""

'Set up the File Dialog.
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
'Allow user to make multiple selections in dialog box
.AllowMultiSelect = False

'Set the title of the dialog box.
.Title = "Please select an Excel or a Text File"
.InitialView = msoFileDialogViewDetails
.ButtonName = "Select a File"
'.InitialFileName = CurrentProject.Path & "\pictures"
'Clear out the current filters, and add our own.
.Filters.Clear
.Filters.Add "Data File", "*.XLS; *.txt"
.Filters.Add "All Files", "*.*"

'Show the dialog box. If the .Show method returns True, the
'user picked at least one file. If the .Show method returns
'False, the user clicked Cancel.
If .Show = True Then
''Loop through each file selected and add it to our list box.
For Each varFile In .SelectedItems
txtInputFile.Value = Format$(varFile)
Next
Dim ExcelApp As Excel.Application
Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Workbooks.Open txtInputFile.Value
' ExcelApp.Sheets("Sheet1").PrintOut
ExcelApp.Workbooks.Close
Set ExcelApp = Nothing
End If
End With
End Sub
Code for import
'This would import the Current Month Data
Private Sub cmdImport_Click()
On Error GoTo ErrorCheck

varFilename = Me.txtInputFile
If IsNull(varFilename) Then
MsgBox "You must enter an input filename.", vbExclamation, " "
Me.txtInputFile.SetFocus
Exit Sub
End If


DoCmd.TransferText acImportDelim, , "Rate_Table_LIBOR_Swap ",
varFilename, True
Exit Sub

ErrorCheck:
Select Case Err.Number
Case 7874 'Import file not there to delete
Resume Next
Case Else
MsgBox "Error " & Err.Number & " - " & Err.Description
Exit Sub
End Select


'End Sub

Thanks in advance

 

[Guest]

I have a button on a form that would allow me to browse and pick a file.
Once I pick the file I have another button that would let me import the file
into the spread sheet but I am get this Error 424 - object required.

Can someone help with this?
Code for Browse
Private Sub cmdBrowse_Click()
'Requires reference to Microsoft Office 11.0 Object Library.
Dim fDialog As Office.FileDialog
Dim varFile As Variant
Dim PicFn As String
'Clear listbox contents.
'Me.FileList.RowSource = ""

'Set up the File Dialog.
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
'Allow user to make multiple selections in dialog box
.AllowMultiSelect = False

'Set the title of the dialog box.
.Title = "Please select an Excel or a Text File"
.InitialView = msoFileDialogViewDetails
.ButtonName = "Select a File"
'.InitialFileName = CurrentProject.Path & "\pictures"
'Clear out the current filters, and add our own.
.Filters.Clear
.Filters.Add "Data File", "*.XLS; *.txt"
.Filters.Add "All Files", "*.*"

'Show the dialog box. If the .Show method returns True, the
'user picked at least one file. If the .Show method returns
'False, the user clicked Cancel.
If .Show = True Then
''Loop through each file selected and add it to our list box.
For Each varFile In .SelectedItems
txtInputFile.Value = Format$(varFile)
Next
Dim ExcelApp As Excel.Application
Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Workbooks.Open txtInputFile.Value
' ExcelApp.Sheets("Sheet1").PrintOut
ExcelApp.Workbooks.Close
Set ExcelApp = Nothing
End If
End With
End Sub
Code for import
'This would import the Current Month Data
Private Sub cmdImport_Click()
On Error GoTo ErrorCheck

varFilename = Me.txtInputFile
If IsNull(varFilename) Then
MsgBox "You must enter an input filename.", vbExclamation, " "
Me.txtInputFile.SetFocus
Exit Sub
End If


DoCmd.TransferText acImportDelim, , "Rate_Table_LIBOR_Swap ",
varFilename, True
Exit Sub

ErrorCheck:
Select Case Err.Number
Case 7874 'Import file not there to delete
Resume Next
Case Else
MsgBox "Error " & Err.Number & " - " & Err.Description
Exit Sub
End Select


'End Sub

Thanks in advance

 

[Guest]

There is no line I am getting a pop up box

 

[Guest]

It is occuring on a specific line. Do you have error handling in your code?
If not, you should get a error message that includes End and Debug. If you
select Debug, the VBA editor will come up with the line getting the error
highlighted.

If you have error handling that is masking the error location, go to the VBA
Editor, Select Tools, Options, the General tab and select Break on All
Errors. That will override your error handling and you will be able to tell
what line is causing the error.