How to insert the Excel formulas in Winbatch Code

Started by BVVNAIDU937, September 13, 2017, 11:23:17 PM

Previous topic - Next topic

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
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade