Formats don't apply on Values during automation from VB6

[daleyman]

I am filling an excel spreadsheet with values and formats (using .value and .numberformat) from vb6 using MS Excel 9.0 Object Library.

The values and formats are applied to the cells, but the value does not become formatted with the specified format. If you go into each cell manually and press enter, each value becomes formatted.

Is there a function i can use to rectify this. I have tried: range("F3").parse and this seems to work although sometimes cuts the values short if plain text with .numberformat="@".

I have programmed Excel VBA and VB6 for many years, and do not see any obvious mistake on my part, am assuming it is a bug in the object library.

Thanks.

[daleyman]

also, have problems with using:

.range(cells(x1,y1),cells(x2,y2)).blablabla

does anybody else experience these problems with the excel 9.0 object library or is it just me? The problem in my first note is my most desperate. Thanks.

[Dave Hawley]

Hi

Are you using the numberformat="@" in all the cells as this is a Text format and will cause problems with numbers.

You could try using:

Columns("F:F").Calculate

To force a recalculation, which what happens when you re-enter the number. But I feel you problems lies in the original format you are using.

What can't you do with:
range(cells(x1,y1),cells(x2,y2)).blablabla

[daleyman]

Thanks Dave, but i feel there are inherent problems with the coding of the dll.

When I use range(cells(x1,y1),cells(x2,y2)) i get the following runtime:

Routine Error '1004':
Method 'Cells' of object '_Global' failed.

...i have a workaround for this anyhow, that works okay, where i put

range(rangename(x1,x2,y1,y2)) ...and use function rangename to calculate the string required to make the statement work. messy but doesn't bother me.

The other problem, however, is more taxing as i do not have a workaround. I have just tested .Calculate and it doesn't do it for me. Basically, I have SQL backend giving output in the form:

Rowx : integer
Colx : integer
Valx : varchar(50) string
Formatx : varchar(50) string

and so a (what should be) simple VB6 routine takes this output and populates the spreadsheet with the following code:

xlsheet.cells(rowx,colx).value=data.valx
xlsheet.cells(rowx,colx).numberformat=data.formatx

where values tie up with the formats (which I have tested using "@" with text values, "0.00" with numericals, "d-mmm-yy" with date values). Now the formats and values both hit the cells, as when you open the sheet up manually and hit enter on each cell the data gets formatted. My problem is i don't know how to do this programmatically.

Probably sounds silly, but .parse works except for slight bug that it abbreviates some text strings. Do you know what .parse is and how it works (i do not have help files, or at least not until the office opens again on Tuesday!). It may be easiest to adjust this so that it does the job.

Thanks again, D.

[Dave Hawley]

D, when you get the Rutime error, what are values are x1,y1 etc holding, They must be Integers.


Are you certain that you numeric cells are being formatted as numbers. before entering the number check it's cell Format, I wouldn't be suprised if they are Text. You may also be better off formatting the entire Column as "General" and letting Excel decide.

[daleyman]

just basic test using

.range(cells(1,1),cells(2,2)).font.italic=true

fails (but 1 time in ten it will work!).



Have tried the format/value insertion in various ways. The workbook is new so opens with default general, and i have tried to do format then value, or value then format, or even format then value then format again, but to no avail... as i say .parse is the only thing that seems to come close...

does anyone have help files & can tell me what .parse does???

thanks once again, D.

[Dave Hawley]

Hi D

Here is the VBE help on Parse

Parses a range of data and breaks it into multiple cells. Distributes the contents of the range to fill several adjacent columns; the range can be no more than one column wide.

Syntax

expression.Parse(ParseLine, Destination)

expression Required. An expression that returns a Range object.

ParseLine Optional Variant. A string that contains left and right brackets to indicate where the cells should be split. For example, "[xxx][xxx]" would insert the first three characters into the first column of the destination range, and it would insert the next three characters into the second column. If this argument is omitted, Microsoft Excel guesses where to split the columns based on the spacing of the top left cell in the range. If you want to use a different range to guess the parse line, use a Range object as the ParseLine argument. That range must be one of the cells that's being parsed. The ParseLine argument cannot be longer than 255 characters, including the brackets and spaces.

Destination Optional Variant. A Range object that represents the upper-left corner of the destination range for the parsed data. If this argument is omitted, Microsoft Excel parses in place.

END OF EXCEL HELP

You say that x1,y1 are just basic text? They should be Integers.

2 Other 'maybe' are:

Columns(1) = Columns(1).Value

With Sheets("Sheet1").Range("A1", Range("A65536").End(xlUp))
.SpecialCells(xltypenumbers).NumberFormat = "0.00"
End With