Results 1 to 10 of 16

Thread: CmdExcel and formatting cells as text

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #5
    Join Date
    Feb 2009
    Posts
    4,814

    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

Posting Permissions

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