Variables in workbook naming procedures

[davidam]

Hello All,
I am using the following to create a workbook. The string derived from Now() will be used as part of the name of the workbook

Workbooks.Add
With ActiveWorkbook.Worksheets("Sheet1").Range("E1")
   .FormulaR1C1 = "=NOW()"
   .NumberFormat = "0.000"
End With
Dim nowCell As Range
Set nowCell = ActiveWorkbook.Worksheets("Sheet1").Range("B1")
nowCell.Value = Range("E1").Value
nowCell.Offset(0, 1).Formula = "=LEFT(RC[-1],5)"
nowCell.Offset(0, 2).Formula = "=Right(Left(RC[-2],9),4)"
leftStr = nowCell.Offset(0,1).Value
rightStr = nowCell.Offset(0,2).Value
ActiveWorkbook.SaveAs thisBook.Path & "/" & leftStr & rightStr
 & ".xls"

Thus I never get two workbooks with the same name.
Q1: Sometimes Right(Left(RC[-2],9),4) replaces a zero with a decimal. It works fine in the environments where I have used it, but I thought a decimal was an illegal character for naming a workbook... I get something like: "406538.3.xls" should I take steps to eliminate this?
Q2. Does the variable nowCell survive after I name the workbook in the same procedure?
Thank you.

 

[Jerry Sullivan]

Hello David,

That is a very unusual method of naming files. How do you retrieve the correct file you want later?

Please see my thoughts below...

Q1: Sometimes Right(Left(RC[-2],9),4) replaces a zero with a decimal. It works fine in the environments where I have used it, but I thought a decimal was an illegal character for naming a workbook... I get something like: "406538.3.xls" should I take steps to eliminate this?

For Windows OS decimals are allowed in the filename. Since you are starting with the numeric value Now(), you might want to avoid using string manipulation functions to build a filename. That introduces unintended variables of the number format of cell B1 and perhaps the effect of trailing zeros.

You might consider deriving your filename directly within VBA using Numeric functions instead of using the worksheet cells to build the filename.

Q2. Does the variable nowCell survive after I name the workbook in the same procedure?

Yes, the variables would retain their values within the procedure after you name your new workbook including the Range variable nowCell. Those values will be lost when the procedure ends unless the variables are defined to have scope outside of the procedure.

 

[davidam]

Thanks Jerry, for your comments,
Good to know that the variables survive the renaming of the book until we depart the module.
I will look at using VBA to do get the Now() values instead of the spreadsheet because I am sure it is much more efficient...FYI I am not too concerned about retrieving the files because I am using them to report errors and they will go to an archive folder where they will only be accessed manually.
Regards,
David

 

[Jerry Sullivan]

Glad that helped, David.

Technically the variables survive until you depart the Procedure (Sub or Function) instead of the Module. But that's probably what you meant.

Good to know that the variables survive the renaming of the book until we depart the module.