importing excel with mult sheets 1

[grmman]

I am trying to import a excel spreadsheet that has many sheets in it. I want to put each sheet into a diff table.
I am running this from vba code.
It imports the first sheet ok into a new table
but the second sheet it does not do it.
And the 2 table is filled with the same data from the first sheet.

here is my code

Sub read_excel_data()
Dim xlapp As Excel.Application
Dim xlwb As Excel.workbook
Dim sheet As Excel.Worksheet
Dim j As Integer
Dim sheetcnt As Integer
On Error GoTo err_startExcel
Set xlapp = GetObject(, "Excel.Application")
xlapp.Visible = False
Set xlwb = xlapp.Workbooks.Open("c:\ESE-FORM1043.NORTHEAST.xls")
sheetcnt = xlapp.ActiveWorkbook.Sheets.Count

'delete data from temp tables
DoCmd.OpenQuery "qry_maj_delete"
DoCmd.OpenQuery "qry_option_delete"

For z = 1 To sheetcnt
Set sheet = xlapp.ActiveWorkbook.Sheets(z)
    
If (sheet.Name = "MAJOR") Then
            DoCmd.TransferSpreadsheet transfertype:=acImport, _
            tablename:="maj", _
            Filename:="c:\ESE-FORM1043.NORTHEAST.xls", Hasfieldnames:=False, _
            Range:="b9:s210", SpreadsheetType:=acSpreadsheetTypeExcel9
End If


If (sheet.Name = "OPTION") Then
            DoCmd.TransferSpreadsheet transfertype:=acImport, _
            tablename:="OPT", _
            Filename:="c:\ESE-FORM1043.NORTHEAST.xls", Hasfieldnames:=False, _
            Range:="b9:s53", SpreadsheetType:=acSpreadsheetTypeExcel9
End If
Next z
    
    'sheet.Application.ActiveWorkbook.Save
    sheet.Application.ActiveWorkbook.Close

    xlapp.Quit
    Set xlapp = Nothing
    Set xlwb = Nothing
    Set sheet = Nothing
    

    End
    Exit Sub

err_startExcel:

If Err.Number = 429 Then 'No current instance of Excel start up Excel
    Set xlapp = GetObject("", "Excel.Application")
    Resume Next
Else
    MsgBox Err.Description, vbExclamation, "Error: " & Err.Number, Err.HelpFile, Err.HelpContext
    Exit Sub
End If

End Sub

thanks for the help.

 

[vbajock]

Transferspreadsheet will only pull the first sheet. If you want to use transferspreadsheet to pull in multiple worksheets, you have to create named ranges on the additional sheets and pull them in by range name.

 

[grmman]

I am doing it by range.
Each sheeet has a name and range.
I am able to loop thru the sheets and show each sheet name.

I did this before with a differ spreadsheet and it work ok.
This spreadsheets has macros on it. Do you think that can cause my problem.

 

[vbajock]

Ok, I looked at your code a little closer. Couple of things. I thought
Range:="b9:s53" was supposed to be Range:="WorksheetName!b9:s53"

Second, did you walk the code thru line by line to make sure it is processing your if thens correctly?

 

[grmman]

yes I walked thru it. I knew for sure that it is hiting each sheet.
I have an If for each sheet and it does go into each IF.

I still dont know why its not working

 

[vbajock]

Well, nothing in your code jumps out. Can you link the sheets to the db and then pull the info using a query?

 

[vbajock]

Well, nothing in your code jumps out. Can you link the sheets to the db and then pull the info using a query? The only other option is to pull the data directly from the cells and transfer it into your tables via ADO.

 

[grmman]

Does anyone else see anything wrong in my code.

Thanks

 

[PHV]

As vbajock said, have you tried this ?
Range:="[highlight]OPTION![/highlight]b9:s53"

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[grmman]

Thank you

 

[vbajock]

Did it work?

 

[grmman]

YES THANK YOU SO MUCH

 

[vbajock]

I figured that was it. Without the OPTION! pointer, it probably thinks your referring to the first page.