Choose file name Save As macro

WeDonNeedNoSteenkgRibbons · #1 07-16-2014, 07:11 AM

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.

BobBridges · #2 07-19-2014, 08:57 AM

I have occasionally deleted files, renamed them, or created folders while in an Excel file dialog. But I hardly ever plan that ahead of time; it's more of an oh-while-I'm-there sort of thing.

But as for opening a non-Excel file: If I open (say) a text file from within Excel, it just tried to import it into Excel, no?

gbaker · #3 07-22-2014, 08:52 AM

Hi Ribbons,
I thought the code below would work. It does to a point. It gets me to the folder on the network and also allows me to change the name for the file but when I click save it doesn't actually save the file. Any Idea's, I really need to get this to work:

Code:

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", _
    InitialFileName:="\\fngn.com\us\Projects\ProgramOps\Exceptions Masters & Data\Aon Hewitt")
'checks to make sure the user hasn't canceled the dialog
If varResult <> False Then
    Cells(2, 1) = varResult
   
End If

I think I am missing something.
Thanks in advance
gbaker

BobBridges · #4 07-22-2014, 09:08 AM

I've never used the GetSaveAsFileName dialogue, but that's exactly what the documentation says about it: "Displays the standard Save As dialog box and gets a file name from the user without actually saving any files."

I suppose the next step would be to take the name collected from the dialogue call—that's varResult—and use it in workbook.SaveAs.

gbaker · #5 07-22-2014, 09:12 AM

How do I actually do that? Can you help me?

WeDonNeedNoSteenkgRibbons · #6 07-22-2014, 09:32 AM

Just add
ThisWorkbook.SaveAs (varResult)

You were doing great on post #8

gbaker · #7 07-22-2014, 09:50 AM

Like This, Wish I new more about writing code, I don't know where to put the line to make it work:

Code:

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", _
    InitialFileName:="\\fngn.com\us\Projects\ProgramOps\Exceptions Masters & Data\Aon Hewitt\Folder to Start")
'checks to make sure the user hasn't canceled the dialog
If varResult <> False Then
ThisWorkbook.SAVEAS = varResult

BobBridges · #8 07-22-2014, 10:15 AM

Gbaker, Ribbons had the right form. But maybe this is a good time to help you know more about writing code, if you want to take the time for it.

The statement you suggested...

Code:

ThisWorkbook.SAVEAS = varResult

...would take the value of varResult and put it in another variable named SaveAs—in this case a variable that belongs to an object named ThisWorkbook. But SaveAs isn't a variable (or "property") of ThisWorkbook, it's something called a "method", ie it does something. So it's as though "ThisWorkbook.SaveAs" is a command, and "varResult" is the argument you supply to tell it how to carry out that command. In this case, SaveAs is a command to save ThisWorkbook under a new name, and varResult contains the name to be used.

So how do you know when something is a property and when it's a method? Sometimes it's obvious; but when it isn't, you have to look it up in the documentation. Do you know where the VBA/Excel documentation is? Because if you don't, it's well worth knowing. I can give you some pointers for looking through it, if you need them.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Macro to save as pdf with ability to choose save as folder	rvessio	Word VBA	4	07-25-2016 12:37 PM
Outlook 2010 Macro Save as MSG, Choose Destination, set default filename	rslck	Outlook	1	06-19-2014 10:16 AM
Save As Macro that changes the file name also	rosscortb	Word VBA	5	05-19-2014 08:40 AM
How do I save a Word file with a macro for distribution?	leemoreau	Word VBA	3	10-04-2013 08:06 AM
Word Macro: Save file as text with current file name	jabberwocky12	Word VBA	2	10-22-2010 12:23 PM