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

 

[vbanovice123]

I tried the below:

I get an error message that says:

"Cannot run the macro. The macro may not be available in this workbook or all macros are disabled


Sub CopyToPowerPointYearQtr()
Workbooks.Open Filename:= _
"F:\Focus\MyFolder\Copy Paste Project\OnePagers_Templates\OnePagersYearQuarter_Template.xlsm"
Application.Run "OnePagersYearQuarter_Template.xlsm!Copy2PowerPoint2"
End Sub

 

[vbanovice123]

I also tried the below by omitting the .xlsm filename as the file is being opened anyway.

I get as far as the .xlsm file being opened (that contains the second macro) and then I get the error




Sub CopyToPowerPointYearQtr()
Workbooks.Open Filename:= _
"F:\Focus\AnitaPradeep\Copy Paste Project\OnePagers_Templates\OnePagersYearQuarter_Template.xlsm"
Application.Run "!Copy2PowerPoint2"
End Sub


Private Sub CommandButton3_Click()
Call CopyToPowerPointYearQtr
End Sub

 

[vbanovice123]

Also since the macro is in a sheet, do I need to specify a sheet. I cannot figure out why the call to the macro in a different workbook is not working.

 

[CharlesChuckieCharles]

try 'around workbook path and name'

 

[vbanovice123]

Thanks you so much! Adding the below worked.

Sub CopyToPowerPointYearQtr()
Workbooks.Open Filename:= _
"F:\Focus\Myfolder\Copy Paste Project\OnePagers_Templates\OnePagersYearQuarter_Template.xlsm"
Application.Run "OnePagersYearQuarter_Template.xlsm!Sheet1.Copy2PowerPoint2"
End Sub
Private Sub CommandButton3_Click()
Call CopyToPowerPointYearQtr
End Sub

 

[borgem]

Also since the macro is in a sheet, do I need to specify a sheet. I cannot figure out why the call to the macro in a different workbook is not working.

I think I just caught a few issues.

First off, the "Private Sub CommandButton1_Click()" calls the macro directly, not the macro that calls the macro...i.e. it should be "Call Copy_YearQtr()", not "Call Copy2PowerPoint2".

The next issue is that, unless the macro security on the sheet(s) you are updating is not set to low, it is getting hung up once you open it. There are a few options, such as setting their security settings to low so that the enable macros box doesn't pop up, configure the trust center settings so that the file path where those files open from is permanently trusted, digitally signing, etc...

This may be a silly question, but have you put breaks into the macro "Copy_YearQtr()" and ran it manually to see what it does step-by-step?

 

[CharlesChuckieCharles]

Normally its single quotes around pathNames

but... well done

 

[borgem]

Reading around, I found that the single quotes were only particularly necessary when one was accessing a file whose name contained a space in it. My test on my files worked without them, so I was just hoping to simplify.

 

[vbanovice123]

Thanks.

I tried adding the below but when I check back the code after the macro ran successfully, the () disappears.

Before running the macro and saving the .xlsm the code appears as below

Sub CopyToPowerPointGrid()
Workbooks.Open Filename:= _
"F:\Focus\Myfolder\Copy Paste Project\OnePagers_Templates\OnePagersGrid_Template.xlsm"
Application.Run "OnePagersGrid_Template.xlsm!Sheet1.Copy2PowerPoint2"
End Sub

Private Sub CommandButton4_Click()
Call CopyToPowerPointGrid()
End Sub


After the macro ran, I check the code and it appears like so:

Sub CopyToPowerPointGrid()
Workbooks.Open Filename:= _
"F:\Focus\Myfolder\Copy Paste Project\OnePagers_Templates\OnePagersGrid_Template.xlsm"
Application.Run "OnePagersGrid_Template.xlsm!Sheet1.Copy2PowerPoint2"
End Sub

Private Sub CommandButton4_Click()
Call CopyToPowerPointGrid
End Sub


Now all the macros are running from within the active workbook.

I will try and figure out how to close the files that contain the called macro.

Maybe Workbook.Close?

Thanks.

 

[borgem]

Glad to hear it, I believe that is the correct command.