Formats don't apply on Values during automation from VB6
Formats don't apply on Values during automation from VB6
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Formats don't apply on Values during automation from VB6

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     

    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.


  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    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.

  3. #3
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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



  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  5. #5
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.



  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  7. #7
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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




User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com