Populating A Combo Box with Excel Worksheet Names

Friday

Friday

Registered User.
Local time
Today, 10:11
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:
Hi. Have you tried stepping through the code to make sure you're actually getting the sheet name?
 
theDBguy said:
Hi. Have you tried stepping through the code to make sure you're actually getting the sheet name?
Click to expand...

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: 545
Friday said:
select the worksheet from a combo box and then import it into my DB
Click to expand...
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?
 
Micron said:
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?
Click to expand...
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:
Friday said:
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
Click to expand...
Code works fine for me? though I did need to change my Row Source Type to "Value List" ?
 
Last edited by a moderator:
Gasman said:
Code works fine for me? though I did need to change my Row Source Type to "Value List" ?
Click to expand...
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
 
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
 
Gasman said:
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.
Click to expand...

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.
 
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
 
Friday said:
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.
Click to expand...
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. :)
 
arnelgp said:
you can hardcode it, just make sure your ColumnCount = 2 and that Row Source Type = Value List.
Click to expand...

Thanks. It's been a while. How in the h*ll did I forget about ColumnCount?
 
You must log in or register to reply here.

Similar threads

D
Calendar displaying autonumber from a combo box..
Replies
5
Views
351
Pat Hartman
P
D
Solved Populate Form & Associated Table with Values from another Table
Replies
5
Views
640
Pat Hartman
P
E
Search Button Not Finding Record and populating the text boxes on form.
Replies
13
Views
1,145
Pat Hartman
P
M
Populate Sub Form with similar to last main form record data
Replies
6
Views
1,004
MHART08
M
S
Speeding up the loading of multiple cascading combo boxes
2
Replies
39
Views
1,574
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom