Hello and welcome to this video tutorial from Computergaga.com. And in this
video we will look at how to use the save as dialog in Excel VBA. And for this
example I have this invoice template, which we have to save regularly. Save
copies of invoices from this template. And you can see that in cell C5 of my
template I have a place where we enter the invoice number, and on the right hand
side of that we have a button which at the moment does nothing. But when we
click that button we want it to run a macro. And that macro will perform
various tasks, and then ultimately save the invoice into a typical folder And
this will save a copy of it using the invoice number that's entered as it's
name. Now this workbook is a macro enabled workbook. It is already saved
that way because we want to save the macro to it. And we want to use this save
as dialog for a familiar environment for our users of this invoice to be able to
save it. So let's click on the Developer tab and jump straight into Visual Basic,
so we can start to look at some of the key properties and methods involved in
using the save as dialog. Now I want to begin just by creating a very simple
macro, just focusing on this save as dialog for now. So I'm going to label the
macro as so. And we want to put this save as dialog within a With construct. We
don't need to do this, but this is going to make our code that little bit cleaner
to look at. So I'm just going to enter With
Application.FileDialog. As we're typing these intellisense lists come up
to assist us as we go. And when I put the open bracket in, we can see we are
prompted for four different file dialogs that we can use in Excel VBA.
The file picker, the folder picker, the open dialog and what we want, the save as
dialog. So I'm going to double click on that. Close off that bracket and put in
my End With statement. Now I'm going through some of the code reasonably
quickly here, because it's not in tutorial in learning VBA.
This is just on focusing on the save as dialog. So there's elements of this thats
unclear to you, please check out the link in the description of this video where
you can access my online Excel VBA course, which is much more comprehensive.
For now though we have the file dialog save as and there were others that you
probably recognize such as open and file picker that can be used in a similar
although different manner. Now within this With construct we want to
access some of the properties and methods which are interesting to us. And
the first ones that I wanted to focus on are Title. So most of these are optional.
In fact all of them are optional. Title is optional, but I would like to assign a
good and personalised and customised title. If I don't the title of the dialog
box will just be called file save. Which is certainly not a terrible thing, but
let's imagine I would like the text "Please choose a location and file name
to save". And obviously that is an amazing title rather than the standard file save.
Now underneath that I'm going to put .ButtonName. So starting with a period
here because we're all within the With construct. Saves me writing Application
.FileDialog blah blah blah multiple times. Here we're
going for button name. I can select it from this list of properties and methods.
And this is the name of the button that we will actually click to save the file. By
default it's called "Save". Let's imagine I want something different. I'm going to
label it "Save Invoice" for something a little bit more customised to this
specific situation rather than a generic "Save", which is obviously Perfectly okay.
Now the next, and final property of this example I want to do is .InitialFileName
Now this gives us the opportunity of not only assigning an initial file
name, but also an initial file path. And that's what I'm really, well I'm
interested in both, but I'm very interested in the file path here. Because
I have a folder called Invoices saved in my Documents folder that I want these
invoices to go into. Now if you do not specify a path, because you certainly do
not have to you. You don't even need to specify an initial file name. But if you
don't do it Excel just do its default stuff, which is probably the Documents
folder for you. Although you can change that in Excel options. And it will pick
up the file name you know depending on your file. And whether and where it can grab
some data from. So to demonstrate this for the initial file name. What I want to
do is I want to use some information from our file. So if I just minimise VBA
for a moment and just get back into that workbook. Here we are. Remember I want to
use this invoice number from cell C5. So I want information from cell C5 to be used
here. So let me just go back into the editor, and I'm going to enter Range("C5")
.Value. So used a value from that cell as the initial file name. The user can still
change it. Now in addition to that I wanted that file path.
Now if I just pop back down again and look into File Explorer. This is the
folder that I'm interested in. I have in my Documents folder an Invoices
folder which is currently empty. Hopefully won't be by the end of this
video. And that is where I want to save these invoices. Now I believe I have that
waiting for me on my clipboard. So in this same section. I'm going to paste in that
file path. Here it is, perfect. So I don't have to type all that. There's the path
to the Invoices folder. Now this will need to be entered as a string, so let me
wrap my inverted commas around it. And then I will use my ampersand to
concatenate onto that, the Range object. So now the official file name is
the path, and also the value of that cell. I'll need one more backslash on the end
there, to separate the end of that folder and and the file name, that cell value. You
don't have to worry about the extension of the file like .xlsx etc. Now we can
specify the initial file name with just a path and not a name. So it will take
the person to that area, but not, just use its default file name. We can specify
a file name and not a path. Or we can do both, which is what I've done here. So it
takes them to that area and suggests that name. Now there are other properties
that we can set. But those are the key three that I want to focus on in this
video. Now there are also two very important methods though to look at. And
the next one is .Show. So that is how we can actually make this dialog appear
on screen for the user. At the moment we've been changing information about it,
to customise its appearance rather than a generic save as. But we haven't really
got it to perform any actions. This is the first time to make it appear itself. So
this is probably a good opportunity to see it in action in then. If I just press the
Run button, the green arrow at the top of
this editor. That will run this and show me what it looks like. So here it is at
the moment. We have our title at the top, please choose location blah blah blah. We
have our initial file path up to Invoices there. We have the filename, just
realised I have not entered a number yet, so that is empty. There's nothing in C5.
And we have the button "Save Invoice". All customised as we wanted. If I just close
down that for the moment. And just pop back into Excel. Let me enter an invoice
number. 10074. One double zero seven four. Back into the editor, let's run that
again. And here you can now see that we do have that file name. That's a better
demonstration in addition to the other properties that we have set.
Ok I'll cancel that again. So that's good so far. But at the moment it doesn't
do anything. If somebody presses save. Nothing really happens. All that happens is
the current file path and filename populate a collection called
.SelectedItems. And we can then access that collection to do certain things. You
know, to then go and save any workbook we want really. Doesn't have to
be the active one. We can just access that, that file path and name. Its
essentially all it is. Don't even really even need to save it. We can do things with
that information, but typically save another workbook. What I want to do
though is save this workbook. And to do that, I just simply need the method of
Execute. So that Execute method will action that save button. So when you press
save, it performs the Execute method, and it will save the active workbook with
those details. So we've got it to appear on screen and
we've got it to execute. Now there is one more issue here. And that is
the potential, that rather than clicking the save button. A user might click the
cancel button. So we need to take that into account and get our macro really to
be responsive to that. So what I want to do is, where we have our .Show method.
let me zoom in. I'm going to simply write an If statement here, that if .Show is
equal to zero. Because if a user clicks the cancel button, the value of zero is
assigned to that method. So now I can get it to perform a different action. I won't
worry about any elseif. This is just if they click cancel, otherwise execute as
normal. And I'm going to show a message box to the user, to say that the file did
not save. I want to confirm that to them. And I'm going to show the VBCritical
icon. So it's kind of like a warning sign. So that will be quite nice just to
kind of draw the users attention to it. This is not a typical dialog, this is one
that you probably want to look at. And then I'm going to Exit Sub. So just
don't bother doing anything else. If they're not going to save it, then just
just quit what's going on. And then we execute. So let me zoom out and let's see
our macro in action. So here we go if I close down this editor window. And right
mouse click on my button, to assign macro. And I will assign this SaveAsDialog
macro. And then click away so it becomes an active button. And if I click on that
button. Here is our save as dialog with the properties we've already seen in
this video. Now if I click on the cancel button, there is an If statement in there
that is considering that. So I get the message box that the file did not save
with the critical icon. And when I click Ok,
just aborts the macro and does nothing. Now if we press Save Invoice again. And
this time I will press the Save Invoice button. And it will execute the save, but I
get this message warning me of a VB project, and whether I should save it
macro enabled. Now this is because the macro is stored in this file, so saving a
copy of it is also storing that macro in it. I don't want that. So I could just
click yes. There's absolutely no problem here. I could just click yes, and carry on
saving it. However I don't really want this message to appear when users are
doing this. So I'm going to click on no at the moment, I'm just going to press no.
End this this error, and just get out of that situation. And I'm going to go back
into the code, and stop that message from appearing. And just say look just carry
on and save it without a macro, we we know what we're doing here, we're
programmed it for it. We don't need that alert each time. I will click on the
Developer tab, and then the Visual Basic button, to get back into the code. And
where we have that Execute method, just before and after it,
I want to disable the alerts. So I'm going to put Application.DisplayAlerts
equals False to switch them off. And I will copy and paste that line
underneath Execute to set it to True, to switch them back again. Because alerts
are important, they are alerting the user to stuff. But directly before that
Execute and directly back after it will go back on. So turn them off I know
what alerts to expect here. I know it's going to ask this, but we're not
interested in it. But then switch them straight away back on afterwards, because
they are important to to be aware of these things.
So with those in there, we can now go and see our code working perfectly.
I'm just going to add my On Error Resume Next statement at the top
to push through any errors that might be displayed by my connection to the
Onedrive, which I'm using right now. Close down this editor window, press my Save
Invoice button again, and we'll get this typical dialog again. And this time if I
press Save Invoice it will work as expected. I can see at the top it's been
saved under the one double zero seven four name. And if I switch over to my
folder, I can see that it is there. So that has been saved as expected by using
our save as dialog. So it provided a familiar interface for Excel users to
feel comfortable in what they're doing. So there's many different ways of saving
in Excel VBA, but by initiating these dialogs certainly gives those users
that familiar setting. And the potential to change locations and names,
and create folders and other stuff that we we know and love about it.
I hope you found this video useful. Please check out some of our other video
tutorials on our YouTube channel. And come check us out at Computergaga.com