Save As help

[JoeSalmi]

Okay I need a little bit of help with this cause I just can't figure it out.

I am using this code to get my save as dialog to pop up which asks for a file name.

 With Application.Dialogs(xlDialogSaveAs).Show
       End With

But what I would like to have happen is the Save as box pop up with with this in the save as area:

ThisWorkbook.Name & "(" & Sheet1.Range("C4") & " " & Sheet1.Range("E4") & ")"

That way each week when the user saves the spreadsheet all they have to do is double check the name and click okay with out having to edit and type out another long name.

Can anyone help me out?

 

[Glory]

Have you considered just automatically saving the file as the desired filename, instead of using a popup dialogue?

 

[JoeSalmi]

Have you considered just automatically saving the file as the desired filename, instead of using a popup dialogue?

I have but the short version of the story is the use clicks a button which runs a macro and at the end of the macro it automatically saves the ss with the new name. Everytime the user enters a new week the ss will be saved with the new name.

I'm attempting to automate just about everything I can.

 

[Glory]

I don't understand, are you saying you've encountered problems with skipping the dialogue popup and saving the new filename directly?

I was able to find this... it's about Access but should work with Excel. The person who answered makes it sound like it's sort of a trick getting a string into that text box.

 

[repairman615]

Hello,

Maybe:

<font face=Courier New>     Application.Dialogs(xlDialogSaveAs).Show (ThisWorkbook.Name & "(" & Sheet1.Range("C4") & " " & Sheet1.Range("E4") & ")")<br><SPAN style="color:#007F00">'</SPAN></FONT>

 

[JoeSalmi]

Hello,

Maybe:

<font face=Courier New>**** Application.Dialogs(xlDialogSaveAs).Show (ThisWorkbook.Name & "(" & Sheet1.Range("C4") & " " & Sheet1.Range("E4") & ")")<br><SPAN style="color:#007F00">'</SPAN></FONT>

The Save As Dialog box pops up but the filename area is blank.

 

[JoeSalmi]

I don't understand, are you saying you've encountered problems with skipping the dialogue popup and saving the new filename directly?

I was able to find this... it's about Access but should work with Excel. The person who answered makes it sound like it's sort of a trick getting a string into that text box.

WOW that seems VERY complicated hahaha I'll hold on to it but I know there is an easier way. I'm actually pretty close but it seems that I may have to strip the file extension off the name before it attempts to save it.

 

[repairman615]

I am still trying...I think it may need to be a string somehow??

You can see this comes up as "Hello"
<font face=Courier New>   Application.Dialogs(xlDialogSaveAs).Show ("Hello")</FONT>

 

[repairman615]

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> SavinOK()<br><br><SPAN style="color:#00007F">Dim</SPAN> v <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><br>v = ThisWorkbook.Name & "(" & Sheet1.Range("C4") & " " & Sheet1.Range("E4") & ")"<br><br>   Application.Dialogs(xlDialogSaveAs).Show (v)<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Glory]

http://support.microsoft.com/kb/139723

Yeah, it might not be a bad idea to include the path as well.

u = ThisWorkbook.Path