Creating a macro to name an Excel file with information predefined in some cells

Question

I've seen similar problems here, I've researched in several blogs but I still can't find a solution. I'm trying to automatically create a name for a file based in some cells.

So, I created a macro which aims to do :

Copy / Paste as values all the info I want to save (that's because some cells are based in dynamic formulas as for instance NOW());
"Save as" the file, trying to use the information defined in one specific cell of the file.

My macro looks like this:

Sub SaveMyWorkbook()
  Cells.Select
  Selection.Copy
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  :=False, Transpose:=False
  Sheets("Parameters").Select
  Cells.Select
  Application.CutCopyMode = False
  Selection.Copy
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  :=False, Transpose:=False
  Sheets("Demande d'Achat").Select
  Range("S3:U3").Select
  Application.CutCopyMode = False

  Dim strPath As String
  Dim strFolderPath As String

    strFolderPath = "\\camo106\remote\Achat_Purchasing\Demande d'achat_Purchase request\2019\"
    strPath = strFolderPath & _
      Parameters.Range("D1").Value & ".xlsm"

   ActiveWorkbook.SaveAs Filename:=strPath, 
   FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub

However, when I debug there is always a problem in the line in which I try to create the name:

strPath = strFolderPath & _ Parameters.Range("D1").Value & ".xlsm"

Could anyone help me? I'm not at all an advanced user of macros, and I guess that this one is far away from my knowledge.

I'm using Excel 2010.

Have a look at how I built the name for the excel file, if it helps, then an upvote would not go amiss : https://stackoverflow.com/a/30605765 — Solar Mike, Nov 22 '18 at 15:14
I recommend to read and apply [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) to make your code reliable and fast. — Pᴇʜ, Nov 22 '18 at 15:15
What do you mean by "there is always a problem"? What is the problem? Is the code name of the "Parameters" sheet actually `Parameters`? — Comintern, Nov 22 '18 at 15:16
Thanks!! When saying "there is always a problem" I meant "there is always an error" in that line... apparently the rest of the macro works, except that line — Santiago MONTES, Nov 23 '18 at 16:47

score 0 · Answer 1 · answered Nov 22 '18 at 16:55

0

How does your filename look before saving? If you are using formulas like NOW() in the filename it can be the case that the it contains characters that are forbidden. You can check it in the immediate window when you step through the code right before you attempt to save the file.

answered Nov 22 '18 at 16:55

Pawels

192
2
12

Before saving the name is the generic one of the template... In the formula I don't use any dynamic formula, that's why before to save, I copy all in values... – Santiago MONTES Nov 23 '18 at 16:44
I see. If you have not solved the case yet, it can be then that you are using VBA code alias of the sheet name instead of standard naming in the line as mentioned above (parameters instead of sheets("parameters")) – Pawels Nov 26 '18 at 15:12
Thanks Pavel, you're right I changed the name of the sheet (Sheet1 instead of Parameters) and passed that line, now I have a syntax error in the last two lines of the code... If I got to solve it probably the macro will work... let see – Santiago MONTES Nov 27 '18 at 18:43

score 0 · Answer 2 · answered Dec 03 '18 at 21:01

after researching a little bit, and thanks to the comments of all of you, I finally got to make the macro works. Some recommendations:

The reference cells used to "built" the name of the file need to be in the active sheet; I found easier to do a formula in the file and after make reference to only one cell in the macro, than doing the formula in the macro itself, however both ways should work, meanwhile the reference is in the active sheet.
If you have dynamic calculated cells in your file, copy-paste in values before to save the file (I did an independent macro, but I also copied it as values if anyone needs it);
Be careful with FileFormat attribute in the command SaveAs; after reading several blogs, I preferred to use the numeric code than the description, but again, both ways should work.

See here the ultra simple final code of my macro (Excel 2010):

Sub Save()
  CopyValues
    'Sub CopyValues()
      'ActiveSheet.Unprotect
      'Columns("A:U").Select
      'Selection.Copy
      'Application.CutCopyMode = False
      'Selection.Locked = True
      'Selection.FormulaHidden = True
      'Columns("A:U").Select
      'Selection.Copy
      'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      ':=False, Transpose:=False
      'Application.CutCopyMode = False
      'Range("G7").Select
      'ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    'End Sub

  Dim strPath As String
  Dim strFolderPath As String

    strFolderPath = "\\camo106\remote\Achat_Purchasing\Demande d'achat_Purchase request\2019\"
    strPath = strFolderPath & _
      Range("A1").Value & ".xlsm"

    ActiveWorkbook.SaveAs Filename:=strPath, FileFormat:=52, ReadOnlyRecommended:=True, CreateBackup:=False
    GoToMainSheet
     'Sub GoToMainSheet()
       'Sheets("Demande d'Achat").Select
       'Range("S2").Select
     'End Sub

End Sub

I hope will be useful for someone else.

Creating a macro to name an Excel file with information predefined in some cells

2 Answers2