5/2/2024 2:29:40 PM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss ADO specific questions for use with SalesLogix. View the code of conduct for posting guidelines.
|
|
|
|
Reading a range in Excel
Posted: 17 Dec 07 1:11 PM
|
I'm trying to read some data from a range in Excel. However, the standard range identifiers do not work, and causes the Saleslogix (7.0) client to crash with an Access Violation in vbscript.dll. First, is there any way for me to access a specific range, and second, is there a method I can use to cause the VBScript to fail gracefully and not bring down the client with it?
This code will work correctly:
dgPrices.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:\Data\Pricelists\TestList.xls;Extended Properties=Excel 8.0;"
dgPrices.SQL = "select * from [Sheet1$]"
However, this code is blowing up:
dgPrices.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:\Data\Pricelists\TestList.xls;Extended Properties=Excel 8.0;"
dgPrices.SQL = "select * from [Sheet1$A1:E5]"
|
|
|
|
Re: Reading a range in Excel
Posted: 18 Dec 07 3:26 AM
|
Does the Excel file have field-name headers in the first row? If so, you could specify HDR=Yes in the Extended Properties to limit the number of columns, and then you could say "SELECT TOP 5 F1, F2, F3, F4, F5 FROM [Sheet1$]" to limit the number of rows, hopefully without it blowing up in the process.
I've not tried the latter, however - I don't see why TOP 5 wouldn't work, though, it's standard syntax...?
For the "failing gracefully" part, if you're already using the usual "On Error" stuff and it still happens, there might not be a good solution for that - I've run into that myself on occasion. You might end up having to read the sheet into ListView via the COM API instead, or else dump it into a temporary table in the database. |
|
|
|
Re: Reading a range in Excel
Posted: 18 Dec 07 10:12 AM
|
I have had success treating well-formatted Excel files as a database using:
Sub GetWorksheetSchema(Path, Worksheet)
'connection object needed to connect to Excel/Access
Dim objConn
Set objConn = CreateObject("ADODB.Connection")
Dim strProvider
strProvider ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Path & _
";Extended Properties=""Excel 8.0;HDR=Yes;"""
objConn.Open strProvider
'treat the workbook as a database catalog, the catalog at the connection
Dim objWbkAsDatabase
Set objWbkAsDatabase = CreateObject("ADOX.Catalog")
objWbkAsDatabase.ActiveConnection = objConn
'get the catalogs tables, which in this case is the worksheets
Dim objTables
Set objTables = objWbkAsDatabase.Tables
'for each table/sheet in the catalog/workbook display the name
Dim objTable
objTable = objTables(Worksheet)
...
End Sub |
|
|
|
Re: Reading a range in Excel
Posted: 18 Dec 07 11:33 AM
|
Thanks for the help everyone. I think my issue was that I was attempting to work with the DataGrid directly.
Working with ADO Connection/Recordset objects instead, and then binding an open Recordset to the DataGrid for display, is working a lot better, and is matching expected behaviour. |
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|