Get Excel Worksheet Names for Importing into Access Tables

[rirus via AccessMonster.com]

I want to import several Worksheets from a dynamic Excel Workbook into a
single Access Table. I currently have the importing VBA code working and can
import the Worksheets in one at a time. What I would like to do, if possible
is automate this by getting the Worksheet Names from the Workbook first then
import each Worksheet one at a time into the Access table.

Also, each Worksheet represents a months worth of data. Each month a new
Worksheet is created and data is entered. Each month a new Worksheet is
created and the lasts month's data is not touched again. Any data that is not
completed is rolled into the new month's spreadsheet.

I know.. I know. It is not my process, I don't own it so I can't change it. I
am just pulling data each month and combining it with other tables I have
imported to achieve an end result, legable reports on the months work.

All I am needing is VBA code that gets an Excel Workbooks Worksheet's names.

thanks,


Rirus

 

[Klatuu]

Here is a basic routine that will return all the names of worksheets in a
workbook:

Public Function WorkSheetNames() As Boolean
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Set xlApp = CreateObject("Excel.application")
Set xlBook = xlApp.Workbooks.Open("c:\MyWorkBook.xls", 0, True)
For Each xlSheet In xlBook.Worksheets
Debug.Print xlSheet.Name
Next xlSheet
xlBook.Close False
xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing
Set xlSheet = Nothing
End Function

 

[rirus via AccessMonster.com]

This is very cool, works great. I had to load in the Microsoft Excel 12.0
Object libraries. Once I did that, it worked.

Awesome... thanks Klatuu

Rirus

Here is a basic routine that will return all the names of worksheets in a
workbook:

Public Function WorkSheetNames() As Boolean
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Set xlApp = CreateObject("Excel.application")
Set xlBook = xlApp.Workbooks.Open("c:\MyWorkBook.xls", 0, True)
For Each xlSheet In xlBook.Worksheets
Debug.Print xlSheet.Name
Next xlSheet
xlBook.Close False
xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing
Set xlSheet = Nothing
End Function

I want to import several Worksheets from a dynamic Excel Workbook into a
single Access Table. I currently have the importing VBA code working and can

[quoted text clipped - 16 lines]

Rirus

 

[Ken Snell [MVP]]

Import Data from All Worksheets in a single EXCEL File into Separate Tables
via TransferSpreadsheet (VBA)
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#ImpAllWktsSepTbl

 

[rirus via AccessMonster.com]

WOW! This is really helpful. I really appreciate the link as it will help get
me to the next level in my Access VBA skills. Thanks you very much Ken...
good stuff.

Rirus

Import Data from All Worksheets in a single EXCEL File into Separate Tables
via TransferSpreadsheet (VBA)
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#ImpAllWktsSepTbl

I want to import several Worksheets from a dynamic Excel Workbook into a
single Access Table. I currently have the importing VBA code working and

[quoted text clipped - 22 lines]

Rirus