Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to call Macro(using ExecuteExcel4Macro) for a particular workbook
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...cel4Macro1.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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to call Macro(using ExecuteExcel4Macro) for a particular workbook
fName = Application.GetSaveAsFilename()
if fName < False then wb.SaveAs fName End if -- Regards, Tom Ogilvy wrote in message oups.com... 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...cel4Macro1.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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to call Macro(using ExecuteExcel4Macro) for a particular workbook
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to call Macro(using ExecuteExcel4Macro) for a particular workbook
Test
wrote in message oups.com... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to call Macro(using ExecuteExcel4Macro) for a particular workbook
What is the problem that keeps you from activating your current workbook?
-- Regards, Tom Ogilvy "Vinay" wrote in message ... Test wrote in message oups.com... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to call Macro(using ExecuteExcel4Macro) for a particular workbook
You can use:
application.Dialogs(xlDialogSaveAs).Show which should be the same. But this will also act on the activeworkbook. -- Regards, Tom Ogilvy "Vinay" wrote in message ... Test wrote in message oups.com... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to call Macro(using ExecuteExcel4Macro) for a particular workbook
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I call a macro in another workbook? | Excel Programming | |||
How do I change a macro to call a sheet from another workbook | Excel Programming | |||
How do I change a macro to call a sheet from another workbook | Excel Programming | |||
Open Another Workbook and Call Macro | Excel Programming | |||
Call a macro in other workbook | Excel Programming |