Populating A Combo Box with Excel Worksheet Names (1 Viewer)

Friday

Registered User.
Local time
Today, 09:48
Joined
Apr 11, 2003
Messages
542
I stopped programming in Access about 12 years ago. Now I'm retired, working on a genealogy project, and had the idea of combining my DNA matches from several different labs into a database, where I narrow down particular matches based on a chromosome. So I've got the workbook with 22 sheets in it, one for each chromosome. I want to be able to select the worksheet from a combo box and then import it into my DB. The import part was easy. Populating the combo box has proven problematic. I haven't coded for years and years. Someone please look at this code and tell me why it doesn't;t work. It does not throw an error, the combo box just doesn't populate.

Code Tags Added by UG
Please use Code Tags when posting VBA Code
https://www.access-programmers.co.u...e-use-code-tags-when-posting-vba-code.240420/
Code:
Dim xlObj As Object
Dim xlWb As Object
Dim varSheet As Variant
Dim i As Integer
   
    On Error GoTo errTrap

    Set xlObj = CreateObject("Excel.Application")
   
    Set xlWb = xlObj.Workbooks.Open("C:\Users\mulde\FamilyHistory\DNAMatches.xlsx")

    'clear the content of your combobox first
    For i = Me.cboSheets.ListCount - 1 To 0 Step -1
        Me.cboSheets.RemoveItem (i)
    Next
    'now add sheet names
    For Each varSheet In xlWb.Worksheets
        Me.cboSheets.AddItem varSheet.Name
    Next
    'were done
    Me.cboSheets.Requery
    Me.cboSheets = ""

    'housekeeping
    Set varSheet = Nothing
    xlWb.Close False
    Set xlWb = Nothing
    xlObj.Quit
    Set xlObj = Nothing
   
    Exit Sub
errTrap:
    Debug.Print "The error was " & Err.Number & "  " & Err.Description
    ' in a code window press Control+G
   Err.Clear
   On Error Resume Next
 
Last edited by a moderator:

theDBguy

I’m here to help
Staff member
Local time
Today, 02:48
Joined
Oct 29, 2018
Messages
21,357
Hi. Have you tried stepping through the code to make sure you're actually getting the sheet name?
 

Friday

Registered User.
Local time
Today, 09:48
Joined
Apr 11, 2003
Messages
542
Hi. Have you tried stepping through the code to make sure you're actually getting the sheet name?

Good idea. Forgot about that, too. When I do that, I immediately get a message box asking for a macro name/
 

Attachments

  • macro.JPG
    macro.JPG
    24.4 KB · Views: 487

Micron

AWF VIP
Local time
Today, 05:48
Joined
Oct 20, 2018
Messages
3,476
select the worksheet from a combo box and then import it into my DB
I would bet money that the spreadsheet layout is not conducive to a relational database table schema. Spreadsheets are wide and everything is on one row. Database tables are tall, data is split up into related tables.

You left off the beginning and ending lines, which is relevant to your last post. I presume it's a sub because that's what I usually get if you try to launch a sub from within a form module in the vb editor. I would have thought that varSheet should be an object?
 

Friday

Registered User.
Local time
Today, 09:48
Joined
Apr 11, 2003
Messages
542
I would bet money that the spreadsheet layout is not conducive to a relational database table schema. Spreadsheets are wide and everything is on one row. Database tables are tall, data is split up into related tables.

You left off the beginning and ending lines, which is relevant to your last post. I presume it's a sub because that's what I usually get if you try to launch a sub from within a form module in the vb editor. I would have thought that varSheet should be an object?
Sorry.

Code Tags Added by UG
Please use Code Tags when posting VBA Code
https://www.access-programmers.co.u...e-use-code-tags-when-posting-vba-code.240420/
Code:
Private Sub cboSheets_Click()

Dim xlObj As Object
Dim xlWb As Object
Dim varSheet As Object
Dim i As Integer
   
    On Error GoTo errTrap

    Set xlObj = CreateObject("Excel.Application")
   
    Set xlWb = xlObj.Workbooks.Open("C:\Users\mulde\FamilyHistory\DNAMatches.xlsx")

    'clear the content of your combobox first
    For i = Me.cboSheets.ListCount - 1 To 0 Step -1
        Me.cboSheets.RemoveItem (i)
    Next
    'now add sheet names
    For Each varSheet In xlWb.Worksheets
        Me.cboSheets.AddItem varSheet.Name
    Next
    'were done
    Me.cboSheets.Requery
    Me.cboSheets = ""

    'housekeeping
    Set varSheet = Nothing
    xlWb.Close False
    Set xlWb = Nothing
    xlObj.Quit
    Set xlObj = Nothing
   
    Exit Sub
