Type Conversion Error on Importing Excel S/sheet

[Chris Gorham]

Hi,

I'm trying to import an excel s/sheet using the following code:

Set xlObj = CreateObject("excel.application")
xlObj.Visible = True
directory = xlObj.Application.GetOpenFilename("Excel Files (*.XLS), *.XLS", 1)
If directory = False Then Exit Sub
filecheck = Dir(directory)
xlObj.Quit
Set xlObj = Nothing
If filecheck <> file_name & ".xls" Then
check = MsgBox(prompt:="You have imported a file NOT titled: " & file_name
& ".xls" & Chr(13) & Chr(13) & "Please try again.", title:="WARNING!!")
GoTo jump
End If
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, file_name,
directory, -1

where filename is a variable containing the name of the file I want to import.
I then perform a series of test on the fields including their type.
One of the fields is contains dates, but although I can test for whether
this field has been imported ain date format:
If rs.Fields(3).Type <> 8 Then i = 3: Err.Number = 1002: GoTo errhandler
Access generate a type conversion failure table for when I have an imported
date of 00/00/0000
How can I test for when these type conversion failure tables are going to be
created so that I can block the s/sheet from being imported...??

Chris

 

[Jey]

I'm working on a similar project, importing a spreadsheet. In my case I'm
importing to already existing tables, so the way I'm approaching it is to
check the format of each cell before it gets imported, so the user can fix
the values. I won't let the code get to DoCmd.TransferSpreadsheet until I
know it will do it without errors.

For example in your case, assuming date is in column "X":

for intI = 1 to 100 'loop through all the rows in your worksheet
if objXL.Worksheets(strSheetName).Range("X" & intI).Value = "00/00/0000"
'do something! report error to user &/or exit sub?
End If
next intI

where obj.XL is the workbook and strSheetName is the name of the worksheet

Some of my cells have formulas, some of which don't end up with input values
and give a value of "#N/A", so another thing I do is to convert all formulas
to values, then run a find and replace on the worksheet to convert "#N/A" to
"" (empty string). For you, replace "00/00/0000" with"":

With objXL.Worksheets(strSheetName)
.Visible = True
.Activate
.Cells.SELECT
.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End With
objXL.CutCopyMode = False
With objXL.Worksheets(strSheetName)
.Cells.NumberFormat = "General"
.Cells.Replace What:="00/00/0000", Replacement:="",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
End With

I'm using late binding so it doesn't matter if the workbook is office 2007
or 2003. I did have to figure out the values for the xl constants (xlPart,
xlByRows, etc) for the arguments:

Dim xlPasteValues As Integer
xlPasteValues = -4163
Dim xlNone As Integer
xlNone = -4142
Dim xlPart As Integer
xlPart = 2
Dim xlByRows As Integer
xlByRows = 1

I hope that gives you some ideas!

Jey