SaveAs via VBA code

**ainnocent1983** · 03-31-2010, 05:48 AM

I'm sure this is a very simple one for most people! I'm trying to run a save as from code, but has a variable filename and to a variable directory so needs user input.

I have set the ChDir to the root directory and used the GetSaveAsFilename application to open the save as dialogue box. This opens up the Save As box, to the correct directory, with the filename in the correct box. However, when I hit save, the box closes, sub ends as planned but the file has not been saved.

Any ideas?
Cheers
A

ChDrive ("S")
Private Sub SaveAs_Control_Sheet()

ChDir ("S:\CLIENTS")
MsgBox "Select the folder in which to save the new control Sheet", , "Control Sheet Setup"
Application.GetSaveAsFilename

End Sub

**nchavan** · 03-31-2010, 06:08 AM

Hi,

Please try below code:

'ChDrive ("S")
Private Sub SaveAs_Control_Sheet()
    Dim fileSaveName
    ChDir ("S:\Clients\")
    MsgBox "Select the folder in which to save the new control Sheet", , "Control Sheet Setup"
    fileSaveName = Application.GetSaveAsFilename(FileFilter:="Excel Files (*.xls), *.xls")
    If fileSaveName <> False Then
        MsgBox "Save as " & fileSaveName
    End If
    ThisWorkbook.SaveAs fileSaveName
End Sub

**JeanRage** · 03-31-2010, 06:11 AM

Hi,

You right with the variables ...
With your full path (i.e. including the drive letter) stored in the variable strwkbPath, and
your file name stored in the variable strwkbName, you could use
following intruction :

ActiveWorkbook.SaveAs Filename:=strwkbPath & strwkbName & ".xls"

HTH

**buran** · 03-31-2010, 06:22 AM

Hi,
GetSaveAsFileName display the standard dialog and get user input, but doesnot save anything. Use something like this:

Do
    fName = Application.GetSaveAsFilename
Loop Until fName <> False
ActiveWorkbook.SaveAs Filename:=fName

Eventually, instead of

ActiveWorkbook.SaveAs Filename:=fName

you may use

ActiveSheet.SaveAs Filename:=fName

to save only one sheet as different workbook

@nchavan: In your code, if user select to Cancel it will save the file with name False.xls
Also, if you use ThisWorkbook and the routine is in Add-in, it will save the add-in.