Calling macro in different workbook

[FredM400]

I am having problems calling a macro that exists in a different workbook.

Workbook "A" is the current workbook.

Workbook "B" contains the macro(named "btnGo") I want to execute from workbook "A". (The correct name of workbook "B" is in the variable vFileAbreName.)

The code in workbook "A" is,

vForeignMacro = (vFileAbreName + "!btnGo")
application.Run vForeignMacro


Workbook "B" is open and activated at the time of the call.
I have no problems manipulating cell values in workbook "B".

But I cannot run the macro of workbook "B"!!!!


I am getting a 1004 Cannot run macro error.
It says the macro may not be in this workbook, or macros may be disabled. Went through the formality of checking the Trust Center and all macros are enabled.

What am I missing???

 

[Trevor G]

Look at this sample, Note single quotes as well as double

Sub cals()
'Run a macro in another workbook
Application.Run "'M:\Excel Files\Excel VBA Files\Calendar Control New.xls'!frmCalendarShow"
End Sub

 

[FredM400]

Thanks!

Sometimes it's the little things it takes others to see

 

[vbanovice123]

Hi,

I have a similar need.

Currently I have three macro templates in three different workbooks and each open the same powerpoint file , copies data tables and charts in different slides in the powerpoint file.

Currently I open the first excel workbook , run the macro. Then I save and close the powerpoint file manually, open the second macro template . run the macro and so on.

I read the above and believe that I can call another macro that exists in another workbook, from within the current active workbook.

I tried applying the above but it did not work.

Below is the code:

Should I paste the below code in the place where I call the function that copies and pastes to powerpoint? I change the file path and the macro name? Please help.

Also, is there a way to automatically save and close a powerpoint file rather than doing it manually three times?

Sub cals()
'Run a macro in another workbook
Application.Run "'M:\Excel Files\Excel VBA Files\Calendar Control New.xls'!frmCalendarShow"


Sub Copy2PowerPoint2()
Application.ScreenUpdating = False
Dim slidenum As Integer
Dim PPTM As PowerPoint.Application
Set PPTM = New PowerPoint.Application
PPTM.Visible = True
PPTM.Presentations.Open Filename:="F:\Focus\MyFolder\Copy Paste Project\OnePagers_Templates\OriginationsMonitorPackage.pptm"

slidenum = 7
'HFI
'copy_chart(sheet, chart_name, slide, aheight, awidth, atop, aleft,lockaspect,vscale)
copy_chart "HFI", "HFI_Vol", slidenum, acheight, acwidth, actop, acleft, msoFalse, 1

slidenum = 8
'HFI
'copy_chart(sheet, chart_name, slide, aheight, awidth, atop, aleft,lockaspect,vscale)
copy_chart "HFI", "HFI_Refi", slidenum, acheight, acwidth, actop, acleft, msoFalse, 1

slidenum = 10
'HFI
'copy_range(sheet, rngname, slide, aheight, awidth, atop, aleft, vscale)
copy_range "HFI", "HFI13M", slidenum, arheight, arwidth, artop, arleft, 1

Sub cals()
'Run a macro in another workbook
Application.Run "'M:\Excel Files\Excel VBA Files\Calendar Control New.xls'!frmCalendarShow"

End Sub


Application.ScreenUpdating = True
End Sub
Private Sub CommandButton1_Click()
Call Copy2PowerPoint2
End Sub

 

[Trevor G]

Adjust your code so you can use a with statement you can then look to use the Save As and Close method and also quit PowerPoint. An extract is shown below

With PPTM
.Visible = True
'.Presentations.Add
.Presentations(1).SaveAs "Year-End Report"
.Presentations("Year-End Report.ppt").Close
.Quit
End With

 

[vbanovice123]

So the below code will help save and close the PowerPoint file. Should I place it after the


With PPTM
.Visible = True
'.Presentations.Add
.Presentations(1).SaveAs "Year-End Report"
.Presentations("Year-End Report.ppt").Close
.Quit
End With


Sub Copy2PowerPoint2()
Application.ScreenUpdating = False
Dim slidenum As Integer
Dim PPTM As PowerPoint.Application
Set PPTM = New PowerPoint.Application
PPTM.Visible = True
PPTM.Presentations.Open Filename:="F:\Focus\Myfolder\Copy Paste Project\OnePagers_Templates\OriginationsMonitorPackage.pptm"


I tried placing the With..EndWith code as below and I get an error that object is not found. Where should I place the code? After each macro is run? I would like to save and close the PowerPoint file after each macro is run sucessfully.

Private Sub CommandButton1_Click()
Call Copy2PowerPoint2
With PPTM
PPTM.Visible = True
'.Presentations.Add
PPTM.Presentations.SaveAs "OriginationsMonitorPackage"
PPTM.Presentations("OriginationsMonitorPackage.pptm").Close
PPTM.Quit
End With
End Sub

 

[vbanovice123]

I tried the below and I get an error message "Could not save the file"

What am I doing wrong?


Private Sub CommandButton1_Click()
Call Copy2PowerPoint2
With PPTM
'Dim PPTM As PowerPoint.Application
Set PPTM = New PowerPoint.Application
PPTM.Visible = True
'.Presentations.Add
PPTM.Presentations(1).SaveAs "OriginationsMonitorPackage"
PPTM.Presentations("OriginationsMonitorPackage.pptm").Close
PPTM.Quit
End With
End Sub

 

[vbanovice123]

I was finally able to save and close the PowerPoint file. Thanks for all your help. Appreciate it.

I have to enhance the automation more:

1) having a pptm template cause a large file size (6 times the original). If original is 4 MB the pptm causes a file size of 25MB. Since the file will be sent as an attachment via e-mail, is there a way i can use pptx or ensure that the file size is small?

2) In future , there may be cases where the slides can be switched. So with the current logical flow by slide numbers, there can be room for error in case slides are added or deleted as the code heavily depends on slide numbers.

Does anyone know whether slides can be labelled and we could use the label instead of slide numbers? Just like how range and sheets and charts can be given names in xls instead of going by range numbers.


Thanks for your help.

 

[Trevor G]

Pleased to read you have a working solution all be it not perfect yet. Sorry I couldn't reply BT Broadband keeps dropping out, very frustrating.

 

[vbanovice123]

Yes. Thanks.

I just found out that one of the macros after being run sucessfully, does not auto save PowerPoint. I have the same exact code. What could be the reason?

The other two macros after being run successfully , autosave and close the PowerPoint file . Am I not seeing something?

Private Sub CommandButton4_Click()
Call CopyToPowerPointGrid
With PPTM
'Dim PPTM As PowerPoint.Application
Set PPTM = New PowerPoint.Application
PPTM.Visible = True
'.Presentations.Add
PPTM.Presentations("OriginationsMonitorPackage.pptm").Save
PPTM.Presentations("OriginationsMonitorPackage.pptm").Close
PPTM.Quit
End With
End Sub