migrate Excel data to Access with ADO(vb6)

[zqtqzq]

Kindly help me with a code snippet to move data from excel to Access. The problem i have is ADO Connection to Excel

 

[bjd4jc]

Please see

http://www.connectionstrings.com

where you will find:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

 

[zqtqzq]

i try the Excel connection. It works but could not read all the Excel rows of data.

while reading through the Excel Recordset, it breaks on a certain row with the error

"Invalid use of Null"

while the column actually has data.
I ve checked through my Excel data and they contain vaald data

im using Excel 2002. I am about trying the same code on Excel 2000.

 

[peterd51]

Hi zqtqzq,

in my excel file I had a few errors caused by people inputting commas or linefeeds into the data.

Perhaps your problem is caused by a multi-line string?

I solved mine by searching the string with 'instr' and replacing any 'hits' with a space...

-----------------
'xfer1 is the data read in from the .xls
Do
Point1 = InStr(xfer1, ",") 'strip out any commas
If Point1 > 0 Then
Mid(xfer1, Point1, 1) = " "
Else
Exit Do
End If
Loop

Do
Point1 = InStr(xfer1, Chr$(10)) 'strip out any LF's
If Point1 > 0 Then
Mid(xfer1, Point1, 1) = " "
Else
Exit Do
End If
Loop
-----------------------

Regards
Peter

 

[johnwm]

If the problem is commas or LFs you could also try the intrinsic Replace function. It's possibly more likely that you need to look for vbNewLine characters rather than straight LFs

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller