R
rvExcelTip
Some may consider this an ADO item, but it concerns Excel users as well
Here's the problem (and a partial solution):
An excel worksheet tries to extract and merge tabular data from anothe
worksheet located in another workbook(that may be opened or not).
Extraction is based on variable criteria. So I used ADO to perform th
operation.
The Connection string looks like:
-Dim strConnection as String
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= & strExcelWorkbook & ";" & _
"Extended Properties=""Excel 8.0;HDR=YES"";"-
where strExcelWorkbook is the path and filename to the externa
workbook.
This is the good news, because that string works (exactly as it i
written)
So what's the problem? That application may run on a compute
different than the one it is written on, probably (certainly) with
diiferent excel version (2000 , 2002, 2003(?)). So I tried to get th
version number dynamically, which is easy to do wit
-Application.Version-. Unfortunately(?) that property returns 10.0 i
the case of XP and substituting the 8.0 in the connection string wit
10.0 (the actual version) results in a runtime error. The fact that i
MUST be 8.0 is poorly documented somewhere in KB257819.
Can't MS get its different divisions to talk to each other?? It i
really frustrating. It surely can't be that difficult for MS?
Now the burden is on the user: I nowhere found a warranty that th
number will stay 8.0 in the future (it is 5.0 for excel95 = v7.0 o
excel). Therefore I have a potential time bomb in my application
Which I consider unacceptable.
Today I "solved" the problem by hard coding a conversion table (array
in the application. If application.version is such then substitut
that. Obviously future versions cannot be included that way.
Does somebody have a better idea
Here's the problem (and a partial solution):
An excel worksheet tries to extract and merge tabular data from anothe
worksheet located in another workbook(that may be opened or not).
Extraction is based on variable criteria. So I used ADO to perform th
operation.
The Connection string looks like:
-Dim strConnection as String
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= & strExcelWorkbook & ";" & _
"Extended Properties=""Excel 8.0;HDR=YES"";"-
where strExcelWorkbook is the path and filename to the externa
workbook.
This is the good news, because that string works (exactly as it i
written)
So what's the problem? That application may run on a compute
different than the one it is written on, probably (certainly) with
diiferent excel version (2000 , 2002, 2003(?)). So I tried to get th
version number dynamically, which is easy to do wit
-Application.Version-. Unfortunately(?) that property returns 10.0 i
the case of XP and substituting the 8.0 in the connection string wit
10.0 (the actual version) results in a runtime error. The fact that i
MUST be 8.0 is poorly documented somewhere in KB257819.
Can't MS get its different divisions to talk to each other?? It i
really frustrating. It surely can't be that difficult for MS?
Now the burden is on the user: I nowhere found a warranty that th
number will stay 8.0 in the future (it is 5.0 for excel95 = v7.0 o
excel). Therefore I have a potential time bomb in my application
Which I consider unacceptable.
Today I "solved" the problem by hard coding a conversion table (array
in the application. If application.version is such then substitut
that. Obviously future versions cannot be included that way.
Does somebody have a better idea