Topic: Macro to save filename as cell contents @ AskWoody

Macro to save filename as cell contents
Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Macro to save filename as cell contents
- This topic has 17 replies, 4 voices, and was last updated 16 years, 10 months ago.
Author

Topic
New Reply

WSArcturus16a
AskWoody Lounger

February 12, 2001 at 4:16 pm #352831

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

Viewing 0 reply threads
Author

Replies
- H. Legare Coleman
  AskWoody Plus
  
  February 12, 2001 at 6:22 pm #514380
  
  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
- WSArcturus16a
  AskWoody Lounger
  
  February 12, 2001 at 8:47 pm #514399
  
  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
  
  H. Legare Coleman
  AskWoody Plus
  
  February 12, 2001 at 8:54 pm #514400
  
  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
  
  H. Legare Coleman
  AskWoody Plus
  
  February 13, 2001 at 12:33 pm #514475
  
  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
  
  WSArcturus16a
  AskWoody Lounger
  
  February 13, 2001 at 4:55 pm #514503
  
  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
  
  H. Legare Coleman
  AskWoody Plus
  
  February 13, 2001 at 8:51 pm #514533
  
  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
  
  WSArcturus16a
  AskWoody Lounger
  
  February 13, 2001 at 9:19 pm #514539
  
  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
  
  WSArcturus16a
  AskWoody Lounger
  
  February 13, 2001 at 10:00 pm #514543
  
  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
  
  H. Legare Coleman
  AskWoody Plus
  
  February 14, 2001 at 7:02 am #514599
  
  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
  
  WSArcturus16a
  AskWoody Lounger
  
  February 14, 2001 at 6:33 pm #514649
  
  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
  
  WSArcturus16a
  AskWoody Lounger
  
  February 14, 2001 at 6:46 pm #514650
  
  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
  
  H. Legare Coleman
  AskWoody Plus
  
  February 14, 2001 at 7:37 pm #514657
  
  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
  
  WSArcturus16a
  AskWoody Lounger
  
  February 14, 2001 at 8:15 pm #514663
  
  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
  
  H. Legare Coleman
  AskWoody Plus
  
  February 14, 2001 at 8:23 pm #514665
  
  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
  
  WSArcturus16a
  AskWoody Lounger
  
  February 14, 2001 at 8:42 pm #514667
  
  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
  
  WSMelanieB
  AskWoody Lounger
  
  August 25, 2008 at 3:08 am #1122976
  
  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
  
  WSHansV
  AskWoody Lounger
  
  August 25, 2008 at 3:22 am #1122977
  
  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
Viewing 0 reply threads

Reply To: Macro to save filename as cell contents
You can use BBCodes to format your content.
Your account can't use all available BBCodes, they will be stripped before saving.

Your information:
Name (required):

Mail (will not be published) (required):

Website:

Cancel

Plus Membership

Donations from Plus members keep this site going. You can identify the people who support AskWoody by the Plus badge on their avatars.

AskWoody Plus members not only get access to all of the contents of this site -- including Susan Bradley's frequently updated Patch Watch listing -- they also receive weekly AskWoody Plus Newsletters (formerly Windows Secrets Newsletter) and AskWoody Plus Alerts, emails when there are important breaking developments.

Welcome to our unique respite from the madness.

It's easy to post questions about Windows 11, Windows 10, Win8.1, Win7, Surface, Office, or browse through our Forums. Post anonymously or register for greater privileges. Keep it civil, please: Decorous Lounge rules strictly enforced. Questions? Contact Customer Support.

Macro to save filename as cell contents

Plus Membership

Search Newsletters

Search Forums

View the Forum

Search for Topics

Recent Topics

Recent blog posts

My Profile

Key Links

Remembering Woody

Macro to save filename as cell contents

Plus Membership

Search Newsletters

Search Forums

View the Forum

Search for Topics

Recent Topics

Recent blog posts

My Profile

Login and Registration

Key Links

Remembering Woody