Runtime error 1004, file not save

[ccarruth]

using VBA to publish section of excel sheet as pdf. getting this error. code below. Any suggestions?

'saving the overview report
Dt = Date
'Setting up the Width of the merged ccolumns
Call CLWid.CLWid
Ovr_Rpt.Activate
Ovr_Rpt.Range("A1:I34").Select
        Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        filePath, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, From:=1, To:=1, _
        OpenAfterPublish:=False
cnt = cnt + 1
Cnt1 = Cnt1 + 1
Loop

debug shows "OpenAfterPublish:=False" as being where it hangs up. Help!

thanks all

C

 

[Chihiro]

It's only partial code. Hard to help you without entire code.

If I had to take a guess, you don't have the code in Module and it's in either the sheet level or workbook level and causing issue as operation becomes out of scope.

 

[Hui]

I suspect that your Filepath variable isn't set correctly

The following works

filepath = "test.pdf"
Selection.ExportAsFixedFormat _
  Type:=xlTypePDF, _
  Filename:=filepath, _
  Quality:=xlQualityStandard, _
  IncludeDocProperties:=True, _
  IgnorePrintAreas:=False, _
  From:=1, To:=1, _
  OpenAfterPublish:=False

The following doesn't work

filepath = "C:\test.pdf"
Selection.ExportAsFixedFormat _
  Type:=xlTypePDF, _
  Filename:=filepath, _
  Quality:=xlQualityStandard, _
  IncludeDocProperties:=True, _
  IgnorePrintAreas:=False, _
  From:=1, To:=1, _
  OpenAfterPublish:=False

returning the same error your getting

If I do this manually you will see the Windows permission error:


So you need to check that you have write permission in the directory you are attempting to save into

Leaving out the directory and just using a file name, saves the file in the Excel's Default directory

 

[ccarruth]

Thanks for all the replies. Was so simple..the "path" designation referenced a cell so we could change the pdf location. Turned out even though the "path" cell looked clear and the pdf should have defaulted to the current path, it wasn't. Contained some type of data you couldn't visibly see. Painted and "cleared contents", reran macro, worked flawlessly. Duh. Again, thanks for all the help!