Importing Multiple Tabs in Same Spreadsheet

[DBAMJA]

Here is the situation:

I get an excel file from a web based program that creates several tabs (not always the same number of tabs) in the same Excel file. The columns are all the same from tab to tab.

I just can't figure out how to progrmatically import each tab from the spreadsheet.

Hope this makes sense. Any and all help would be greatly appreciated.

[flush]

DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.

 

[BitZero]

Here is some code that shows processing for multiple tabs (or sheets) in a spreadsheet. This code is simplified to show an example of handling multiple tabs. It doesn't have error handling or the actual code to copy data into a table.

When working with spreadsheets, there are 3 levels of objects: application, workbook, and worksheet. Each instance of an excel application corresponds to one EXCEL.EXE that is in memory. Within an application you can have multiple workbook objects, and within a workbook you can have multiple worksheet objects.

Hope this helps.

Private Sub btn_load_Click()

' make sure there is a reference to Microsoft Excel 11.0 Object Library
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim strInputName As String

strInputName = "c:\tektips\testspreadsheet.xls"

Set xlApp = New Excel.Application
Set xlWB = xlApp.workbooks.Open(strInputName)

For Each xlSheet In xlWB.Worksheets
    Call ProcessSheet(xlSheet)
Next xlSheet

Set xlSheet = Nothing
Set xlWB = Nothing
xlApp.Quit
Set xlApp = Nothing

End Sub


Private Sub ProcessSheet(xlSheet As Excel.Worksheet)

Dim lgLastRow As Long

lgLastRow = xlSheet.Range("A65536").End(xlUp).Row

' for testing:
MsgBox xlSheet.Name & "  " & lgLastRow

' put code to copy data in sheet here:

End Sub

 

[lameid]

No matter what you do, to find out information about the Excel document, you will need to programatically manipulate the sheet using an Excel application object. This means setting a reference to Excel. There are bound to be countless examples in this thread as well as the Access Other topics as well as the generic VBA forum (the last tends to have the best answers on how to do stuff in Excel).

My Excel knowledge is a bit stale but I think there is a way to reference a sheet as a range. If so you can pass that to docmd.transferspreadsheet. Or you could simply stack the data in Excel or work on saving the data to CSV for a clean import into Access. Keep in mind that Excel's save to CSV is a bit evil in that it exports the Cell format to CSV not the raw data.

SkipVought in my experience is the Excel VBA guru on these fora.

I hope this enough to get you started.

 

[lameid]

Bitzero beat me to the punch on submit... The code looks good for looping through the sheets; xlsheet.name would give you the sheet name for use in a range.