Exporting Some Sheets from Excel Workbook to PDF

Question

I am working on writing a VBA code to export some of the sheets in excel to same PDF. I have several chart sheets in my excel file each of which name ends with "(name)_Chart". I want to export all sheets with names ending wioth chart to one PDF file. Here is the code I am trying to write.

Sub FindWS()
        'look if it at least contains part of the name
        Dim s As Worksheet
        Dim strPath As String

        strPath = ActiveWorkbook.Path & "\"

        For Each s In ThisWorkbook.Sheets
            If InStr(1, s.Name, Chart) Then
                s.Activate
                ActiveSheet.ExportAsFixedFormat xlTypePDF, strPath & s.Name & ".pdf"
                Exit Sub
            End If
        Next s
End Sub

This code is not limting export to only the chart sheets but exporting thy whole workbook. Can anyone help me with figurint out whats is missing in my code.

Thanks!

MODIFIED CODE:

Sub FindWS()
'look if it at least contains part of the name
Dim s As Worksheet
Dim strPath As String

strPath = ActiveWorkbook.Path & "\"

For Each s In ThisWorkbook.Worksheets
      If InStr(1, s.Name, "Chart") = 0 Then
          ' Hide the sheet so it is not exported as PDF
          s.Visible = False
             End If
Next s
          With ActiveWorkbook
          .ExportAsFixedFormat xlTypePDF, strPath & "TEST.pdf"
                End With

End Sub

What happens if you first hide your unwanted sheets in the loop above, then just export it once as PDF? — Nick.Mc, Feb 18 '13 at 04:18
@ElectricLlama Thanks for your response. I have over 300 excel sheets and hiding all unwanted sheets in these would be too time consuming. So, is there a way I cam modify the code? — datacentric, Feb 18 '13 at 04:21
You hide the sheets in your code, export, then unhide them. I will post some code for you to try — Nick.Mc, Feb 18 '13 at 04:24
@ElectricLlama Please do. Thank you so much. I have been really struggling getting the code to work right. — datacentric, Feb 18 '13 at 04:27
The code as is actually just loops until it finds the first named sheet then exports the whole lot regardless. — Nick.Mc, Feb 18 '13 at 04:36

Siddharth Rout · Accepted Answer · 2013-02-18T05:44:52.177

2

I am surprised that your code is running in the first place :) You should have actually got an error run time error '13', type mismatch

Sheets and Worksheets are two different things in Excel

The Worksheets collection is a collection of all the Worksheet objects in the specified or active workbook. Each Worksheet object represents a worksheet. Whereas the Sheets collection, on the other hand, consist of not only a collection of worksheets but also other types of sheets to include Chart sheets, Excel 4.0 macro sheets and Excel 5.0 dialog sheets.

So if you declare your object as Worksheet

Dim s As Worksheet

Then ensure that while looping you loop through the correct collection

For Each s In ThisWorkbook.Worksheets

and not

For Each s In ThisWorkbook.Sheets

else you will get a run time error '13', type mismatch

FOLLOWUP (Based on Comments)

@ Siddharth: 1. Yes, I want to export Chart sheets that ends with name "Chart". 2. I want all those charts in one PDF and the name of the PDF should be the "original" file name. (I will have to save the final PDF files in different location so there will be no overlapping of files.) – datacentric

Option Explicit

Sub Sample()
    Dim ws As Object
    Dim strPath As String, OriginalName As String, Filename As String

    On Error GoTo Whoa

    '~~> Get activeworkbook path
    strPath = ActiveWorkbook.Path & "\"
    '~~> Get just the name without extension and path
    OriginalName = Left(ActiveWorkbook.Name, (InStrRev(ActiveWorkbook.Name, ".", -1, vbTextCompare) - 1))
    '~~> PDF File name
    Filename = strPath & OriginalName & ".pdf"

    '~~> Loop through Sheets Collesction
    For Each ws In ActiveWorkbook.Sheets
        '~~> Check if it is a Chart Sheet and also it ends in "Chart"
        If ws.Type = 3 And UCase(Right(Trim(ws.Name), 5)) = "CHART" Then
            ws.Visible = True
        Else
            ws.Visible = False
        End If
    Next ws

    '~~> Export to pdf
    ActiveWorkbook.ExportAsFixedFormat xlTypePDF, Filename

LetsContinue:
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

edited Feb 18 '13 at 05:44

answered Feb 18 '13 at 05:12

Siddharth Rout

147,039
17
206
250

