Listing Excel Workbooks and Sheets from Microsoft Access using VBA

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.

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

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", ",")
  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)
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)
End If

End Sub

Where you take this is up to you. Just a couple simple but effective utility functions.

Posted in Microsoft Access, Programming and tagged , , .

Leave a Reply

Your email address will not be published. Required fields are marked *