errTrap:
    Debug.Print "The error was " & Err.Number & "  " & Err.Description
    ' in a code window press Control+G
   Err.Clear
   On Error Resume Next



End Sub
 
Last edited by a moderator:

Gasman

Enthusiastic Amateur
Local time
Today, 09:48
Joined
Sep 21, 2011
Messages
14,038
I stopped programming in Access about 12 years ago. Now I'm retired, working on a genealogy project, and had the idea of combining my DNA matches from several different labs into a database, where I narrow down particular matches based on a chromosome. So I've got the workbook with 22 sheets in it, one for each chromosome. I want to be able to select the worksheet from a combo box and then import it into my DB. The import part was easy. Populating the combo box has proven problematic. I haven't coded for years and years. Someone please look at this code and tell me why it doesn't;t work. It does not throw an error, the combo box just doesn't populate.
Code:
Dim xlObj As Object
Dim xlWb As Object
Dim varSheet As Variant
Dim i As Integer
 
    On Error GoTo errTrap

    Set xlObj = CreateObject("Excel.Application")
 
    Set xlWb = xlObj.Workbooks.Open("C:\Users\mulde\FamilyHistory\DNAMatches.xlsx")

    'clear the content of your combobox first
    For i = Me.cboSheets.ListCount - 1 To 0 Step -1
        Me.cboSheets.RemoveItem (i)
    Next
    'now add sheet names
    For Each varSheet In xlWb.Worksheets
        Me.cboSheets.AddItem varSheet.Name
    Next
    'were done
    Me.cboSheets.Requery
    Me.cboSheets = ""

    'housekeeping
    Set varSheet = Nothing
    xlWb.Close False
    Set xlWb = Nothing
    xlObj.Quit
    Set xlObj = Nothing
 
    Exit Sub
errTrap:
    Debug.Print "The error was " & Err.Number & "  " & Err.Description
    ' in a code window press Control+G
   Err.Clear
   On Error Resume Next
Code works fine for me? though I did need to change my Row Source Type to "Value List" ?
 
Last edited by a moderator:

Friday

Registered User.
Local time
Today, 09:48
Joined
Apr 11, 2003
Messages
542
Code works fine for me? though I did need to change my Row Source Type to "Value List" ?
When I run the code, the box is empty and an icon appears below it. If you hover over the icon, a message appears, "Edit List Items". cboBox.jpg
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:48
Joined
Sep 21, 2011
Messages
14,038
Well the combo is empty to start with as you set it to that.?

Here is mine.

Walk through your code with F8 after setting a breakpoint.
1586684503669.png
 

Friday

Registered User.
Local time
Today, 09:48
Joined
Apr 11, 2003
Messages
542
Well the combo is empty to start with as you set it to that.?

Here is mine.

Walk through your code with F8 after setting a breakpoint.

Okay, let's start over. I have it working, to a degree. I split the task into two sections.
1. Populate a combo box with the sheet names from an excel spreadsheet.
2. From a command button, import the selected sheet into Access.

First, I created a combo box and used a value list to display the sheet names and the sheet code names. Then I created a command button that used the TransferSpreadsheet function to import the selected sheet. I used both of the names, the sheet code name is used to identify the sheet. The sheet name is used to name the new table in Access.

Code:
Private Sub Command0_Click()

Dim msg As String
Dim strChr As String
Dim strChr2 As String
Dim strChr3 As String
Dim strSht As String

Dim xlObj As Object
Dim xlWb As Object

Set xlObj = CreateObject("Excel.Application")
Set xlWb = xlObj.Workbooks.Open("C:\Users\mulde\FamilyHistory\DNAMatches.xlsx")

strChr = cboSheet.Value
strChr2 = cboSheet.Column(1)
strChr3 = cboSheet.Value
strSht = strChr2 & "!"

DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentDb().Name, acTable, "Chr13", strChr, True
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, strChr2, "C:\Users\mulde\FamilyHistory\DNAMatches.xlsx", True, strSht

xlWb.Close False
    Set xlWb = Nothing
    xlObj.Quit
    Set xlObj = Nothing


End Sub

This worked fine, and I accomplished my task, importing all 26 sheets in one at a time with the two mouse clicks. Great.

However, a thought occurred to me that I could populate the combo box with an array containing the sheet names. I managed to do that with this code:

Code:
Private Sub cmdArray_Click()

Set xlObj = CreateObject("Excel.Application")
Set xlWb = xlObj.Workbooks.Open("C:\Users\mulde\FamilyHistory\DNAMatches.xlsx")

