How to load worksheet name into combo box?

[cheer]

As title, i am using MS Access 2003 to write VBA to call a MS Excel and wish to load all worksheet name into combo box. Anyone has the idea how to write this piece of VBA code ?

 

[boblarson]

As title, i am using MS Access 2003 to write VBA to call a MS Excel and wish to load all worksheet name into combo box. Anyone has the idea how to write this piece of VBA code ?

This should give you what you need. Feel free to modify it to fit your needs:

Function LoadWSNames(strFileAndPath As String, CmbBx As ComboBox)
    Dim objXL As Object
    Dim xlWB As Object
    Dim lngCount As Long
    
    Set objXL = CreateObject("Excel.Application")
    Set xlWB = objXL.Workbooks.Open(strFileAndPath)
    
    CmbBx.RowSourceType = "Value List"
    For lngCount = 1 To xlWB.Worksheets.Count
          CmbBx.AddItem xlWB.Worksheets(lngCount).Name
    Next
    
    xlWB.Close False
    objXL.Quit
    Set objXL = Nothing
        
End Function

 

[cheer]

This should give you what you need. Feel free to modify it to fit your needs:

Function LoadWSNames(strFileAndPath As String, CmbBx As ComboBox)
    Dim objXL As Object
    Dim xlWB As Object
    Dim lngCount As Long
    
    Set objXL = CreateObject("Excel.Application")
    Set xlWB = objXL.Workbooks.Open(strFileAndPath)
    
    CmbBx.RowSourceType = "Value List"
    For lngCount = 1 To xlWB.Worksheets.Count
          CmbBx.AddItem xlWB.Worksheets(lngCount).Name
    Next
    
    xlWB.Close False
    objXL.Quit
    Set objXL = Nothing
        
End Function

TQ. exactly what i want.