Author Topic: How to insert the Excel formulas in Winbatch Code  (Read 309 times)

BVVNAIDU937

  • Newbie
  • *
  • Posts: 10
How to insert the Excel formulas in Winbatch Code
« on: September 13, 2017, 11:23:17 pm »
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

  • Tech Support
  • *****
  • Posts: 2185
    • WinBatch
Re: How to insert the Excel formulas in Winbatch Code
« Reply #1 on: September 14, 2017, 07:57:42 am »
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
"Success is a lousy teacher. It seduces smart people into thinking they can't lose."
  - Bill Gates