Reading excel file and writing to access table

[Paul]

I was wondering if anyone has had an issue where using vba code to
read an excel file and import the data into an access table some
records are not imported from the excel file. It seems looking at the
data in the excel file that if the first character in the excel file
cell is numeric it will read and write only numeric values only. If I
sort the coloumn in the excel file and the first character in the cell
read is alphanumeric then only alphanumeric values will read and write
in to the table. Any help would be greatly appreciated cause I am
going nuts here. I am using access 2000 and excel 2000. The code I am
using is as follows:

Dim dbExcel As Database
Dim thisdb As Recordset
Dim mydb As Database
Dim ITABLE As Recordset
Dim boolfound As Boolean
Dim i As Integer
Dim intpressedcancel As Integer

On Error GoTo whatthebeephappened:

'call open dialog box module
ShowOpen

boolfound = False

DoCmd.Hourglass True

Set mydb = DBEngine.Workspaces(0).Databases(0) 'Current DB
Set ITABLE = mydb.OpenRecordset("IMPORTED_TABLE", dbOpenTable) 'Import
Table

'Code used to open excel sheet as database rather than as excel object
Set dbExcel = OpenDatabase(FileName, False, True, "excel 8.0")

'loop used for each worksheet in workbook( there should only be one
but just in case)
'For i = 0 To dbExcel.TableDefs.Count - 1
strName = dbExcel.TableDefs(i).Name
Set thisdb = dbExcel.OpenRecordset(strName, dbOpenSnapshot)

thisdb.MoveLast
thisdb.MoveFirst
'code to skip importing of headings
'thisdb.AbsolutePosition = 1
Do While Not thisdb.EOF
If Not Left(thisdb.Fields(2), 3) = "Nex" Then '
'thisdb.MoveNext
'ElseIf thisdb.Fields(2).Value Then
With ITABLE
.AddNew
.Fields(0).Value = thisdb.Fields(3).Value ' SERIES
.Fields(1).Value = thisdb.Fields(4).Value ' VIN
.Fields(2).Value = thisdb.Fields(6).Value ' ENGINE NO
.Fields(3).Value = thisdb.Fields(0).Value ' RETAILER
.Fields(6).Value = thisdb.Fields(5).Value ' REPAIR
ORDER NO.
.Fields(7).Value = thisdb.Fields(7).Value ' REPAIR
DATE
.Fields(8).Value = thisdb.Fields(19).Value ' ODOMETER
.Fields(9).Value = thisdb.Fields(8) ' CAUSAL PART
.Fields(10).Value = thisdb.Fields(17).Value ' LABOUR
CODE
.Fields(11).Value = thisdb.Fields(18).Value ' LABOUR
Hours
.Fields(13).Value = thisdb.Fields(11).Value ' PART
COST
.Fields(14).Value = thisdb.Fields(12).Value ' LABOUR
COST
.Fields(15).Value = thisdb.Fields(14).Value ' NET ITEM
COST
.Fields(16).Value = thisdb.Fields(13).Value ' SALES
TAX
.Fields(17).Value = thisdb.Fields(16).Value ' TOTAL
.Fields(18).Value = thisdb.Fields(19).Value ' PAYMENT
.Fields(19).Value = thisdb.Fields(20).Value ' CASE
TYPE
.Fields(20).Value = thisdb.Fields(10).Value ' Nature
CODE
.Fields(37).Value = thisdb.Fields(9).Value ' Cause
code
.Fields(27).Value = thisdb.Fields(15).Value ' HANDLING
.Fields(29).Value = thisdb.Fields(24).Value '
AUTHORISED BY
.Fields(30).Value = thisdb.Fields(1).Value ' Sequence

.Update
End With
End If
thisdb.MoveNext
Loop
'Next i

[Salad]

Paul wrote:

I was wondering if anyone has had an issue where using vba code to
read an excel file and import the data into an access table some
records are not imported from the excel file. It seems looking at the
data in the excel file that if the first character in the excel file
cell is numeric it will read and write only numeric values only. If I
sort the coloumn in the excel file and the first character in the cell
read is alphanumeric then only alphanumeric values will read and write
in to the table. Any help would be greatly appreciated cause I am
going nuts here. I am using access 2000 and excel 2000. The code I am
using is as follows:

Dim dbExcel As Database
Dim thisdb As Recordset
Dim mydb As Database
Dim ITABLE As Recordset
Dim boolfound As Boolean
Dim i As Integer
Dim intpressedcancel As Integer

On Error GoTo whatthebeephappened:

'call open dialog box module
ShowOpen

boolfound = False

DoCmd.Hourglass True

Set mydb = DBEngine.Workspaces(0).Databases(0) 'Current DB
Set ITABLE = mydb.OpenRecordset("IMPORTED_TABLE", dbOpenTable) 'Import
Table

'Code used to open excel sheet as database rather than as excel object
Set dbExcel = OpenDatabase(FileName, False, True, "excel 8.0")

'loop used for each worksheet in workbook( there should only be one
but just in case)
'For i = 0 To dbExcel.TableDefs.Count - 1
strName = dbExcel.TableDefs(i).Name
Set thisdb = dbExcel.OpenRecordset(strName, dbOpenSnapshot)

thisdb.MoveLast
thisdb.MoveFirst
'code to skip importing of headings
'thisdb.AbsolutePosition = 1
Do While Not thisdb.EOF
If Not Left(thisdb.Fields(2), 3) = "Nex" Then '
'thisdb.MoveNext
'ElseIf thisdb.Fields(2).Value Then
With ITABLE
.AddNew
.Fields(0).Value = thisdb.Fields(3).Value ' SERIES
.Fields(1).Value = thisdb.Fields(4).Value ' VIN
.Fields(2).Value = thisdb.Fields(6).Value ' ENGINE NO
.Fields(3).Value = thisdb.Fields(0).Value ' RETAILER
.Fields(6).Value = thisdb.Fields(5).Value ' REPAIR
ORDER NO.
.Fields(7).Value = thisdb.Fields(7).Value ' REPAIR
DATE
.Fields(8).Value = thisdb.Fields(19).Value ' ODOMETER
.Fields(9).Value = thisdb.Fields(8) ' CAUSAL PART
.Fields(10).Value = thisdb.Fields(17).Value ' LABOUR
CODE
.Fields(11).Value = thisdb.Fields(18).Value ' LABOUR
Hours
.Fields(13).Value = thisdb.Fields(11).Value ' PART
COST
.Fields(14).Value = thisdb.Fields(12).Value ' LABOUR
COST
.Fields(15).Value = thisdb.Fields(14).Value ' NET ITEM
COST
.Fields(16).Value = thisdb.Fields(13).Value ' SALES
TAX
.Fields(17).Value = thisdb.Fields(16).Value ' TOTAL
.Fields(18).Value = thisdb.Fields(19).Value ' PAYMENT
.Fields(19).Value = thisdb.Fields(20).Value ' CASE
TYPE
.Fields(20).Value = thisdb.Fields(10).Value ' Nature
CODE
.Fields(37).Value = thisdb.Fields(9).Value ' Cause
code
.Fields(27).Value = thisdb.Fields(15).Value ' HANDLING
.Fields(29).Value = thisdb.Fields(24).Value '
AUTHORISED BY
.Fields(30).Value = thisdb.Fields(1).Value ' Sequence

.Update
End With
End If
thisdb.MoveNext
Loop
'Next i

I'm winging this, have no idea if this will work.

Perhaps you can type cast the values. Let's say the table requests text
for a field. Type cast the value to a string.
Fields(0).Value = CStr(thisdb.Fields(3).Value) ' SERIES
in this case, you alway convert a numeric or string value of Field(3) to a
string value

You can type cast to Lng, Double, Currency, etc if this method works.

Let's say there is a numeric field but some rows have N/A in it. You
might want to do a
If IsNumeric(thisdb.Fields(3).Value) Then ...

And you may want to check for nulls
If IsNumeric(thisdb.Fields(3).Value) Or IsNull(thisdb.Fields(3).Value)
Then x = NZ(thisdb.Fields(3).Value,0)

Is it possible to use Docmd.TransreSpreadsheet ....?

I suppose you could create a link to the spreadsheet and also run an
append query.

And you could create an import/esport spec. If it failed on that, you
know your code needs to be updated.

[Paul]

Salad <oi*@vinegar.com> wrote in message news:<40***************@vinegar.com>...

Paul wrote:

I was wondering if anyone has had an issue where using vba code to
read an excel file and import the data into an access table some
records are not imported from the excel file. It seems looking at the
data in the excel file that if the first character in the excel file
cell is numeric it will read and write only numeric values only. If I
sort the coloumn in the excel file and the first character in the cell
read is alphanumeric then only alphanumeric values will read and write
in to the table. Any help would be greatly appreciated cause I am
going nuts here. I am using access 2000 and excel 2000. The code I am
using is as follows:

