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!
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!