Code_Mania
asked on
Forcing Driver To Read Data From Excel
I regularly use the OLEDB and ODBC connections to access Excel. The problem I always run into is that unless the user has a registry change accessing mixed content in rows is problematic.
Example connection: "string Provider=Microsoft.Jet.OLE
Registry setting: [HKEY_LOCAL_MACHINE\SOFTWA
Is there a way to force Excel to read the contents of a sheet as text or more accurately to scan all rows for the content type via the driver. I can't change the content of the XL sheet or force a registry key change on some users and this solution needs to be across all machines. Maxscanrows=0 has no effect on the connection string and Microsoft themselves say that it hasn't worked since ADO 2.1
Any ideas?
Example connection: "string Provider=Microsoft.Jet.OLE
Registry setting: [HKEY_LOCAL_MACHINE\SOFTWA
Is there a way to force Excel to read the contents of a sheet as text or more accurately to scan all rows for the content type via the driver. I can't change the content of the XL sheet or force a registry key change on some users and this solution needs to be across all machines. Maxscanrows=0 has no effect on the connection string and Microsoft themselves say that it hasn't worked since ADO 2.1
Any ideas?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks John, sadly though I need to use the driver to do the work as I can't guarantee that the client has Excel on their machine.
Can you pre-process the spreadsheet and save it as a .csv file? The text OLEDB client will handle it then.
ASKER
Again no, generally information is fed down from a third party in an Excel sheet. The third party outputs the sheet in a fixed format, in my case trades in futures, options etc. bank transactions and balances, currency exchange rates. These are defined standards from the organisations that provide the feed.
C) Make Excel convert everything into text, I have a little gadget that does this. It is a macro I sometimes use to force zip codes into text. I open a spreadsheet, highlight the data I want to be text, and then run this macro. It replaces any value with the Excel =T("blah") function. It is coded specifically for zip codes, so you might have to change it some.
Sub MakeZipStrings()
'make all the selected values into formulas like =T("15212") to fake Excel into reporting them as string.
Dim w As Excel.Worksheet
Dim r As Integer
Dim c As Integer
Dim SelRange As Excel.Range
If TypeName(Application.Selec
MsgBox "select the cells to modify"
Exit Sub
End If
Set SelRange = Application.Selection
For r = 1 To SelRange.Rows.Count
For c = 1 To SelRange.Columns.Count
'If Left(SelRange.Cells(r, c).Formula, 3) <> "=T(" Then
If SelRange.Cells(r, c).NumberFormat <> "General" Then
SelRange.Cells(r, c).NumberFormat = "General"
End If
If Len(SelRange.Cells(r, c)) = 9 And InStr(1, SelRange.Cells(r, c), "-") <= 0 Then
SelRange.Cells(r, c).Formula = "=T(""" & Left(Replace(SelRange.Cell
ElseIf Len(SelRange.Cells(r, c)) = 4 Then 'missing leading 0
SelRange.Cells(r, c).Formula = "=T(""" & Format(Replace(SelRange.Ce
Else
SelRange.Cells(r, c).Formula = "=T(""" & Replace(SelRange.Cells(r, c).Text, "'", "") & """)"
End If
'The replace is in case someone put' in front
'End If
Next c
Next r
MsgBox "Done"
End Sub