By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,012 Members | 988 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,012 IT Pros & Developers. It's quick & easy.

Help with On Error GoTo

P: 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
Jun 14 '07 #1
Share this Question
Share on Google+
3 Replies


hariharanmca
100+
P: 1,977
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




So you can use

Expand|Select|Wrap|Line Numbers
  1. On Error Resume Next
Jun 15 '07 #2

hariharanmca
100+
P: 1,977
So you can use

Expand|Select|Wrap|Line Numbers
  1. On Error Resume Next

Expand|Select|Wrap|Line Numbers
  1. On Error GoTo ErrHandler 
Change To

Expand|Select|Wrap|Line Numbers
  1. On Error Resume Next
[/quote]


and remove the lable ErrHandler
Jun 15 '07 #3

P: 2
That suggestion worked well. Thanks a lot.
Jun 15 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.