Choose file name Save As macro

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

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

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

Just add
ThisWorkbook.SaveAs (varResult)

You were doing great on post #8

gbaker · #3 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 · #4 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.

gbaker · #5 07-22-2014, 10:50 AM

Thanks Bob,
I would appreciate any pointers you could give me. For now I need to make this work. I've gone through 22 post and still can't get the code I need. I don't have enough knowledge in writing script to be able to complete this task.
All I'm simply trying to do is develop a code that will take the existing Workbook and Save it to a directory on my network and be able to rename the workbook so the original doesn't change.
I'll try another forum for now and see if someone can help me write the code. I'll get back to you when I have time to learn more about it.
Thanks for all your help!!!

BobBridges · #6 07-22-2014, 11:07 AM

"I can't take the time to know how; I just want it to work!"

But don't give up on the forum yet; Ribbons was helping you, maybe she still will.

gbaker · #7 07-22-2014, 11:54 AM

Hi Bob,
This project is very important and although I always want to know I can't take the time on this one.

I found a code that works. It allows me to save the workbook to a directory on the network, take a name from the A1 cell in the original, adds today's date which renames the workbook, and now original goes back unchanged for the next time I need to use it. It also removes a tab called UPDATE (where the buttons for the Macro's are located) from the new saved workbook: Thanks Again for your help!! I'll get back to you when time allows. Here is the code I found:

Code:

'Saves filename as value of A1 plus the current date
     
    Dim newFile As String, fName As String
     ' Don't use "/" in date, invalid syntax
    fName = Range("A1").Value
     'Change the date format to whatever you'd like, but make sure it's in quotes
    newFile = fName & " " & Format$(Date, "mm-dd-yyyy")
     ' Change directory to suit your PC, including USER NAME
    ChDir _
    "J:\ProgramOps\Exceptions Masters & Data\Aon Hewitt"
    ActiveWorkbook.SAVEAS Filename:=newFile
    Sheets("UPDATE").Select
    ActiveWindow.SelectedSheets.Delete
    ActiveWorkbook.Save

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

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