file save as dialog with new filename

[John Keith]

Is it possible to open the file save as dialog window and change the
name of the file from the current name?


John Keith
(e-mail address removed)

 

[OssieMac]

Hi John,

Is this what you are looking for?

Application.Dialogs(xlDialogSaveAs).Show

 

[John Keith]

Hi John,

Is this what you are looking for?

Application.Dialogs(xlDialogSaveAs).Show

Not quite. That opens the dialogue box which is what I need in order
to let a user select the location but I want to set the filename to
something different than what the current workbook name is.



John Keith
(e-mail address removed)

 

[Gary Keramidas]

this should save it to whatever name you give it, if that's what you want.
you'll need to supply the path, too.

activeworkbook.SaveAs "YourFilename.xls"

 

[OssieMac]

I don't understand John.
Your question was "Is it possible to open the file save as dialog window and
change the name of the file from the current name?"

You can change the file name in the dialog box as well select the location
for the file.

 

[John Keith]

this should save it to whatever name you give it, if that's what you want.
you'll need to supply the path, too.

activeworkbook.SaveAs "YourFilename.xls"

Obviously I wasn't clear enough on my original post. :-(

I want the user to select the path but the new file name is fixed and
is different from what the currentl workbook is.

My co-worker, who will also be using this macro, likes to save files
to her desktop, while I like to put items in My Documents. So I want
to use the save as so that each of us can can pick the location that
matches our preferences. The file name needs to be different from the
current name because this is actually one part of a multi part
operation and after each step the current contents of the workbook are
saved under a different filename. I did all the development work on
this macro and set it up to save the resulting files to My Documents
and it wasn't until today when I shared it with my co-worker that she
asked "can it save to my desktop?".



John Keith
(e-mail address removed)

 

[John Keith]

I don't understand John.
Your question was "Is it possible to open the file save as dialog window and
change the name of the file from the current name?"

You can change the file name in the dialog box as well select the location
for the file.

I don't want the user to fill in the name of the file. I want the
macro to fill in the name of the file but I want the user to select
the path to where that file will be saved.



John Keith
(e-mail address removed)

 

[John Keith]

I don't want the user to fill in the name of the file. I want the
macro to fill in the name of the file but I want the user to select
the path to where that file will be saved.

BTW, I did do some web searching before posting here to see if I could
find a solution. I did find some examples of the same question I am
asking but none of the responses solved the problem. I'm hoping the
experts lurking here will have an idea.

Anyway, thanks all for looking so far. I've got to turn in now, a very
early appointment tomorrow.


John Keith
(e-mail address removed)

 

[MeistersingerVonNurnberg]

Hi -

Try the following please and see if that works.

-good luck



Sub dlg_test1()
Const c_fType = ".xls"

Dim var_FileName As Variant
Dim sNewFile As String

var_FileName = Empty
sNewFile = "MyNewFile" & c_fType

var_FileName = Application.GetSaveAsFilename( _
InitialFileName:=sNewFile, _
fileFilter:="Microsoft Excel Workbook (*" & c_fType & "), *" &
c_fType, _
Title:="MyApp Save As...")

If var_FileName = False Then Exit Sub
If Len(CStr(var_FileName)) = 0 Then Exit Sub


' DO WHATEVER HERE


var_FileName = Empty

End Sub

 

[Tim Zych]

zorvek posted this excellent method at Experts-Exchange, and there are some
other methods too.

There is a non-trivial amount of code here, but you can just paste this in a
new module and use it immediately...it is easy to use and flexible.

http://www.experts-exchange.com/Sof...Office_Suites/MS_Office/Excel/Q_23560018.html

 

[Tim Williams]

If you just want the user to pick a folder then google "browse folder vba".
Which solution works best for you may depend on the target version of Excel.

Eg:
http://www.cpearson.com/excel/BrowseFolder.aspx
http://www.mrexcel.com/forum/showthread.php?t=294728

Tim

 

[Tim Zych]

Tim,

Of course that is valid approach too, but I find the Windows Browse For
Folder dialog very "work intensive" to use. It is a tiny dialog and gets
tedious to use almost immediately.

I'm curious if others have a similar experiences with this dialog. Whenever
I encounter this being used for file-management applications where directory
choice is somewhat regular, I want to bolt. But I'm more of a typist than a
mouse-clicker, so others may not mind.

Just my $.02.

Regards,

 

[John Keith]

Thanks for all the pointers that arrived late last night.

I'll check them out later today and report back.


John Keith
(e-mail address removed)

 

[Tim Williams]

I'm not a big fan of that little dialog either, but if picking a directory
is the aim and that's the only solution then it's a bit confusing to present
the user with a "save as" dialog.

The "shell" approach on Chip's page does offer options for the appearance of
the dialog (BIF_NEWDIALOGSTYLE, BIF_EDITBOX): if your target users'
environment supports it then that's the option I prefer.

Tim