Choose file name Save As macro

[gbaker]

I am trying to create a macro that would allow me to choose a new file name. How do I stop the Macro when it get to the folder and be able to name the file what ever I want.

[BobBridges]

Gbaker, ask this again on the other Excel thread, the one about Excel programming, and you'll get more detail. But I'm pretty sure you'll find what you need if you look up Application.FileDialog(msoFileDialogSaveAs).

[gbaker]

How can I incorporate the code into the macro I have.

Sub SAVEAS()
'
' SAVEAS Macro
'

'
    ChDir "J:\ProgramOps\Exceptions Masters & Data\Aon Hewitt"
     
    ActiveWorkbook.SAVEAS Filename:= _
        "J:\ProgramOps\Exceptions Masters & Data\Aon Hewitt\Aon Hewitt Exceptions 07-07-14.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    Sheets("IMPORT-SAVE").Select
    ActiveWindow.SelectedSheets.Delete
    ActiveWorkbook.Save
    Application.Left = 58.75
    Application.Top = 41.5
    Sheets("Hewitt Delayed Retirement Plans").Select
    Range("E25").Select
    Sheets("Retirement Update").Select
    Range("G19").Select
End Sub

[WeDonNeedNoSteenkgRibbons]

Assuming this hasn't been moved to the other forum as seems imminent

You don't need what he suggested if you use .SaveAs as you've shown. Test your code. You wiill see that it stops and presents the dialog you want with the .SaveAs command.

It looks like you set it up right too.

[gbaker]

Hi,
Thanks anyway but I did run the macro and it saves the file whee I want it but doesn't let me change the name for the file.

[WeDonNeedNoSteenkgRibbons]

Below is what I get from

sDestPath = Application.GetSaveAsFilename(, , , Title:="Set destination dir")

I apologize, I thought and incorrectly said that the command to do this was .SaveAs, but it was .GetSaveAsFilename. Thank you for your patience.

[gbaker]

OK Thanks, How do I put this code in the macro below:

Sub SAVEAS()
'
' SAVEAS Macro
'

'
    ChDir "J:\ProgramOps\Exceptions Masters & Data\Aon Hewitt"
     
    ActiveWorkbook.SAVEAS Filename:= _
        "J:\ProgramOps\Exceptions Masters & Data\Aon Hewitt\Aon Hewitt Exceptions 07-07-14.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    Sheets("IMPORT-SAVE").Select
    ActiveWindow.SelectedSheets.Delete
    ActiveWorkbook.Save
    Application.Left = 58.75
    Application.Top = 41.5
    Sheets("Hewitt Delayed Retirement Plans").Select
    Range("E25").Select
    Sheets("Retirement Update").Select
    Range("G19").Select
End Sub

[gbaker]

I tried the code Bob gave me and it works except it goes to my C Drive and I want the path to be another network drive. How do I write that in.

Path = J:\ProgramOps\Exceptions Masters & Data\JPM\

Sub Saveandget()


FileName = Application.GetSaveAsFilename

MsgBox (FileName)

ThisWorkbook.saveas (FileName)


 Sheets("UPDATE").Select
    ActiveWindow.SelectedSheets.Delete





End Sub

[WeDonNeedNoSteenkgRibbons]

Replace your .SAVEAS command with mine.

A tip - I recommend not using "reserved words" for user names. Even sub MySaveAs() is better, although an even more descriptive name could be used.

If you're going to work with code I would suggest getting real good with the Immediate window (debug window) in the VBA interface, and F8 and shift-F8 to step through code, and shift-F9 to inspect or watch variables. Then you can follow the code line by line and observe what it does.

[gbaker]

Replace your .SAVEAS command with mine (Not sure what you mean)

Can you show me?

Sub Saveandget()


FileName = Application.GetSaveAsFilename

MsgBox (FileName)

ThisWorkbook.saveas (FileName)


 Sheets("UPDATE").Select
    ActiveWindow.SelectedSheets.Delete





End Sub

[gbaker]

Getting lost. How does this line of code fit in:
sDestPath = Application.GetSaveAsFilename(, , , Title:="Set destination dir")

[WeDonNeedNoSteenkgRibbons]

Use the .getsaveasfilename line instead of the line you have with the .SAVEAS command, and you can navigate to the location you want, and type in a filename too.

Or use .Saveas if you want to specify the filename in code; it will save in the current directory (there's a CD or chdir command to set that if you need to; ask if you need it)

.FileDialog is interesting, allowing multiple selection for one thing, but is not what I think you want.

[gbaker]

Thanks Anyway, but I found a solution and it works perfectly!!!

Sub SaveAsNew()
Dim varResult As Variant
'displays the save file dialog
varResult = Application.GetSaveAsFilename(FileFilter:= _
    "Excel Files (*.xlsx), *.xlsx, Macro Enabled Workbook" & _
    "(*.xlsm), *xlsm", Title:="Some Random Title", _
    InitialFileNameH:\Exceptions Reports\Folder to Start")
'checks to make sure the user hasn't canceled the dialog
If varResult <> False Then
    Cells(2, 1) = varResult
End If
End Sub

Thanks again for trying to help!!!

[BobBridges]

Ribbons, the file dialog can be set to accept only one file when that's what you need. Its real utility (in my opinion) isn't to replace GetSaveAsFileName and the like, but to provide a way to let the user indicate a file path and name to a program when the program isn't going to use it for ordinary opens and saves. I use it, for example, when I'm going to read a text file that the user downloaded from the mainframe; the user can then run my VBA program, using the file dialog to point it to the file just downloaded, and the program then reads that import and processes it.

[WeDonNeedNoSteenkgRibbons]

Does this sound right?

- .FileDialog to (pre)specify folder
- .SaveAs to (pre)specify filename
- .getsaveasfilename to (pre)specify neither

By the way, Bob, and you probably also know this well - the file dialog has more bonus utility available. As it's closely related to or integrated with Explorer or folder displays, you can do many of the same things. Thus when in Excel (which is 100% of the time for some of us, right?), if you don't feel like opening a folder, just go alt-F/O or alt-F/A and e.g. delete or open files from there, including nonExcel ones. Often the current dir in Excel is already sitting on a location where you're doing other file operations on a project, so it's a little timesaver.