none
In Excel, how can I save selected sheets as a PDF using C# VSTO? RRS feed

  • Question

  • Hello,

    I am using C# in VS 2008 to create an Excel addin. Part of this is to create a PDF from just some of the sheets in the workbook. I can do this just fine manually by selecting the sheets I want (CTRL-click) and do a Save As to a PDF file. Looking at how VBA records it, is shows an array of selected sheets and selects that array. I couldn't figure that out in C#.

    In C# I've tried to use the ExportAsFixedFormat and set the page numbers, but it only seems to output the first sheet.

    Is there a way I can save selected sheets as a PDF using C# VSTO?

    Thank you.

    ~J

    Friday, December 20, 2013 3:08 PM

Answers

  • Hello Jenna_Fire,

    Here is the code for selecting the second and fourth sheets in the workbook:

    Excel.Workbook wBook = ExcelApp.ActiveWorkbook as Excel.Workbook;
    Excel.Sheets wSheets = wBook.Worksheets as Excel.Sheets;
    Excel.Worksheet secondSheet = wSheets[2] as Excel.Worksheet;
    secondSheet.Select(true);
    Excel.Worksheet fourthSheet = wSheets[4] as Excel.Worksheet;
    fourthSheet.Select(false);
    
    Excel.Window window = ExcelApp.ActiveWindow;
    Excel.Sheets sheets = window.SelectedSheets as Excel.Sheets;

    But the Sheets class, which represents the selected sheets, doesn't provide the ExportAsFixedFormat method. Instead, you can use the Workbook class - create a temporarily workbook in memory and then add the selected sheets to it, call the method for creating a pdf file and then remove it. You can use the Application.ScreenUpdating method for hiding the whole process from user's eyes.

    • Marked as answer by Jenna_Fire Monday, December 23, 2013 5:50 PM
    Sunday, December 22, 2013 7:24 PM

All replies

  • Hello Jenna_Fire,

    Here is the code for selecting the second and fourth sheets in the workbook:

    Excel.Workbook wBook = ExcelApp.ActiveWorkbook as Excel.Workbook;
    Excel.Sheets wSheets = wBook.Worksheets as Excel.Sheets;
    Excel.Worksheet secondSheet = wSheets[2] as Excel.Worksheet;
    secondSheet.Select(true);
    Excel.Worksheet fourthSheet = wSheets[4] as Excel.Worksheet;
    fourthSheet.Select(false);
    
    Excel.Window window = ExcelApp.ActiveWindow;
    Excel.Sheets sheets = window.SelectedSheets as Excel.Sheets;

    But the Sheets class, which represents the selected sheets, doesn't provide the ExportAsFixedFormat method. Instead, you can use the Workbook class - create a temporarily workbook in memory and then add the selected sheets to it, call the method for creating a pdf file and then remove it. You can use the Application.ScreenUpdating method for hiding the whole process from user's eyes.

    • Marked as answer by Jenna_Fire Monday, December 23, 2013 5:50 PM
    Sunday, December 22, 2013 7:24 PM
  • Eugene,

    I really appreciate this information. It is a great help! I was really stuck and I didn't realize about creating a new temporary Workbook in memory.

    Thank you so much.

    ~J

    Monday, December 23, 2013 5:50 PM
  • I tried to duplicate this, based on your statement, "But the Sheets class, which represents the selected sheets", but I find it doesn't actually represent the selected sheets but all the sheets in the workbook.  Is there a way to retrieve only the selected sheets in dotNet VSTO?
    Tuesday, March 11, 2014 2:18 PM
  • Hi Tom,

    I know it sounds weird but the Sheets class represents the selected sheets :)

    Of course, you can use the sample code in C# I listed above in your VSTO based add-ins. Do you get any errors or exceptions?

    Tuesday, March 11, 2014 3:05 PM