date as filename vba

twl2009

Board Regular
Joined
Jan 7, 2016
Messages
247
Im using this line to save a worksheet as a workbook. I want to use a date from the sheet as part of the filename. Vba doesnt like it. Im pretty sure I need to use 'Format..."dd-mm-yy"' around or within 'sh.range("q3")'
But whatever ive tried is giving an error.
Any guidance gratefully received. Thanks


<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; background-color: #ffffff}</style>ActiveWorkbook.SaveAs FileName:="/Users/User/Desktop/" & sh.Name & sh.Range("Q3")
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Q3 contains a date, the line works if Q3 is not a date, it doesn't like the date format at the end of the filename.

also just found out that I need to save as a pdf. Does that make things more complicated.
 
Upvote 0
Hi,

My guess would be that / in the date format throws the error, as it will cause the macro to search for a folder based on your date.
Therefore, I would suggest using the substitute function to replace the / with something else, like:

ActiveWorkbook.SaveAs Filename:="/Users/User/Desktop/" & sh.Name & WorksheetFunction.Substitute(sh.Range("Q3"), "/", "-")

Replaces / with - and should save your file accordingly.
 
Upvote 0
Q3 contains a date, the line works if Q3 is not a date, it doesn't like the date format at the end of the filename...

If your default formatting for date includes slashes, that is going to hiccup, as a filename cannot contain slashes. For instance, the default format for me in US English is m/d/yyyy. So if I enter =TODAY() in cell A1 and I type in "?cells(1).value" , it returns 4/24/2017.

You could try Format(sh.Range("Q3").Value, "mm-dd-yyyy") or similar with hyphens.

Hope that helps,

Mark
 
Upvote 0
Okay, in that case you need to change the format....without seeing the date....I'd suggest you need to remove the slashes or dots from the date.
Something like

Code:
ActiveWorkbook.SaveAs Filename:="/Users/User/Desktop/" & sh.Name & Format(Range("Q3"), "yyyymmdd") & ".pdf"
 
Upvote 0
Hi twl....
add the below lines to your code and try...

Dim filename As String
filename = Range("B2")
Path = "INPUT YOUR PATH HERE"
ActiveWorkbook.SaveAs filename:=Path & filename & ".xls", FileFormat:=xlNormal

Cheers!!!
(y)
 
Upvote 0
Hi twl....
add the below lines to your code and try...

Dim filename As String
filename = Range("B2")
Path = "INPUT YOUR PATH HERE"
ActiveWorkbook.SaveAs filename:=Path & filename & ".xls", FileFormat:=xlNormal

Cheers!!!
(y)

PLEASE IGNORE DID NOT READ THE POST CORRECTLY

Try this instead

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("A1").Value _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True

Cheers!!!
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top