Adding worksheets to a Listbox

[Aussiebear]

Some time ago a member asked (and for what ever reason did not receive a reply), if it was possible to add the sheet names of workbook to a listbox. According to the member, the workbook although selected wasn't opened and had tried to use the following :


Set Wkb = Workbooks.Open(fileName:=mypath)
For Each ws In Wkb.Worksheets
SheetList.Add ws.name & ".xls"
Next
n = SheetList.Count
For i = 1 To n
UserControl.List1.AddItem SheetList(i)
Next i

The code appears to be in a non logical order and I've never seen the use of "UserControl" before. How should it be written?

[Paul_Hossler]

Possibly 'UserControl' is the name of a UserForm??

[Aussiebear]

Maybe....!

[Aflatoon]

Or the codename of a worksheet?

[Aussiebear]

Does the order of the code strike you as normal?

[georgiboy]

I don't see why not:

Private Sub UserForm_Initialize()
Dim SheetList As New Collection
Dim Wkb As Workbook
Dim ws As Worksheet
Dim n As Long
Dim i As Long

Set Wkb = Workbooks.Open(Filename:="C:\Users\jbloggs\Desktop\TEST\test.xlsx")

For Each ws In Wkb.Worksheets ' loop through the sheets of specified workbook
SheetList.Add ws.Name & ".xls" ' add each sheet name from the opened workboook to a collection
Next

n = SheetList.Count ' count of items in the filled collection

For i = 1 To n ' loop through the collection and add the items one by one to the listbox
UserControl.List1.AddItem SheetList(i)
Next i
End Sub

[Aflatoon]

I don't see the point of two loops but it's definitely not the strangest code I've ever seen. :)

[georgiboy]

I don't see the point of two loops but it's definitely not the strangest code I've ever seen. :)

I agree, the code could be better in that respect.

[Aussiebear]

Can I see your alternative then please?

[georgiboy]

Just one loop:

Private Sub UserForm_Initialize()
Dim wb As Workbook, ws As Worksheet

Set wb = Workbooks.Open(Filename:="C:\Users\jbloggs\Desktop\TEST\test.xlsx")

For Each ws In wb.Worksheets ' loop through the sheets of specified workbook
UserControl.List1.AddItem ws.Name & ".xls" ' add each sheet name from the opened workboook to the listbox
Next
End Sub

[Aflatoon]

Or if you don't want to open the workbook, something like this will get an array of the sheet names:


Function ListSheetsInFile(ByVal strFile As String) As String()
Dim xlConn As Object 'ADODB.Connection
Dim xlSheets As Object 'ADODB.Recordset
Dim astrSheets() As String
Dim strType As String
Dim strSheet As String
Dim lngSheetCounter As Long
On Error GoTo err_handler
Select Case LCase$(Mid$(strFile, InStrRev(strFile, ".") + 1))
Case "xlsx"
strType = "Excel 12.0 Xml"
Case "xlsm"
strType = "Excel 12.0 Macro"
Case "xlsb"
strType = "Excel 12.0"
Case "xls"
strType = "Excel 8.0"
End Select
'connect to the file
Set xlConn = CreateObject("ADODB.Connection")
With xlConn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Properties("Extended Properties") = strType & ";IMEX=1"
.Open strFile
End With
Set xlSheets = xlConn.OpenSchema(20) '20=adSchemaTables
With xlSheets
Do While Not .EOF
ReDim Preserve astrSheets(lngSheetCounter)
strSheet = .Fields("TABLE_NAME").Value
astrSheets(lngSheetCounter) = Mid$(strSheet, 2, InStr(1, strSheet, "$") - 2)
lngSheetCounter = lngSheetCounter + 1
.MoveNext
Loop
End With
clean_up:
On Error Resume Next
ListSheetsInFile = astrSheets()
xlSheets.Close
xlConn.Close
Exit Function
err_handler:
MsgBox err.Number & ": " & err.Description
Resume clean_up
End Function


and you can then assign that to the list property of the control.

[georgiboy]

Nice, added to my code bank :yes

[Paul_Hossler]

Does the order of the code strike you as normal?


Without more information it's hard to say

I'd combine the loops if possible, but SheetList might be a Collection and IF List1 is a ListBox, I don't think you can AddItem a worksheet



Set Wkb = Workbooks.Open(fileName:=mypath)
For Each ws In Wkb.Worksheets
SheetList.Add ws.name & ".xls"
UserControl.List1.AddItem ws
Next

[Aussiebear]

Thank you one and all for an interesting conversation

[GTO]

Late to the party, but I would have thought of Application.UserControl (which it is not of course).