Thanks for your response. I figured out that there is some issue with using sheets instead of worksheet. So, I modified my code but now its only exporting the first sheet in excel and not all sheets. The modified code is in my question above. – datacentric Feb 18 '13 at 05:14
If you want to check if the sheet is ending with "Chart" then `instr()` is not the right way. You have to use `Right()` – Siddharth Rout Feb 18 '13 at 05:17
Also, I have both worksheets and chart sheets in excel. I only want to export chart sheets. – datacentric Feb 18 '13 at 05:17
Ok before I give you the code, can you please confirm 2 things for me 1) You want to export chart sheets which `end` in "Chart" 2) You want all those charts in 1 PDF or separate pdfs? – Siddharth Rout Feb 18 '13 at 05:25
@ Siddharth Rout: Based on all the responses to my question, I finally figured out the code. Hide all unnecessary sheets ("Worksheets") and then export workbook (remaining chart sheets) into one PDF. I have addded the corrected code in my question as a refernce for other people. Thank you so much. :) Appreciate all your help! – datacentric Feb 18 '13 at 05:30
Your "Final Corrected Code" is incorrect. 1) `ThisWorkbook.Worksheets` will never loop through your chart sheets. 2) `Instr` is the wrong way to check if the chart sheet name ends in "Chart" – Siddharth Rout Feb 18 '13 at 05:31
Please see my previous to previous comment. I asked you two questions :) – Siddharth Rout Feb 18 '13 at 05:33
@ Siddharth: 1. Yes, I want to export Chart sheets that ends with name "Chart". 2. I want all those charts in one PDF and the name of the PDF should be the "original" file name. (I will have to save the final PDF files in different location so there will be no overlapping of files.) – datacentric Feb 18 '13 at 05:36
Gimme few minutes. Updating my answer. Will also test my code before posting. – Siddharth Rout Feb 18 '13 at 05:37
Siddharth, you are great :). This worked perfect, exactly what I want. :).. Thanks again for all your help. Have a good one! – datacentric Feb 18 '13 at 05:50
One last thing. Can you recommend me a book to start with learning VBA? – datacentric Feb 18 '13 at 05:55
Why buy a book when there are so many free tutorials online? ;) MSDN covers VBA in a very comprehensive manner. But if you still want to buy a book then checkout books by `John Walkenbach` – Siddharth Rout Feb 18 '13 at 05:57
errrr you post my code and accept someone else's answer? oh well. – Nick.Mc Feb 18 '13 at 05:59
@ElectricLlama: I am sorry.. your code? the only part which is similar is `ws.Visible = True` part... Rest I don't see any similarity? My post further addresses the main issues between `worksheets` and `sheets` and the incorrect use of `INSTR` – Siddharth Rout Feb 18 '13 at 06:01
@ElectricLlama You must be kidding. Anybody who reads the original question/code and Siddharth's detailed replies will know who really answered it. – datacentric Feb 18 '13 at 06:07
@Siddharth: Thanks. I will check out those resources. :) – datacentric Feb 18 '13 at 06:08
Siddharth don't take it personally - I wasn't saying you posted my code I was saying datacentric posted my code in his original answer! Apologies for any miscommunication. By all means Siddharth took a lot of time and detailed effort to answer your question. For clarity I suggest you take my code out of your original question if you don't feel it met your needs as it will mislead anyone who needs an answer to this question. – Nick.Mc Feb 18 '13 at 22:41

Nick.Mc · Answer 2 · 2013-02-18T04:47:42.623

This code will look through all the sheets. If the sheet name doesn't match it will hide it. When it's finished that it exports all visible sheets into one PDF. Make sure yuo don't save the Excel file afterwards or the sheets will remain hidden.

Of course this code is not tested so if you have issues ask back (or try and resolve themself as you may learn something)

Sub FindWS()
    'look if it at least contains part of the name
    Dim s As Worksheet
    Dim strPath As String

    strPath = ActiveWorkbook.Path & "\"

    For Each s In ThisWorkbook.Sheets
        If InStr(1, s.Name, "Chart") = 0 Then
              ' Hide the sheet so it is not exported as PDF
              s.Visible = False 
        End If
    Next s

    ' Export all sheets as PDF         
    ActiveSheet.ExportAsFixedFormat xlTypePDF, strPath & "TEST.pdf"
End Sub

OH! I didn't realise your code didn't actually work before - let me fix it. — Nick.Mc, Feb 18 '13 at 04:46

Exporting Some Sheets from Excel Workbook to PDF

2 Answers2

Linked