Reading in an Excel file.

[BerkshireGuy]

I need to import an Excel file. For reasons I won't get into, I cannot
just link to the file or do an import specification, so I need to do it
via code.

I know of two ways of doing this - via a recordset and using the Excel
file as a datasource and also as setting up an Excel object and
stepping through that object's collections. (cells, rows, etc)

Can anyone tell me which one is better and why? They both seem to
work. Or perhaps, there is even a better way?

Long and short, we are getting a data file - in Excel - from a vendor -
and need to import it into our system. Stepping through the rows is
ideal to allow us to add business rules to the data.

Thanks
Brian

[Keith Wilby]

"BerkshireGuy" <bd*****@yahoo.comwrote in message
news:11**********************@e3g2000cwe.googlegro ups.com...

>I need to import an Excel file. For reasons I won't get into, I cannot
just link to the file or do an import specification, so I need to do it
via code.

I know of two ways of doing this - via a recordset and using the Excel
file as a datasource

This would require you to link to it using the linked table method so ...

and also as setting up an Excel object and
stepping through that object's collections. (cells, rows, etc)

.... this is your only option isn't it?

>
Can anyone tell me which one is better and why? They both seem to
work. Or perhaps, there is even a better way?

I've used both and there's not much to choose between them, performance
wise, as far as I can tell at any rate. I only moved to manipulating Excel
using code because I had to *write* data to Excel, and with SP2 on Office
this is no longer supported via the linked table method.

HTH - Keith.
www.keithwilby.com

[BerkshireGuy]

Keitth - Actually I can access the Excel workbook as a recordset
without a linked table. I do so by creating and ADODB connection and
use Excel as the DSN.

I can provide air code if you'd like.

-Brian

Keith Wilby wrote:

"BerkshireGuy" <bd*****@yahoo.comwrote in message
news:11**********************@e3g2000cwe.googlegro ups.com...

I need to import an Excel file. For reasons I won't get into, I cannot
just link to the file or do an import specification, so I need to do it
via code.

I know of two ways of doing this - via a recordset and using the Excel
file as a datasource

This would require you to link to it using the linked table method so ...

and also as setting up an Excel object and
stepping through that object's collections. (cells, rows, etc)

... this is your only option isn't it?

Can anyone tell me which one is better and why? They both seem to
work. Or perhaps, there is even a better way?

I've used both and there's not much to choose between them, performance
wise, as far as I can tell at any rate. I only moved to manipulating Excel
using code because I had to *write* data to Excel, and with SP2 on Office
this is no longer supported via the linked table method.

HTH - Keith.
www.keithwilby.com

[Keith Wilby]

"BerkshireGuy" <bd*****@yahoo.comwrote in message
news:11*********************@i42g2000cwa.googlegro ups.com...

Keitth - Actually I can access the Excel workbook as a recordset
without a linked table. I do so by creating and ADODB connection and
use Excel as the DSN.

I can provide air code if you'd like.

Ah, ADO ... it is evil and must be stopped ;-) Just kidding. Yes I'm always
keen to learn new stuff and I've never used ADO, please do post your code.

Regards,
Keith.

[BerkshireGuy]

Here it is:

Feel free to comment everyone. Good method?

Dim cn As New ADODB.Connection, RS As New ADODB.Recordset
Dim i As Integer
Dim strSourcePath, strSQL As String
strSourcePath = "C:\ExcelDocs\"
'---note: need semicolon at end of .xls; for ADO to Excel
strSourcePath = strSourcePath & "Book1.xls;"
RS.CursorLocation = adUseClient
'cn.Mode = adModeReadWrite
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strSourcePath & _
"Extended Properties=""Excel 8.0;HDR=NO;"""

strSQL = "SELECT * FROM [Sheet1$]"
RS.Open strSQL, cn, adOpenDynamic, adLockPessimistic
i = 3
With RS
.MoveFirst
.AbsolutePosition = i
Do While Not .EOF

'Other Code here
.MoveNext
Loop
End With
RS.Close
cn.Close

Keith Wilby wrote:

"BerkshireGuy" <bd*****@yahoo.comwrote in message
news:11*********************@i42g2000cwa.googlegro ups.com...

Keitth - Actually I can access the Excel workbook as a recordset
without a linked table. I do so by creating and ADODB connection and
use Excel as the DSN.

I can provide air code if you'd like.

Ah, ADO ... it is evil and must be stopped ;-) Just kidding. Yes I'm always
keen to learn new stuff and I've never used ADO, please do post your code.

Regards,
Keith.