How to add color format to excel file

[BVVNAIDU937]

Hi Winbatch Tech Team,

We are doing one small project for convert csv to excel file,by using the below program am able to convert but i need some color format in that  converted excel file.could some one help me please.

CSV to Excel Program.

;////////////////////////////////////////////////////////////////////////
;//  WINBATCH - CSV File To Excel - Could be used with LAFF databases  //

; some standard pre-formats to practice with
xlRAF3DEffects1    = 13
xlRAF3DEffects2    = 14
xlRAFAccounting1   = 5
xlRAFAccounting2   = 6
xlRAFAccounting3   = 7
xlRAFAccounting4   = 17
xlRAFClassic1      = 1
xlRAFClassic2      = 14
xlRAFClassic3      = 2
xlNormal           = -4143
cCSV               = AskFileName("Select Comma Separated File",".\","CSV Files|*.csv|","*.csv",1)
cXLS               = StrReplace( StrUpper(cCSV), ".CSV",".XLS")
cText              = StrCat("Transferring %cCSV%",@CRLF,"TO %cXLS%")

IF FileExist(cXLS) Then  FileDelete(cXLS)
BoxOpen("Creating Excel WorkBook",cText)

oAPP               = ObjectOpen( "Excel.Application" )
oAPP.Visible       = @FALSE
oAPP.DisplayAlerts = @FALSE
oWK                = oAPP.workbooks

oWK.open(cCSV)
oACT               = oAPP.ActiveWorkbook
oWKS               = oAPP.Worksheets
oWS                = oAPP.Worksheets(1)

oWS.Activate
gosub AutoFit
oACT.SaveAs ( cXLS,xlNormal  , , , , ,@True)
ObjectClose(oWS)
oACT.Close()
ObjectClose(oACT)
ObjectClose(oWK)
ObjectClose(oAPP)
BoxShut()
IF FileExist(cXLS) Then  Message("Conversion Successful","%cXLS% Created.")
Exit

:AutoFit
oCell              = oWS.Cells(1,1)
oRegion            = oCell.CurrentRegion
oCols              = oRegion.Columns
oCols.AutoFit
oCell              = oWS.UsedRange
oCell.AutoFormat(:: Format=xlRAFClassic1)

ObjectClose(oRegion)
ObjectClose(oCell)
Return

[td]

There are plenty of VBA examples of cell coloration on the Web. VBA is usually a straight forward conversion to  WIL syntax. Here's a very crude code snippet that sort of shows one approach:

Code (winbatch) Select Expand

strXLS = "C:\Projects\Automation\RegTest\TestData\":"Cat320.xls"
objExcel = ObjectCreate("excel.application" )
objExcel.Visible          = 1 
objExcel.ScreenUpdating   = 1   
objExcel.UserControl      = 1
objExcel.DisplayAlerts    = 0
objExcel.Workbooks.Open(strXLS )

objWS = objExcel.Workbooks(1).Worksheets(1)
objWS.Activate()
xlSolid = 1
RangeStr = "A1:Z100"                           
objWS.Range(RangeStr).EntireColumn.Autofit
objWS.Range(RangeStr).Interior.ColorIndex = 3    ; Red
objWS.Range(RangeStr).EntireColumn.Autofit   
objWS.Range(RangeStr).Interior.Pattern = xlSolid
objExcel.ActiveWorkBook.Close(::savechanges= @False)
objExcel.quit
objExcel = 0

Here's a link to the MSFT documentation for the "interior" object:
https://msdn.microsoft.com/en-us/library/office/ff196598.aspx

[BVVNAIDU937]

Thanks for your support TD,

Could you please tell me where we can insert the above color code in below mentioned program.I'm bit confusing where i have to change.

My Program:

#DefineSubroutine udfCSVtoXLS(cCSV)
;////////////////////////////////////////////////////////////////////////
;//  WINBATCH - CSV File To Excel - Could be used with LAFF databases  //

; Some standard pre-formats to practice with
xlRAF3DEffects1    = 13
xlRAF3DEffects2    = 14
xlRAFAccounting1   = 4
xlRAFAccounting2   = 5
xlRAFAccounting3   = 6
xlRAFAccounting4   = 17
xlRAFClassic1      = 1
xlRAFClassic2      = 2
xlRAFClassic3      = 3
xlNormal           = -4143
;cCSV               = AskFileName("Select Comma Separated File",".\","CSV Files|*.csv|","*.csv",1)
cXLS               = StrReplace(cCSV, ".CSV",".xls")
cXLS               = StrReplace(cCSV, ".csv",".xls")
cText              = StrCat("Transferring %cCSV%", @CRLF, "to %cXLS%.")

ErrorMode(@Off)
oAPP               = ObjectOpen("Excel.Application")
ErrorMode(@Cancel)
If oAPP==0 || oAPP==@False Then
   Display(2, ApplName, "Excel 2003/2007 is not installed. Cannot export to .xls.")
   BoxShut()
   Return
Endif

If FileExist(cXLS)==@false Then
   ErrorMode(@Off)
   ChkA=FileDelete(cXLS)
   ErrorMode(@Cancel)
   If ChkA==@False Then
      Display(2, ApplName, "%cXLS% cannot be created. Check access rights or file may be open.")
      Return
   Endif
Endif

Display(2, ApplName, "Creating Excel WorkBook %cText%")

oAPP.Visible       = @FALSE
oAPP.DisplayAlerts = @FALSE
oWK                = oAPP.workbooks

oWK.open(cCSV)
oACT               = oAPP.ActiveWorkbook
oWKS               = oAPP.Worksheets
oWS                = oAPP.Worksheets(1)

oWS.Activate
gosub AutoFit

oACT.SaveAs (cXLS, xlNormal, , , , ,@True )
ObjectClose(oWS)
oACT.Close()
ObjectClose(oACT)
ObjectClose(oWK)
ObjectClose(oAPP)
BoxShut()
If FileExist(cXLS)==@True Then Display(2, ApplName, "Conversion successful: %cXLS% created.")
Return


:AutoFit
oCell              = oWS.Cells(1,1)
oRegion            = oCell.CurrentRegion
oCols              = oRegion.Columns
oCols.AutoFit
oCell              = oWS.UsedRange
oCell.AutoFormat(:: Format=xlRAFClassic2)
ObjectClose(oRegion)
ObjectClose(oCell)
Return
#EndFunction


#DefineSubroutine udfSortXLS()
file = "%Temp%\udfSortXLS.log"
xlAscending = 1
xlYes =1
xlTopToBottom = 1
xlSortNormal = 0
= 1

objExcel = ObjectCreate("Excel.Application")
objExcel.DisplayAlerts = @FALSE
objExcel.visible = @TRUE

objExcel.WorkBooks.Open(file)
objWork_Sheet =  objExcel.Activeworkbook.ActiveSheet


objWork_Sheet.Columns("C:C").Select
objWork_Sheet.Range("A1:E666").sort(:: Key1=objWork_Sheet.Range("b1"), Order1=xlAscending, Header=xlYes, OrderCustom=1, MatchCase=@FALSE, Orientation=xlTopToBottom, DataOption1=xlSortNormal )


Pause("Notice", "Sorted Ascending based on Column B")


objRangecol = 0
objRange = 0
objWork_Sheet = 0
objExcel.WorkBooks.Close()
objExcel.Quit()
objWork_Sheet = 0
objExcel = 0
Return

[BVVNAIDU937]

Hi TD,

Could you please help on above request

[td]

You need to access the Interior property  and it is a property of the Range object so, logically, you could use it when you have the Range object available.  The Cells method simply returns a range of all the cells in the spread sheet and you directly access the Range object in another place.   Some where around either of those locations would make sense. 

[stanl]

If you are loading the .csv data into a new sheet as a table then want to assign a pre-exisiting format such as xlRAF3DEffects1, first access the .usedrange property then assign the format, otherwise as Tony replied use the .interior property.

[BVVNAIDU937]

Hi Stanl,

Could you please tell me the Pre-existing excel formats ,so that i can use those in above code and could you please help me to where i could changes in above code according to TD suggestion.

Thanks for your valuable support 

[stanl]

Perhaps in your autofit section something like (again assuming the .csv is tabular) oXL = Excel Object, oWS=Curent worksheet

Code (WINBATCH) Select Expand


oWS.UsedRange.Select()
oXL.Selection.Font.Name = 'Tahoma'
oXL.Selection.Font.Size = 9
oXL.Selection.Font.Bold = @True
oWS.UsedRange.Columns.Autofit()
oWS.ListObjects.Add(:1,oWS.UsedRange, , 1).Name ="MyTable"
oWS.Range("MyTable[#All]").Select
oWS.ListObjects("MyTable").TableStyle = "TableStyleMedium2"
oXL.ActiveWindow.DisplayGridlines = @False
oWS.Cells(1,1).Select()


[BVVNAIDU937]

Hi TD,

By using your give excel code is working fine but i'm not able to insert the "Conditional Formatting" feature in above code and i tried Microsoft example VBA scripts,couldn't able to write the code. Please find the below winbatch code.

Could you please help me

File= "C:\LOGS\":"ServerSpace.xls"
objExcel = ObjectCreate("excel.application" )
objExcel.Visible          = 0
objExcel.ScreenUpdating   = 1   
objExcel.UserControl      = 1
objExcel.DisplayAlerts    = 0
xlCellTypeAllFormatConditions = -4172
objExcel.Workbooks.Open(File)
objWS = objExcel.Workbooks(1).Worksheets(1)
objWS.Activate()
xlSolid =1
RangeStr1= "A1:G1"
RangeStr2 = "A2:G2"
RangeStr3 = "A3:G3"
RangeStr4= "A3:G3" 
objWS.Range(RangeStr1).EntireColumn.Autofit
objWS.Range(RangeStr1).Font.Name = "Calibri"
objWS.Range(RangeStr1).Font.Size = "12"
objWS.Range(RangeStr2).Font.Name = "Calibri"
objWS.Range(RangeStr2).Font.Size = "12"
objWS.Range(RangeStr3).Font.Name = "Calibri"
objWS.Range(RangeStr3).Font.Size = "12"
objWS.Range(RangeStr1).Font.FontStyle = " "
objWS.Range(RangeStr1).Interior.ColorIndex =4
objWS.Range(RangeStr1).Font.ColorIndex =1
objWS.Range(RangeStr2).Interior.ColorIndex =32
objWS.Range(RangeStr2).Font.ColorIndex =2
objWS.Range(RangeStr3).Interior.ColorIndex =32
objWS.Range(RangeStr3).Font.ColorIndex =2
objWS.Range(RangeStr1).EntireColumn.Autofit   
objWS.Range(RangeStr1).Interior.Pattern = xlSolid
objWS.Range(RangeStr2).Interior.Pattern = xlSolid
objWS.Range(RangeStr3).Interior.Pattern = xlSolid
objExcel.ActiveWorkbook.Save()
objExcel.quit
objExcel = 0

[stanl]

Applying 'Conditional' formatting from scratch can be a bit difficult. I have always found it easier to create a template and set the formatting in the selected sheet or sheets, then just fill in the data and refresh.

[BVVNAIDU937]

Hi TD,

Could you please help on above request.Still i'n not able to implement "Conditional Formatting" in Excel.

Thanks in Advance.

[td]

Perhaps you should consider Stan's suggestion and create a template.

[stanl]

Another reason to consider a template is just to avoid re-writing code. I use WB for the compiler and have EXE's in multiple places. Almost everything lately involves working with Excel, and it is a big NO GO to have to re-write, re-compile and replace an EXE for the frequent requested changes involving formatting - especially when working with slicers, linked data or powerpivot. 

[mattcarl]

I encountered this thread while tracking down another issue.
Thought others mind find it valuable.

The example given saves your file with an older *.xls  extension.
Inherent to that Excel standard, only 65536 rows are allowed.
I wanted/needed to output more... and thus needed the extension *.xlsx   

There is an easy solution:

Add a new variable called  "xlWorkbookDefault"  and set to = 51
xlNormal           = -4143
xlWorkbookDefault  = 51     ;    Workbook default   


Change your value for cXLS to have then longer extension
cXLS               = StrReplace( StrUpper(cCSV), ".CSV",".XLSX")


Change the value in you save as line...  remove xlNormal and replace with xlWorkBookDefault  (or value 51)
oACT.SaveAs ( cXLS, xlWorkBookDefault, , , , ,@True )   



Thats it.....  find other tidbits at: 
https://docs.microsoft.com/en-us/office/vba/api/Excel.XlFileFormat