Forum Discussion

hyper111's avatar
hyper111
Contributor
11 years ago

Writing to Excel two colored words.


Hi,



I would like to know is it possible to write to cell two colored word ?

ex. WO (red) RD (black)



Thanks,


  • googleid_118035's avatar
    googleid_118035
    11 years ago
    Hi Paul,



    Now we have an Excel file with an above macro which will format letter in row number 10 and cloumn number 4.



    We will create following scripts in Testcomplete using VB scripts.

    Which will write letter "WORD" into the file and run the text format macro.




    Sub Test1()


    Call Insert_Val("C:\Dev_Chaminda\Book1.xls", "Sheet6", 4, 10, "WORD")


    Call Open_Excel_File("C:\Dev_Chaminda\Book1.xls", oExcel, oBook)


    macro_name1   = "Macro1"


    oExcel.Run(oBook.Name & "!" + macro_name1)


    oBook.Close True


    oExcel.Quit


    End Sub


    Function Insert_Val(file, sheet, x, y, textval)


    Set b = CreateObject("Excel.Application")


    b.DisplayAlerts = False


    Set c = b.Workbooks.Open(file)


    c.Worksheets(sheet).Cells(y, x).Value = textval 


    c.Save


    c.Close True 


    b.Quit


    End Function


    Function Open_Excel_File(App, oExcel, oBook)


    Set oExcel = CreateObject("Excel.Application")


    oExcel.DisplayAlerts = False  


    Set oBook = oExcel.Workbooks.Open(App)


    End Function

  • googleid_118035's avatar
    googleid_118035
    Contributor
    11 years ago



    Hi Paul,



    Here is a small macro which will set the first two letter colour into red and second two letter colour into black.

    ---------


    Sub Macro1()


    'format first two letters


    Worksheets("Sheet6").Cells(10, 4).Characters(Start:=1, Length:=2).Font.Color = RGB(255, 0, 0)


    'format second two letters


    Worksheets("Sheet6").Cells(10, 4).Characters(Start:=3, Length:=2).Font.Color = RGB(0, 0, 0)


    End Sub



    ---------



    Letters are in "Sheet6", row number 10 and cloumn number 4.




  • hyper111's avatar
    hyper111
    Contributor
    11 years ago
    I was trying to write first part of word and then second, cells content was changing whole and in cell was only second part. How can I edit cells content?
  • googleid_118035's avatar
    googleid_118035
    Contributor
    11 years ago
    Hi Paul,



    Now we have an Excel file with an above macro which will format letter in row number 10 and cloumn number 4.



    We will create following scripts in Testcomplete using VB scripts.

    Which will write letter "WORD" into the file and run the text format macro.




    Sub Test1()


    Call Insert_Val("C:\Dev_Chaminda\Book1.xls", "Sheet6", 4, 10, "WORD")


    Call Open_Excel_File("C:\Dev_Chaminda\Book1.xls", oExcel, oBook)


    macro_name1   = "Macro1"


    oExcel.Run(oBook.Name & "!" + macro_name1)


    oBook.Close True


    oExcel.Quit


    End Sub


    Function Insert_Val(file, sheet, x, y, textval)


    Set b = CreateObject("Excel.Application")


    b.DisplayAlerts = False


    Set c = b.Workbooks.Open(file)


    c.Worksheets(sheet).Cells(y, x).Value = textval 


    c.Save


    c.Close True 


    b.Quit


    End Function


    Function Open_Excel_File(App, oExcel, oBook)


    Set oExcel = CreateObject("Excel.Application")


    oExcel.DisplayAlerts = False  


    Set oBook = oExcel.Workbooks.Open(App)


    End Function

  • hyper111's avatar
    hyper111
    Contributor
    11 years ago
    Thanks a lot Dev !



    I was looking for only ideas or concepts. It is very helpful solution :)
  • hyper111's avatar
    hyper111
    Contributor
    11 years ago
    I'm using without excel macro:



          sheet.Cells(row, col).Interior.Color = RGB(168,8,8)

          sheet.Cells(row, col).Font.Color = RGB(255,255,255)

          sheet.Cells(row, col) = "Fail " &time

          sheet.Cells(row, col).Characters(5).Font.Size = 1

          sheet.Cells(row, col).Characters(5).Font.Color = RGB(168,8,8)



    Each character after fifth will be changed. In this case datetime will be invisible.