Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Macro to save filename as cell contents

    I use a macro to save a portion of a worksheet as a daily report. But for each daily report, I must save the file with a new filename for that day. I would like to create a macro that uses the contents of one cell to generate a filename so that all users will save the extracted daily report with a consistent filename format.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to save filename as cell contents

    The following should do something like you want to do:

    <pre>Dim sFName As String
    sFName = "C:MyDir" & Range("A1")
    ActiveWorkbook.SaveAs (sFName)
    </pre>


    If you don't want Excel to display the SaveAs dialog box and ask the user to confirm the save, then change the above to:

    <pre>Dim sFName As String
    sFName = "C:MyDir" & Range("A1")
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs (sFName)
    Application.DisplayAlerts = True
    </pre>

    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro to save filename as cell contents

    Legare,
    THX for such a timely response. I still need your help so don't leave just yet. I inserted your code into an existing macro, which simply saves the new sheet to a default name. I placed your code just below the instruction to change directory:
    ChDir "Gaily Operating ReportTable Games2001_02".
    I substituted the full path (including closing backslash) and populated the cell reference with a formula to concatenate a date abbreviation. But the code halts at the SaveAs (with or without DisplayAlerts) and produces an error message. I know that the folder exists, and that it is not read only. The filename does not contain illegal characters and the file/path is less than 218 characters.
    Any suggestions?

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to save filename as cell contents

    Could you show me the exact code that you are using? It is a little hard to tell what is happening without anything to look at. Unfortunately, it is bed time here in Switzerland, so I won't see it until tomorrow (very early morning your time). If someone else doesn't come up with an answer before then, I'll take a look.
    Legare Coleman

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to save filename as cell contents

    I did some more playing with this code this morning, and it works fine for me as long as the directory name is correct, and the directory exists. If the directory does not exist or the directory name is invalid, I get an error 1004. Therefore, for me to help you find the problem you are having, I will need the following:

    1- The exact code you are using in your macro.
    2- Exactly what is in the sheet cell that you are using for the name. If it contains a formula, I need to see the formula, not the value it displays.
    3- What error message you are getting

    I'm kind of shooting in the dark without that information.
    Legare Coleman

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Macro to save filename as cell contents

    Legare,
    Thanks for all the time you've spent on this. Sorry it took so long to get back to you. Here's the code. The macro copies a sheet to a new file, converts the formulas to values and the saves the file with the default filename email_Dly_TG_Rpt.xls (commented out here).



    Sub Extract_Dly_TG_rpt_for_email()
    '
    ' Extract_Dly_TG_rpt_for_email Macro
    ' Macro recorded 4/17/00 by Bob Buckley-CP Fin Plan
    '

    '
    ActiveSheet.Previous.Select
    Sheets("Daily Table Games Report").Select
    Sheets("Daily Table Games Report").Copy
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Range("A1").Select
    Selection.End(xlToRight).Select
    Selection.End(xlToRight).Select
    Range("L2").Select
    Application.CutCopyMode = False
    ChDir "Gaily Operating ReportTable Games2001_02"
    ' ActiveWorkbook.SaveAs FileName:= _
    ' "Gaily Operating ReportTable Games!_email_Dly_TG_Rpt.xls", FileFormat:= _
    ' xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
    ' False, CreateBackup:=False

    Dim sFName As String
    sFName = "Gaily Operating ReportTable Games2001_02" & Range("Ap18")
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs (sFName)
    Application.DisplayAlerts = True


    End Sub

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to save filename as cell contents

    If that directory exists, I can't see anything here that would cause an error on the SaveAs method. Can you tell me what is in cell Ap18 on the active sheet when this macro executes and what error you are getting. I also might suggest that you insert the following statement in the macro just before the SaveAs line:

    <pre> Msgbox sFName
    </pre>


    That should display what is in the sFName variable so you can make sure that it looks valid. One possibility that I see is that the cell Ap18 that you want is not the one on the ActiveSheet, and the file name that is getting put on the end of that path is maybe null.
    Legare Coleman

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro to save filename as cell contents

    Legare,
    At first I tried
    =CONCATENATE(RIGHT(AP23,2),IF(AP19<10,CONCATENATE( 0,AP19),AP19),IF(AQ21<10,CONCATENATE(0,AQ21),AQ21) ,"Tables")
    but when that didn't work, I changed it to "dly_TG_rpt" as a test.
    I'm trying to achieve a date format with text in a filename, like "yymmddTables.xls". The if() should handle a date less than ten and add a leading zero. With this format the files should sort very nicely in our directory. (We save everything forever).
    Thx again for your time spent with this one.

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro to save filename as cell contents

    Legare,

    The macro executes from a button on the active sheet.

    If I modify your code to remove the trailing backslash, the file is named 2001_02.xls under the Gaily Operating ReportTable Games folder. Otherwise, the code still bombs at "ActiveWorkbook.SaveAs (sFName)" even with the message box.

    The error message "file could not be accessed" indicates possible problems with 1) folder does not exist, 2) folder not read-only, 3) There are not illegal char in filename and 4) path not longer than 218 char.

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to save filename as cell contents

    OK, I think we now have an explaination of why you are getting the error. The fact that you do not get an error when you remove the final backslash says that something is wrong with the file name in the cell on the worksheet. The fact that the file is named 2001_02 indicates that the cell you are picking up is empty since nothing is added to the 2001_02 when the cell is concatenated to it. This macro may be started from a button on the active sheet which contains the cell with the file name, but the macro changes the active sheet. One of the first lines in the macro is:

    ActiveSheet.Previous.Select

    So, you are picking up the file name from whatever sheet that activates, and Cell Ap18 on that sheet is apparently empty.

    There are several ways to solve this. First, you could use a statement like this:

    <pre> sFName = "Gaily Operating ReportTable Games2001_02" & WorkSheets("sheetname").Range("Ap18")
    </pre>


    Where "sheetname" is the name of the sheet that contains the file name.

    However, I think that there is a better solution. I would change that line to:

    <pre> sFName = "Gaily Operating ReportTable Games2001_02" & Format(Date(),"yyyy-mm-dd") & "Tables"
    </pre>


    That will generate the file name in the macro. If you really need it on the worksheet, you can put it there from the macro, or you can put the formula =Now() in the cell and format to look the same as the above.

    One other suggestion. I assume that the 2001_02 is the current year and month. If you leave that hard coded in the macro, you will have to modify the macro every month. My final suggestion would be to change the above line to the following:

    <pre> sFName = "Gaily Operating ReportTable Games" & Format(Date(),"yyyy_mm")
    sFNAME = sFName & "" & Format(Date(),"yyyy-mm-dd") & " Tables"
    </pre>


    Between those two lines, you might also want to check that the directory name generated in the first line exists, and if does not create it. That way, the macro will not fail on the first day of the month if you have not manually created the directory.
    Legare Coleman

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro to save filename as cell contents

    Legare,

    I tried the first option you posed since there is still a use for a cell reference in the filename (like adding a letter "a" (more concatenation) for a second version). But I got an error "Subscript out or range" which bombed at that statement after creating a new book. Can you help with that?

    Part two, where you recommend incorporation of the date function into the macro works like a charm. Adding the MsgBox to let the user know what's happening is a great idea.

    Part three: If I follow this correctly, means that I'll only have to create a new directory each month, using this date format. Fantastic! No more editing the macro code.

    Thanks so much for your help Legare. This is so cool. I can incorporate these features into several other reports used by our department.

  12. #12
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro to save filename as cell contents

    Another reason to re-work the first option: Sometimes we re-run a report after adjustments with a user selected date. Or, when creating a new file for the next month, we enter a daily budget, then run a report with the last date of the month. The second option naturally defaults to the current date.

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to save filename as cell contents

    In the first formula, did you substitute the name of your worksheet (the name on the sheet tab) for "sheetname".? If your sheet where the file name is in Ap18 is named Sheet1, then you need to put "Sheet1" where I had "sheetname". If you didn't, it would cause the error you got.
    Legare Coleman

  14. #14
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro to save filename as cell contents

    The sheetname is "Daily Current" which I substituted for sheetname. Subbing "Sheet2" for "Daily Current" does not change the error message.
    Code now appears as:
    Dim sFName As String
    ' sFName = "Gaily Operating ReportTable Games2001_02" & Format(Date, "yyyy-mm-dd") & "Tables"
    ' sFName = "Gaily Operating ReportTable Games2001_02" & WorkSheets("sheetname").Range("Ap18")
    ' sFName = "Gaily Operating ReportTable Games" & Format(Date, "yyyy_mm")

    ' sFName = sFName & "" & Format(Date, "yyyy-mm-dd") & " Tables"
    sFName = "Gaily Operating ReportTable Games2001_02" & Worksheets("Daily Current").Range("Ap18")
    Application.DisplayAlerts = False
    MsgBox sFName
    ActiveWorkbook.SaveAs (sFName)
    Application.DisplayAlerts = True

  15. #15
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to save filename as cell contents

    The only thing that I can see in that line that would give you a "Subscript out of range" error is if "Daily Current" is not an exact match for the name of the sheet. You might want to check that there are no extra spaces in the name of the sheet. You might also try copying the sheet name from the tab and pasting between the quotes in that statement.
    Legare Coleman

Page 1 of 2 12 LastLast

Posting Permissions

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