How to call Macro(using ExecuteExcel4Macro) for a particular workbook

[bhagwandas]

Hi All,
I have to call this function :
Application.ExecuteExcel4Macro("SAVE.AS?()")
for a particular workbook.

Right now, if I have more than 1 workbook open and close the excel then
in BeforeSaveAs event, I use this macro to save a workbook but it is
called for current active workbook which I do not want. I want to call
it for a particular workbook which I get as input parameter in
beforeclose and beforesave events.

I tried following but it did not work:-(
Wb.Application.ExecuteExcel4Macro("SAVE.AS?()")

Wb is the workbook for which this macro should execute but it gets
executed for active workbook(Wb is not active workbook).

On enquiring more about this function at following link:
http://msdn.microsoft.com/library/en-us/vbaxl11/html/xlmthExecuteExcel4Macro1.asp

The Microsoft Excel 4.0 macro isn't evaluated in the context of the
current workbook or sheet. This means that any references should be
external and should specify an explicit workbook name. For example, to
run the Microsoft Excel 4.0 macro "My_Macro" in Book1 you must use
"Book1!My_Macro()". If you don't specify the workbook name, this method
fails.

So I tried following syntax so that Macro can be called in context of
Workbook Wb:
Dim savefile as Boolean
savefile = Application.ExecuteExcel4Macro("" & Wb.FullName & "!" &
"SAVE.AS?()" & "")

It give run time error with error code 1004 (Formula you typed contains
error).

Since this macro runs for active workbook, I tried to make the Wb as
active workbook, but then close event does not work properly. It just
saves that file and does not close any workbook.

Hope you can understand my problem and help me in solving this.
Code snippet is given below to understant the problem better.

Thank you
Bhagwandas


Current Code:

Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As
Boolean)
'Changed by bhaveshp 16/06/2004
Dim retVal As Long
SaveAs:
g_beforeSaveAsCancel = False
If Not Wb.IsAddin And Wb.Saved = False Then
retVal = MsgBox("Do you want to save the changes you made to '"
+ Wb.Name + "'?", vbYesNoCancel + vbExclamation, "MyXLS")
Select Case retVal
Case vbYes
If Wb.Path = "" Then
App_WorkbookBeforeSave Wb, True, Cancel
Else
App_WorkbookBeforeSave Wb, False, Cancel
End If
If (g_beforeSaveAsCancel = True) Then
GoTo SaveAs
End If
'WB.Close
Cancel = False

Case vbNo
Wb.Saved = True
'WB.Close
Cancel = False

Case vbCancel
Cancel = True
End Select
End If
'end

On Error Resume Next
If Not Wb.IsAddin Then
ASetCurrentWorkBook Wb
Wb.Close
App.OnTime Now, "AAfterClose"
g_bBeforeCloseCalled = True
g_bIsValidBeforeCloseCalled = True
Else
g_xlaWasClosed = True
End If
End Sub

Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI
As Boolean, Cancel As Boolean)
'Changed by bhaveshp 16/06/2004
Dim MySaveAs As String
Dim savefile As Boolean
Application.EnableEvents = False 'To avoid event Loop
MySaveAs = Wb.FullName
ASetCurrentWorkBook Wb
If SaveAsUI Then
Dim saveMacro As String
saveMacro = "SAVE.AS?()"
'how to call this macro for a particular macro, that is the problem
savefile = App.ExecuteExcel4Macro(saveMacro)

If (savefile = False) Then
g_beforeSaveAsCancel = True
GoTo EndSub
End If
Wb.Saved = True
MySaveAs = Wb.FullName
End If

On Error Resume Next
If Not Wb.IsAddin Then
If Not g_bIsValidBeforeCloseCalled Then
g_bBeforeCloseCalled = False
End If
AAfterSave
End If

If Not SaveAsUI Then
Wb.Save
Wb.Saved = True
End If
EndSub:
Application.EnableEvents = True
Cancel = True
'end
End Sub

 

[Tom Ogilvy]

fName = Application.GetSaveAsFilename()
if fName <> False then
wb.SaveAs fName
End if

 

[bhagwandas]

Hi Tom Ogilvy,
Thanks for replying. I had already tried with this function but problem
is I have to manually give filename and extension types which may not
be sufficient. That is why I opted for using Excelmacro "SAVE.AS?()"
which gives all options for extension types. Now only problem is I have
to make this macro work for a particular workbook (current Wb) but it
executes for active workbook and there is certain problem which stopes
me from activating my current workbook.
Problem occurs only if I have more than 1 workbook open.
Regards
Bhagwandas

 

[Vinay]

Test

 

[Tom Ogilvy]

What is the problem that keeps you from activating your current workbook?

 

[Tom Ogilvy]

You can use:

application.Dialogs(xlDialogSaveAs).Show

which should be the same. But this will also act on the activeworkbook.

 

[bhagwandas]

Hi Tom,
actually if I activate the workbook then it does not close that
workbook and no action happens after beforeSave event.
Thanx

Regards,
Bhagwandas