All Things WinBatch > WebBatch

How to insert the Excel formulas in Winbatch Code

(1/1)

BVVNAIDU937:
Hi Team,

I have  done some code implements in existing Winbatch Excel code and still i need some more adjustments like add some other formulas and  conditional formatting formulas.Could some one help me to how to add those formulas in existing code.

Winbatch Excel Code :

File=
objExcel = ObjectCreate("excel.application" )
objExcel.Visible          = 0
objExcel.ScreenUpdating   = 1   
objExcel.UserControl      = 1
objExcel.DisplayAlerts    = 0
objExcel.Workbooks.Open(File)
objWS = objExcel.Workbooks(1).Worksheets(1)
objWS.Activate()
xlSolid =1
xlContinuous = 1
xlHairline = 1
xlMedium = -4138
xlThick = 4
xlThin = 2
xlCenter = 3
xlEdgeLeft =7
xlEdgeRight=10
xlEdgeTop=8
xlEdgeBottom=9
xlCenterAcrossSelection=-4108
xlCellValue = 1
xlGreater   = 10
;xlLess = 6
;xlCellValue
;xlLow  =  -4134
;xlHigh = -4127
;xlBetween = 1
start = 10
stop  = 15
xlFormula = "=SUM(100-F3:F7)"
RangeStr1 = "A1:G1"
RangeStr2 = "A2:G2"
RangeStr3 = "A3:G3"
RangeStr4 = "A4:G4"
RangeStr5 = "A5:G5"
RangeStr6 = "A6:G6"
RangeStr7 = "B3:B6"
RangeStr8 = "A1:A6"
;RangeStr9 = "G3:G6"
RangeStr10 = "G3:G7"
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 = "11"
objWS.Range(RangeStr4).Font.Name = "Calibri"
objWS.Range(RangeStr4).Font.Size = "11"
objWS.Range(RangeStr5).Font.Name = "Calibri"
objWS.Range(RangeStr5).Font.Size = "11"
objWS.Range(RangeStr6).Font.Name = "Calibri"
objWS.Range(RangeStr6).Font.Size = "11"
objWS.Range(RangeStr1).Font.FontStyle = " "
objWS.Range(RangeStr2).Font.FontStyle = " "
objWS.Range(RangeStr1).MergeCells= xlCenterAcrossSelection
objWS.Range(RangeStr2).EntireColumn.Autofit
objWS.Range(RangeStr1).Interior.ColorIndex =5
objWS.Range(RangeStr1).Font.ColorIndex =2
objWS.Range(RangeStr2).Interior.ColorIndex =4
objWS.Range(RangeStr2).Font.ColorIndex =1
objWS.Range(RangeStr3).Interior.ColorIndex =2
objWS.Range(RangeStr3).Font.ColorIndex =1
objWS.Range(RangeStr4).Interior.ColorIndex =2
objWS.Range(RangeStr4).Font.ColorIndex =1
objWS.Range(RangeStr5).Interior.ColorIndex =2
objWS.Range(RangeStr5).Font.ColorIndex =1
objWS.Range(RangeStr6).Interior.ColorIndex =2
objWS.Range(RangeStr6).Font.ColorIndex =1
objWS.Range(RangeStr7).Font.ColorIndex =5
objWS.Range(RangeStr3).HorizontalAlignment = xlCenter
objWS.Range(RangeStr4).HorizontalAlignment = xlCenter
objWS.Range(RangeStr5).HorizontalAlignment = xlCenter
objWS.Range(RangeStr6).HorizontalAlignment = xlCenter
objWS.Range(RangeStr1).Interior.Pattern = xlSolid
objWS.Range(RangeStr2).Interior.Pattern = xlSolid
objWS.Range(RangeStr3).Interior.Pattern = xlSolid
objWS.Range(RangeStr4).Interior.Pattern = xlSolid
objWS.Range(RangeStr5).Interior.Pattern = xlSolid
objWS.Range(RangeStr6).Interior.Pattern = xlSolid
objWS.Range(RangeStr1).Borders.ColorIndex =0
objWS.Range(RangeStr2).Borders.ColorIndex =0
objWS.Range(RangeStr3).Borders.ColorIndex =0
objWS.Range(RangeStr4).Borders.ColorIndex =0
objWS.Range(RangeStr5).Borders.ColorIndex =0
objWS.Range(RangeStr6).Borders.ColorIndex =0
objWS.Range(RangeStr1).Borders.Weight = xlThin
objWS.Range(RangeStr2).Borders.Weight = xlThin
objWS.Range(RangeStr3).Borders.Weight = xlThin
objWS.Range(RangeStr4).Borders.Weight = xlThin
objWS.Range(RangeStr5).Borders.Weight = xlThin
objWS.Range(RangeStr6).Borders.Weight = xlThin
objWS.Range(RangeStr10).select
objWS.Range(RangeStr10).Formula= xlFormula
objExcel.ActiveWorkbook.Save()

 

td:
You might have posted this to the wrong board.   It looks more like something that should have been posted in the WinBatch board instead of the WebBatch board.

Anyway, there are several examples of adding formula to an Excel sheet in the Tech Database so that should be the first place to go.  Here is a link to just one of those examples:

http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/nftechsupt.web+WinBatch/OLE~COM~ADO~CDO~ADSI~LDAP/OLE~with~Excel/Samples~from~Users+Auto~Sum~Two~Columns~In~Excel.txt

Navigation

[0] Message Index

Go to full version