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

 

[Andrew Poulsom]

Does this work for you?

Application.Dialogs(xlDialogOpen).Show

 

[VoG]

You probably need something like

Dim fName As Variant
fName = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLS), *.XLS", Title:="Select File To Be Opened")
If fName = False Then Exit Sub
Workbooks.Open Filename:=fName

 

[perp1exed]

@Andrew

Yes thank you, this does work.

Maybe the solution then is to replace parts of the Excel 4 macro sheet functions that no longer work in 2010.

What are your thoughts?

thanks.

 

[Andrew Poulsom]

@Andrew

Yes thank you, this does work.

Maybe the solution then is to replace parts of the Excel 4 macro sheet functions that no longer work in 2010.

What are your thoughts?

thanks.

I'd rewrite the lot, but maybe that's too big a job.

 

[perp1exed]

@Andrew,

Thats what I am trying to avoid haha.

thanks.

 

[RoryA]

If you are using the CustomUI.xml to add the ribbon buttons, then the onAction is actually a callback that requires a particular signature, not a macro. You should however be able to call your macro from the callback.

 

[perp1exed]

@rorya,

I am using the following:

Private Sub Opener(ByVal control As IRibbonControl) <------onAction is Opener and here is signature of callback
OpenFile <---- macro which opens dialog
End Sub

This does not work for me.

thanks.

 

[perp1exed]

@rorya, @Andrew,

Actually, I think the ribbon button functions do not like calling any of these Excel 4 macros. I get the same error for another macro which is not menu related.

Maybe a rewrite it is ugghhh!

 

[RoryA]

Have you tried:

Application.run "openfile"