Formats don't apply on Values during automation from VB6

daleyman

Board Regular
Joined
Mar 28, 2002
Messages
167
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.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top