Solved: Error while printing pdf from excel

[abraham30]

Hello Everyone,

With the help of this forum, I got my solution to print pdf from excel spreadsheet with variable range.
But I got error no 41 in the line .PrintArea = lc & lr

Can anyone help me with my attached spreadsheet. (Also can I get the same output in word document format along with pdf)

[vba]
Sub pdf_doc()
Dim lr As Long, lc As Long
lr = Cells(Rows.Count, 8).End(xlUp).Row
lc = Cells(Columns.Count, 8).End(xlToLeft).Column
With ActiveSheet.PageSetup
.PrintArea = lc & lr
.PrintTitleRows = "$1:$10"
.CenterFooter = "&P/&N"
.PrintQuality = 600
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\Public\saabx.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True
End Sub
[/vba]

Advance thanks

[enrand22]

you should do something like this:

[VBA]Sub pdf_doc()

Dim lr As Long, lc As Long
Dim mycolumnletter As String

lr = Cells(Rows.Count, 8).End(xlUp).Row
lc = Cells(Columns.Count, 8).End(xlToLeft).Column

If lc > 26 Then
mycolumnletter = Chr(Int((lc - 1) / 26) + 64) & Chr(((lc - 1) Mod 26) + 65)
Else
mycolumnletter = Chr(lc + 64)
End If


With ActiveSheet.PageSetup
.PrintArea = "a1:" & mycolumnletter & lr
.PrintTitleRows = "$1:$10"
.CenterFooter = "&P/&N"
.PrintQuality = 600
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ActiveWorkbook.Path & "saabx.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True

End Sub

[/VBA]

[sassora]

Hi Abraham, this should address the print area issue and saving to PDF

[VBA]Sub pdf_doc()

Dim lr As Long
Dim lc As Long
Dim Printrng As Range

lr = Cells(Rows.Count, 8).End(xlUp).Row
lc = Cells(11, Columns.Count).End(xlToLeft).Column

Set Printrng = Range(Cells(1, 1), Cells(lr, lc))

With ActiveSheet.PageSetup
.PrintArea = Printrng.Address
.PrintTitleRows = "$1:$10"
.CenterFooter = "&P/&N"
.PrintQuality = 600
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\Public\saabx.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

End Sub
[/VBA]

[abraham30]

Thank you very much for spending ur time with my query.

Is it possible to print the same data in word document format.

that means when I run the macro, both pdf and doc should be printed simultaneously from the excel output

[sassora]

Since you can't save the document as a Word Document, I gather that this isn't a particularly easy thing to do well

See http://www.pcreview.co.uk/forums/pri...d-t922308.html for some code that may be of use.

[abraham30]

Hello Sassora,

Thanks for your valuable comment. I don't know, might be silly for you. If the spreadsheet is saved in a particular path, then can we create the process.

Thanks