Hello,
My program exports data from lotus documents into an excel worksheet.
Some of the data which is put into cells is strings and some are strings of numbers. I need the strings of numbers to be converted into proper numerical values in order to allow some formulas to do their job.
I figured that setting proper formating in some cells will do the trick. But unfortunately there is no autoformating and when the data is put into worksheet I got errors that in cels that are supposed to keep numerical values there are strings. And when I click on this error they are convertet to numerical values.
So my question is how do this in lotus script using OLE (I have got reference to proper excel object) ?
Subject: Excel
Some clarification first: by ‘the string of numbers’ do you mean you have values in your Notes documents that are numbers stored as text. i.e in a field of type text you have the value “1400.00” OR that you have many numerical values in a string stored in a field, like “24, 4555, 98.6”.
In the few times I’ve exported view or document values into Excel, as long as the Notes data was already typed the way you want it, text or number, Excel know how to format it on the sheet. So check to see if there’s any opportunity to convert the text into numerical values before placing it in Excel, either in your script with Cint or maybe on the form itself - (does the field have to be a text field if you’re pulling from document items?). If the ‘text’ is multi-value list then that’s a different issue. HTH.
Subject: RE: Excel
Sorry for mistake. I meant string of digits like "22.4"I know that I can convert it in Lotus script but that is not an option. When i place values in excel cels I never know what type it is … it can be either common string or the numerical one.
Its up to cell format settings into what it should be converted.
Thats why I want to force it programmaticly to auto format itself.
(I know that there is AutoFormat method in Range … but it doesn’t want to cooperate.
Subject: RE: Excel
If You could provide the lines where the problem is it would be easier for someone to help You.I myself never used AutoFormat, but if You could do some simple checking (if the value is str or int) or atleast if You know which columns need to be numbers maybe try:
dim ExcelSheet as Variant 'Your worksheet object
…
ExcelSheet.Columns(“A:A”).Select
ExcelSheet.Selection.NumberFormat = “0.0” 'numbers/int
or
ExcelSheet.Range(“A1:F28”).Select
ExcelSheet.Selection.NumberFormat = “@” 'text/string
hope it helps