Macro problems related to file names

Hi all

I've developed a spreadsheet with a number of macros to perform fairly simple functions. The macros were recorded rather than written from scratch.
Some of the macros seem to contain the file name with lines such as:

' Windows("Packaging tool Version 4.xls").Activate'

Other macros do not contain such lines.

If I save the spreadsheet under a new file name then the macro(s) containing the above line crash as the file name does not match.

Can anyone help me out with this? Why do some macros contain the filename and how can I set this up so that I can save the spreadsheet under any name I want and make sure all the macros still work?

Thanks in advance

Originally Posted by lphuk

Hi all

I've developed a spreadsheet with a number of macros to perform fairly simple functions. The macros were recorded rather than written from scratch.
Some of the macros seem to contain the file name with lines such as:

' Windows("Packaging tool Version 4.xls").Activate'

Other macros do not contain such lines.

If I save the spreadsheet under a new file name then the macro(s) containing the above line crash as the file name does not match.

Can anyone help me out with this? Why do some macros contain the filename and how can I set this up so that I can save the spreadsheet under any name I want and make sure all the macros still work?

Thanks in advance

Hi,

You need to capture the name of the workbook (I guess you really mean workbook rather than spreadsheet) in a variable and then use the variable in your line of VBA macro code that saves the workbook. So for instance declare a variable and set it to the name of the activeworkbook - assuming the macro is being run from the workbook you're interested in, as follows:

Please Login or Register  to view this content.

A further tip. You've discovered one of the problems with recording macros. They're great for getting started but as you get deeper into the subject you'll find that for instance most of the stuff the recorder captures is superfluous. It records every single available parameter, and in my experience most of these are not needed since the default parameters are quite acceptable. It also records all the .Activate and .Select stuff. These are generally not needed since you can use objects like sheets and workbooks without activating or selecting them. For instance if you're copying a cell, a macro will record something like:

Please Login or Register  to view this content.

In larger applications a lot of selecting and activating objects slows code down. Instead of the above you can use just:

Please Login or Register  to view this content.

HTH

Thanks very much! I'll give it a try.

No joy but that's becuase I'm dumb and don't really know what I'm doing!

I've recorded a new macro called 'saving' as you specified

Sub saving()
Dim stWbName As String
stWbName = ActiveWorkbook.Name

End Sub

I have replace all references to Windows("Packaging tool Version 4.xls").Activate with stWbName.Activate

I run the macro and it runs (don't see any errors) but when I try to run the macros in the workbook (for example on called Primary) I now get a different error than before.

If I have the 'saving' macro as a macro in it's own right I get the error 'Runtime Error 424 Object Required' and it points to the first line in the 'Primary' macro that says ' stWbName.Activate'

I thought this might mean that I need the code for the 'saving' macro as part of the 'Primary' macro so included the code at the beinging of that particular macro. So the code reads:

Sub Primary()

Dim stWbName As String
stWbName = ActiveWorkbook.Name
ActiveSheet.Shapes("Chart 7").IncrementLeft -13.5
ActiveSheet.Shapes("Chart 7").IncrementTop 15#
ActiveWindow.Visible = False
stWbName.Activate

etc.

When I run this I get 'compile error - Invalid qualifier' and the debugger points to the line 'sub Primary()'

Any suggestions. Apologies for being dumb - you can tell I don't usually mess with macros

Originally Posted by lphuk

No joy but that's becuase I'm dumb and don't really know what I'm doing!

I've recorded a new macro called 'saving' as you specified

Sub saving()
Dim stWbName As String
stWbName = ActiveWorkbook.Name

End Sub

I have replace all references to Windows("Packaging tool Version 4.xls").Activate with stWbName.Activate

I run the macro and it runs (don't see any errors) but when I try to run the macros in the workbook (for example on called Primary) I now get a different error than before.

If I have the 'saving' macro as a macro in it's own right I get the error 'Runtime Error 424 Object Required' and it points to the first line in the 'Primary' macro that says ' stWbName.Activate'

I thought this might mean that I need the code for the 'saving' macro as part of the 'Primary' macro so included the code at the beinging of that particular macro. So the code reads:

Sub Primary()

Dim stWbName As String
stWbName = ActiveWorkbook.Name
ActiveSheet.Shapes("Chart 7").IncrementLeft -13.5
ActiveSheet.Shapes("Chart 7").IncrementTop 15#
ActiveWindow.Visible = False
stWbName.Activate

etc.

When I run this I get 'compile error - Invalid qualifier' and the debugger points to the line 'sub Primary()'

Any suggestions. Apologies for being dumb - you can tell I don't usually mess with macros

Hi,

You were correct in that you moved the declaration of Dim stWbName inside the Primary() procedure. A Dim statement within a procedure only lasts and has scope within that procedure. You could incidentally have put the Dim statement at the top of the module, outside any procedures and then it would still have worked.

Was that a typo when you put the # after the .IncrementTop 15 ? If it was try removing that and see if that cures it.

Rgds

Using Rob's small amend the macro now runs OK - which is great

Cheers for the help guys.

Originally Posted by broro183

hi,

I have a small correction for Richard's code because a string can not be activated (only objects can).

Please Login or Register  to view this content.

hth
Rob

Hello Rob,

Well spotted, thanks for that.

That'll teach me to write code without checking the syntax.