Using VBA to export a worksheet to a pdf file

[Edwin_C]

Hi All,


I'm new to the forum (first post) and am hoping to get some help.


I have a vba procedure in a workbook that, after updating some tables that a chart references - prints a page to a pdf file.


The code I use for this is as follows:


Sheets("Report").Range("B2:U59").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
ThisWorkbook.Path & "\" & storelist(i) & ".pdf", to:=2, IgnorePrintAreas:=False


This part of code is within a loop (basically prints a page to a pdf file and then updates the page and charts before printing again).


It exports the file fine on all computers in the office.


The client who I set this up for however is having issues - the charts on the pdf output change in size and are much larger than they should be (they actually don't even fit on the page).


Is there any advice anyone can offer?

 

[fserrano]

All,

Any help with this is greatly appreciated. I am trying to create a VBA code that will write several select worksheets in a workbook to PDF and print them concurrently. I have yet to figure out the print function because I cannot get these to export as individual files yet. Here is what I have so far.

Sub savesheetsasPDF()


Dim ws As Worksheet
Dim Fname As String

Dim book(11) As String

book(1) = "Sheet2"
book(2) = "Sheet3"
book(3) = "Sheet7"
book(4) = "Sheet8"
book(5) = "Sheet9"
book(6) = "Sheet10"
book(7) = "Sheet11"
book(8) = "Sheet12"
book(9) = "Sheet13"
book(10) = "Sheet14"
book(11) = "Sheet15"

For x = 1 To 11

Fname = "Invoice#" & Worksheets(book(x)).Range("G1").Value
'having issues with the above line, I'm receiving a "run time error 9 subscript out of range" error.


ws.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:="G:\Cal Valley Rental Services\Dispatch Program\Billing Workbooks\TEST INVOICE REPORT" & Fname, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False

Next x
End Sub

 

[sgmpatnaik]

please check this link

OfficeTalk: Creating and Sending PDF Files from Excel 2007 and Excel 2010

it will give some idea to you, if not please come back again with your sample file

Thanks

SP

 

[fserrano]

That board did not address exporting multiple select worksheets withing a workbook. Can you advise how to attach my workbook to the board?

 

[mosulii]

sgmpatnaik
perfect it wok very good
thanks

 

[Mushtaq86]

Dear All,

Hope I am not late in posting a question here.
Below is the code I am using to export the files to PDF. The issue is it is not taking the same page size I am using for the file. For A4, it is publishing in A3 & for A3 it is publishing in A2 size.

Can anyone help me cracking the issue.

Sub Create_PDF()
 
 Dim pdfname As String
 Dim filepath As String
 Dim folder As String
 Dim Ans As Integer
 
 
 pdfname = Range("D4").Text
 'For Col Sheet: D4 =MID(LEFT(CELL("filename",A1), FIND("]", CELL("filename", A1)) - 6),FIND("[", CELL("filename", A1)) + 1, 255)
 
 'For Qty Sheet: D4 =(AA1 & " - Reinf. Details and Qty")
 'For Qty Sheet: AA1 =MID(LEFT(CELL("filename",A1), FIND("]", CELL("filename", A1)) - 6),FIND("[", CELL("filename", A1)) + 1, 255)
 
 filepath = Range("AA2").Value & pdfname & ".pdf"
 folder = Range("AA2").Value
 'AA2 =LEFT(CELL("filename"),FIND("[",CELL("filename"),1)-1)
 
 MsgBox "Kindly Close the PDF File (if it is open)"
 
 If Dir(filepath) <> "" Then
    Ans = MsgBox("File already exists.  Overwrite?", vbQuestion + vbYesNo, "Overwrite?")
    If Ans = vbNo Then Exit Sub
 End If
 
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    FileName:=ThisWorkbook.Path & "\" & pdfname, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False
 
   
    MsgBox "Look in " & folder & " for the files"
    Shell "Explorer.exe /n,/e," & folder, vbNormalFocus
   
End Sub

Thanks in advance.

 

[Mushtaq86]

One thing which i noticed that, in my file the scaling is not 100%. Width is adjusted to 1 page instead of automatic, hence scaling goes to 70%.