Results 1 to 4 of 4

selecting excel worksheet with multiple sheets from listbox to import into existing access tables

  1. #1
    JoBland is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    5

    Exclamation selecting excel worksheet with multiple sheets from listbox to import into existing access tables

    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

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    6,948
    I have a form, that the user selects,
    it then opens it , graps the sheets,
    user selects the sheets in the multi select list box,
    then click Import.

    Is this what youre wanting?
    Attached Thumbnails Attached Thumbnails import multi sheets of workbook.png  

  3. #3
    JoBland is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    5
    That sounds about right! I need to fix the code that takes the selected excel spreadsheet and imports the separate sheets into separate database tables. Here is what my form looks like.Click image for larger version. 

Name:	form.PNG 
Views:	7 
Size:	10.4 KB 
ID:	35957
    Attached Thumbnails Attached Thumbnails form.PNG  
    Last edited by JoBland; 10-23-2018 at 04:43 PM. Reason: added screen capture of form

  4. #4
    JoBland is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    5
    More specifically I'm having an issue with coding to point to the excel file selected to then apply the import to. So this bit of code (below) doesn't seem to do anything as the DH_Log portion doesn't appear in the MsgBox and so I assume the function can't find it. I'm trying to avoid changing the code too much as it worked in the past. Thanks again!

    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)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 09-13-2017, 05:19 AM
  2. Replies: 2
    Last Post: 02-28-2017, 12:34 AM
  3. Replies: 5
    Last Post: 08-31-2012, 01:59 PM
  4. Replies: 1
    Last Post: 08-25-2012, 05:11 PM
  5. Replies: 0
    Last Post: 04-29-2009, 03:27 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums