Get a month of TabletWise Pro for free! Click here to redeem

Adding and Saving Workbooks - Part 2

10 minutes
Share the link to this page
You need to purchase the class to view this lesson.
One-time Purchase
List Price:  $139.99
You save:  $40
List Price:  د.إ514.18
You save:  د.إ146.92
List Price:  A$201.42
You save:  A$57.55
List Price:  ৳11,885.38
You save:  ৳3,396.06
List Price:  CA$190.22
You save:  CA$54.35
CHF 94.11
List Price:  CHF 131.76
You save:  CHF 37.64
List Price:  kr922.75
You save:  kr263.66
List Price:  €123.88
You save:  €35.39
List Price:  £110.90
You save:  £31.68
List Price:  HK$1,085.28
You save:  HK$310.10
List Price:  ₹10,526.61
You save:  ₹3,007.82
List Price:  RM597.19
You save:  RM170.64
List Price:  ₦54,241
You save:  ₦15,498.53
List Price:  kr1,319.67
You save:  kr377.07
List Price:  NZ$212.70
You save:  NZ$60.77
List Price:  ₱6,925.45
You save:  ₱1,978.84
List Price:  ₨23,257.07
You save:  ₨6,645.35
List Price:  S$194.74
You save:  S$55.64
List Price:  ฿4,386.84
You save:  ฿1,253.47
List Price:  ₺960.89
You save:  ₺274.56
List Price:  R2,348.19
You save:  R670.96
Already have an account? Log In


Hey guys welcome back. So in this video we're going to discuss the workbook dot Save as method. So the workbook dot Save as method allows us to save a newly created workbook to a certain location. And what we're going to do here we're going to add a new workbook and try to save it. So first of all, we're going to use this statement here workbooks, to add a new workbook or to create a new workbook. And this is similar to pressing Ctrl N on your keyboard, and then active workbook Save As to save the workbook.

So let's go through these lines of code step by step by pressing f8 on our keyboard. So now we've added a new workbook and the reason I've used active workbook here is because when you create a new workbook, it becomes the active workbook. So you can see here when we executed the first statement, workbooks that add a new workbook was added here with the name of book one. So if we execute this statement here right away and we haven't provided a location or anything, you can see that the statement one through five And actually, this workbook book one has been saved. It's been saved actually in the default location for saving workbooks, which is actually my Documents folder as well. So if you check that you can see here that book one has been saved in the Documents folder.

Of course, this is not the recommended way of doing it because it is better to provide a location and a file format. So actually, I'd recommend when use the Save as method to always specify a file name and a file format. And I'll tell you why in just a second. So actually, in the file name parameter here, you need to provide the full path for the directory or for the folder that you need to save the file in. And you need to also include the file name and its extension as well. So let's say I'm going to save it actually to the test folder on my desktop.

I'm going to call it test and it's an extension is going to be actually dot XLS M. Okay, so I've closed book one that we had saved to the Documents folder and I'm going to execute the code Again, and watch what happens now when I actually provide the file name and do not provide the file format parameter. So actually now a new book has been open here book two. So if I tried to save that, it actually does not save and it says the extension cannot be used with the selected file type change the file extension the file, name textbox, or select a different file type by changing the Save as type. So because I have not provided the file format parameter, I gotten an error. And actually sometimes and don't get an error, if actually the extension I'm saving with is the same as the default extension for Excel that I've set in the Excel settings, which you can set actually by going to File and then options and then save.

Okay, and this is actually the default extension and although the default extension is the same, it has not saved but sometimes actually saves. But this is why you You need to actually develop a habit of always using the file format parameter, as this will save you any problems. So you need to make sure to use the file format parameter all the time. And to determine the valid values for the file format parameter, you can actually go to the workbook the Save as page on MSDN on the Microsoft directory network by highlighting this active workbook, save as and pressing f1 on your keyboard or you can just google workbook Save as method MSDN. And you should be able to reach that page. So let's go to the page on the Microsoft directory network.

So I'm going to highlight that and press f1 on my keyboard. Okay, so we're now on the workbook, save as method page on the Microsoft directory network. And actually, you can see here, here's the fall format parameter. and here if you click on this link here, Excel file format, you're going to be broken To the page that contains all the file formats that you can put into that parameter. So here are all the file formats that you can put, okay, these are the Excel file formats. You can either input the name or you can input the value.

