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]

Your last set of code is different from previous

PPTM.Presentations(1).SaveAs "OriginationsMonitorPackage"
PPTM.Presentations("OriginationsMonitorPackage.pptm").Close

Last you have

PPTM.Presentations("OriginationsMonitorPackage.pptm").Save
PPTM.Presentations("OriginationsMonitorPackage.pptm").Close

 

[vbanovice123]

It does not make sense:

I am using the below in the other two and it works fine.

Private Sub CommandButton3_Click()
Call CopyToPowerPointYearQtr
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


I tried changing the above as below for the third macro and it still did not auto save and close power point file. What could be wrong? I maybe missing a small piece.

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(1).SaveAs "OriginationsMonitorPackage"
'PPTM.Presentations("OriginationsMonitorPackage.pptm").Save
PPTM.Presentations("OriginationsMonitorPackage.pptm").Close
PPTM.Quit
End With
End Sub

 

[Trevor G]

I have dug out a netbook with Office 2002 on it so I was able to record a macro in PowerPoint to open a presentation and then do something and then adapt it to run it in Excel, so here you go, what it suggests about the Saving and Closing is that you are dealing with the ActiveWindow.

Sub openPower()
Dim PPTM As PowerPoint.Application
Set PPTM = CreateObject("PowerPoint.Application")
With PPTM
.Visible = True
.Presentations.Open Filename:="C:\Documents and Settings\Trevor G\Desktop\Excel And PowrPoint Slides March 2011.ppt", ReadOnly:=msoFalse
.ActiveWindow.Selection.SlideRange.SlideShowTransition.Hidden = msoFalse
.ActivePresentation.Save
.ActiveWindow.Close
End With
PPTM.Quit
End Sub

 

[Trevor G]

How did my code work out for you?

 

[vbanovice123]

I am testing it for the third module which is having an issue.

Makes no snese to me why the same code works for the other two. The only difference is that the third file has a different format.

Thanks.

 

[vbanovice123]

Hi,

Can you please help. I am still unable to auto save the powerpoint file after the copy paste is completed.

Sub CopyToPowerPointGrid()
Workbooks.Open Filename:= _
"F:\Focus\Myfolder\Copy Paste Project\OnePagers_Templates\OnePagersGrid_Template.xlsm"
Application.Run "OnePagersGrid_Template.xlsm!Sheet1.Copy2PowerPoint2"
Workbooks("OnePagersGrid_Template.xlsm").Close SaveChanges:=False
End Sub
Private Sub CommandButton4_Click()
Call CopyToPowerPointGrid
'With PPTM
'Set PPTM = New PowerPoint.Application
'PPTM.Visible = True
'PPTM.Presentations("OriginationsMonitorPackage.pptm").Save
'PPTM.Presentations("OriginationsMonitorPackage.pptm").Close
'PPTM.Quit
'End With
Sub openPower()
Dim PPTM As PowerPoint.Application
Set PPTM = CreateObject("PowerPoint.Application")
With PPTM
.Visible = True
.Presentations.Open Filename:="F:\Focus\Myfolder\Copy Paste Project\OnePagers_Templates\OriginationsMonitorPackage.pptm", ReadOnly:=msoFalse
.ActiveWindow.Selection.SlideRange.SlideShowTransition.Hidden = msoFalse
.ActivePresentation.Save
.ActiveWindow.Close
End With
PPTM.Quit
End Sub
End Sub

 

[vbanovice123]

I was able to combine the three separate calls to three macros in the active master workbook into one single macro for the copy and paste to powerpoint.

I am trying to do the similar task with copy and pasting range from the active master workbook to three different workbooks which is currently driven by three separate macros.
1. 13M
2. YearQtr
3. Grid

I am unable to get the similar functionality. What am I doing wrong?


In this case the target file is three different files unlike the powerpoint target file which was one.

The below code is in my master file

Sub CopyTo13MHardCopy()
' CopyHardCopy Macro
'
'HFI
'copy_range_sheet(sheet, rngname)
copy_range_sheet "HFI", "HFI13M"
'copy_range_sheet "Agency", "Agency13M"
End Sub
Private Sub CommandButton2_Click()
Call CopyTo13MHardCopy
CopyToYearQtrHardCopy
'Call CopyToPrimeGridHardCopy
End Sub



Sub CopyToYearQtrHardCopy()
Workbooks.Open Filename:= _
"F:\Focus\Myfolder\Copy Paste Project\OnePagers_Templates\OnePagersYearQuarter_Template.xlsm"
Application.Run "OnePagersYearQuarter_Template.xlsm!Sheet1.CopyToYearQtrHardCopy"
Workbooks("OnePagersYearQuarter_Template.xlsm").Close SaveChanges:=False
Application.ScreenUpdating = True
End Sub


The below module contains the funtion for the first sheet(13M) in the active workbook.

'Public Function copy_range(sheet, rowStart, columnStart, row_count, columnCount, slide, aheight, awidth, atop, aleft)
'Public Function copy_range(sheet, rngname, slide, aheight, awidth, atop, aleft, vscale)
Public Function copy_range_sheet(sheet, rngname)
'Dim Filename As String
'Set Filename = "F:\Focus\AnitaPradeep\Copy Paste Project\OnePagers_Templates\One Pagers_13Month ViewHardCopy.xlsx"
Sheets(sheet).Select
'Cells(rowStart, columnStart).Resize(row_count, columnCount).Select
Range(rngname).Select
' Make sure a range is selected
If Not TypeName(Selection) = "Range" Then
MsgBox "Please select a worksheet range and try again.", vbExclamation, _
"No Range Selected"
Else
Range(rngname).Activate
Selection.Copy
Workbooks.Open Filename:= _
"F:\Focus\Myfolder\Copy Paste Project\OnePagers_Templates\One Pagers_13Month ViewHardCopy.xlsx"
Sheets("HFI").Select
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.Close
End If
End Function



The second workbook which contains the macro being called from the master workbook has the below module that calls the function.


'Public Function copy_range(sheet, rowStart, columnStart, row_count, columnCount, slide, aheight, awidth, atop, aleft)
'Public Function copy_range(sheet, rngname, slide, aheight, awidth, atop, aleft, vscale)
Public Function copy_range_sheet_qtr(sheet, rngname)
'Dim Filename As String
'Set Filename = "F:\Focus\Myfolder\Copy Paste Project\OnePagers_Templates\One Pagers_yr_qtr_viewHardCopy.xlsx"
Sheets(sheet).Select
'Cells(rowStart, columnStart).Resize(row_count, columnCount).Select
Range(rngname).Select
' Make sure a range is selected
If Not TypeName(Selection) = "Range" Then
MsgBox "Please select a worksheet range and try again.", vbExclamation, _
"No Range Selected"
Else
Range(rngname).Activate
Selection.Copy
Workbooks.Open Filename:= _
"F:\Focus\Myfolder\Copy Paste Project\OnePagers_Templates\One Pagers_yr_qtr_viewHardCopy.xlsx"
Sheets("HFI").Select
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.Close

End If
End Function


The second workbook(yearQtr) has trhe below code in the main macro sheet.

Thanks