Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: CmdExcel and formatting cells as text

  1. #1
    Join Date
    Feb 2009
    Location
    Cayman Islands
    Posts
    3,969

    Default CmdExcel and formatting cells as text

    I'm using the excellent CMDExcel to write worksheets for our accounts package. However there's on thing catching me out.

    Our AR account number is in the form nnnAnnn where the A represents the first letter of the account name. Unfortunately we have some accounts in the 105Ennn range. As you can imagine Excel treats that as scientific notation, 105E17 shows as 1050000000000000000000, or something like that.

    I've ploughed through CMDExcel and Excel12 packages and can't find how to set that column as text. Anybody done this?
    I should be on a beach ...

  2. #2
    Join Date
    Feb 2009
    Location
    SW Connecticut/NY area
    Posts
    9,181

    Default Re: CmdExcel and formatting cells as text

    That's one of the reasons I ended up writing a .Net dll to do it all in Excel rather than use CMDExcel. CMDExcel is good but there are a few things that it couldn't do and some of those things were important to me. And of course wrapped the dll in a DF wrapper...
    Bob Worsley
    203-249-2633
    rlworsley at gmail.com

    Do or do not. There is no try. — Yoda

  3. #3
    Join Date
    Feb 2009
    Posts
    5,467

    Default Re: CmdExcel and formatting cells as text

    I might be able to help. I will look tomorrow
    Success consists of going from failure to failure without loss of enthusiasm - Winston Churchill

  4. #4
    Join Date
    Feb 2009
    Location
    Cayman Islands
    Posts
    3,969

    Default Re: CmdExcel and formatting cells as text

    Quote Originally Posted by Focus View Post
    I might be able to help. I will look tomorrow
    please. My workaround is somewhat crude.

    send errorreport 'account on line 256 should be 105E17'
    I should be on a beach ...

  5. #5
    Join Date
    Feb 2009
    Location
    Brazil
    Posts
    5,445

    Default Re: CmdExcel and formatting cells as text

    Try adding a single quote ‘ in front of the value

    ‘157Ennn

    when feeding the cell value
    Samuel Pizarro

  6. #6
    Join Date
    Feb 2009
    Posts
    5,467

    Default Re: CmdExcel and formatting cells as text

    It's been a very long time since I've looked at this. we've done different interfaces over the years. COM, Writing XLSX directly and LibXL

    On the COM interface version I've found this code that uses TextToColumns to get rid of the little triangle in the corner for things like telephone numbers

    This may or may not help with your issue but it was the thing I had at the back of my mind that might be of use

    Code:
                    // Use TextToColums to solve the "Number as Text" errors
                    For iLoop2 from 1 to iMaxCols
                        Get ColumnNumberToLetter (iLoop2+iInsertOffsetFromFirstColumn) to sColumnLetter
                        // note you need to select the whole column rather than a range of cells otherwise any blank cells in range will show an error.
                        Get ComXlRange of hoWorksheet (sColumnLetter+":"+sColumnLetter) Nothing to vObj
    
    
                        If (IsComObject(vObj)) Begin
                            Set pvComObject of hoCells to vObj
                            // if the first cell in the column is empty it will raise an error
                            Move False to bDumyCell
                            Get ComXlItem of hoCells 1 1 to vObj
                            If (IsComObject(vObj)) Begin
                                Set pvComObject of (phoXLCell(Self)) to vObj
                                Get ComXlValue of (phoXLCell(Self)) OLEXLxlRangeValueDefault to sCellData
                                If (Length(sCellData)=0) Begin 
                                    // put a dummy value in the fisrt cell to prevent an error with ComXlTextToColumns
                                    Set ComXlValue of (phoXLCell(Self)) OLEXLxlRangeValueDefault to 1
                                    Move True to bDumyCell
                                End
                                Get ComXlSelect of hoCells to vVoid
                                Get ComXlTextToColumns of hoCells Nothing OLEXLxlDelimited OLEXLxlTextQualifierNone False True False False False False Nothing Nothing Nothing Nothing Nothing to vData
                                If (bDumyCell) Begin 
                                    Set ComXlValue of (phoXLCell(Self)) OLEXLxlRangeValueDefault to ""
                                End
                            End
                        End
                    Loop
    Code:
        // recursive function to calc the Excel letter reference from a given column number. Not set an upper limit but excel 2010 is 16384 (XFD)
        Function ColumnNumberToLetter Integer iCol Returns String
            Integer iAlpha iRemainder 
            String sLetters sNewLetters
            If (iCol>0) Begin 
                If (iCol < 27) Begin 
                    Move (Character(iCol+64)) to sLetters
                End
                Else Begin 
                    Move (Mod(iCol,26)) to iRemainder
                    Move (iCol/26) to iAlpha
                    If (iRemainder=0) Begin 
                        Move 26 to iRemainder
                        Decrement iAlpha
                    End
                    Get ColumnNumberToLetter iAlpha to sNewLetters
                    Move (sNewLetters+Character(iRemainder+64)) to sLetters
                End
            End
            Function_Return sLetters
        End_Function
    Last edited by Focus; 11-Mar-2020 at 04:33 AM. Reason: added function
    Success consists of going from failure to failure without loss of enthusiasm - Winston Churchill

  7. #7
    Join Date
    Feb 2009
    Posts
    5,467

    Default Re: CmdExcel and formatting cells as text

    Note the FieldInfo (about 4 from the end) that I have not used takes XlColumnDataType which says how it should be parsed which might be of use to you as one of them is Text
    Success consists of going from failure to failure without loss of enthusiasm - Winston Churchill

  8. #8
    Join Date
    Feb 2009
    Location
    SW Connecticut/NY area
    Posts
    9,181

    Default Re: CmdExcel and formatting cells as text

    That works but it can be a pain to deal with on occasion
    Bob Worsley
    203-249-2633
    rlworsley at gmail.com

    Do or do not. There is no try. — Yoda

  9. #9
    Join Date
    Feb 2009
    Location
    Cayman Islands
    Posts
    3,969

    Default Re: CmdExcel and formatting cells as text

    Quote Originally Posted by Samuel Pizarro View Post
    Try adding a single quote ‘ in front of the value

    ‘157Ennn

    when feeding the cell value
    that's what I used to do when writing as csv, but the target program won't eat them (and I don't control that part of the process)
    I should be on a beach ...

  10. #10
    Join Date
    Feb 2009
    Location
    Cayman Islands
    Posts
    3,969

    Default Re: CmdExcel and formatting cells as text

    Quote Originally Posted by Focus View Post
    It's been a very long time since I've looked at this. we've done different interfaces over the years. COM, Writing XLSX directly and LibXL..................
    that might give me a start, thank you, I will commence excavation.

    Do you recall what version of Excel you based that on? The A1 interface went away in favour of 1 1, I believe.
    I should be on a beach ...

Page 1 of 2 12 LastLast

Posting Permissions

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