Okay? So you can either input for example, Excel add in or input 18. Okay, so one of the famous ones that you would use a lot is the macro enabled workbook, the Excel macro enabled workbook, a school Excel, Open XML workbook macro enabled, this is the one or you could also input 52. That would work as well. So I'm just going to copy that here. I'm going to head back to my Visual Basic Editor.

Okay, so I'm back on the Visual Basic Editor and I've pasted the value for the file format parameter here. And now if you're trying to run the code, I'll step through it step by step. So workbooks dot add, and then active workbook. Save as With that, this donation and with that fall format, and you can see here that the code has run fine and we've been able to save the file. So let's check our test folder on my desktop. Okay, so here's the test folder on my desktop.

And you can see here, we've been able to save the file and its type is a Microsoft Excel macro enabled worksheet. Okay, guys, so I want to explain more parameters on the Save as method. So actually, you can see here that this code is taking so long it's so horizontally, it's taking a lot of real estate. Actually, I can go to a new line and still type parameters into the Save as method by pressing a space and then an underscore and then I can press enter to go to a new line. And now I can continue typing into the Save as method in the new line. So to space and then an ns score and then you press Enter.

And now we are in a new line. But we're still typing values into the parameters for the workbook dot Save as method as you can see here. Okay guys, so now we're going to explain another parameter, which is the password parameter. And as its name implies, this parameter allows you to create a password for your workbook. So if use the password parameter here, password colon equals and then you need to write your password as string. So you need to write it between double quotes.

So say I'm going to make it VBA course is going to be my password. And this password is case sensitive. And that means that you need to stick to the same casing for the letters that you've typed the first time. So for example, VBA and the letter C need to be written in capital letters. And the rest of the doctors need to be written as small letters. Okay, so let's say I'm going to save my file with a password of VBA course and I'm going to make the file name to be password in the same location, which is my test folder.

So if I run this macro, the file will be saved in that folder with the password which is VBA course. So let's check that out. Okay, so here is my password file here. So if I open it, you can see here that it's telling me that it's password protected. And I need to type in a password. So I've typed the password and I'm able to access the file.

So this is what the password parameter does. So I'm going to close the file here. I'm going to go back to my Visual Basic Editor. Now we're going to explain another parameter which is the right rez password, which basically restricts writing to your file. So that means that no one can make changes in your file unless he types the password. So the first password parameter that we've just explained here, this denies any access to your file except if you type the password but this one, the right rez password, so writing restriction password, this is actually allows people to access the file.

Even if you don't type the password. So they there is a read only option. But if they don't type the password, they won't be able to change anything in the file. They can read its contents, but they cannot change anything. Okay, so let's say I'm gonna make the password the rights rez for example, like that, there's going to be my password, and the file name, I'm going to make it right whereas as well, and I'm actually going to remove this password parameter here. Just say I'm just going to create a writing restriction password only.

And if I run this macro, this is actually going to save my file with the name right trez and with a writing restriction password as well. So let's check that file. Okay, guys. So here is my file here. I'm going to open it and it's going to tell me that I can only open it as a read only if I don't type the posture but I if I need to have right access. So if I need to have editing, access I'd be able to edit the file and need to type this faster.

If I type it, I'm able to edit the file. And I'm able to save it as well save my, what I've edited, but let's just close it and reopen it again. Alright guys, so I've reopened it again. And if I asked for a read only access on I try to edit in the file and try to save it as it tells me that I cannot save it because the files are read only. And it actually prompts me to save another copy of it, I cannot save my changes on the same copy. So I can go to the browser and start saving as usual.

Okay, guys, so we're not going to go into the rest of the parameters because these are actually more than enough for a beginner in VBA. I'm actually going to include also a link for a blog post that actually explains the rest of the parameters, though they are not as important and you probably won't use them, especially if you're a beginner and excel vba and you're still trying to learn excel vba. So actually, these parameters that we've explained are more than enough for a beginner which is actually what this course is started at for this course is targeted for beginners. So that's it guys for the workbook. Save as method. Thank you very much for watching this video and I'll see you on the next one.

Sign Up