IMPORT Excel Sheet into Access allowing user to select file 3

[kculpepper77]

Well, the question is in the following line:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel2000, "1", "V:\PC Fax\Novemberbilling2002.xls", True

I need to omit the path V:\PC Fax\Novemberbilling2002.xls and allow user to browse the file in the API window.
Is it possible to do?

Thanks

 

[Tomadams]

You can use a common dialog control to browse and get the file name then use the following code.

With comdlg1
.Filter = "All Files (*.*)|*.*"
.ShowOpen
End With

If Not IsNull(comdlg1.FileName) Or comdlg1.FileName <> &quot;&quot; Then
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3, &quot;INPUTTABLENAME&quot;, comdlg1.FileName
End If

 

[mystuff]

Hi,

When I try to use this code, I get this error: "424 Object Required."

Any idea why?

 

[RockyD]

Try the below...then you can use either "FileName" in your transfer spreadsheet method. Below I am searching for a text file, but you can change the .txt to .xls to get the same results. Good Luck!


Public ProsDataFile As String, fileerror As Boolean, FileName As String, FileGrab As String
Public varFile As Variant

Public Function GetFile()
Dim fDialog As Office.FileDialog

Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
'Do not allow user to make multiple selections
.AllowMultiSelect = False
'Set the title of the dialog box
.Title = "Please select Sales file to import."
'Clear out the current filters, and add our own.
.Filters.Clear
.Filters.Add "Text", "*.txt"
'show the dialog box. If the .show method returns "true", the user picked
'at least one file. If the .show method is "false", the user clicked Cancel.
If .Show = True Then
For Each varFile In .SelectedItems
'This pulls out the file name from the path string
Dim ReversedString As String, FirstFind As Integer
ReversedString = StrReverse(varFile)
FirstFind = InStr(ReversedString, "\") - 1
FileName = StrReverse(Mid(ReversedString, 1, FirstFind))
Next
Else
MsgBox ("You either hit Cancel or did not select a file. Please try again.")
End If
End With
End Function