CODEWITHSUNDEEP
vbaexcel
NewToVBA1
NewToVBA1

Reputation: 31

How to run a macro from a different workbook in a shared network?

So, I've done a lot of research on this and my code isn't working still. As per the title, the problem is this:

I pull a data report off of a website, this report is downloaded as an .xlsx file. I created a macro on the ribbon so I when I click it, it will then open another workbook and run that macro. The code I'm using is as below:

    Option Explicit
    Sub NotHardAtAll()

    Dim ws As Worksheet,
    Dim wb As Workbook

    Set wb = ActiveWorkbook

    Set ws = ActiveSheet


    Workbooks.Open Filename:="C:\Users\a0c27n\Desktop\Projects\incident_extended_report1.xlsm"

    'With Sheets("Sheet4").Activate  '*Not sure if this is enter code here 
     necessary...at all*

    Application.Run "!ADDHMKRFID"
    'End With

End Sub

I've tried putting the path before the macro (i.e. Application.Run"'incident_extended_report1.xlsm!ADDHMKRFID") but it doesn't work either*

I'm aware, at least form the research I've done, that I should be able to just use the 'Application.Run' Method, however I couldn't get it to access the correct sheet.

When I run the Macro, it pulls a Run-time error '1004' error, a '400', or the it pulls the most is: "Cannot run the macro '!ADDHMKRFID'. The macro may not be available in this workbook or all macros may be disable."

The file that I'm trying to pull the macro from is below:

Workbook name: incident_extended_report1.xlsm

Worksheet name: Sheet4 (TEST MACRO)

Macro Name: Sub ADDHMKRFID() End Sub

I understand that the C:\ is not a shared network, the one I will be working out of will be the S:\, however I'm not sure how much information I can post due to confidentiality. Please ask for any clarification or questions you may have. I've been stuck for a bit and am not sure what I'm doing wrong. Thanks in advance!

Upvotes: 2

Views: 8566

Answers (2)

NewToVBA1
NewToVBA1

Reputation: 31

First of all, I solved my own problem. I would, however, be grateful if someone might explain to me why it worked the way it did.

I saved the original macro on the shared network, but I had to save it as a module (in this case Module1). I also saved the 2nd macro (to run the original one) in a different workbook (though it shouldn't matter, as long it is not a .xlsx file).

The Code I wrote was:

Sub Test()  'Name doesn't matter
Application.Run "'S:\xxxx\xxxx\xxxx\incident_extended_report.xlsm'!module1.ADDHMKRFID"
End Sub

Then I saved this macro to the ribbon so I could run it on the data report.xlsx file I have to download. Now, anytime I want to run the original macro, I just click the Test Macro, and it'll run the other one!

I'm guessing if you want to close the other workbook that you opened, you can just add a 

 Workbooks (“S:\xxxx\xxxx\xxxx\incident_extended_report.xlsm").Close Savechanges:=False

Good Luck!

Upvotes: 1

thanto_
thanto_

Reputation: 26

The string you need to pass to Application.Run depends on whether the workbook containing the macro is active, and if it isn't, the filename of the macro-containing workbook (IE: what's in the workbook.Name property).

if the macro is supposed to be run while the data report workbook is active, you want:

dim wb_data as Workbook: set wb_data = ActiveWorkbook
dim ws_data as Worksheet: set ws_data = ActiveSheet
dim wb_macro as Workbook
set wb_macro = Workbooks.Open(Filename:="C:\Users\a0c27n\Desktop\Projects\incident_extended_report1.xlsm")
ws_data.Activate
Application.Run wb_macro.Name & "!ADDHMKRFID"

This will guarantee that the correct string is supplied, even if you change the name of the macro file.

Otherwise, if the macro workbook is supposed to be active, skip activating the data worksheet, as the last opened workbook will be active by default, then use "ADDHMKRFID" as your string. Note that the "!" is missing. You need that only if you are specifying a macro in another workbook. It's the same kind of notation used when referring to data in other worksheets.

Upvotes: 1

Related Questions