"saveas" macro with Cell Value as filename
Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: "saveas" macro with Cell Value as filename

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Anyone ever automated the saveas process utilizing a cell value for PART of the filename?
    I need to put a button on the sheet that saves the file with the cuurent filename + contents of a cell (k2).. for example if the current filename is "file1" and the contents of Cell K2 is "test" I need a macro to save the workbook as "file1_test.xls"

    Here is what I have but don't know how to integrate the contents of a cell..

    ChDir "I INSERT PATH HERE"
    ActiveWorkbook.saveas Filename:= _
    "I INSERT FULL PATH & FILENAME HERE", FileFormat:= _
    xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
    , CreateBackup:=False

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    Here is what I have but don't know how to integrate the contents of a cell..

    ChDir "I INSERT PATH HERE"
    ActiveWorkbook.saveas Filename:= _
    "I INSERT FULL PATH & FILENAME HERE", FileFormat:= _
    xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
    , CreateBackup:=False

    assuming that K2 is on sheet1

    ChDir "C:SaveMeNow"
    ActiveWorkbook.saveas Filename:= _
    "C:SaveMeNowfile1_" & sheet1.range("K2").value & ".xls", FileFormat:= _
    xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
    , CreateBackup:=False


    Do not use the double , just single forward slashes

    Tom



    [ This Message was edited by: TsTom on 2002-03-26 11:53 ]

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok - Got it - I had to use
    Worksheets("Sheet1").Range("K2")
    for the sheet/cell reference cause
    ssworksheet.Range(C6).Value
    kept giving me runtime errors..

    but the &'s worked great..

    Thanks

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    sorry cut and pasted wrong line..

    Sheet1.Range(K2).Value

    was giving me runtime errors..

  5. #5
    New Member
    Join Date
    Jul 2009
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "saveas" macro with Cell Value as filename

    HI,
    I have the same problem, I want to modify the file name field in the Save As dialog to put in it the value of the cell G3.

    I don't know how to do that and in what event should I add a code.....I tried to put a code in the Workbook_BeforeSave event, yet I am getting two consecutive save as dialogs....
    ANy help would be much appreciated......

  6. #6
    Board Regular mooseman's Avatar
    Join Date
    Jul 2004
    Location
    Pittsburgh, PA
    Posts
    192
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "saveas" macro with Cell Value as filename

    This can be added to the button code in the On_click. just change folder (path) and sheet number as needed

    Code:
    Sub Saveasfilename()
    Dim fname, folder, cvalue, NewFname As String
    
    ' Macro by mooseman
    '
    fname = ActiveWorkbook.Name           'Gets the active workbook's current name
    folder = "C:\test\"                   'put in the path to where you want the file saved
    cvalue = Sheets(1).Range("K2").value  ' assumes that the K2 cell is in the first sheet of the workbook
    NewFname = folder & fname & "_" & cvalue & ".xls"  'puts it all together
    
    'Saves the currect workbook with the new name
        ActiveWorkbook.SaveAs Filename:=NewFname, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
            ReadOnlyRecommended:=False, CreateBackup:=False
        
    End Sub

  7. #7
    New Member
    Join Date
    Jul 2009
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "saveas" macro with Cell Value as filename

      
    Thanks,
    I think using a button is a good idea...

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com