Running macro code externally with vbs and batch file

Copper Contributor

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

 

2 Replies
@Haytham Amairah Can you please take a look at my issue

@Fatman003

 

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