Running macro code externally with vbs and batch file

Fatman003

New Member
Joined
Aug 22, 2019
Messages
18
[COLOR=#BBC0C4 !important][COLOR=#6A737C !important]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.
[/COLOR]
[/COLOR]

The VB code:



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:
Code:
[/FONT][/COLOR]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
[COLOR=#242729][FONT=Arial]

And finally the batch file I intend to run the vbs with
Code:
[/FONT][/COLOR]cscript script.vbs "C:\Users\G852589\cnq transfer\Result.xlsm"
[COLOR=#242729][FONT=Arial]

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
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Watch MrExcel Video

Forum statistics

Threads
1,127,443
Messages
5,624,803
Members
416,054
Latest member
Ariel2219

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top