First a warning that my vba experience is quite limited. I inherited an old access database with a form that uses a button to grab one to multiple excel files and lists them in a ListBox (called FileList). A separate import button was created which runs a function that grabs these excel files listed and imports them into separate tables in the database. Each sheet in the excel file is imported into a separate table, these tables are also already populated with other data, so I'm adding to the tables. The button that selects the excel file work fine and displays the files in the ListBox however the problem is that when I click the import button, the selected file(s) is not recognized and not imported (the function is linked to the button). I'm told this code worked at some point, I just can't figure out why it's not working now. There's also an option to import all sheets, all sheets except the assay sheet and the assay sheet only. Any help is appreciated!
This part of the code works:
Private Sub cmdFileDialog_Click()
' This requires a reference to the Microsoft Office 16.0 Object Library.
Dim fDialog As Office.FileDialog
Dim varFile As Variant
' Clear the list box contents.
Me.FileList.RowSource = ""
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
.InitialFileName = "X:\Geology"
.AllowMultiSelect = True
.Title = "Select One or More Files"
.Filters.Clear
.Filters.Add "Excel Files", "*.xls*"
.Filters.Add "Access Projects", "*.ADP"
.Filters.Add "All Files", "*.*"
If .Show = True Then
' Loop through each file that is selected and then add it to the list box.
For Each varFile In .SelectedItems
Me.FileList.AddItem varFile
Next
Else
MsgBox "You clicked Cancel in the file dialog box."
End If
End With
End Sub
IMPORT BUTTON - DOES NOT WORK:
Public Function Import_DH_Log_Excel()
Dim DH_Log As Object
Set DH_Log = Forms![f_main_menu]!FileList
If Forms![f_main_menu]!Frame79.Value = 3 Then GoTo Assay_only_Load
MsgBox ("Hole selected is " & DH_Log)
'Collar Sheet
On Error GoTo Err_Msg1
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "collar", DH_Log, True, "collar_in!A:BC"
MsgBox ("Collar Data Imported")
GoTo cont1
Err_Msg1:
MsgBox ("Error - Collar data not imported")
Exit Function
cont1:
'Survey Sheet
On Error GoTo Err_Msg2
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Survey", DH_Log, True, "Survey_in!A:Z"
MsgBox ("Survey Data Imported")
GoTo cont2
Err_Msg2:
MsgBox ("Error - Survey data not imported")
cont2:
'Geotech Sheet
On Error GoTo Err_Msg3
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "rock_rqd", DH_Log, True, "Geotech_in!A:Z"
MsgBox ("Geotech Data Imported")
GoTo cont3
Err_Msg3:
MsgBox ("Error - Geotech data not imported")
cont3:
'Lithology Sheet
On Error GoTo Err_Msg4
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "lithology", DH_Log, True, "litho_in!A:Z"
MsgBox ("Lithology Data Imported")
GoTo cont4
Err_Msg4:
MsgBox ("Error - Lithology data not imported")
cont4:
'Specific_Gravity Sheet
On Error GoTo Err_Msg5
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Specific_gravity", DH_Log, True, "sg_in!A:Z"
MsgBox ("Specific_Gravity Data Imported")
GoTo cont5
Err_Msg5:
MsgBox ("Error - Specific_Gravity data not imported")
cont5:
'Mineralization Sheet
On Error GoTo Err_Msg6
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "mineralization", DH_Log, True, "min_in!A:AA"
MsgBox ("Mineralization Data Imported")
GoTo cont6
Err_Msg6:
MsgBox ("Error - Mineralization data not imported")
cont6:
'Alteration Sheet
On Error GoTo Err_Msg7
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "alteration", DH_Log, True, "alt_in!A:Z"
MsgBox ("Alteration Data Imported")
GoTo cont7
Err_Msg7:
MsgBox ("Error - Alteration data not imported")
cont7:
'Structure Sheet
On Error GoTo Err_Msg8
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "structure", DH_Log, True, "struc_in!A:Z"
MsgBox ("Structure Data Imported")
GoTo cont8
Err_Msg8:
MsgBox ("Error - Structure data not imported")
cont8:
'QAQC Sheet
On Error GoTo Err_Msg9
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "QA_QC", DH_Log, True, "qaqc_in!A:E"
MsgBox ("QAQC Data Imported")
GoTo cont9
Err_Msg9:
MsgBox ("Error - QAQC data not imported")
cont9:
'Magsus Sheet
On Error GoTo Err_Msg10
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "magsus", DH_Log, True, "magsus_in!A:Z"
MsgBox ("MagSus Data Imported")
GoTo cont10
Err_Msg10:
MsgBox ("Error - MagSus data not imported")
cont10:
'Assay Sheet
If Forms!f_main_menu!Frame79.Value = 1 Then GoTo NoAssay_Msg
Assay_only_Load:
On Error GoTo Err_Msg11
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "assay", DH_Log, True, "assay_in!A:Z"
MsgBox ("Assay Data Imported")
GoTo cont11
Err_Msg11:
MsgBox ("Error - Assay data not imported")
NoAssay_Msg:
MsgBox ("No Assay Data Imported as per user selection")
cont11:
MsgBox ("DH Log Import Successful")
End Function