Dim dbExcel As Database
Dim thisdb As Recordset
Dim mydb As Database
Dim ITABLE As Recordset
Dim boolfound As Boolean
Dim i As Integer
Dim intpressedcancel As Integer

On Error GoTo whatthebeephappened:

'call open dialog box module
ShowOpen

boolfound = False

DoCmd.Hourglass True

Set mydb = DBEngine.Workspaces(0).Databases(0) 'Current DB
Set ITABLE = mydb.OpenRecordset("IMPORTED_TABLE", dbOpenTable) 'Import
Table

'Code used to open excel sheet as database rather than as excel object
Set dbExcel = OpenDatabase(FileName, False, True, "excel 8.0")

'loop used for each worksheet in workbook( there should only be one
but just in case)
'For i = 0 To dbExcel.TableDefs.Count - 1
strName = dbExcel.TableDefs(i).Name
Set thisdb = dbExcel.OpenRecordset(strName, dbOpenSnapshot)

thisdb.MoveLast
thisdb.MoveFirst
'code to skip importing of headings
'thisdb.AbsolutePosition = 1
Do While Not thisdb.EOF
If Not Left(thisdb.Fields(2), 3) = "Nex" Then '
'thisdb.MoveNext
'ElseIf thisdb.Fields(2).Value Then
With ITABLE
.AddNew
.Fields(0).Value = thisdb.Fields(3).Value ' SERIES
.Fields(1).Value = thisdb.Fields(4).Value ' VIN
.Fields(2).Value = thisdb.Fields(6).Value ' ENGINE NO
.Fields(3).Value = thisdb.Fields(0).Value ' RETAILER
.Fields(6).Value = thisdb.Fields(5).Value ' REPAIR
ORDER NO.
.Fields(7).Value = thisdb.Fields(7).Value ' REPAIR
DATE
.Fields(8).Value = thisdb.Fields(19).Value ' ODOMETER
.Fields(9).Value = thisdb.Fields(8) ' CAUSAL PART
.Fields(10).Value = thisdb.Fields(17).Value ' LABOUR
CODE
.Fields(11).Value = thisdb.Fields(18).Value ' LABOUR
Hours
.Fields(13).Value = thisdb.Fields(11).Value ' PART
COST
.Fields(14).Value = thisdb.Fields(12).Value ' LABOUR
COST
.Fields(15).Value = thisdb.Fields(14).Value ' NET ITEM
COST
.Fields(16).Value = thisdb.Fields(13).Value ' SALES
TAX
.Fields(17).Value = thisdb.Fields(16).Value ' TOTAL
.Fields(18).Value = thisdb.Fields(19).Value ' PAYMENT
.Fields(19).Value = thisdb.Fields(20).Value ' CASE
TYPE
.Fields(20).Value = thisdb.Fields(10).Value ' Nature
CODE
.Fields(37).Value = thisdb.Fields(9).Value ' Cause
code
.Fields(27).Value = thisdb.Fields(15).Value ' HANDLING
.Fields(29).Value = thisdb.Fields(24).Value '
AUTHORISED BY
.Fields(30).Value = thisdb.Fields(1).Value ' Sequence

.Update
End With
End If
thisdb.MoveNext
Loop
'Next i

I'm winging this, have no idea if this will work.

Perhaps you can type cast the values. Let's say the table requests text
for a field. Type cast the value to a string.
Fields(0).Value = CStr(thisdb.Fields(3).Value) ' SERIES
in this case, you alway convert a numeric or string value of Field(3) to a
string value

You can type cast to Lng, Double, Currency, etc if this method works.

Let's say there is a numeric field but some rows have N/A in it. You
might want to do a
If IsNumeric(thisdb.Fields(3).Value) Then ...

And you may want to check for nulls
If IsNumeric(thisdb.Fields(3).Value) Or IsNull(thisdb.Fields(3).Value)
Then x = NZ(thisdb.Fields(3).Value,0)

Is it possible to use Docmd.TransreSpreadsheet ....?

I suppose you could create a link to the spreadsheet and also run an
append query.

And you could create an import/esport spec. If it failed on that, you
know your code needs to be updated.

Hello Salad and thanks for replying. I have tried case typing to a
string and now I get an invalid use of null. When in debug mode I have
noticed that access reads the data in a cell as null when I know there
is valid data in the cell. I have tried removing the value and
changing the table field properties but still no good. The reason I am
not using the transfer spreadsheet function is due to me wanting to
place specific data from the spreadsheet to specific fields in the
table. There is more than one spreadsheet coming from different
sources but esentially have the same sort of data in different formats
and by importing the data this way I esentially do not have to modify
the reports, queries and relationships that are already applied.

[Salad]

Paul wrote:

I'm winging this, have no idea if this will work.

Perhaps you can type cast the values. Let's say the table requests text
for a field. Type cast the value to a string.
Fields(0).Value = CStr(thisdb.Fields(3).Value) ' SERIES
in this case, you alway convert a numeric or string value of Field(3) to a
string value

You can type cast to Lng, Double, Currency, etc if this method works.

Let's say there is a numeric field but some rows have N/A in it. You
might want to do a
If IsNumeric(thisdb.Fields(3).Value) Then ...

And you may want to check for nulls
If IsNumeric(thisdb.Fields(3).Value) Or IsNull(thisdb.Fields(3).Value)
Then x = NZ(thisdb.Fields(3).Value,0)

Is it possible to use Docmd.TransreSpreadsheet ....?

I suppose you could create a link to the spreadsheet and also run an
append query.

And you could create an import/esport spec. If it failed on that, you
know your code needs to be updated.
Hello Salad and thanks for replying. I have tried case typing to a
string and now I get an invalid use of null. When in debug mode I have
noticed that access reads the data in a cell as null when I know there
is valid data in the cell.

That would be a problem.
I have tried removing the value and
changing the table field properties but still no good. The reason I am
not using the transfer spreadsheet function is due to me wanting to
place specific data from the spreadsheet to specific fields in the
table. There is more than one spreadsheet coming from different
sources but esentially have the same sort of data in different formats
and by importing the data this way I esentially do not have to modify
the reports, queries and relationships that are already applied.

I would think you should try to create an import/export spec. At Dev's site
http://www.mvps.org there is a FileOpen diablog under api's. You could then grab
the spreadsheet name and import with a specification. With a specification file,
you can tag a column to a field.

Oh well, I'll make a small test of your routine...see what happens. In the
meantime, you may want a spec file.

[Salad]

Paul wrote:

OK, I have a solution for you. My sample code is below, but that really is not the
solution. I created a table with 4 fields, text, number, and ran it. Worked fine.
Then I remembered the thing about a column with a mixture of text and numberic. And
it did just like you said. The value was Null.

I recalled from the old days or Lotus I'd enter a number in a cell as a number but
if it were an alpha I'd enter an apostrophe before the number to define it as a
label. So I highlited the column that had a mix of alphas and numerics and did a
Format/Cells/Text. Now that the column was formated as text (it states "Text format
cells are treated as text even when a number is in the cell) I ran my routine and it
imported fine.

So...instead of casting at the Access level it appears you need to cast at the Excel
level. Not a big deal. Perhaps you can set it via a routine.

I also think you could get some good Excel functionality (input routines, etc) from
a newsgroup that specialized in VBA for Excel. See also http://www.mvps.org. and
check out the Excel link.

Anyway, here's my code

Sub UpdateX()
Dim dbExcel As Database
Dim thisdb As Recordset
Dim mydb As Database
Dim ITABLE As Recordset
Dim boolfound As Boolean
Dim i As Integer
Dim intpressedcancel As Integer
Dim strName As Variant

On Error GoTo whatthebeephappened:

boolfound = False

Set mydb = DBEngine.Workspaces(0).Databases(0) 'Current DB
Set ITABLE = mydb.OpenRecordset("Table1", dbOpenTable) 'Import Table

'Code used to open excel sheet as database rather than as excel object
Set dbExcel = OpenDatabase("D:\AccessApps\Testing.XLS", False, True, "excel
8.0")

'loop used for each worksheet in workbook( there should only be one but just in
case)
strName = dbExcel.TableDefs(i).Name
Set thisdb = dbExcel.OpenRecordset(strName, dbOpenSnapshot)

thisdb.MoveLast
thisdb.MoveFirst
MsgBox "This db count is " & thisdb.RecordCount

'code to skip importing of headings
'thisdb.AbsolutePosition = 1
Do While Not thisdb.EOF
With ITABLE
i = i + 1

