How to create an excel to PDF macro with auto filename

[SaltyWalty]

I have been trying over and over to get a script to work from this forum but with no success. I'm reaching out to know why I cant get this to work. The script is as Follows:

Option Explicit
Sub Automatic_Save_As()
Dim TempPDFFileName As String
Dim tempPSFileName As String
Dim tempPDFRawFileName As String
Dim tempLogFileName As String
TempPDFFileName = "C:\Desktop\Test" & Range("D2").Value
tempPSFileName = tempPDFRawFileName & ".ps"
TempPDFFileName = tempPDFRawFileName & ".pdf"
tempLogFileName = tempPDFRawFileName & ".log"
ActiveSheet.PrintOut Copies:=1, preview:=False, ActivePrinter:="Adobe PDF", printtofile:=True, Collate:=True, prtofilename:=tempPSFileName
Dim myPDFDist As New PdfDistiller
myPDFDist.FileToPDF tempPSFileName, TempPDFFileName, ""
End Sub

I'm running widows 7, office 2010 and have adobe acrobrat XI Pro.

No errors are generated when I run the Macro, but It doesn't print in the specified file location.

Any help would be greatly appreciated.

 

[BiocideJ]

It appears to me that this is the problem.
The red text below needs to be added.
Right now, tmpPDFRawFileName is a null string ("") so you are trying to save a PDF to the following full path ".pdf"


TempPDFRawFileName = "C:\Desktop\Test" & Range("D2").Value
tempPSFileName = tempPDFRawFileName & ".ps"

 

[SaltyWalty]

BiocideJ,

I made your recommended change to no effect. Whenever I run the Macro, nothing is saved in the desired folder.

 

[BiocideJ]

I think I need more information then.

What exactly is...
Range("D2").Value

What is the name (and path) of the .PS file you are trying to convert?
I suspect it is not
C:\Desktop\Test{whatever is in D2}.PS

 

[SaltyWalty]

Cell D2 is a merged cell that contains a building address.

The actual filepath that I'm trying to save to (or convert) is a shared drive on a network that I don't feel absolutely comfortable sharing. I was hoping to get this working on a local drive first before making available to others.