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
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