Save workbook using variable for save as and for a new folder name

[TheNewGuy]

I have figured out how to save the workbook using a variable to saveas but I would like to create a new folder to place the new workbook into. I have tried a few things a am not having any luck. I recorded a macro and the code it produced is as follows.

Please Login or Register  to view this content.

As I said I tried this and it works fine to name the workbook the variable name but when I try to create a new folder it tells me the path does not exist.
So is there a way to create a new folder, and name it with a variable?
Thank you for your time, any advice would be great, and if I have not been clear just let me know. Thanks

[Paul]

You can use the MkDir command in VBA, like so:

Please Login or Register  to view this content.

Then change to it and save the file.

[Leith Ross]

Hello TheNewGuy,

The way your code is written, the folder must already exist for it to work. There a several ways to create a new folder. The simplest is to use MkDir. This only allows you to create one new folder at a time.

Example

Please Login or Register  to view this content.

[TheNewGuy]

Thank you for the quick reply.
You suggestion appears to work great!
I would like to complicate things a bit. I have created another variable named JobNumber, this variable will be the folder name and the variable foldername will be the workbook name. This way there is only 1 folder for each job and all the Items for that job will go into that folder. This is what I have so far...

Please Login or Register  to view this content.

It appears to work fine except that if some one makes a mistake and tries to create 2 workbooks with the same name the excel error msg appears and says that a file with that name already exists,and would I like to replace it. That is all fine except no matter what I select(yes,no, or cancel) the code gets int interrupted and returns an error.
The error is "method of saveas workbook failed"
also 2 workbooks are created named book1 and book2. Is there a way I can cause it to return back to my original workbook to allow the user to make corrections? I hope this makes sense. Thank you for your time

[TheNewGuy]

OK I have been working on this all weekend and have not made much progress.
The line that seems to be the problem is this:

Please Login or Register  to view this content.

I think the problem is that the duplicate file msg that excel displays is not actually an error msg. So, I think I am really going to need you experts on this one. So please help if you can I need a way to make this part of the procedure more user friendly. Any advice would be appreciated. Even if your idea is different. Thank you very much for your time.

[Paul]

You could easily check if the file exists first before saving it. (A quick Google search brought up many results for "vba check if file exists".) For example:

Please Login or Register  to view this content.

[TheNewGuy]

Oh Man, I should have thought of that!!! Thank you for your help. That seems to work and should eliminate the problem. Thank you very much for all your help with this.