Dim SNarray, i
    ReDim SNarray(1 To Sheets.Count)
    For i = 1 To Sheets.Count
        SNarray(i) = xlWb.Sheets(i).Name
Debug.Print SNarray(i)
    Next
    
    For i = 1 To Sheets.Count
        Me.cboArray.AddItem (SNarray(i))
    Next
    

End Sub

However, I need both the sheet name and the sheet code name. Obviously, I can go to the workbook, and rename each sheet code name to match the sheet name and be done. I tested this and it works. However, I'm anal when it comes to this stuff. I want a two-column array with both sheet name and sheet code name in it. I cannot figure out the code to do this. Looking for some guidance.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:48
Joined
May 7, 2009
Messages
19,169
you can hardcode it, just make sure your ColumnCount = 2 and that Row Source Type = Value List.
Code:
Private Sub cmdArray_Click()
Dim xlObj As Object
Dim xlWB As Object
Dim xlWS As Object
Dim sSource As String
Dim i As Integer

Set xlObj = CreateObject("Excel.Application")
Set xlWb = xlObj.Workbooks.Open("C:\Users\mulde\FamilyHistory\DNAMatches.xlsx")

i = 1
For Each xlWS In xlWb.worksheets
    strSource = strSource & xlWS.Name & ";Sheet" & i & ";"
    i = i + 1
Next
Me.cboArray.RowSource = strSource
xlWB.Close False
xlObj.Quit
Set xlWB = Nothing
Set xlObj = Nothing
End Sub
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:48
Joined
Sep 21, 2011
Messages
14,038
Okay, let's start over. I have it working, to a degree. I split the task into two sections.
1. Populate a combo box with the sheet names from an excel spreadsheet.
2. From a command button, import the selected sheet into Access.

First, I created a combo box and used a value list to display the sheet names and the sheet code names. Then I created a command button that used the TransferSpreadsheet function to import the selected sheet. I used both of the names, the sheet code name is used to identify the sheet. The sheet name is used to name the new table in Access.

Code:
Private Sub Command0_Click()

Dim msg As String
Dim strChr As String
Dim strChr2 As String
Dim strChr3 As String
Dim strSht As String

Dim xlObj As Object
Dim xlWb As Object

Set xlObj = CreateObject("Excel.Application")
Set xlWb = xlObj.Workbooks.Open("C:\Users\mulde\FamilyHistory\DNAMatches.xlsx")

strChr = cboSheet.Value
strChr2 = cboSheet.Column(1)
strChr3 = cboSheet.Value
strSht = strChr2 & "!"

DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentDb().Name, acTable, "Chr13", strChr, True
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, strChr2, "C:\Users\mulde\FamilyHistory\DNAMatches.xlsx", True, strSht

xlWb.Close False
    Set xlWb = Nothing
    xlObj.Quit
    Set xlObj = Nothing


End Sub

This worked fine, and I accomplished my task, importing all 26 sheets in one at a time with the two mouse clicks. Great.

However, a thought occurred to me that I could populate the combo box with an array containing the sheet names. I managed to do that with this code:

Code:
Private Sub cmdArray_Click()

Set xlObj = CreateObject("Excel.Application")
Set xlWb = xlObj.Workbooks.Open("C:\Users\mulde\FamilyHistory\DNAMatches.xlsx")

Dim SNarray, i
    ReDim SNarray(1 To Sheets.Count)
    For i = 1 To Sheets.Count
        SNarray(i) = xlWb.Sheets(i).Name
Debug.Print SNarray(i)
    Next
   
    For i = 1 To Sheets.Count
        Me.cboArray.AddItem (SNarray(i))
    Next
   

End Sub

However, I need both the sheet name and the sheet code name. Obviously, I can go to the workbook, and rename each sheet code name to match the sheet name and be done. I tested this and it works. However, I'm anal when it comes to this stuff. I want a two-column array with both sheet name and sheet code name in it. I cannot figure out the code to do this. Looking for some guidance.
I do not really see the point of assigning the sheet names to an array and then the items of that array to the combo? :unsure:

This link shows you how to get the codename https://docs.microsoft.com/en-us/office/vba/api/excel.worksheet.codename just build the combo with each codename as well?

Arne has just shown you another way to build the combo values.

I've just had a deja vu about that last statement. :)
 

Friday

Registered User.
Local time
Today, 09:48
Joined
Apr 11, 2003
Messages
542
you can hardcode it, just make sure your ColumnCount = 2 and that Row Source Type = Value List.

Thanks. It's been a while. How in the h*ll did I forget about ColumnCount?
 

Users who are viewing this thread

Top Bottom