Excel macro query

  Chris the Ancient 17:37 08 Feb 2005

I have successfully used the following routine methods in Excel workbooks so that I can run 'sub'-macros regardless of the workbook name - thus allowing me to rename workbooks as required. The macro is declared in a 'standard' module.

Option explicit

Public PathName as String

Sub Test()

PathName = ThisWorkbook.Name

Application.Run (PathName & "!" & ThisMacro)

End Sub

Sub ThisMacro()


ActiveCell = "This Macro has run"

Range("A2").Activate 'checking

ActiveCell = PathName 'checking

End Sub

And very happy I've been with it as well.

Until today!

I have now found that if the workbook is given a name which has a space it it, the macro won't work! Obviously, PathName as a variable can't cope with spaces.

Is there a way of overcoming it, please?

I'm out until after 8pm - when I get back from work - but hope someone (VOG?) has an idea.



  Chris the Ancient 20:04 08 Feb 2005

I'm home and desperate for an answer! It's doin' my head in.

  VoG II 20:57 08 Feb 2005

Um, I don't know. Try this

FullPath = ActiveWorkbook.FullName

  Chris the Ancient 21:06 08 Feb 2005

Sorry I've not been keeping you on your toes of late - too many other distractions!


Still doesn't like a space in the name!

I had visions of some sort of string manipulation - with no success.

No G&Ts until we've got it!


  pc moron 21:25 08 Feb 2005

In the above macro change

PathName = ThisWorkbook.Name


PathName= ThisWorkbook.FullName

This seems to work okay, I'm using Excel 2002.

  VoG II 21:29 08 Feb 2005


After the day I've had, it is too late for that.

Excel, in its oddity, seems to make a substitution. So instead of "My Book" it creates the string "MyspaceBook". Why the heck (!) does it do that? (words carefully chosen there!)

I cannot immediately think of a way round this but maybe you or others can whilst I give the brain a well-deserved soaking!

Maybe time to consult the gods at mrexcel?

  Chris the Ancient 21:30 08 Feb 2005

All is fine until I put a space in the workbook name...

When the workbook was called tester.xls on my desktop, all worked fine.

Then when I changed the workbook to macro tester.xls on the desktop, the sytem fell flat. It just doesn't like having a space in the name. I can put up with that, but a couple of other users of the spreadsheet can't be relied on to not use spaces in their 'meaningful' titles.


  VoG II 21:31 08 Feb 2005

Sorry pc moron, was thinking along the same lines but not quite right. Thanks.

  Chris the Ancient 21:34 08 Feb 2005

Shhh! Don't tell anyone, but the same question is in at Mr Excel and at also at Tek-Tips.

You've lost me a little on this Myspacebook. I've not seen/heard/found this!

I know what you mean about one of those days. More like one of those weeks today!


  pc moron 21:41 08 Feb 2005

Ahh, now I see.

I created a workbook with a space in the name, pasted in the above macro and changed Name to FullName.

I closed the workbook, opened it and all was okay.

I've just run the same workbook again, and it gives an error at ThisMacro.

I had this sort of thing happen a while ago with a template, I never did get it sorted- it works the first time you open the file, but gives an error on all subsequent attempts to run it.

  VoG II 21:43 08 Feb 2005

This is off the top of my head.

Try saving

Range("A1").Value = ThisWorkbook.Name

then using Range("A1") in your run command.

Sorry but I am multi-tasking gone mental tonight.

Here we go - Messenger is back :o) or :o(

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

Elsewhere on IDG sites

iMac Pro review

Illustrator of witty, relatable Instagram comics Julia Bernhard touches on our humble moments

iMac Pro review

Quelle est la meilleure application de podcast pour Android (2018) ?