Excel save to PDF macro error

Question

when trying to save an excel sheet as a PDF and naming it , the macro is returning a runtime  error 1004  Document not saved ....   the code is as follows

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=_

strPdfFinalPath2, Quality:=_

xlQualityStandard, IncludeDocProperties:= True, IgnorePrintAreas:=False, _

OpenAfterPublish:= False

strPdfFinalPath2 is a Dim named String which works fine, it even recognizes it here. it will just not save. If I manually type in the same information that is brought in by strPdfFinalPath2 it works fine. ????? I need it to be a named , changable Filename as it archives(saves) this daily and uses that date as part of the name.

I have tried numerous variations of creating the Filename but all bring back the same error, even though the correct path is shown when hovering over strPdfFinalPath2  ( \\CHOFS02\Groups\CVO_Utilities\Relief Logs\Archive\PDF\06-06-2014.pdf)  we as a group  do not have standard Drives. that is my G: Drive has a different path (CHOFS02\Groups) as someone else (CXOFS01) for example. Any sugesstions would be appreciated

Answer 1

There is an interesting approach to trying to export the file multple times to a server location here:

http://www.ozgrid.com/forum/showthread.php?183969

Perhaps you could try this approach and the code will start succeeding.

Hope this helps,

Eric

P.S. - ran your line of code several times, saving to a server location, and it worked every time.

Answer 2

Being a newby, I could not for some reason copy and paste the code out of VBA into this area. so I may have miss typed. I actually copied a working excel to pdf piece of code into this macro and updated the Filename path. that is why i dont understand the error.

Answer 3

The code that you have posted has invalid line breaks. Should have a space and an underscore for a line break and the spaces are missing from the first 2 line breaks.

This could be due to editing after copying and pasting the code into your post but needs confirming.

Other than the above the code works fine to save on a PC Hard drive but I can't test to save on a server.