?
Solved

Save Powerpoint Template into new folder using Excel

Posted on 2012-08-21
8
Medium Priority
?
879 Views
Last Modified: 2012-08-21
So I have a bit of a strange request that involves autosaving a PowerPoint presentation (a template that users will add to) to a custom generated folder on our network by using VBA in Excel.  I know how to create the folder and I know how to save the open Workbook to that folder (see code below), but I'm not quite certain how to rename and save the PowerPoint template that sits with the Workbook template and put it in the new folder.  This needs to be completely automated because we are not training the users how to do it manually (to avoid mistakes, typos, etc).  Any assistance/guidance would be much appreciated!

Dim strNewFolder As String
Dim strSaveAs As String
    
    Const conBaseFolder As String = "G:\GROUPS1\Sponsor\"

    strNewFolder = conBaseFolder & Sheets("Sponsor").Range("D39").Value
    strSaveAs = strNewFolder & "\" & Sheets("Sponsor").Range("D39").Value & ".xlsm"

    If Len(Sheets("Sponsor").Range("D39").Value) > 0 Then
        If Len(Dir(strNewFolder, vbDirectory)) > 0 Then
            MsgBox "This folder already exists."
            Exit Sub
        Else
            MkDir strNewFolder
            ActiveWorkbook.SaveAs strSaveAs, FileFormat:=52
        End If
    End If

Open in new window

0
Comment
Question by:Lord2FLI
  • 4
  • 4
8 Comments
 
LVL 23

Expert Comment

by:JSRWilson
ID: 38317981
It's not clear where the PowerPoint template is now, its name and what format it's in.
0
 

Author Comment

by:Lord2FLI
ID: 38318018
It's in the same folder as the Excel workbook (line 4, conBaseFolder identified in the provided code).  At the moment the PowerPoint template is a 97-2003 Presentation, not all users have migrated to 2010 yet.  I assume I have to identify the FileFormat like I did when saving the workbook, so I'll need to know both if it makes a difference in the process.  It's name is just PowerPoint Template and will change to the same identifier as the Excel workbook (it's a cell reference also identified in the code above, line 7)
0
 
LVL 23

Expert Comment

by:JSRWilson
ID: 38318035
Is the name of the PowerPoint fixed? can you not just use FIleCopy to move a copy?

FileCopy(Source Address, Destination Address)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:Lord2FLI
ID: 38318080
I might be able to do that, but I suspect they will want the filename to change.  I got myself in a pickle because I was able to do it for the Excel workbook, and when the users saw that they asked about including the PowerPoint template they use.  What I suspect I have to do is open the PowerPoint template, SaveAs into the new folder with the new name (pulled from the Excel workbook cell) and then close it, but I'm not sure how to go about doing this within Excel.
0
 
LVL 23

Accepted Solution

by:
JSRWilson earned 2000 total points
ID: 38318103
Probably not.

If there's only one PowerPoint file in the folder you can get the name with:

 sFilename = Dir$(conbaseFolder & "*.ppt*")

Then

sOldPath=conBaseFolder & sFilename
sNewpath=strnewFolder & "\"  & sFileName

FileCopy ( sOldPath,snewPath)
0
 

Author Comment

by:Lord2FLI
ID: 38318163
Code works if I take the parentheses out of FileCopy, much appreciated!  Accepting your solution as the answer.
0
 
LVL 23

Expert Comment

by:JSRWilson
ID: 38318196
Try:
FileCopy Source:=sOldPath, Destination:=sNewPath

Or just remove my previous ( )
0
 

Author Comment

by:Lord2FLI
ID: 38318211
Final Code:

Dim strNewFolder As String
Dim strSaveAs As String
Dim strPPFileName As String
Dim strPPOldPath As String
Dim strPPNewPath As String
    
    Const conBaseFolder As String = "G:\GROUPS1\Sponsor\"

    strNewFolder = conBaseFolder & Sheets("Sponsor").Range("D39").Value
    strSaveAs = strNewFolder & "\" & Sheets("Sponsor").Range("D39").Value & ".xlsm"

    If Len(Sheets("Sponsor").Range("D39").Value) > 0 Then
        If Len(Dir(strNewFolder, vbDirectory)) > 0 Then
            MsgBox "This folder already exists."
            Exit Sub
        Else
            MkDir strNewFolder
            ActiveWorkbook.SaveAs strSaveAs, FileFormat:=52

            strPPFileName = Dir$("G:\GROUPS1\Sponsor\PowerPoint Template.ppt")

            strPPOldPath = conBaseFolder & strPPFileName
            strPPNewPath = strNewFolder & "\" & Sheets("Sponsor").Range("D39").Value & ".ppt"

            FileCopy strPPOldPath, strPPNewPath
        End If
    End If

Open in new window


Thanks again! :)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

616 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question