Exclude "#DIV/0!" from Excel Import

[BTU_needs_assistance_43]

I'm writing fields of values from Excel to tables in Access. At some random
points in the Excel Spreadsheets some numbers will get divided by zero and
there will be this value at completely random places "#DIV/0!". Obviously I
cant divide by zero, but I need to write an exception into my program so that
if it does come across this value while importing data, it will instead write
a "0" value to the table. I still need to fill in the cell in my table that
would have had that value in it, even if it's only a zero. I've tried a few
things but my code doesn't seem to recognize "#DIV/0!" as a value and it
won't let me interchange it. Heres the bit of code I'm working with...
____________________________________________________________

Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set xlw = xlx.Workbooks.Open(strLocation, , True) ' opens in read-only mode
Set xls = xlw.Worksheets("Cast")
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("PitTotal", dbOpenDynaset, dbAppendOnly)

rst.AddNew
If xlc.Value <> 0 Then

For lngColumn = 0 To rst.Fields.Count - 1 'Selects all columns -1
If xlc.Offset(0, lngColumn).Value <> "#DIV/0!" Then
rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
Else
rst.Fields(lngColumn).Value = "0"
End If
Next lngColumn
Set xlc = xlc.Offset(1, 0)
rst.Update
_____________________________________________________________

It works completely fine when I don't try to exclude the "#DIV/0!" value and
gathers all the data I need. Please help me figure out how to exclude those
values. Thanks in advance

 

[JimBurke via AccessMonster.com]

I'm assuming you know what the fields are that are involved in the
computation. Rather than looking for the zero-divide error in the one field,
look at the field that is used as the denominator that is causing the 0
divide. If that field is 0 then set the computed field to 0.

 

[BTU_needs_assistance_43]

Im actually drwawing in reports so unless I want to proofread every Excel
table I'm going to import I can't do that.

 

[Graham Mandeno]

Hi BTU

To check for an error in the Excel cell, use IsError():

If IsError(xlc.Offset(0, lngColumn).Value) Then
rst.Fields(lngColumn).Value = "0"
Else
rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
End If

If you need to ascertain *which* error has occurred in the cell, you can use
the CLng() function:

CLng(xlc.Offset(0, lngColumn).Value)

This will return 2007 for #DIV/0! and 2023 for #REF!

You can experiment with other values.

 

[BTU_needs_assistance_43]

It took a little tweaking but that was exactly what I needed to make it work,
thank you very much!

Hi BTU

To check for an error in the Excel cell, use IsError():

If IsError(xlc.Offset(0, lngColumn).Value) Then
rst.Fields(lngColumn).Value = "0"
Else
rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
End If

If you need to ascertain *which* error has occurred in the cell, you can use
the CLng() function:

CLng(xlc.Offset(0, lngColumn).Value)

This will return 2007 for #DIV/0! and 2023 for #REF!

You can experiment with other values.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I'm writing fields of values from Excel to tables in Access. At some
random
points in the Excel Spreadsheets some numbers will get divided by zero and
there will be this value at completely random places "#DIV/0!". Obviously
I
cant divide by zero, but I need to write an exception into my program so
that
if it does come across this value while importing data, it will instead
write
a "0" value to the table. I still need to fill in the cell in my table
that
would have had that value in it, even if it's only a zero. I've tried a
few
things but my code doesn't seem to recognize "#DIV/0!" as a value and it
won't let me interchange it. Heres the bit of code I'm working with...
____________________________________________________________

Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set xlw = xlx.Workbooks.Open(strLocation, , True) ' opens in read-only
mode
Set xls = xlw.Worksheets("Cast")
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("PitTotal", dbOpenDynaset, dbAppendOnly)

rst.AddNew
If xlc.Value <> 0 Then

For lngColumn = 0 To rst.Fields.Count - 1 'Selects all
columns -1
If xlc.Offset(0, lngColumn).Value <> "#DIV/0!" Then
rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
Else
rst.Fields(lngColumn).Value = "0"
End If
Next lngColumn
Set xlc = xlc.Offset(1, 0)
rst.Update
_____________________________________________________________

It works completely fine when I don't try to exclude the "#DIV/0!" value
and
gathers all the data I need. Please help me figure out how to exclude
those
values. Thanks in advance