SUMMARY: Tutorial to show how to use Microsoft Access VBA to retrieve and display open Workbooks and their Worksheets.
I create a lot of dashboards, reporting systems, and document automation projects built extensively using Visual-Basic for Applications.
Often, the infrastructure looks like:
- Microsoft Access as a front-end application.
- SQL Server as the back-end data repository.
- Microsoft Excel as a report or data presentation tool for distribution or as a way for users to load data into the database.
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.
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
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.