Save As Dialog Box with File Name Predetermined for Ppt file through Excel

[tbakbradley]

I have a large Macro for building a PowerPoint Sketch from information on an Excel File. The VBA is within Excel. Everything works as intended. The Macro will Open a Powerpoint file "MyTemplate.pptx" and it will update Text Boxes and Objects within that "MyTemplate.pptx" powerpoint. I do NOT want the user to accidentally click "Save" and it overwrite the Template with whatever their Macro just updated it with.

In testing, I thought I had this working, but never actually "Saved" my file, so assumed it worked until I put it in production and now I can't figure out the issue.

Here is what I have at the END of my VBA Code in Excel. My entire macro runs fine, it opens teh "MyTemplate.pptx" file, it updates everything I need it to on that PowerPoint sketch, and it even opens up the Save As Diaglog box with the File name pre populated with "CaseNumber_PrelimSketch.pptx". I didn't want to Save it so the User can choose the directory they want to save it. However, when I click Save, the dialog box disappears as expected, but the File Name shown on my sketch is still "MyTemplate.pptx". I didn't realize this until after I clicked Save, and made an adjustment to my powerpoint sketch and clicked the "Save" Button, then realized I saved a new "MyTemplate.pptx" File to the server. I have a backup and was able to resolve.....but this is an issue.

Any ideas what I did wrong below? Again, the Save As Dialog box does popup just like had I clicked Save As.....it even has the File Name pre-populated as described above, but when you click "Save" it closes and doesn't actually save the file to your destination nor file name.

Thanks!!

Dim defaultFileName As String
 Dim CaseNumber As String
 
 CaseNumber = Sheets("Data").Range("CsxCaseNumber")
 
 defaultFileName = CaseNumber & "_" & "PrelimSketch"
 
 With MyPPT.FileDialog(2)
 .InitialFileName = defaultFileName
 .Show
 End With

 

[Micron]

I didn't review your code because this may have a simple solution. Did you try just setting the template file Read Only property to true? I know you can make changes to a RO file but can't save it with the same name. At least not in the same directory. If the user wants to point to a newly created folder they can do that and save it with the same name, but at least your template file will be protected. Your modified file will then become the file that was just saved.

 

[tbakbradley]

I didn't review your code because this may have a simple solution. Did you try just setting the template file Read Only property to true? I know you can make changes to a RO file but can't save it with the same name. At least not in the same directory. If the user wants to point to a newly created folder they can do that and save it with the same name, but at least your template file will be protected. Your modified file will then become the file that was just saved.

That's an option IF I can't figure out why the Save As Dialog comes up correctly, using my Variables as the File Name, but won't actually "Save" the file when you click save. That's my ultimate goal....but if that can't work, then Protecting it may be my only choice. Priority is that once the ppt file opens, and the Macro populates all the data onto the ppt, to have them NOT save over the file. So Protecting it in the Macro after the Macro has finished is a start.....but I would like to force them to save it with a specific File Name if possible.

 

[Micron]

To be clear, I meant just right click on the template file in File Explorer and check the read only box. I'm not suggesting you try to set the file as read only via code. Then you can let Windows handle the protection.

 

[tbakbradley]

Ok. I was thinking Protection like I do with Excel. Yes, this worked fine to prevent folks from Saving over. I'd still like to mess around with that code and figure out why the Save As Dialog box pops up with the File Name listed per my variables, but doesn't actually Save the file when you click Save.

Thank you for this resolution to prevent it from being saved over

 

[Micron]

JMO but I don't think you've shown enough code to even begin to guess. I mean, perhaps you have set On Error Resume Next prior to that and an error is raised so the file isn't saved. Could be anything. Did you step through the code (F8) and ensure variables contain the values you expect and the flow is as you expect? I'm more familiar with Access vba and AFAIK, the save as dialog isn't supported so I have little experience with it. Mostly, I'd use Scripting.FileSystemObject to work with files.

 

[tbakbradley]

The variables are fine as they are used elsewhere in the code. The SaveAs Dialog Box opens up correctly and uses my Variables to populate the File Name correctly. The issue is, when you click the "Save" Button from the Box, it disappears like it should. You think it saved that file name to the folder you selected. But low and behold, it didn't. The Powerpoint file name still shows as the Template File name....didn't actually save. The code I added is just the portion I added to do the Save As with the File name at the end of my macro code, where it was writing data to Text Boxes in power point. Nothing special with that part. I did some googling and appears the .FileDialog isn't always supported, but didn't see where it is vs not or how to make it supported. For now, I am good that they can't over write my template following your Read Only suggestion. I'm happy enough with that. I plan to just remove the .FileDialog section of my code and remove that altogether. There are a couple other things I want to try when I get time, but don't want to put too much time into it if that doesn't work. As long as they can't save over the Template, that is good.

 

[Micron]

Using the File Save As tool in the back stage of Excel I get this dialog on the left. Using your code (as best I can) I get the one on the right.

Notice the difference? The only thing is that I had to use Application.FileDialog because I have nothing to substitute for MyPPT but maybe that doesn't matter. If your dialog caption is File Save then it's no wonder you can't Save As. Perhaps 2 is the wrong dialog constant (I believe it's not) or as you say, it's not supported in my and your version. If I wanted to go this route, I'd suggest using msoFileDialogFolderPicker and Scripting.FileSystemObject.

 

[tbakbradley]

You are absolutely right. I just checked it and never noticed it. I found the (2) was supposed to be the Save As, but that actually just opens up the "Save" and not the "Save As". At least now I'm not scratching my head in confusion on why it wasn't saving as a different file name and to a different folder.

 

[Micron]

I created a 1 to 5 loop and opened the dialog each time. All I got was File Open, File Save, Browse and Browse. 5 raised an error.
So it seems the documentation I read is wrong - there is no Save As (at least not for my version)