ActiveWorkbook.SaveAs Filename fail

[toasterlad]

Need another set of eyes on this. Trying to save a workbook with dates in the title that are specified by using an input box. Workbook saves when I leave out the dates, but I can't see any reason why using the dates would cause the method to fail:

AnsRS = InputBox("Report Start Date (mm/dd/yy)", , Format(Date, "mm/dd/yy"))
AnsRe = InputBox("Report End Date (mm/dd/yy)", , Format(Date, "mm/dd/yy"))
ActiveWorkbook.SaveAs ("N:\Accpay\Approval Lists\Approval Listing Source Data\Scratchpad\Weekly Updates " & AnsRS & "-" & AnsRe & ".xlsx")

 

[Joe4]

You cannot use "/" in your file name. They are illegal characters in file names.

Try changing your "mm/dd/yy" references to something like "mm-dd-yy" or "mmddyy".

 

[toasterlad]

Good catch, but tried both mm-dd-yy and mmddyy, and it's still failing.

 

[Joe4]

Did you changes them in the second reference on each row (that is the most important part):

AnsRS = InputBox("Report Start Date (mm/dd/yy)", , Format(Date, "mm/dd/yy"))
AnsRe = InputBox("Report End Date (mm/dd/yy)", , Format(Date, "mm/dd/yy"))

If that does not work, what is the exact error message you are getting?

Note that if you are saving the current file, meaning the file containing this VBA code, you cannot save is as an "xlsx" file. VBA code cannot be stored in "xlsx" files.
You will need to save it as an "xlsm" or "xlsb".

 

[toasterlad]

Yep, changed them both:

AnsRS = InputBox("Report Start Date (mmddyy)", , Format(Date, "mmddyy"))
AnsRe = InputBox("Report End Date (mmddyy)", , Format(Date, "mmddyy"))
ActiveWorkbook.SaveAs ("N:\Accpay\Approval Lists\Approval Listing Source Data\Scratchpad\Weekly Updates " & AnsRS & "-" & AnsRe & ".xlsx")

The macro is in a separate workbook: I am saving the ActiveWorkbook as a new file.

 

[Joe4]

Please answer all 3 of the following questions.

1. What is the exact error message you are getting?

2. How are AnsRS and AnsRe declared in your code?

3. If you make this change to your code:

AnsRS = InputBox("Report Start Date (mmddyy)", , Format(Date, "mmddyy"))
AnsRE = InputBox("Report End Date (mmddyy)", , Format(Date, "mmddyy"))
fname = "N:\Accpay\Approval Lists\Approval Listing Source Data\Scratchpad\Weekly Updates " & AnsRS & "-" & AnsRE & ".xlsx"
MsgBox fname
ActiveWorkbook.SaveAs fname

Please post exactly what the Message Box is returning.

 

[toasterlad]

1.

2.

Dim AnsRS As Date
Dim AnsRe As Date

3.

 

[Joe4]

Look at the image you posted in step 3. The date slashes are still there!

You either need to declare AnsRS and AnsRe as Strings, or you must convert them to values without the slashes in creating your file name (using the same FORMAT function).

Note: Using MsgBox in this manner is a good way to debug things like this.
Once you do that, it becomes readily appareent what the issue is.

 

[toasterlad]

Yep, just reran it while you were typing with the dates dim'd as strings, and it runs and saves correctly. Thanks for your help! I knew it would be something dumb.

 

[Joe4]

You are welcome.

The only caveat there is that a String will accept anything they enter. If you want to ensure that they only enter valid dates, you can leave them declared as Dates, and then convert them to text and remove the slashes in building the file name, like this:

Dim AnsRS As Date
Dim AnsRE As Date
Dim fname As String

AnsRS = InputBox("Report Start Date (mm/dd/yy)", , Format(Date, "mm/dd/yy"))
AnsRE = InputBox("Report End Date (mm/dd/yy)", , Format(Date, "mm/dd/yy"))
fname = "N:\Accpay\Approval Lists\Approval Listing Source Data\Scratchpad\Weekly Updates " & Format(AnsRS, "mmddyy") & "-" & Format(AnsRE, "mmddyy") & ".xlsx"
ActiveWorkbook.SaveAs fname

Then if they try entering an invalid date, they will get an error (which you can handle with error handling code, if you like).

I have seen people take it a step further, and instead of using Input Boxes, they use Calendar Date Pickers, which ensures that they can only pick dates (and nothing is entered free-form).