Importing from several worksheets in excel 2

[kpereira]

HELP!!!! My frustration level is rising.

I have tried to import all of the worksheets into a table in Access, but for some reason when I change active sheets, it doesn't change...I have traced the value of the active sheet ,but it isn't changing. I have tied iterating through all spreadsheets and it didn't work so I tried this. Here is my code PLEASE HELP!!!

' Create a new workbook in Excel
Dim oExcel As Excel.Application
Dim oBook As Excel.workbook
Dim oSheet As Excel.Worksheet
Dim dbs As Database
Set oExcel = CreateObject("Excel.Application")
Dim counter As Integer
Dim x As Integer
Set dbs = CurrentDb '
Dim folder As String
Dim strPath As String

folderName = Me.alias
strPath = "C:\" & folderName & "\" & Me.TeacherLastName & ".xls"

' set Excel objects, sheets etc
Set oBook = oExcel.Workbooks.Open(strPath)
counter = oBook.Worksheets.Count

' Loop throught worksheets
For x = 1 To counter
Set oSheet = oBook.Worksheets(x)
oSheet.Activate
If Left(oSheet.Name, 5) <> "sheet" Then
Set oSheet = oBook.Worksheets(x)
tststring = ActiveSheet.Name
' Transfer the data from Excel
DoCmd.TransferSpreadsheet , , "IMPORTS", strPath, -1

End If

Next x

' Save the Workbook and Quit Excel
oBook.Save
oBook.Close
oExcel.Quit
Set oSheet = Nothing
Set oBook = Nothing
Set oExcel = Nothing
Set dbs = Nothing
MsgBox "DONE"
DoCmd.SetWarnings True
DoCmd.Hourglass False

 

[PHV]

Replace this:
tststring = ActiveSheet.Name
By this:
tststring = oExcel.ActiveSheet.Name

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

 

[SkipVought]

    For x = 1 To counter
      Set oSheet = oBook.Worksheets(x)
      If Left(oSheet.Name, 5) <> "sheet" Then
        tststring = oSheet.Name
    '   Transfer the data from Excel
        oSheet.Activate
        DoCmd.TransferSpreadsheet , , "IMPORTS", strPath, -1

      End If

    Next x

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[kpereira]

Thanks anyway guys, but the active spreadsheet is always the same. The code won't change the active sheet to the next one. I must have some problem with the install or some reference.

I appreciate your help...this is a great forum!!

Karen L. Pereira

"Great spirits often meet violent opposition with mediocre minds" - Albert Einstein

 

[PHV]

And this ?
DoCmd.TransferSpreadsheet , , "IMPORTS", strPath, -1, "'" & tststring & "'!" & oSheet.UsedRange.Address(False, False)

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

 

[kpereira]

PHV YOU ARE FANTASTIC!!! Thanks a bunch!!!!

Karen L. Pereira

"Great spirits often meet violent opposition with mediocre minds" - Albert Einstein