VBA to create PDF and named with cell text.

[asyamonique]

Good Day,
Below code is creating the PDF file and named with worksheet name.
My question is how can we change it, instead of worksheet name to create as cell text or value?
Many Thanks

Sub Button1_Click()
Dim wsA     As Worksheet
Dim strFile, myfile As String
On Error Resume Next
wsA.Activate
    strFile = "\\172.16.23.181\folder\xxxxx\xxxxx\"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
strFile & "" & ActiveSheet.Name, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub

 

[6StringJazzer]

Instead of ActiveSheet.Name use ActiveSheet.Range("A1") for whatever cell you want to reference.

 

[6StringJazzer]

Have you actually tried to run this code yet? The red line will raise an error because wsA has not been Set:

Sub Button1_Click()
Dim wsA     As Worksheet
Dim strFile, myfile As String
On Error Resume Next
wsA.Activate
    strFile = "\\172.16.23.181\folder\xxxxx\xxxxx\"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
strFile & "" & ActiveSheet.Name, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub

 

[asyamonique]

Have you actually tried to run this code yet? The red line will raise an error because wsA has not been Set:

Sub Button1_Click()
Dim wsA     As Worksheet
Dim strFile, myfile As String
On Error Resume Next
wsA.Activate
    strFile = "\\172.16.23.181\folder\xxxxx\xxxxx\"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
strFile & "" & ActiveSheet.Name, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub

Its working with my file.

 

[6StringJazzer]

Aha. You have On Error Resume Next. I don't advise that because it will cause errors to be ignored. The reason your code works is that the line

wsA.Activate

raises an error, the error is ignored, so it does nothing it all. You might as well delete it. Whatever sheet is active when the macro runs is that one that will be exported to the PDF.

 

[asyamonique]

Yes you are right
I have summary report which located on one worksheet named as Summary Report, so everyday that report converting to pdf and kept as pdf file by changing name as date due to pdf automaticly creating with the worksheet name.
I was wonderind that the cell value a1 has date formula =today() which will help me to create pdf with that cell name.
Hope its clear.

 

[6StringJazzer]

If you refer to that cell value in VBA you will get the default date format, such as "9/9/2022", which is an illegal file name, so will cause an error.

If you want to use the current date you don't need to refer to a cell at all. You can do this. Adjust date format as desired:

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
strFile & " " & ActiveSheet.Name & " " & Format(Date, "yyyy-mm-dd"), _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True

 

[asyamonique]

Thanks