Excel macro query

  Chris the Ancient 17:37 08 Feb 05
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 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!

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 05

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 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.

Chris

  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!

Chris

  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.

Best phone camera 2016/2017: Galaxy S7 vs iPhone 7 vs Google Pixel vs HTC 10 Evo vs OnePlus 3T vs…

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

The Pantone Colour of the Year 2017 is Green

Super Mario Run preview | Hands-on first impressions of Super Mario Run: Mario's iPhone & iPad…