Macro Code to open Save As dialog box

[robertguy]

Hi,

What I am trying to achieve is to replace the ‘Save’ icon’ on my tool bar with a ‘Save As’ icon. I’m OK with creating a new icon on the tool bar but can anyone advise of the macro code I need to open up the Save As dialog box i.e. where I can then select the drive, folder and file name etc
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Any help would be greatly appreciated
<o></o>
Many thanks in advance
<o></o>
Rob
<o></o>
N.B. Excel version 2003

 

[Smitty]

Here's an example for the GetSaveAsFileName method:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> GetSaveAsFileName()<br>    <SPAN style="color:#00007F">Dim</SPAN> FileName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> Filt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, Title <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> FilterIndex <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, Response <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#007F00">'   Set to Specified Path\Folder</SPAN><br>        ChDir "C:\Documents and Settings\All Users\Desktop\"<br>    <SPAN style="color:#007F00">'   Set File Filter</SPAN><br>        Filt = "Excel Files (*.xls), *.xls"<br>    <SPAN style="color:#007F00">'   Set *.* to Default</SPAN><br>        FilterIndex = 5<br>    <SPAN style="color:#007F00">'   Set Dialogue Box Caption</SPAN><br>        Title = "Please select a different File"<br>    <SPAN style="color:#007F00">'   Get FileName</SPAN><br>        FileName = Application.GetSaveAsFileName(InitialFileName:=Range("A1"), FileFilter:=Filt, _<br>            FilterIndex:=FilterIndex, Title:=Title)<br>    <SPAN style="color:#007F00">'   Exit if Dialogue box cancelled</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> FileName = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            Response = MsgBox("No File was selected", vbOKOnly & vbCritical, "Selection Error")<br>            <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#007F00">'   Display Full Path & File Name</SPAN><br>        Response = MsgBox("You selected " & FileName, vbInformation, "Proceed")<br>    <SPAN style="color:#007F00">'   Save & Close Workbook</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> ActiveWorkbook<br>            .SaveAs FileName<br>            .Close<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[brettdj]

Another option is to bring up the SaveAs Dialog directly

Application.Dialogs(xlDialogSaveAs).Show

P.S. Hey there Smitty

Cheers

Dave