• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3871
  • Last Modified:

Need VBA code to browse for Excel file to import into Access table

This is my current code.  But instead of this I want the user to be able to browse to their C; driveto find the Excel file to import into the table.

    'Import Excel file into tblTempImport
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblTempImport", "C:\Users\Steve\Desktop\Import Files\ImportFile.xlsx", True

What would I replace this code with?
0
SteveL13
Asked:
SteveL13
1 Solution
 
Helen FeddemaCommented:
Use the FileDialog object, as in this code:

Public Function SelectXLFile() As String
'Requires Office XP (2002) or higher
'Requires a reference to the Microsoft Office Object Library
'Created by Helen Feddema 25-Jan-2011
'Last modified 25-Jan-2011

On Error GoTo ErrorHandler

   Dim fd As Office.FileDialog
   Dim varSelectedItem As Variant
   Dim strFileNameAndPath As String
   
   'Create a FileDialog object as a File Picker dialog box.
   Set fd = Application.FileDialog(msoFileDialogFilePicker)
   
   With fd
      .AllowMultiSelect = False
      .Title = "Browse for Excel File"
      .ButtonName = "Select"
      .Filters.Clear
      .Filters.Add "Documents", "*.xls; *.xlsx; *.xlsm", 1
      .InitialView = msoFileDialogViewDetails
      If .Show = -1 Then
         'Get selected item in the FileDialogSelectedItems collection
         'Have to use collection even if just one item is selected
         For Each varSelectedItem In .SelectedItems
            strFileNameAndPath = CStr(varSelectedItem)
         Next varSelectedItem
      Else
         Debug.Print "User pressed Cancel"
         strFileNameAndPath = ""
      End If
   End With
   
   SelectXLFile = strFileNameAndPath
   
ErrorHandlerExit:
   Set fd = Nothing
   Exit Function

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in SelectXLFile procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Function

Open in new window

0
 
MzLibertyCommented:
Nevermind I figured it out. Thanks!
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now