Question about saving a workbook with a macro

[Colombo]

I've got a question about saving a workbook with a macro. I can save the workbook fine with the macro. What I would like to do is have the macro generate the file name based upon a given text & add the month year into the title of the file name.

So for example, I want to save the file name as "Feb 2003 Monthly Report.xls"

Is there anyway to setup the macro to use the date function or something else to generate the Feb 2003 based upon the current month, or possibly by looking at a cell in the workbook?

Thanks in advance for any help.

Josh

 

[LTunnicliffe]

ActiveWorkbook.SaveAs Filename:=Range("FolderNmae").value & Range("Filename").Value

Depending on whether your Folder Name has a "/" at the end of it will tell you whether you need to add one.

Hope this helps!

 

[LTunnicliffe]

anyone got an idea about creating a foldername based on cell in the workbook?

Do you want a formula that gives you the path of the current workbook?

 

[Colombo]

ActiveWorkbook.SaveAs Filename:=Range("FolderNmae").value & Range("Filename").Value

Depending on whether your Folder Name has a "/" at the end of it will tell you whether you need to add one.

Hope this helps!

I've added the "/" in the end of the foldername path

This is the code I have

ActiveWorkbook.SaveAs Filename:=Range("Foldername").Value & Range("Filename").Value

However I get the error "MS Excel cannot access the file C:\Monthly Performance Reports\06-2002"

I don't think it's creating the folder. I need it to create the folder based off a cell in the workbook, before it tries to save it to that location.

 

[Colombo]

anyone got an idea about creating a foldername based on cell in the workbook?

Do you want a formula that gives you the path of the current workbook?

No, I don't really need to know that. I guess what I'm looking to do is hard code the first part foldername. For example "c:\Monthly Reports\"

Everything would go in there. However I want to create a folder within the monthly reports folder. I want the name of the folder that is created in the monthly reports folder to be based off a cell in the workbook.

So for example, if the cell said 04-2002. I want the macro to create a folder 04-2002, within the monthly reports folder.

That make more sense? I should've been more clear in my question.

Thanks

josh

 

[Joe4]

If the date was in cell A1, to create that directory before your save statement, use MkDir.

Example:
MkDir "C:\monthly reports\" & Range("A1").Value

 

[Colombo]

Thanks, that worked.

Now I have another issue that was brought up with that. If the folder is already created I get an error message.

I want to create an if then statement that says if the folder already exists don't create it, but what I've tried so far doesn't seem to be working.

Sorry for the numerous questions.

Josh

 

[LTunnicliffe]

if len(dir("C:\monthly reports\" & Range("A1").Value))=0 then "C:\monthly reports\" & Range("A1").Value

Hope this works!

 

[Joe4]

Simply add this line before your MkDir command:

On Error Resume Next

Then after the MkDir command, set it back to

On Error Goto 0

This will, in effect, simply ignore an error.

Edit:
Using an IF string probably would be better, because this would also ignore other errors when trying to make a new directory, like if you tried to create a folder with an invalid name.

 

[Colombo]

if len(dir("C:\monthly reports\" & Range("A1").Value))=0 then "C:\monthly reports\" & Range("A1").Value

Hope this works!

This does work to create the statement if it doesn't exist, however I still get an error if it does exist. I tried swapping things around from 0 to 1, and then putting the Mkdir statement in. That didn't work. I played around with it, and still couldn't get it to work.

I'd like the if statement to look if the folder exists. If it does exist, I don't want it to do anything, just go to the next line. But if it doesn't exist I'd like it to use the mkdir command and create the directory.

Any suggestions?


For the time being I've used jmiskey's suggestion by using the On Error commands.

I do agree though, that it would probably be better to use an if statement if I can get it to work.

thanks

josh

 

[LTunnicliffe]

Narf...

if len(dir("C:\monthly reports\"))=0 then MkDir("C:\Monthly reports\")

Sorry, don't know what got into me.

LT