Calling macro in and xlsm workbook not working

[vbanovice123]

Hi,

When I try to invoke a macro while being in the active macro enabled (xlsm) workbook, I get an error.

The workbook in which the called macro exists is also an xlsm workbook.

Can we not call or invoke a macro in an xlsm workbook?


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:="C:\Users\All\Downloads\OriginationsMonitor.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


Application.Run "C:\Users\All\Downloads\'OnePagersYearQtr_Template.xlsm'!Copy2PowerPoint2"

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

 

[borgem]

the OP hasn't responded in this thread as to whether or not the most recent solution worked, but this was posted yesterday...hope it helps.

http://www.mrexcel.com/forum/showthread.php?t=554606

 

[vbanovice123]

I tried the below in the workbook that contains the main macro and calls the other macro as below:

No error but nothing happened



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:="C:\Users\Pradeep Family\Downloads\OriginationsMonitor.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


'PPTM.Presentations.CheckOut (OriginationsMonitorPackage)
'Application.Run "C:\Users\Pradeep Family\Downloads\'OnePagersYearQtr_Template'!Copy2PowerPoint2"

Application.ScreenUpdating = True

End Sub

Sub Test()

Dim oWb As Workbook

Set oWb = GetObject _
("C:\Users\Myfolder\Downloads\'OnePagersYearQtr_Template.xlsm")

Call oWb.Copy2PowerPoint2

End Sub
Private Sub CommandButton1_Click()
Call Copy2PowerPoint2

Dim oWb As Workbook

Set oWb = GetObject _
("C:\Users\MyFolder\Downloads\'OnePagersYearQtr_Template.xlsm")

Call oWb.Copy2PowerPoint2
End Sub


Not sure why the macro within the workbook being called is not executing?

 

[borgem]

You've got an apostrophe in from of OnePagersYearQtr_Template.xlsm in the last two instances, could it just be a syntax issue?

 

[vbanovice123]

I got rid of the apostrophe . Still the call to the second macro within the active workbook that contains the first macro does not work. I want to be able to call multiple macros that exist in multiple workbooks plus run the macro in the active workbook.

Please help . I cannot figure out what I am doing wrong.



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:="C:\Users\Myfolder\Downloads\OriginationsMonitor.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

Application.ScreenUpdating = True

End Sub

Sub Test()

Dim oWb As Workbook

Set oWb = GetObject _
("C:\Users\Myfolder\Downloads\OnePagersYearQtr_Template.xlsm")

Call oWb.Copy2PowerPointQtr

End Sub
Private Sub CommandButton1_Click()

Call Copy2PowerPoint2
'Call oWb.Copy2PowerPointQtr

End Sub



Sub CopyTo13MonthHardCopy()

'
' CopyHardCopy Macro
'

'
'HFI
'copy_range_sheet(sheet, rngname)
copy_range_sheet "HFI", "HFI13M"

Application.ScreenUpdating = True

End Sub

Private Sub CommandButton2_Click()

Call CopyTo13MonthHardCopy

End Sub

 

[vbanovice123]

I also tried changing the name of the macro that was called. Originally, the macro in the active workbookand the macro in the called workbook had the same name and I thought this could be an issue. So I renamed the macro in the called workbook. But that did not help.

 

[borgem]

I just looked around and maybe something as simple as

Application.Run "Workbook1!MyMacro"

would work. I tried it on some sheets I have and it works as long as the workbook you are calling has no spaces in the name and both files are saved in the same directory.

 

[vbanovice123]

What type are the workbooks. I have the .xlsm workbooks. I tried the below and still no luck.

Sub cals()
'Run a macro in another workbook
Application.Run "'OnePagersYearQtr_Template.xlsm'!Sheet22.Copy2PowerPointQtr"
End Sub

 

[borgem]

Mine are both .xlsm...Try without the apostrophe's, I think if you don't have an actual space, but rather an underscore, you shouldn't need anything more than quotes on the outsides. I have both sheets open when running it, so extra code to open the other sheet may be required, but here is my test that works for me.

Sub Macro1()

Application.Run "GemSheet.xlsm!FilterGems"

End Sub

 

[vbanovice123]

I tried keeping both templates(.xlsm) open. It still did not work.

Question: Where should I place the subroutine that calls the macro in the second workbook? I might be placing incorrectly.

I place it separately and then I have the command button that calls the macro within the active(master) workbook.

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:="C:\Users\Pradeep Family\Downloads\OriginationsMonitor.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

Application.ScreenUpdating = True

End Sub

'Sub Copy_YearQtr()

'Dim oWb As Workbook

'Set oWb = GetObject _
'("C:\Users\Pradeep Family\Downloads\OnePagersYearQtr_Template.xlsm")

'Call oWb.Copy2PowerPoint2

'End Sub

'Sub cals()
'Run a macro in another workbook
'Application.Run "'OnePagersYearQtr_Template.xlsm'!Sheet22.Copy2PowerPointQtr"
'End Sub

Private Sub CommandButton1_Click()

Call Copy2PowerPoint2

'Application.Run "OnePagersYearQtr_Template.xlsm!Sheet22.Copy2PowerPointQtr"

'Call Copy_YearQtr

'Call oWb.Copy2PowerPointQtr

End Sub


Sub Copy_YearQtr()

Application.Run "OnePagersYearQtr_Template.xlsm!Sheet22.Copy2PowerPoint2"


End Sub


Private Sub CommandButton1_Click()

Call Copy2PowerPoint2


End Sub