SaveAs Dialogue Box

[Tony D]

Hi, I am trying to save a file to directory. I want the saveas dialogue box
to pop up with the file name and pathe I specify, but I want the user to be
able to change the path given that it changes on a monthly basis.

With Sheets("MissingSTAvg_Corp")
.SaveAs Filename:= _
"Q:\Results\March 06\MissingSTAvg_Corp" & InputBox("Please Enter
Date As MM-DD-YY")
End With

The "March 06" will change monthly. Thanks

[renegan]

Didn't test it but should work:

First create 2 ranges on your workbook for current_month and current_year

Sub Test()
Dim Input_Date as Date
Dim Current_Month as String
Dim Current_Day As String

Current_Month=Range("current_month")
Current_Year=Range("current_year")

Input_Date=InputBox("Please Enter Date As MM-DD-YY")

Sheets("MissingSTAvg_Corp").SaveAs Filename:= _
"Q:\Results\" & Current_Month & " " & Current_Year & "\MissingSTAvg_Corp" & Input_Date

End Sub

Don't forget spaces before and after the & signes.

[Tony D]

Thanks. I was already familiar with your suggestion. But what I really want
to do is be able to see the saveas dialog box at the suggested path and then
let the user change it from the dialog box. I think you can use:

Application.dialogs(xlDialogSaveas).show but I am not sure how to make it
open in the suggested directory with the suggested file name already in the
dialog box. Thanks again.

>
> Didn't test it but should work:
>
> First create 2 ranges on your workbook for current_month and
> current_year
>
> Sub Test()
> Dim Input_Date as Date
> Dim Current_Month as String
> Dim Current_Day As String
>
> Current_Month=Range("current_month")
> Current_Year=Range("current_year")
>
> Input_Date=InputBox("Please Enter Date As MM-DD-YY")
>
> Sheets("MissingSTAvg_Corp").SaveAs Filename:= _
> "Q:\Results\" & Current_Month & " " & Current_Year &
> "\MissingSTAvg_Corp" & Input_Date
>
> End Sub
>
> Don't forget spaces before and after the & signes.
>
>
> --
> renegan
> ------------------------------------------------------------------------
> renegan's Profile: http://www.excelforum.com/member.php...o&userid=10450
> View this thread: http://www.excelforum.com/showthread...hreadid=528716
>
>

[Patricia Shannon]

I think "FileDialog" might do what you want. Look it up in help, and see the
examples.


"Tony D" wrote:

> Thanks. I was already familiar with your suggestion. But what I really want
> to do is be able to see the saveas dialog box at the suggested path and then
> let the user change it from the dialog box. I think you can use:
>
> Application.dialogs(xlDialogSaveas).show but I am not sure how to make it
> open in the suggested directory with the suggested file name already in the
> dialog box. Thanks again.
>
> >
> > Didn't test it but should work:
> >
> > First create 2 ranges on your workbook for current_month and
> > current_year
> >
> > Sub Test()
> > Dim Input_Date as Date
> > Dim Current_Month as String
> > Dim Current_Day As String
> >
> > Current_Month=Range("current_month")
> > Current_Year=Range("current_year")
> >
> > Input_Date=InputBox("Please Enter Date As MM-DD-YY")
> >
> > Sheets("MissingSTAvg_Corp").SaveAs Filename:= _
> > "Q:\Results\" & Current_Month & " " & Current_Year &
> > "\MissingSTAvg_Corp" & Input_Date
> >
> > End Sub
> >
> > Don't forget spaces before and after the & signes.
> >
> >
> > --
> > renegan
> > ------------------------------------------------------------------------
> > renegan's Profile: http://www.excelforum.com/member.php...o&userid=10450
> > View this thread: http://www.excelforum.com/showthread...hreadid=528716
> >
> >