Macro template in vba

[macroppt123]

Hi

I am doing some reaserch on various online sources but could not find much help.

Can a vba macro template act as a driver to open a ppt or and xls file?

If we need to copy and paste from xls to ppt, cab the vba macro template file act as the main driver to open a ppt file and xls file? Can this be done?

[Kenneth Hobs]

Sure. Just record a macro to see the vba syntax.

[macroppt123]

I tried the macro recorder and get the below code. But opening a Powerpoint file is not possible since the vba code is an xls and in .xlsm format. Does this mean that we would need to open the Powerpoint file and then run the macro to auto open the xls file.


Sub Macro1()
'
' Macro1 Macro
'
'
ChDir "F:\Focus\Copy Paste Project\TestExcelToPPT"
Workbooks("HFIDetails.xls").Connections.AddFromFile
Workbooks("HFIDetails.xls").Connections.AddFromFile
Workbooks("HFIDetails.xls").Connections.AddFromFile
Workbooks("HFIDetails.xls").Connections.AddFromFile
Workbooks("HFIDetails.xls").Connections.AddFromFile
Workbooks("HFIDetails.xls").Connections.AddFromFile
Workbooks("HFIDetails.xls").Connections.AddFromFile
Workbooks("HFIDetails.xls").Connections.AddFromFile
Workbooks("HFIDetails13monthYrQtrGrids.xlsm").Connections.AddFromFile
Workbooks("HFIDetails.xls").Connections.AddFromFile
Workbooks.Open Filename:= _
"F:\FocusCopy Paste Project\TestExcelToPPT\HFIDetails.xls"
End Sub

[macroppt123]

And this worked . I was able to open the xls file. So the Powerpoint file needs to open by use manually?

Sub Macro1()
'
' Macro1 Macro
'
'
ChDir "F:\Focus\Copy Paste Project\TestExcelToPPT"

Workbooks.Open Filename:= _
"F:\Focus\Copy Paste Project\TestExcelToPPT\HFIDetails.xls"
End Sub

[Kenneth Hobs]

You don't need ChDir().

For ppt:
Sub Macro1()
'Requires Tools > References... > Microsoft Office PowerPoint 14.0 Object Library
Dim pPT As PowerPoint.Application
Dim pPTopen As PowerPoint.Presentation
Dim PptName As String
PptName = "c:\myfiles\ppt\12SQLIII.ppt"
Set pPT = New PowerPoint.Application
pPT.Visible = True
Set pPTopen = pPT.Presentations.Open(PptName)
pPTopen.SlideShowSettings.Run
End Sub

[Bob Phillips]

No, it can be opened from VBA



Set ppt = CreateObject("Powerpoint.Application")

[macroppt123]

Wow, I am talking to the genius. Kudos.

I am able to open the xls , but I get a compile error for the ppt file and it does not open the ppt file.

Should I give the Powerpoint file name in
Set pPTopen = pPT.Presentations.Open(PptName)

as

Set pPTopen = pPT.Presentations.Open("New Orig Package.ppt")

User_define type not defined.

Public Function open_File(file_name)
ChDir "F:\Focus\Copy Paste Project\TestExcelToPPT"

Workbooks.Open Filename:= _
"F:\Focus\Copy Paste Project\TestExcelToPPT\HFIDetails.xlsm"
End Function

Public Function open_ppt_File(file_name)
'Requires Tools > References... > Microsoft Office PowerPoint 14.0 Object Library
Dim pPT As PowerPoint.Application
Dim pPTopen As PowerPoint.Presentation
Dim PptName As String
PptName = "F:\Focus\Copy Paste Project\TestExcelToPPT\New Orig Package.ppt"
'Set pPT = New PowerPoint.Application
Set pPT = CreateObject("Powerpoint.Application")
pPT.Visible = True
Set pPTopen = pPT.Presentations.Open(PptName)
pPTopen.SlideShowSettings.Run
End Function

Regards,
macroppt123

[macroppt123]

Sorry, my bad. I did not have the library for Powerpoint checked. I just did but now I get a blank ppt App. I expect to see ppt file.

[Kenneth Hobs]

Leave the last line out of the function if you want to just open the ppt and not run it. Of course you could add an Optional 2nd parameter to the Sub or Function to Run or Open the ppt file.

You probably have a typo in your filename but you need an adjustment as well. I also added a Dir() to see if the filename passed exists. If you are going to use it as a function, then maybe:Sub Test_open_ppt_file()
'run_ppt_File "F:\Focus\Copy Paste Project\TestExcelToPPT\New Orig Package.ppt"
'run_ppt_File "c:\myfiles\ppt\12SQLIII.ppt"
MsgBox run_ppt_File("F:\Focus\Copy Paste Project\TestExcelToPPT\New Orig Package.ppt"), , "New Orig Exists?"
MsgBox run_ppt_File("c:\myfiles\ppt\12SQLIII.ppt"), , "12SQLIII.ppt Exists?"
End Sub


Public Function run_ppt_File(PptName As String) As Boolean
'Requires Tools > References... > Microsoft Office PowerPoint 14.0 Object Library
Dim pPT As PowerPoint.Application
Dim pPTopen As PowerPoint.Presentation
If Dir(PptName) = "" Then Exit Function
'Set pPT = New PowerPoint.Application
Set pPT = CreateObject("Powerpoint.Application")
pPT.Visible = True
Set pPTopen = pPT.Presentations.Open(PptName)
pPTopen.SlideShowSettings.Run
run_ppt_File = True
End Function

[macroppt123]

I was able to open the ppt file now. I realized that I had to specify teh below in my main module.

Question: The ppt file opens in slide show. I f we need to open in Normal view within vba code is that possible?

open_ppt_File ("pPTopen")

[Kenneth Hobs]

To run or open:
Sub Test_run_ppt_file()
'run_ppt_File "e:\City of Tulsa\Superpave Today.pptx" 'Open and play show
run_ppt_File "e:\City of Tulsa\Superpave Today.pptx", False 'Open only
End Sub

Public Function run_ppt_File(PptName As String, Optional tfRun As Boolean = True) As Boolean
'Requires Tools > References... > Microsoft Office PowerPoint 14.0 Object Library
Dim pPT As PowerPoint.Application
Dim pPTopen As PowerPoint.Presentation
If Dir(PptName) = "" Then Exit Function
'Set pPT = New PowerPoint.Application
Set pPT = CreateObject("Powerpoint.Application")
pPT.Visible = True
Set pPTopen = pPT.Presentations.Open(PptName)
If tfRun = False Then Exit Function
pPTopen.SlideShowSettings.Run
run_ppt_File = True
End Function