XLS to PDF problem

[thunder_anger]

Hi all

i found this thread http://www.mrexcel.com/forum/showthread.php?t=535944&page=3and found that thing interesting but when applying the code below it returns "Run-Time error '5'

Sub WbToPdfToZip()
  
  ' --> Settings, change to suit
  Const PdfName$ = "Test.pdf"
  Const ZipFullName$ = "D:\Test.zip"
  ' <-- End of settings
  
  Dim PdfTempName$
  PdfTempName = Environ("Temp") & "\" & PdfName
  If Len(Dir(PdfTempName)) Then Kill PdfTempName
  ActiveWorkbook.ExportAsFixedFormat xlTypePDF, PdfTempName, xlQualityStandard, True, False, OpenAfterPublish:=False
  Zip ZipFullName, PdfTempName
  If Len(Dir(PdfTempName)) Then Kill PdfTempName

End Sub

' ZVI: Copy Files to ZipFullName archive
Sub Zip(ZipFullName, ParamArray Files())
  Dim FileNumber%, ZipFile$, i&, x
  ' Add ZIP extentions if not set
  ZipFile = Trim(ZipFullName)
  If LCase(Right(ZipFile, 4)) <> ".zip" Then ZipFile = ZipFile & ".zip"
  ' Create an empty zip archive if not already present
  If Len(Dir(ZipFile)) = 0 Then
    FileNumber = FreeFile
    Open ZipFile For Output As #FileNumber
    Print #FileNumber, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)
    Close #FileNumber
  End If
  ' Copy files to archive
  With CreateObject("Shell.Application").Namespace((ZipFile))
    For Each x In Files
      .CopyHere (x)
    Next
    Do Until .Items.Count = UBound(Files) + 1
      DoEvents
    Loop
  End With
End Sub

the error high lights the bold code
why and how to fix it??

 

[hossamazab]

same question here!!

 

[daverunt]

Hi,

the code appears to be working.
What version of Excel are you using as this requires 2007.

There is a PDF add-in for 2007
http://www.microsoft.com/downloads/...11-3E7E-4AE6-B059-A2E79ED87041&displaylang=en

If you install Office SP2 you don't need the Add-in