Aug 22 2019 11:35 PM
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:
Sub CopyRange()
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\"
ChDir strPath
strExtension = Dir("*.xlsx*")
Do While strExtension <> ""
Set wkbSource = Workbooks.Open(strPath & strExtension)
With wkbSource
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)
.Close savechanges:=False
End With
strExtension = Dir
Loop
Application.ScreenUpdating = True
End Sub
The VB script I have:
Dim args, objExcel
Set args = WScript.Arguments
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open args(0)
objExcel.Visible= True
objExcel.Run "OpenRange"
objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.Close(0)
objExcel.Quit
And finally the bat file i intend to run the vbs with:
cscript script.vbs "C:\Users\G852589\cnq transfer\Result.xlsm"
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
Aug 23 2019 01:49 AM
Aug 23 2019 06:13 AM
Hi,
I've never tried to run macros outside Excel before!
But after searching the web I found a good way to do so in this StackOverflow question.
I've tried the solution in the last reply, and it works just fine.
Regards