Save a PDF error

[josullivan601632]

Hi someone very kindly helped me with a VBA code to save to a pdf which worked wonders and I was so grateful. I have continued working on the workbook and moved a few things around, and now the code will not work.

the code is

Sub SaveAsPDF1()
Dim strTempFile As String
Dim strData As String
Dim spath As String

spath = Environ("USERPROFILE") & "\Newman Refrigeration Ltd\Newman Refrigeration Ltd - General\Brian\Cost Book\"
strData = Sheets("Quotation").Range("K9").Value & " - " & Sheets("Quotation").Range("K1").Value & ".pdf"
strTempFile = spath & strData
MsgBox strData, , "PDF has been saved with the following filename:"

Sheets("Quotation").ExportAsFixedFormat Type:=xlTypePDF, Filename:=strTempFile, OpenAfterPublish:=True

As I have changed the layout of the worksheet I now need the file name to be "L6" and "L2"instead of what is in red above, so I changed these references in the code and now get the Error 400. I have painstakingly tried everything, even starting a fresh with a new workbook, but I get so far and then go back to getting the error 400. I have even tried having K9 = L6 and K1 = L2 on the sheet to get around this, but this also returns the error.

I have been trying to fix this for over a week now and am so frustrated, can anyone suggest what I might be doing wrong? or how I get around this. If was such a nice feature for my workbook as I use the PDF's created to create a flipbook for the sales team!

Any wise words are much appreciated

Jo

 

[Candyman8019]

Have you tried stepping through the code (F8) to see which line is causing the problem?

 

[Candyman8019]

Also, have you confirmed that L6 and L2 do not contain any invalid characters for a filename, such as { } \ : ", etc?

 

[josullivan601632]

Hi I thought I had tried everything, the cells were a formula (a TEXT & formula) and that contained a "/".

It all works lovely, I can't believe I struggled for so long trying to work it out.

have a great evening