Save as with variable file name and directory (simple no!)

[heathb]

Hi All,

Have spent a few hours going through this forum again and cannot find my answer (Probably poor search technique))

I have a macro that finishes by copying a sheet (tab) to a new work book.

The problem comes when I try to save it. all the posts I have found hard code the file name and directory, these need to be selected by the users

Below are many tries, all those with " ' " do not work

attached is the output sheet that has to be saved (can't use template as to much sensitve data)

Please Login or Register  to view this content.

[Leon V (AW)]

Please Login or Register  to view this content.

should work fine. you probably want to replace activesheet with Sheets("IPLUS Output") if you want to do a specific sheet.

[heathb]

Thanks, not sure why this works now and not earlier

Is not saving the file as .xlsx though, just a type "file" (not given a choice to select it either) and I have to select the program to open?

[Leon V (AW)]

Please Login or Register  to view this content.

[heathb]

thanks guys,

could you please explain how this works, as my limited understanding of "GetSaveAsFilename" means show me names I could use

[Leon V (AW)]

http://msdn.microsoft.com/en-us/libr...ffice.11).aspx
they explain it well enough.

basically "GetSaveAsFilename" outputs a string combining the directory you selected and the filename you entered when you click save. It also limits what you can name the file as e.g. "\" as that would be seen as a folder by "Workbook.SaveAs"
if you click cancel it outputs false.

[heathb]

Sorry, this is where I am confused. Your link states:

Displays the standard Save As dialog box and gets a file name from the user without actually saving any files.

How does the code allow the user to save the file

Apologies for being slow

[Leon V (AW)]

that bit doesn't. Application.GetSaveAsFilename is just that, a file name. It couldn't save anything if it wanted to as it is part of the excel "APPLICATION" and not any open workbooks.

the file is saved on the last line of the code I supplied "ActiveWorkbook.SaveAs savingname" which saves the activeworkbook using the filename and location generated by "Application.GetSaveAsFilename" earlier in the code.

you could think of "Application.GetSaveAsFilename" as just a "GetName" command with a "FileSaveType" character validation built into it