Macro to save filename as cell contents

Topic

New Reply

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.

Reply | Quote

Replies

The following should do something like you want to do:

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

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:

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

Reply | Quote

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 “G:Daily 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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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 “G:Daily Operating ReportTable Games2001_02”
‘ ActiveWorkbook.SaveAs FileName:= _
‘ “G:Daily Operating ReportTable Games!_email_Dly_TG_Rpt.xls”, FileFormat:= _
‘ xlNormal, Password:=””, WriteResPassword:=””, ReadOnlyRecommended:= _
‘ False, CreateBackup:=False

Dim sFName As String
sFName = “G:Daily Operating ReportTable Games2001_02” & Range(“Ap18”)
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs (sFName)
Application.DisplayAlerts = True

End Sub

Reply | Quote

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:

    Msgbox sFName

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.

Reply | Quote

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.

Reply | Quote

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 G:Daily 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.

Reply | Quote

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:

    sFName = "G:Daily Operating ReportTable Games2001_02" & WorkSheets("sheetname").Range("Ap18")

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:

    sFName = "G:Daily Operating ReportTable Games2001_02" & Format(Date(),"yyyy-mm-dd") & "Tables"

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:

    sFName = "G:Daily Operating ReportTable Games" & Format(Date(),"yyyy_mm") 
    sFNAME = sFName & "" & Format(Date(),"yyyy-mm-dd") & " Tables"

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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 = “G:Daily Operating ReportTable Games2001_02” & Format(Date, “yyyy-mm-dd”) & “Tables”
‘ sFName = “G:Daily Operating ReportTable Games2001_02” & WorkSheets(“sheetname”).Range(“Ap18”)
‘ sFName = “G:Daily Operating ReportTable Games” & Format(Date, “yyyy_mm”)

‘ sFName = sFName & “” & Format(Date, “yyyy-mm-dd”) & ” Tables”
sFName = “G:Daily Operating ReportTable Games2001_02” & Worksheets(“Daily Current”).Range(“Ap18”)
Application.DisplayAlerts = False
MsgBox sFName
ActiveWorkbook.SaveAs (sFName)
Application.DisplayAlerts = True

Reply | Quote

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.

Reply | Quote

Legare,

I moved cell ref to AP18 on sheet1 “Daily Table Games Report”. I also changed “Daily Current” sheet ref to (1) for sheet1. Works fine. Now I can tweak a concatenate formula to look like whatever format I would like.

I’m not sure what causes the problem. The space in the sheetname perhaps or something else.

I’ll be sure to let you know if I ever find out. Thanks for your time helping me with my problem.

Reply | Quote

I found this thread and I think it is what I’m trying to do, but I’m having problems getting it to work. I am pretty code illiterate, so please be patient with me.
My file is names “Sustations” and the worksheet is called “Inspection report”. The users will be opening this worksheet as a template, making their edits and then saving it.
I would like to save the workbook saved with a new name each time someone uses the form. I’d like the name to be a combination of data picked from a list in C1 and the date entered in T1.
I’d like it to be saved on our network drive (for example, M:MelanieForms).
How can I modify this code to work? And, how do it put it in the workbook. I know to press Alt+F11 to get to the code, but I don’t know where to put it after that.

Thank you for any help you can give me.

Reply | Quote

If you save your workbook as an Excel template (*.xlt), the user will get a fresh copy each time the template is opened.

You could put a command button on the worksheet that saves the workbook with a name derived from some cells:
– Display the Forms toolbar.
– Click on the Command Button button, then click in your worksheet.
– Excel will ask you to assign a macro to the button.
– Change the name of the macro if you wish, then click New.
– Excel will take you to the Visual Basic Editor and create the first and last lines of the macro for you.
– It will look similar to

Sub Button1_Click()

End Sub

– Make the code look like this:

Sub Button1_Click()
' Path must end in backslash
Const strPath = "M:MelanieForms"
Dim strName As String

If Range("C1") = "" Then
MsgBox "Please select an item from the list", vbExclamation
Range("C1").Select
Exit Sub
End If

If Range("T1") = "" Then
MsgBox "Please enter a date", vbExclamation
Range("T1").Select
Exit Sub
End If

strName = strPath & Range("C1") & Format(Range("T1"), "yyyymmdd") & ".xls"
ActiveWorkbook.SaveAs strName
End Sub

– Switch back to Excel.
– Save the workbook / template.

Reply | Quote