PDA

View Full Version : [SOLVED] ExportAsFixedFile Error



PaulM
06-26-2015, 03:15 PM
Hello All!

I am having trouble fixing this error that pops up. The error number is 1004 and is says "Method of ExportAsFixedFormat of object 'Range' failed."

I'm fairly certain this has to do with the selection I am using to export. I have tried fixing this several ways and will get it to work for a few times and then it will break again. Can anyone help me with this?

What the code is suppose to do:
This is the beginning of a sub that will export a pdf of the user selected selection and email it while extracting data from the selection and using that to personalize the e-mail. I have everything working but the exporting to pdf.

The code is:


Dim Rng As Range
Dim ws As Worksheet
Dim wb As Workbook
Dim FileName As String

Application.ScreenUpdating = False

Set Rng = Selection.SpecialCells(xlCellTypeVisible)
If Rng Is Nothing Then
MsgBox "Please select a range and try again.", Title:="Error! No selection found!"
Exit Sub
End If

Rng.Copy

If ActiveWorkbook.Path = "" Then
MsgBox "Please save your Workbook and try again!", Title:="Error! No save found!"
Exit Sub
End If

Set wb = ActiveWorkbook
Set ws = ActiveSheet
FileName = ActiveWorkbook.Path & ":" & Left(ActiveWorkbook.Name, InStr(1, ActiveWorkbook.Name, ".", 1) - 1) _
& " (Enviado " & Format(Now, "[$-10]mmm d") & ").pdf"


On Error Resume Next
With Rng
.ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:=FileName, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True
End With

If Err.Number <> 0 Then
MsgBox "We are sorry! There was an error in forming the PDF document. Please fix and try again." & Chr(13) & _
"----------Error Information----------" & Chr(13) & "Error Number: " & Err.Number & Chr(13) & Err.Description & Chr(13) & FileName, Title:="Oh no! Error!"
Exit Sub
End If


I've found that the order of the code does matter (in making this work) but I have no idea how to make it so everything works.

*Note, with the FileName, this is for mac (2011) so the filename is valid. The VBA code should be almost exactly the same as all newer excel vba versions. So please help even if you don't have this version!

SamT
06-26-2015, 04:59 PM
:dunno Try this

Rng.ExportAsFixedFormat(Type:=xlTypePDF, _
FileName:=FileName, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True)

snb
06-27-2015, 02:30 AM
No need to copy the range...


Sub M_snb()
If selection Is Nothing Then c00= "Please select a range and try again."
If C00<>"" then Goto XL90

with ActiveWorkbook
If .Path = "" Then c00="Please save your Workbook and try again."
If C00<>"" then Goto XL90

selection.ExportAsFixedFormat 0,.Path & ":" & split(.Name, ".")(0) & " Enviado " & Format(Now, "[$-10]mmm d.p\df")
end with

XL90:
if c00<>"" then Msgbox c00
End sub

PS. I don't know whether OS accepts ( ) in filenames. You'd better stick to a-z,0-9

PaulM
06-27-2015, 04:16 PM
Sam,

Sadly, that didn't help. But thank you for suggesting something!

Snb,

Thank you! I got it to work. It ended up being a little different. But it helped quite a bit!