Problem writing numbers and dates to spreadsheet using ADO

[Jesse Hogan]

When I write numbers, dates or currency values to an Excel file using
ADO (VB6) there is a little green mark at the top left of each column
indicating there is an error with the data. When I click on the cell I
get a small exclamation mark pops up to the left. I click on it and it
shows a drop down which provides the following selections:
"Number Stored as Text"
"Convert to Number"
"Help on this Error"
"Ignore Error:"
"Edit in Formula Bar"
"Error Checking Options"
"Show Formula Auditing Toolbar"

Apparently Excel thinks there is a problem with the data. But all I
have to do to correct the problem and get rid of the little green mark
at the top left corner of the cell is click in the formula bar and the
click out. So there really isn't a problem (the cell is formated as
"General" so numbers or dates should work as well as anything else).
Here is the code I use.

With objConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & strExcelFile & "; Extended
Properties=Excel 8.0"
.Open
End With

rs.Open "select * from [Sheet1$]", objConn, adOpenKeyset,
adLockOptimistic

mrsPrepaidQuotes.MoveFirst ' mrsPrepaidQuotes is an ado recordset
from a mssql2000 db
While Not mrsPrepaidQuotes.EOF
rs.AddNew
rs.Fields("OriginalOrderDate") = DateOnly(mrsPrepaidQuotes!
OriginalOrderDate) 'returns a data string "07/07/2006" - Has the
little green thing in corner
rs.Fields("OriginalS#") =
mrsPrepaidQuotes.Fields("OriginalS#").Value ' returns a letter and
numbers ' no prob
rs.Fields("Quote#") = mrsPrepaidQuotes.Fields("Quote#").Value '
returns a letter and numbers ' no prob
rs.Fields("CustomerNumber") =
mrsPrepaidQuotes.Fields("CustomerNumber").Value ' returns a number, as
green thing
rs.Fields("Amt") = FormatCurrency(mrsPrepaidQuotes!Amt) ' return
currency string "$100.00" also has green thing
rs.Fields("Amt w/Tax") =
FormatCurrency(mrsPrepaidQuotes.Fields("Amt w/Tax").Value) return
currency string "$100.00" also has green thing
mrsPrepaidQuotes.MoveNext
Wend
rs.Update

Thanks in advance.

 

[Guest]

the data is stored as text. When you edit it, it is reevaluated and stored
as number and thus since it is a number, excel is no longer concerned.

 

[MaC]

If we talk about numbers, I guess the green thing can be made by the Excel
error checking options.
Maybe just try to run your code after turning of this option, e.g.:
Application.ErrorCheckingOptions.NumberAsText = false

Regards
Mariusz

 

[Jesse Hogan]

MaC
You are right about why the little green triangle gets displayed. I
can turn this option off if I adjust the Error Checking options in
Excel. Since I am using ADO and not Excel Automation I can't do this:
Application.ErrorCheckingOptions.NumberAsText = false (technical
reasons prevent me from doing automation).
So this is weird, it would appear that I can't use ADO to write to an
Excel file numbers, dates or currencies without incurring that little
green triangle. I've looked all over the Internet for code examples of
how others have dealt with this and can't find anything. It wouldn't
bother me so much but I need this report to be automatically sent to
our client and I would rather them not see all these green little
triangles in almost all the cells.

Thanks for you help though

the data is stored as text. When you edit it, it is reevaluated and stored
as number and thus since it is a number, excel is no longer concerned.

--
Regards,
Tom Ogilvy

When I write numbers, dates or currency values to an Excel file using
ADO (VB6) there is a little green mark at the top left of each column
indicating there is an error with the data. When I click on the cell I
get a small exclamation mark pops up to the left. I click on it and it
shows a drop down which provides the following selections:
"Number Stored as Text"
"Convert to Number"
"Help on this Error"
"Ignore Error:"
"Edit in Formula Bar"
"Error Checking Options"
"Show Formula Auditing Toolbar"

Apparently Excel thinks there is a problem with the data. But all I
have to do to correct the problem and get rid of the little green mark
at the top left corner of the cell is click in the formula bar and the
click out. So there really isn't a problem (the cell is formated as
"General" so numbers or dates should work as well as anything else).
Here is the code I use.

With objConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & strExcelFile & "; Extended
Properties=Excel 8.0"
.Open
End With

rs.Open "select * from [Sheet1$]", objConn, adOpenKeyset,
adLockOptimistic

mrsPrepaidQuotes.MoveFirst ' mrsPrepaidQuotes is an ado recordset
from a mssql2000 db
While Not mrsPrepaidQuotes.EOF
rs.AddNew
rs.Fields("OriginalOrderDate") = DateOnly(mrsPrepaidQuotes!
OriginalOrderDate) 'returns a data string "07/07/2006" - Has the
little green thing in corner
rs.Fields("OriginalS#") =
mrsPrepaidQuotes.Fields("OriginalS#").Value ' returns a letter and
numbers ' no prob
rs.Fields("Quote#") = mrsPrepaidQuotes.Fields("Quote#").Value '
returns a letter and numbers ' no prob
rs.Fields("CustomerNumber") =
mrsPrepaidQuotes.Fields("CustomerNumber").Value ' returns a number, as
green thing
rs.Fields("Amt") = FormatCurrency(mrsPrepaidQuotes!Amt) ' return
currency string "$100.00" also has green thing
rs.Fields("Amt w/Tax") =
FormatCurrency(mrsPrepaidQuotes.Fields("Amt w/Tax").Value) return
currency string "$100.00" also has green thing
mrsPrepaidQuotes.MoveNext
Wend
rs.Update

Thanks in advance.