I have a macro that copies data from multiple workbooks into a Master wkbook. I am only able to do this by running the macro from the Master wkbk. However I want to be able to run this code externally (outside of the excel sheet).
I have tried using a vb script but it tells me I dont have the admin rights to run it (I dont have rights). I also found somewhere where I could use a batch file to run the vbs but unfortunately it doesnt work. Here is my macro code, the VB script and the bat file intended to run the code externally.
The VB code:
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim wkbDest As Workbook
Dim wkbSource As Workbook
Set wkbDest = ThisWorkbook
Dim LastRow As Long
Const strPath As String = "C:\Users\G852589\cnq transfer\"
strExtension = Dir("*.xlsx*")
Do While strExtension <> ""
Set wkbSource = Workbooks.Open(strPath & strExtension)
LastRow = .Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
.Sheets("Sheet1").Range("A5:GJ" & LastRow).Copy wkbDest.Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
strExtension = Dir
Application.ScreenUpdating = True
The VB script I have:
Dim args, objExcel
Set args = WScript.Arguments
Set objExcel = CreateObject("Excel.Application")
And finally the bat file i intend to run the vbs with:
Result.xlsm is the Master workbook where the macro code is seated. The bat file does nothing after running so I would appreciate if someone can point out where my code is wrong or what I am not doing right