Re: Get list of Excel sheets in MS Access

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hi Rob,
I'm missing something.
In my MS Access application I need to read an excel spread*** with various
tabs. The number of tabs and their names may change from time to time. So,
I need to read the tabs names, load them into a table and import the data
from each tab, accordingly.
How do I use your Sub to point to a specific Excel spread***?
Thank you,
Nick
"RobFMS" <Rob@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:Ogjndu6YGHA.4688@xxxxxxxxxxxxxxxxxxxxxxx
Here is a prototype you can work with. Make sure you have your references
set to Excel from the code IDE(Tools->References).

Public Sub GetWorksheets()

Dim objExcel As Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objWork*** As Excel.Work***
Dim intX As Integer

Set objExcel = New Excel.Application
Set objWorkbook = objExcel.Workbooks.Add

Set objWorkbook = objExcel.Workbooks(1)

For intX = 1 To 5
objWorkbook.Worksheets.Add Type:=Excel.XlSheetType.xlWorksheet
Next intX

For Each objWorksheet In objExcel.ActiveWorkbook.Worksheets
Debug.Print objWork***.Name
Next

objExcel.Quit

Set objWorkbook = Nothing
Set objExcel = Nothing

End Sub

HTH

--
Rob Mastrostefano

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com


"Nick Giordano" <nick@xxxxxxxxxx> wrote in message
news:%23i40juwYGHA.5012@xxxxxxxxxxxxxxxxxxxxxxx
Hi,

How can I get a list of sheets in an Excel file loaded into a MS Access
table?

Thank you,

Nick





.

Quantcast