S
ScardyBob
Hello all,
I've run into an infuriating problem when using Automation to import
data from an Excel spreadsheet to Access.
The Situation:
My boss gives me an Excel spreadsheet with data I need to import into
specific tables in an Access Database. Unfourtunately, due to the
numerous errors and bad data, I decided to use VBA Automation to have
the most control over the imported data.
The Problem:
All works well until I run into a (all to frequent) #DIV/0! error on
the spreadsheet. VBA gives me a 'Type Mismatch' error associated with
this line of code:
If Left(Trim(Nz(.Cells(row, col).Value)), 1) <> "-" And Nz(.Cells(row,
col).Value) <> "" Then <----- Error occurs here!!!!
rs.Fields(FldArr(i)) = .Cells(row, col).Value
End If
Is there a way that I can check to see if the cell has the #DIV/0!
error before trying to check its value or is there a function that I
can use to ignore/convert this error, like Nz() does for null values?
Thanks in Advance!
Mike
I've run into an infuriating problem when using Automation to import
data from an Excel spreadsheet to Access.
The Situation:
My boss gives me an Excel spreadsheet with data I need to import into
specific tables in an Access Database. Unfourtunately, due to the
numerous errors and bad data, I decided to use VBA Automation to have
the most control over the imported data.
The Problem:
All works well until I run into a (all to frequent) #DIV/0! error on
the spreadsheet. VBA gives me a 'Type Mismatch' error associated with
this line of code:
If Left(Trim(Nz(.Cells(row, col).Value)), 1) <> "-" And Nz(.Cells(row,
col).Value) <> "" Then <----- Error occurs here!!!!
rs.Fields(FldArr(i)) = .Cells(row, col).Value
End If
Is there a way that I can check to see if the cell has the #DIV/0!
error before trying to check its value or is there a function that I
can use to ignore/convert this error, like Nz() does for null values?
Thanks in Advance!
Mike