Dear all,
I unfortunately have no clue how to build macros. I need a Save to PDF macro that saves my dashboard following the document path, takes the worksheet name and puts the date next to the worksheet name when saving the PDF.
I have been looking through multiple blogs and forums, but I seem not able to put the right bits and pieces together.
My brother built the following macro for me, which already does 80 % of what I would need.
Sub Save_as_pdf()
Dim FSO As Object
Dim s(1) As String
Dim sNewFilePath As String
Dim cut_date As String
Dim add_date As String
Set FSO = CreateObject("Scripting.FileSystemObject")
s(0) = ThisWorkbook.FullName
cut_date = Left(s(0), Len(s(s0)) - 5)
add_date = cut_date & "_" & Format(Date$, "dd.mm.yy")
If FSO.FileExists(s(0)) Then
'//Change Excel Extension to PDF extension in FilePath
s(1) = FSO.GetExtensionName(add_date)
If s(1) <> "" Then
s(1) = "." & s(1)
sNewFilePath = Replace(add_date, s(1), ".pdf")
'//Export to PDF with new File Path
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
filename:=sNewFilePath, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True
End If
Else
'//Error: file path not found
MsgBox "Error: this workbook may be unsaved. Please save and try again. A team of highly traind monkeys is working on a solution for this problem!"
End If
Set FSO = Nothing
End Sub
However, in the saved document name, the year stamp (.yy) is not visible. Other than that I would need to save the file with the worksheet name and not the workbook name, since I have more than one dashboard in the same file. Would it also be possible to add a time stamp next to the date stamp in the saved document name?
If this helps I could also include the document name and date/timestamp from cell values. (Cell: B2 and the date/timestamp Cell; BI2) as I have seen many solutions trying it that way.
I really appreciate your help!
Cheers,
Rea
I unfortunately have no clue how to build macros. I need a Save to PDF macro that saves my dashboard following the document path, takes the worksheet name and puts the date next to the worksheet name when saving the PDF.
I have been looking through multiple blogs and forums, but I seem not able to put the right bits and pieces together.
My brother built the following macro for me, which already does 80 % of what I would need.
Sub Save_as_pdf()
Dim FSO As Object
Dim s(1) As String
Dim sNewFilePath As String
Dim cut_date As String
Dim add_date As String
Set FSO = CreateObject("Scripting.FileSystemObject")
s(0) = ThisWorkbook.FullName
cut_date = Left(s(0), Len(s(s0)) - 5)
add_date = cut_date & "_" & Format(Date$, "dd.mm.yy")
If FSO.FileExists(s(0)) Then
'//Change Excel Extension to PDF extension in FilePath
s(1) = FSO.GetExtensionName(add_date)
If s(1) <> "" Then
s(1) = "." & s(1)
sNewFilePath = Replace(add_date, s(1), ".pdf")
'//Export to PDF with new File Path
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
filename:=sNewFilePath, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True
End If
Else
'//Error: file path not found
MsgBox "Error: this workbook may be unsaved. Please save and try again. A team of highly traind monkeys is working on a solution for this problem!"
End If
Set FSO = Nothing
End Sub
However, in the saved document name, the year stamp (.yy) is not visible. Other than that I would need to save the file with the worksheet name and not the workbook name, since I have more than one dashboard in the same file. Would it also be possible to add a time stamp next to the date stamp in the saved document name?
If this helps I could also include the document name and date/timestamp from cell values. (Cell: B2 and the date/timestamp Cell; BI2) as I have seen many solutions trying it that way.
I really appreciate your help!
Cheers,
Rea