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
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:
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 (https://msdn.microsoft.com/en-us/library/office/ff196598.aspx)
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
Hi TD,
Could you please help on above request
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.
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.
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
Perhaps in your autofit section something like (again assuming the .csv is tabular) oXL = Excel Object, oWS=Curent worksheet
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()
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
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.
Hi TD,
Could you please help on above request.Still i'n not able to implement "Conditional Formatting" in Excel.
Thanks in Advance.
Perhaps you should consider Stan's suggestion and create a template.
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.
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