VBA Functions for Retrieving and Displaying Excel Workbooks and Worksheets
One feature I use a lot is showing the user available workbooks and worksheets. The idea is to display a list of open workbooks. When a workbook is selected, display available worksheets.
Depending upon the need, the user might select a Workbook and the MS Access application writes data to the workbook, creating a new sheet. Or the user might view the sheets in a workbook and write the data to that specific sheet. Or perhaps, read the data from selected workbooks or sheets.
You get the picture.
To that end, here are two functions and some ancillary code to help you display workbooks or sheets as needed.
Note: these are easily modified to run in Excel natively, of course.
GetOpenWB Function
Simple... returns an array of open workbooks.
Function GetOpenWB()
Dim xlApp As Excel.Application
Dim strWBList As String
strWBList = ""
'it is going to check for open instances of Excel. If not, the array will be blank.
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err.Number = 0 Then
Dim xlWB As Excel.Workbook
For Each xlWB In xlApp.Workbooks
If Len(strWBList) > 0 Then
strWBList = strWBList & ","
End If
strWBList = strWBList & xlWB.Name
Next xlWB
Set xlApp = Nothing
Set xlWB = Nothing
End If
aryWB = Split(strWBList, ",")
GetOpenWB = aryWB
End Function
GetXLSheets Function
Pass an Excel Workbook name to this function and get a list of Sheets
Function GetXLSheets(inFilen, Optional isvisible As Boolean = False)
'returns the name of sheets contained in an Excel file as an array
Dim strSheets As String
Dim xl As Excel.Application, wb As Excel.Workbook
' i could probably check for an existing Excel application and use it - but... you get the idea
Set xl = New Excel.Application
xl.Visible = isvisible
Set wb = xl.Workbooks.Open(inFilen, False)
strSheets = ""
For x = 1 To wb.Sheets.Count
If Len(strSheets) > 0 Then
strSheets = strSheets & ","
End If
strSheets = strSheets & wb.Sheets(x).Name
Next
wb.Close
xl.Quit
Set wb = Nothing
Set xl = Nothing
If Len(strSheets) = 0 Then
' I could have an empty array on return but used an array with the first element as NONE to indicate it was empty. However, a single
' sheet named NONE could mess this up. 😉
aryRet = Split("NONE", ",")
Else
aryRet = Split(strSheets, ",")
End If
GetXLSheets = aryRet
End Function
Okay, that's the two function. Below is some code on how you might use them
Fill listbox with open workbooks
With a listbox named, lstOpenWorkbooks
Sub BuildWBList()
'clear the list
Me.lstOpenWorkbooks.RowSource = ""
'builds the workbook list
aryWB = GetOpenWB()
If UBound(aryWB) >= 0 Then
For x = 0 To UBound(aryWB)
Me.lstOpenWorkbooks.AddItem aryWB(x)
Next
End If
End Sub
Fill ListBox with the sheets
With a listbox named, lstWBSheets
Sub BuildWSList()
'using a selected workbook from the above list
If Not IsNull(me.lstOpenWorkbooks.Column(0)) Then
strWB = me.lstOpenWorkbook.Column(0)
aryWS = GetXLSheets(strWB,False)
Me.lstOpenWBSheets.RowSource = ""
If UBound(aryWS) >= 0 Then
For x = 0 To UBound(aryWS)
Me.lstwbSheets.AddItem aryWS(x)
Next
End If
End Sub
Where you take this is up to you. Just a couple simple but effective utility functions.
