+ Reply to Thread
Results 1 to 3 of 3

Call in Variable File Directory and Filename from Cell into Save As Prompt

  1. #1
    Registered User
    Join Date
    03-08-2013
    Location
    Logan, UT
    MS-Off Ver
    Excel 2007/2010
    Posts
    1

    Call in Variable File Directory and Filename from Cell into Save As Prompt

    Hi Everyone,

    This is my first time posting in this forum (or any for that matter), but I have used the forum extensively and have really appreciated the value of the knowledge here. I really need some help that I'm sure will be simple, but I am very new to VBA so it's difficult for me to piece together.

    I basically just want a macro that will activate every time the Save or SaveAs functions in Excel are pressed, that will open up the SaveAs Dialogue with a file name and file directory in Sheets("Input Sheet").Range("BN1"). The value in BN1 is a concatenated filename and directory that changes based on an order number and today's date (currently "13-MELLON INV\13-Trans Stmts\1st Qtr\02-FEB\1302 - 1111A Trans Statements.xls). I would like the "SaveAs" dialog to open up to "S:\13-MELLON INV\13-Trans Stmts\1st Qtr\02-FEB\", with "1302 - 1111A Trans Statements.xls" as the suggested filename to be saved. There is a chance the name will need to be modified on an occasional report, i.e. one that has an anomaly that needs to be checked might have the filename altered slightly, so I do need the dialog rather than just having the macro save it completely.

    I have played around with some other code I have found online, which I will list below, but run into a couple of problems. One, because it is a BeforeSave event, it brings it up the way I would like, but then immediately comes up to save again, at which point I have to either cancel the second save or select to override the file the event just saved. I'd like the saveas from the event to be the only save. Second, I don't know if it's possible to make a normal Save require the SaveAs dialog as well, since these are important files, but if there is a way to do it (or disable normal Save altogether), I would also like to include that. Here is what I have:

    Please Login or Register  to view this content.
    Thanks for any help you can give me!

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Call in Variable File Directory and Filename from Cell into Save As Prompt

    In this macro, notice the Cancel as Boolean listed in the header? This means you have the option to CANCEL the primary SAVE command within this macro. So, before you end the sub, add a Cancel = True and it will suppress the other SAVE functions, leaving only the macro code to work every time.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Call in Variable File Directory and Filename from Cell into Save As Prompt

    Try this:

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1