2
Hello,
I'm trying to use the following code to read data from Excel files and put the data into an MS Access Table. Some of the Excel files have the tabs in the sequence Chart1, Sheet1, Sheet2 etc while others have the order Sheet1, Sheet etc. The required information is on sheet1 in all cases. The line
Set xlsheet = xlwbook.Sheets.Item(1) specifies the first tab from the left with could be the chart or sheet1 depending on the file. If the first tab is actually a chart, then runtime error 13 (type mismatch) occurs. I thought that my On Error GoTo statement (which is located in the For loop that starts with For a = 0 To filecount - 1) would take care of this. What I hoped it would do is to go to the ErrHandler if the first tab is a chart and then change the parameter in the Item procedure to 2 so that it goes to sheet1. It works fine on the first execution of the loop, but on the second execution the ErrHandler doesnt even work and I get the runtime error 13. I know it should kick in the second time around since the first two files in the folder have chart before sheet1. Any ideas on why this is happening? Thanks AN
Option Compare Database
Sub LogTable()
'Creates a new table in the db1 database
Dim dat As DAO.Field
Dim shift As DAO.Field
Dim bls_day As DAO.Field
Dim db1 As DAO.Database
Dim MyTableDef As DAO.TableDef
' Make db1 the current DB.
Set db1 = DBEngine.Workspaces(0).Databases(0)
' Create new Table.
Set MyTableDef = db1.CreateTableDef("LogTable2")
' Create new Field.
Set dat = MyTableDef.CreateField("Date", dbDate)
Set shift = MyTableDef.CreateField("Shift", dbText)
Set bls_day = MyTableDef.CreateField("Bls/Day", dbText)
'Appends field to table's Fields Collection
MyTableDef.Fields.Append dat
MyTableDef.Fields.Append shift
MyTableDef.Fields.Append bls_day
' Add table to the collection.
db1.TableDefs.Append MyTableDef
'Get file names and place them in an array
Dim fso As New FileSystemObject
Dim fls As Files
Dim f As file
Dim fileNameArr(35) As String
Dim i As Integer
Dim path As String
Dim filecount As Integer
path = "C:\Documents and Settings\strat-temp\Desktop\TestFolder\Copy of Log Files"
Set fls = fso.GetFolder(path).Files
i = 0
For Each f In fls
fileNameArr(i) = path + "\" + f.Name
i = i + 1
Next
filecount = fls.Count 'Counts the number of files in the folder
'Get information from Excel Log Files and add to Table
Dim xl As New Excel.Application
Dim xlsheet As Excel.Worksheet
Dim xlwbook As Excel.Workbook
Dim row As Integer
Dim month As String
Dim dat1 As String 'dat1 stores the date of the Log File
Dim shift1 As String
Dim a As Integer
Dim blsdata As String
'Dim db1 As DAO.Database
Dim LogData As DAO.Recordset
Set db1 = CurrentDb()
Set rstLog = db1.OpenRecordset("LogTable2")
For a = 0 To filecount - 1
Set xlwbook = xl.Workbooks.Open(fileNameArr(a))
On Error GoTo ErrHandler
Set xlsheet = xlwbook.Sheets.Item(1) 'Specifies which sheet to look at
ErrHandler:
If Err = 13 Then
Set xlsheet = xlwbook.Sheets.Item(2)
Else
If Err <> 13 Then
Set xlsheet = xlwbook.Sheets.Item(1)
End If
End If
row = 1
Do Until xlsheet.Cells(row, 1) = "FRC-1 (Bls/day)"
blsdata = xlsheet.Cells(row, 1)
row = row + 1
Loop
blsdata = xlsheet.Cells(row, 2)
dat1 = xlsheet.Cells(3, 9)
shift1 = xlsheet.Cells(3, 3)
xlsheet.Parent.Close (False) 'Closes excel workbook
rstLog.AddNew
rstLog("Bls/Day") = blsdata
rstLog("Date") = dat1
rstLog("Shift") = shift1
rstLog.Update 'Saves the added record
Next a
'closing excel objects
Set xl = xlsheet.Parent.Parent ' Page 1082 of Access Book
xl.Quit
rstLog.Close 'Clean up object
End Sub
