Data not importing from .xlsx file (1 Viewer)

Access_Help

Registered User.
Local time
Today, 03:46
Joined
Feb 12, 2005
Messages
136
I have a form to upload data from a XLSX file to an access table. When I click upload, I am getting a blank record, the data does not seem to copy over:


File Selector:

Code:
Private Sub Command9_Click()
'Requires reference to Microsoft Office 12.0 Object Library.
 
   Dim fDialog As Office.FileDialog
   Dim varFile As Variant
 
   'Clear File Path contents.
   Me.FilePath = ""
 
   '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 the Student.xlsx file"
 
      'Clear out the current filters, and add our own.
      .Filters.Clear
      .Filters.Add "Excel Spreadsheets", "*.xls, *.xlsx*"
 
      '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 the list box.
         Me.FilePath.Value = .SelectedItems.Item(1)
        
      Else
         MsgBox "You did not pick a file."
      End If
   End With
End Sub


Upload Button

Code:
Private Sub cmdImportExcel_Click()

'the path to the excel workbook
Dim strExcelPath As String

strExcelPath = FilePath
'import data from excel
Call DoCmd.TransferSpreadsheet(acImport, acSpreadsheetTypeExcel12, "Students", strExcelPath, True, "A1:L9000")

On Erro GoTo ErrorHanMsg1
MsgBox "Import Successful", vbInformation

Exit Sub
'This comes before End Sub or End Function Statement
ErrorHanMsg1:
MsgBox "Import Successful", vbInformation


End Sub

Not sure where I am going on wrong?
 

Minty

AWF VIP
Local time
Today, 10:46
Joined
Jul 26, 2013
Messages
10,353
Shouldn't this
strExcelPath = FilePath

Be
strExcelPath = Me.FilePath
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:46
Joined
May 7, 2009
Messages
19,169
will this make a difference by changing:

If .Show = True Then
'Loop through each file selected and add it to the list box.
Me.FilePath.Value = .SelectedItems.Item(1)


to:

If .Show = True Then
'Loop through each file selected and add it to the list box.
Me.FilePath.Value = .SelectedItems(1)
 

Users who are viewing this thread

Top Bottom