L
liam.caffrey
Hi,
I am running SS2000 SP4 and loading Excel 2003 SP2 files using a
VB.NET 1.1 console app.
It uses the following OLEDB connection parameters
Product = 'Excel'
Provider = 'Microsoft.Jet.OLEDB.4.0'
ProviderString Thing = 'Excel 8.0; HDR=NO; IMEX=1;'
In the registry... in the right place!
ImportMixedTypes = "Text"
TypeGuessRows = "0"
Also...
Tools | Options (Calculations) Precision As Displayed checkbox is Off
(I don't really want to have to switch it on. The Excel file is a
controlled document)
It works very nicely except for...
I have a percent field that is a formula that is displayed to 2
decimal places. There is no rounding around the formula. (It needs to
be like this because averages are calculated from this field, among
others, i.e. more accurate average)
1. The actual value using a calculator is
0.079446808510638297872340425531915 and it displays correctly as 7.94%
2. Copying the cell contents and Paste Special (Value) in a new sheet
results in 0.079446809
Yet, when I suck the data into a datatable in VB.NET or read it from
QA using a linked server, I get a value of 7.95%. In other words the
OLEDB driver is doing some stuff.
The only way I can turn 0.079446809 or 7.9446% into 7.95% is to round
it to 3 and then round it to 2.
What is going on here or am I missing something simple?
Regards
Liam
I am running SS2000 SP4 and loading Excel 2003 SP2 files using a
VB.NET 1.1 console app.
It uses the following OLEDB connection parameters
Product = 'Excel'
Provider = 'Microsoft.Jet.OLEDB.4.0'
ProviderString Thing = 'Excel 8.0; HDR=NO; IMEX=1;'
In the registry... in the right place!
ImportMixedTypes = "Text"
TypeGuessRows = "0"
Also...
Tools | Options (Calculations) Precision As Displayed checkbox is Off
(I don't really want to have to switch it on. The Excel file is a
controlled document)
It works very nicely except for...
I have a percent field that is a formula that is displayed to 2
decimal places. There is no rounding around the formula. (It needs to
be like this because averages are calculated from this field, among
others, i.e. more accurate average)
1. The actual value using a calculator is
0.079446808510638297872340425531915 and it displays correctly as 7.94%
2. Copying the cell contents and Paste Special (Value) in a new sheet
results in 0.079446809
Yet, when I suck the data into a datatable in VB.NET or read it from
QA using a linked server, I get a value of 7.95%. In other words the
OLEDB driver is doing some stuff.
The only way I can turn 0.079446809 or 7.9446% into 7.95% is to round
it to 3 and then round it to 2.
What is going on here or am I missing something simple?
Regards
Liam