Migrate Excel 4 macro file to 2010

[perp1exed]

Hi,

In the process of this I had one problem... the Excel 4 sheet macros have commands that manipulate the menu system. Since 2010 uses ribbon these will obviously no longer work.

So I removed those macro lines that touched the menu system.

There is one macro which calls the File Open dialog box.

The Macro is called OpenFile and its contents are:

=OPEN?()

Now, when I go to macros and manually Run this it works and opens the dialog.

But when I try to assign this macro to the onAction of a custom ribbon button I get an error Sub or Function not defined.

Is there a workaround/fix for this? Why can my ribbons onAction event not fire the macro when I can do it manually?

thanks,

KS

 

[perp1exed]

Ok, I stumbled across a web page that did the job! rorya you were close

The correct way to call a sheet macro is as below:

Application.Run "'SheetName'!MacroName"

And voila - Bob's your Uncle!

thanks all.

 

[perp1exed]

Have you tried:

Application.run "openfile"

One issue arises still...

After I use the "Open" button from the ribbon to open another Excel file - the ribbon, in the opened file dissappears (maybe because that file has no ribbon). So I am unable to click one of the other buttons which performs operations against the opened file.

Any solutions for this?

The Excel 2002 sheet macro has a =RETURN() command - I'm guessing that this retained the menu, but it is not doing this for 2010.

thanks.

 

[RoryA]

Any customisations you make to the CustomUI only apply to that file unless it is an add-in. You may wish to revert back to the old (but still newer than XLM!) commandbars code to add your buttons to the Addins tab instead, or create an slam file to hold the ribbon customisations.

 

[perp1exed]

Thank you.

I am attempting to create an add-in xlam file.

I have added the ribbon and buttons.

When I open my xlsm file, the add-in custom tab is visible now. How do I change the xlam file's vba code to target macros on the xlsm file?

thanks.

 

[RoryA]

I would put the macros in the slam if you can. If not, Application.Run will work across workbooks. (though I don't know if it works with XLM across workbooks - never really bothered with XLM much)

 

[perp1exed]

I would put the macros in the slam if you can. If not, Application.Run will work across workbooks. (though I don't know if it works with XLM across workbooks - never really bothered with XLM much)

It does not seem to work across workbooks.

My call is:

Application.Run "'SheetName'!MacroName"

It seems that I cannot save my xlsm file as an xlam file (so I cannot put the macros in an add-in file).

Success seems palpable yet not quite there!

thanks for your assistance.

 

[perp1exed]

Also, there error when clicking the buttons is below:

Run-Time error 1004

C:\Users\Public\Documents\Test\Main.xlsx could not be found.

It seems to think the sheet name is the xlsx file name (although it should be xlsm) perhaps I need to pass the FileName!SheetName!Macro, hmmm will try it...

 

[RoryA]

For a normal sheet contained routine (i.e. VBA) it would be:

'FileName'!SheetName.Macro

 

[perp1exed]

Ok this worked...!

Application.Run "Filename.xlsm'!MacroName"

It appears that I do not need the sheetname!

thanks!

 

[perp1exed]

For a normal sheet contained routine (i.e. VBA) it would be:

'FileName'!SheetName.Macro

Many thanks for your help, hopefully it will be easy going to get this thing working in 2010 now.

Regards,

KS