VBA code to execute a module in a different workbook

**Need_help_please** · 04-05-2012, 08:51 AM

Like the title says: I want to execute a module which is located in a certain workbook by clicking a button in a different workbook.
This all is also in the picture attached.

Please have a look.
Thanks

16lk50k.jpg

**Kelshaer** · 04-05-2012, 09:04 AM

Use this:
change the red words so they match what you have.

Sub RunRemote()
Dim oWB As Workbook
Set oWB = Workbooks.Open("D:\PLCHKSPTL.xls")
Application.Run (oWB.Name & "!UpdBtn_Update")
oWB.Close
End Sub

**111StepsAhead** · 04-05-2012, 09:06 AM

http://www.mrexcel.com/archive/VBA/15551.html

The above link shows how to work with different workbooks. You just have to apply it correctly.

**Kelshaer** · 04-05-2012, 10:20 AM

or you can use this without any variables.

Sub RunRemote()
Application.Run (Workbooks.Open("D:\PLCHKSPTL.xls").Name & "!UpdBtn_Update")
Workbooks("PLCHKSPTL.xls").Close
End Sub

**Need_help_please** · 04-06-2012, 01:48 AM

Thanks for the responses.
It seems to work fine except if the file has spacings in its name.
I don't know if that's the reason it don't work in those cases or if there's another reason.
I just noticed it only happened so far when the file has spacings in its name.

The error I get says something about macros disabled. I checked but all macros are enabled.

See the attachment

untitled.jpg

**Need_help_please** · 04-06-2012, 05:00 AM

it's definitely the space in the name
(when i removed it the code worked fine).

So my question is what to change in the code to make it working for files with spaces in their name also.

Thanks in advance

**Need_help_please** · 04-06-2012, 06:27 AM

Made some changes to the code so I could put the file name between an extra pair of single quotes ' ' to deal with spaces and "-" in file names:

Private Sub CommandButton1_Click()
    Workbooks.Open ("\\Nw_edc_02\apps1\Starquery\Werkfolder\WR\xls\aantal picks.xls")
    Application.Run ("'aantal picks.xls'!Updbtn_Update")
End Sub

Found here: http://stackoverflow.com/questions/2...ontains-spaces