Excel macro query

  Chris the Ancient 17:37 08 Feb 05

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 05

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

  VoG II 20:57 08 Feb 05

Um, I don't know. Try this

FullPath = ActiveWorkbook.FullName

  Chris the Ancient 21:06 08 Feb 05

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 05

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 05


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 05

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 05

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

  Chris the Ancient 21:34 08 Feb 05

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 05

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 05

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.

Surface Pro (2017) vs Surface Pro 4

Where HTML5 is headed next

MacBook Pro v Surface Pro 5