Excel macro query

  Chris the Ancient 17:37 08 Feb 2005
Locked

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()


Range("A1").Activate


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.

TIA

CtA

  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!

Nope...

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!

Chris

  pc moron 21:25 08 Feb 2005

In the above macro change

PathName = ThisWorkbook.Name

to


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.

Chris

  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!

Chris

  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

Alienware 17 R4 2017 review

These brilliant Lego posters show just what children's imaginations are capable of

Mac power user tips and hidden tricks

Comment réinitialiser votre PC, ordinateur portable ou tablette Windows ?