Type conversion issue

[teser3]

I am trying to import an Excel 2003 sheet into my Access 2003
database.

I get error message saying sheet could not be imported and it referred
me to an Import error table which showed type conversion error for one
of the columns. Please advise what that means and how I can make it
work?

[ARC]

I've seen funny things happen with importing from Excel to Access.

I had a field I was importing, which was a part number field. My part number
field was a text field in my access table, however, the user that was having
trouble had only numbers in his import spreadsheet for the part number
column.

I think what was happening was Excel was considering the entire column to be
numeric, since the user only used numbers in this column. However, since my
import field in access was text, a data type conversion was resulting.

I ended up writing a "fixup" routine that would edit the import spreadsheet
directly and insert the literal character ( ' ) in front of the part number
for each row in the spreadsheet. This did the trick, but was a royal pain.

If you need a code sample, let me know.

Andy
<te****@hotmail.comwrote in message
news:11**********************@d55g2000hsg.googlegr oups.com...

>I am trying to import an Excel 2003 sheet into my Access 2003
database.

I get error message saying sheet could not be imported and it referred
me to an Import error table which showed type conversion error for one
of the columns. Please advise what that means and how I can make it
work?

[Rick A.B.]

On Jul 31, 9:37 pm, "[e-mail]" <[e-mail]:

I am trying to import an Excel 2003 sheet into my Access 2003
database.

I get error message saying sheet could not be imported and it referred
me to an Import error table which showed type conversion error for one
of the columns. Please advise what that means and how I can make it
work?

Check out using an Import spec.

[teser3]

On Jul 31, 11:37 pm, "ARC" <[e-mail]:

I've seen funny things happen with importing from Excel to Access.

I had a field I was importing, which was a part number field. My part number
field was a text field in my access table, however, the user that was having
trouble had only numbers in his import spreadsheet for the part number
column.

I think what was happening was Excel was considering the entire column to be
numeric, since the user only used numbers in this column. However, since my
import field in access was text, a data type conversion was resulting.

I ended up writing a "fixup" routine that would edit the import spreadsheet
directly and insert the literal character ( ' ) in front of the part number
for each row in the spreadsheet. This did the trick, but was a royal pain.

If you need a code sample, let me know.

Andy

<[e-mail] in message

news:11**********************@d55g2000hsg.googlegr oups.com...

I am trying to import an Excel 2003 sheet into my Access 2003
database.

I get error message saying sheet could not be imported and it referred
me to an Import error table which showed type conversion error for one
of the columns. Please advise what that means and how I can make it
work?- Hide quoted text -

- Show quoted text -\

Thanks, I would like to see some sample code.

[ARC]

Here's the entire proc. I didn't have time to edit, but it is commented...
----------------------------------
Public Function FixPartsImportSheet() As Boolean
'This will add the ' character to the part number field.
'For some reason, even though the field in the table for importing parts is
text, and the
'spreadsheet column for part number is text, if there is a mixture of
straight numbers in the part number field
'and some cells with text, the ones with text do not get imported, and the
ones with numbers get transposed
'to scientific notation.
'The fix is to go through the import sheet and add the ' character
preceeding each part number.
'
On Error Resume Next
Dim mysheet As Object, xlApp As Object, sheetnm As String, cnt As Integer, j
As Integer, i As Integer, lastrow As Long, firstrow As Integer
cnt = 0
j = 2 'col 1=supplier part column is first column, col 2=description
column, col 8=manuf. num, col 9=UPC
i = 1
'
DoCmd.Hourglass -1
sheetnm = Forms!fImport.Form!txtFile
Set xlApp = CreateObject("Excel.Application")
Set mysheet = xlApp.workbooks.Open(sheetnm).sheets(1)
'1st, find last row of data, use description field as this is required
Do Until cnt 2 'break out if 2 blank cells in a row were detected
If Trim$(mysheet.cells(i, j).Value) <"Description" Then
If IsNull(mysheet.cells(i, j).Value) Then
cnt = cnt + 1
ElseIf Trim$(mysheet.cells(i, j).Value) = "" Then
cnt = cnt + 1
End If
Else
firstrow = i + 1
cnt = 3
End If
i = i + 1
'Call SetMessage("Getting last row number..." & i)
Loop
lastrow = i - 4
'2nd, find first row of part number column, and fix up with the ' symbol
j = 1
If Left(mysheet.cells(firstrow, j).Value, 1) <"'" Then
mysheet.cells(firstrow, j) = "'" & mysheet.cells(firstrow, j).Value
End If
'3rd, edit first data row for description, this is required field, so no
need to go through
j = 2
If Left(mysheet.cells(firstrow, j).Value, 1) <"'" Then
mysheet.cells(firstrow, j) = "'" & mysheet.cells(firstrow, j).Value
End If
'4th, edit first occurence in manuf num field
j = 8
If Left(mysheet.cells(firstrow, j).Value, 1) <"'" Then
mysheet.cells(firstrow, j) = "'" & mysheet.cells(firstrow, j).Value
End If
'last, edit the upc first occurence
j = 9
If Left(mysheet.cells(firstrow, j).Value, 1) <"'" Then
mysheet.cells(firstrow, j) = "'" & mysheet.cells(firstrow, j).Value
End If
''old routine below...i found that you don't have to put the ' in front of
each row, just the 1st row of data
'
'Do Until cnt 2 'break out if 2 blank cells in a row were detected
' If Trim$(mysheet.cells(i, j).Value) <"Supplier Part" Then
' If IsNull(mysheet.cells(i, j).Value) Then
' cnt = cnt + 1
' ElseIf Trim$(mysheet.cells(i, j).Value) = "" Then
' cnt = cnt + 1
' Else
' If Left(mysheet.cells(i, j).Value, 1) <"'" Then
' mysheet.cells(i, j) = "'" & mysheet.cells(i, j).Value
' End If
' End If
' End If
' i = i + 1
'Loop
'Save and exit out of Excel
'
'mysheet.Application.windows("Purchase.xls").Visib le = True
mysheet.Application.activeworkbook.Save
mysheet.Application.activeworkbook.Close
xlApp.Quit
Set mysheet = Nothing '
DoCmd.Hourglass 0
Exit Function
End Function

[teser3]

Thanks!!

[ARC]

You're welcome. Hope it helped, and wasn't major overkill.

Good luck!
<te****@hotmail.comwrote in message
news:11**********************@19g2000hsx.googlegro ups.com...

Thanks!!