APPLY AND DRAG THE FORMULA IN EXCEL USING VBS SCRIPT
In this Article, we are discussing about how we can use VBS Script to Apply formula on a Excel Sheet and drag the formula down the whole column.
We got different types of methods for that and they are as follows :
Three Methods :
M1: Copy-Paste
M2: Fill Down
M3: Keystrokes
___________________________________________________________________________________
HERE :
' On error resume Next
' set args = WScript.Arguments
' vExcelFilePath = args.Item(0)
' vLogPath = args.Item(1)
'!!!!!!!STATIC PATH_INFO
vFilePath = "C:\Users\Lav
Tiwari\Desktop\PRACTICE\Emp.xlsx"
vTabName = "Sheet1"
vLogPath = "C:\Users\Lav
Tiwari\Documents\practiseOL1\Log.txt"
MsgBox("FilePath :" & vFilePath & vbCrLf & " TabName :" & vTabName &
vbCrLf & " vLogPath :" & vLogPath)
'!!!!!!!!!!!!!! Create Object of Log File
Set objFS=CreateObject("Scripting.FileSystemObject")
Set FSO_Handle = objFS.OpenTextFile(vLogPath,8,True)
Set objData = Nothing
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.DisplayAlerts = False
Set objData = objExcel.Workbooks.Open(vFilePath,False)
FSO_Handle.WriteLine(Now & "Excel Operation has been
completed successfully " )
objExcel.windowstate = -4137
objExcel.Calculation = x1Manual
objData.Worksheets(vTabName).Activate
Dim RowCount
objData.Worksheets(vTabName).Activate
RowCount=objData.Worksheets(vTabName).UsedRange.Rows.Count
MsgBox("Last Row :" & RowCount)
' Performing And Dragging Formula
‘!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! ' Method 1
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
vColFormula = "=CONCAT(A2,"" "",B2)"
objData.Worksheets(vTabName).Range("K2:K"& RowCount).Formula
= vColFormula
MSGBOX("K2:K"& SRowCount &
vbCrlf & "Formula
Applied")
wscript.sleep 500
objData.Worksheets(vTabName).Range("I2:I"& RowCount).Copy
wscript.sleep 500
objData.Worksheets(vTabName).Range("I2:I"& RowCount).PasteSpecial
-4104
MsgBox("I2:I"& RowCount & " M1 : Formula
CoPas OP Done")
!!!!!!!!!!!!!!!!!!!!!!!!!!!!' Method 2!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
WScript.Sleep 600
objData.Worksheets(vTabName).Activate
vPCECount = objData.worksheets(vTabName).UsedRange.Rows.Count
objData.worksheets(vTabName).Activate
objExcel.ActiveSheet.Range("K2:K" & vPCECount).Filldown ' Method 2
MsgBox(" M2 : DragFill OP Done")
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!' Method 3!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
WScript.Sleep 500
objData.Worksheets(vTabName).Activate
vPCECount
= objData.worksheets(vTabName).UsedRange.Rows.Count
objData.worksheets(vTabName).Activate
objExcel.ActiveSheet.Range("K2:K" & vPCECount).Select
set mySendKeys = CreateObject("wscript.shell")
mysendkeys.SendKeys("^(d)")
MsgBox("M3 : Send Key OP
Successful")
If Err.Number <> 0 Then
FSO_Handle.WriteLine(Now
& "Excel Operation |
FormulaNdDrag.vbs | Error | " &
Err.Number & ":" & Err.Description )
vStatus = "Fail"
Else
FSO_Handle.WriteLine(Now
& "Excel Operation |
FormulaNdDrag.vbs has been completed succesfully " )
vStatus ="Pass"
End If
MsgBox("Status-: " & vStatus )
'
WScript.StdOut.WriteLine vStatus
' for automation purposes
On Error Goto 0
WScript.Quit
FSO_Handle.Close()
'Autoclose
message box in vbs Script
Function MegaBox_QC(p1)
CreateObject("Wscript.Shell").Popup p1 , 3 , "Qc Robo" , 4 +
32
End Function