Syntax help in Excel VB macro please

  Chris the Ancien 20:18 03 Dec 05
Locked

My big project on my spreadsheet is still on-going. And I'm still very reliant on help from the Master!

Within an existing macros, I want to call up another macro and I can achieve this with absolutely no hassle using...

Application.Run "'MasterDiary.xls'!MakeCopy"

So far, so good.

But the name of the original workbook could change, and all the things I want to do with opening and closing books I can get to work out fine by creating a variable named after the workbook using...

WbName = ActiveWorkbook.Name

But now, I would like to change the 'MasterDiary.xls' in the Application.Run... statement to use the WbName variable instead.

Can it be done, and if so, what's the syntax? I've tried all sorts - and, needless to say, unsuccessfully!

  VoG II 21:02 03 Dec 05

Sub bp()

Beep

End Sub

Sub test_CTA()

Dim WbName As String

WbName = ActiveWorkbook.Name

Application.Run WbName & "!bp"

End Sub

  Monoux 21:29 03 Dec 05

bookmark

  Chris the Ancien 23:13 03 Dec 05

Sorry to be late getting back...

Food and falling-down water intervened!

I shall play with that tomorrow when I'm back in the saddle.

First thought is that it is nothing like anything I've tried yet!

Chris

  Chris the Ancien 15:36 04 Dec 05

Had time (and space) to look at VoG™'s routine.

And it works.

Except...

if the file name has a space in it such as Master Diary.xls

And I can't find a syntax to cover that one!

  VoG II 16:42 04 Dec 05

Sub bp()

Beep

MsgBox "Will CTA ever finish this project ;o)"

End Sub

Sub test_CTA()

Dim WbName As String

WbName = ActiveWorkbook.Name

Application.Run "'" & WbName & "'!bp"

End Sub

  Chris the Ancien 17:09 04 Dec 05

And all coz I never thought of using the single quotes!

I also found a fudge (in between 2 (illegal) cigarettes) while the wife was out...

Sub bp()

Beep

MsgBox "CtA is getting there(?)"

End Sub

Sub test_CTA()

Dim WbName As String

WbName = ActiveWorkbook.Name

RunMe = WbName & "!bp"

Application.Run RunMe

End Sub

As always, I learn so much from the Master, VoG™, and I would be totally lost without his help.

But... one day I'll pose a problem he can't answer (?)

And I'm sure his brain would atrophy without me ;o))

CtA

  Chris the Ancien 17:12 04 Dec 05

The project actually works, but I'm trying to tweak it for the people who can't follow simple rules like "Don't change the name of the master file!"

Plus one or two other twiddles for the less than average user. It needs to be s-i-m-p-l-e!

This thread is now locked and can not be replied to.

How to get Windows 10 for free | How to install Windows 10: There is still a way to avoid paying…

1995-2015: How technology has changed the world in 20 years

Alex Chinneck’s giant ice cube Christmas tree at Kings Cross

Apple rumours & predictions 2017: The iPhone 8, new iPads, and everything else you should expect fr7…