Save As Dialog Box VBA

[michaelp1029]

Here's what I need to do. I need to press a button and have the file I am currently to saved to a folder called "N:\Group\Gateway\McCarthy's Sausage Factory\Jim Monthly Reports". So I need a macro that brings up a Save As dialog box and automatically goes to this folder. I am a beginner macro guy so some help would be appreciated.

Danke!!

Mike

 

[ajetrumpet]

try googling this:

ms excel save as VBA

 

[Kenneth Hobson]

Sub Test()
  SaveAs "N:\Group\Gateway\McCarthy's Sausage Factory\Jim Monthly Reports\"  & _
Format(Date, "yyyymmdd") & ".xls"
End Sub

Sub SaveAs(initialFilename As String)
  With Application.FileDialog(msoFileDialogSaveAs)
    .AllowMultiSelect = False
    .ButtonName = "&Save As"
    .initialFilename = initialFilename
    .Title = "File Save As"
    .Execute
    .Show
  End With
End Sub

 

[michaelp1029]

Everything in the code works but the file doe snot actually save to the folder. Everything goes through like it's supposed to. It pulls up the Save As dialog box in the folder I want but when I press save, the file doesn't save. I am using the exact code in the previous post

 

[michaelp1029]

Nevermind. I got it to work. Many thanks

 

[Kenneth Hobson]

This will give you more flexibility.

Sub Test()
  Dim myPath As String, v
  myPath = "N:\Group\Gateway\McCarthy's Sausage Factory\Jim Monthly Reports\" & _
    Format(Date, "yyyymmdd") & ".xls"
  'myPath = "x:\" & Format(Date, "yyyymmdd") & ".xls"
  v = SaveAs(myPath)
  If v <> False Then ThisWorkbook.SaveAs v
End Sub

Function SaveAs(initialFilename As String)
  On Error GoTo EndNow
  SaveAs = False
  With Application.FileDialog(msoFileDialogSaveAs)
    .AllowMultiSelect = False
    .ButtonName = "&Save As"
    .initialFilename = initialFilename
    .Title = "File Save As"
    '.Execute
    .Show
    SaveAs = .SelectedItems(1)
  End With
EndNow:
End Function