.AddNew
'starting at field1 since field(0) is an authonumber
.Fields(1) = thisdb.Fields(0).Value ' SERIES (mixture text & number)
.Fields(2) = thisdb.Fields(1).Value ' SERIES (number)
.Fields(3) = thisdb.Fields(2).Value ' VIN (text)
.Fields(4) = thisdb.Fields(3).Value ' ENGINE NO (date)
.Update
End With
thisdb.MoveNext
Loop
thisdb.Close
Set thisdb = Nothing
ITABLE.Close
Set ITABLE = Nothing
MsgBox "Done"
Exit_sub:
Exit Sub

whatthebeephappened:
MsgBox Err.Description
Resume Exit_sub
End Sub

[Paul]

Salad <oi*@vinegar.com> wrote in message news:<40***************@vinegar.com>...

Paul wrote:

OK, I have a solution for you. My sample code is below, but that really is not the
solution. I created a table with 4 fields, text, number, and ran it. Worked fine.
Then I remembered the thing about a column with a mixture of text and numberic. And
it did just like you said. The value was Null.

I recalled from the old days or Lotus I'd enter a number in a cell as a number but
if it were an alpha I'd enter an apostrophe before the number to define it as a
label. So I highlited the column that had a mix of alphas and numerics and did a
Format/Cells/Text. Now that the column was formated as text (it states "Text format
cells are treated as text even when a number is in the cell) I ran my routine and it
imported fine.

So...instead of casting at the Access level it appears you need to cast at the Excel
level. Not a big deal. Perhaps you can set it via a routine.

I also think you could get some good Excel functionality (input routines, etc) from
a newsgroup that specialized in VBA for Excel. See also http://www.mvps.org. and
check out the Excel link.

Anyway, here's my code

Sub UpdateX()
Dim dbExcel As Database
Dim thisdb As Recordset
Dim mydb As Database
Dim ITABLE As Recordset
Dim boolfound As Boolean
Dim i As Integer
Dim intpressedcancel As Integer
Dim strName As Variant

On Error GoTo whatthebeephappened:

boolfound = False

Set mydb = DBEngine.Workspaces(0).Databases(0) 'Current DB
Set ITABLE = mydb.OpenRecordset("Table1", dbOpenTable) 'Import Table

'Code used to open excel sheet as database rather than as excel object
Set dbExcel = OpenDatabase("D:\AccessApps\Testing.XLS", False, True, "excel
8.0")

'loop used for each worksheet in workbook( there should only be one but just in
case)
strName = dbExcel.TableDefs(i).Name
Set thisdb = dbExcel.OpenRecordset(strName, dbOpenSnapshot)

thisdb.MoveLast
thisdb.MoveFirst
MsgBox "This db count is " & thisdb.RecordCount

'code to skip importing of headings
'thisdb.AbsolutePosition = 1
Do While Not thisdb.EOF
With ITABLE
i = i + 1

.AddNew
'starting at field1 since field(0) is an authonumber
.Fields(1) = thisdb.Fields(0).Value ' SERIES (mixture text & number)
.Fields(2) = thisdb.Fields(1).Value ' SERIES (number)
.Fields(3) = thisdb.Fields(2).Value ' VIN (text)
.Fields(4) = thisdb.Fields(3).Value ' ENGINE NO (date)
.Update
End With
thisdb.MoveNext
Loop
thisdb.Close
Set thisdb = Nothing
ITABLE.Close
Set ITABLE = Nothing
MsgBox "Done"
Exit_sub:
Exit Sub

whatthebeephappened:
MsgBox Err.Description
Resume Exit_sub
End Sub

Salad,
Thanks once again for replying to the post. I toke your advice on
board and went back to the source and discovered that the file
presented to me had been "touched up" to look pretty. The actual file
as it is produced from an as 400 type machine formats the xls file in
a text format if that makes any sense. The person touching it up was
formatting all the cells in "general". So i have been able to recieve
teh file untouched and formatted it in text and wolla it works. I
would like to be able to maybe produce a macro in excel that formats
the cells before importation. So that might be my next post if I cant
find anything on there already.

[Salad]

Paul wrote:

Salad,
Thanks once again for replying to the post. I toke your advice on
board and went back to the source and discovered that the file
presented to me had been "touched up" to look pretty. The actual file
as it is produced from an as 400 type machine formats the xls file in
a text format if that makes any sense. The person touching it up was
formatting all the cells in "general". So i have been able to recieve
teh file untouched and formatted it in text and wolla it works. I
would like to be able to maybe produce a macro in excel that formats
the cells before importation. So that might be my next post if I cant
find anything on there already.

Thanks for your update. Your input is appreciated

For help with Excel macros you might want to check out an excel newsgroup. I am sure
there is one specifically dealing with macros.