import excel (1 Viewer)

kolait · Oct 11, 2019

Hi.I just got acquainted with it here. I want to import an excel with a macro that has a different file name. What do i write in the photo?

arnelgp · Oct 11, 2019

you need to do it in VBA so you can build the output path and filename.

theDBguy · Oct 11, 2019

Hi. Welcome to AWF! Where would the name of the file come from? Did you want to prompt the user to enter it? If you want to continue using macros, you could try creating a LocalVar for the filename.

Pat Hartman · Oct 12, 2019

Here's how I do it:
Form Picture:

Code for the ... button opens the Windows file dialog so you can browse to the file you want to import.

Code:

Private Sub cmdBrowse_Click()
    Me.txtPath = fChooseFile()
End Sub

Code for the Import button - I deleted everthing except the basic code you need. My app did some complicated processing because the spreadsheet was not a standard "file" type layout.

Code:

Private Sub cmdImport_Click()

'Open link spreadsheet
    DoCmd.DeleteObject acTable, "TEMP_ImportSpreadsheet"
    DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12, "TEMP_ImportSpreadsheet", Me.txtPath, True

    ''     YOUR CODE to append data from linked spreadsheet to your table.  Mine was complicated so I didn't post it
    ''     you can simply run an append query.
End Sub

This code goes in a standard module NOT the module behind the form. You might need to use it in another situation so it can't go in the form's class module.

Code:

Public Function fChooseFile()
  
   ' Requires reference to Microsoft Office 11.0 Object Library.
 
   Dim fDialog As Office.FileDialog
   Dim varFile As Variant
 
 
   ' 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 one file"
 
      'starting location
      .InitialFileName = CurrentProject.path
      
      ' Clear out the current filters, and add our own.
      .Filters.Clear
''''      .Filters.Add "Excel ", "*.XLSX"
      .Filters.Add "Access Databases", "*.ACCDB, *.MDB"
''''      .Filters.Add "Access Projects", "*.ADP"
      .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
            fChooseFile = varFile
''''            Me.FileList.AddItem varFile
         Next
        
      Else
         MsgBox "You clicked Cancel in the file dialog box."
      End If
   End With

End Function

VERY IMPORTANT To use FSO (the file system object library), you MUST set a reference to:

Microsoft Office 16.0 Object Library

"16" is Office 16. If you have a newer or older version of Office installed, pick whatever number library you see in the list.
To pick the file go to Tools/References and scroll to the library you want.

import excel (1 Viewer)

kolait

Registered User.

Attachments

arnelgp

..forever waiting... waiting for jellybean!

theDBguy

I’m here to help

Pat Hartman

Super Moderator

Attachments

Similar threads

Users who are viewing this thread