none
Want to export only selected Sheets to PDF format in MSExcelplugin developed in VS.NET RRS feed

  • Question

  • hey,

    I am developing the MS-EXCEL plugin in which i am trying to export my currently open workbook.  through my code i have to export the complete workbook but i want to export the selected sheets only.

    Please go through the code and help me how to do it:

     

    Dim paramSourceBookPath As String =

    "d:\Test.xlsx"

     

    Dim excelApplication As New

    Microsoft.Office.Interop.Excel.Application()

     

    Dim excelWorkbook As Microsoft.Office.Interop.Excel.Workbook =

    Nothing

    excelWorkbook =

    Globals

    .ThisAddIn.Application.ActiveWorkbook

     

    Dim paramExportFilePath As String =

    "d:\Test3.pdf"

     

    Dim paramExportFormat As

    XlFixedFormatType = _

     

    XlFixedFormatType

    .xlTypePDF

     

    Dim paramExportQuality As XlFixedFormatQuality

    = _

     

    XlFixedFormatQuality

    .xlQualityStandard

     

    Dim paramOpenAfterPublish As Boolean =

    False

     

    Dim paramIncludeDocProps As Boolean =

    True

     

    Dim paramIgnorePrintAreas As Boolean =

    True

     

    Dim paramFromPage As Object = Type

    .Missing

     

    Dim paramToPage As Object = Type

    .Missing

     

    ' Open the source workbook.

     

    Try

     

    ' excelWorkbook = excelApplication.Workbooks.Open(paramSourceBookPath)

     

    ' Save it in the target format.

     

    If Not excelWorkbook Is Nothing

    Then

    excelWorkbook.ExportAsFixedFormat(paramExportFormat, _

    paramExportFilePath, paramExportQuality, _

    paramIncludeDocProps, paramIgnorePrintAreas, _

    paramFromPage, paramToPage, paramOpenAfterPublish)

     

    End

    If

     

    Catch ex As

    Exception

     

    ' Respond to the error.

     

    Finally

     

    ' Close the workbook object.

     

    If Not excelWorkbook Is Nothing

    Then

    excelWorkbook.Close(

    False

    )

    excelWorkbook =

    Nothing

     

    End

    If

     

    ' Quit Excel and release the ApplicationClass object.

     

    If Not excelApplication Is Nothing

    Then

    excelApplication.Quit()

    excelApplication =

    Nothing

     

    End

    If

     

    GC

    .Collect()

     

    GC

    .WaitForPendingFinalizers()

     

    GC

    .Collect()

     

    GC

    .WaitForPendingFinalizers()

     

    End

    Try

    Please tell me how can i do this?
    Thursday, December 8, 2011 9:42 AM

All replies

  • I have written the code for exporting my worksheet into XPS format but i want to export it in protected excel format..

    How can i do this ?

     

     

    Dim paramSourceBookPath As String =

    "d:\Test.xlsx"

     

    ' Dim sheets1 As Microsoft.Office.Tools.Excel.Worksheet

     

     

    Dim excelApplication As New

    Microsoft.Office.Interop.Excel.Application()

     

    Dim excelWorkbook As Microsoft.Office.Interop.Excel.Workbook =

    Nothing

    excelWorkbook =

    Globals

    .ThisAddIn.Application.ActiveWorkbook

     

    'sheets1 = (Excel.Sheets)Globals.ThisAddIn.Application.ActiveWorkbook.Sheets(0)

     

    Try

    act =

    Globals

    .ThisAddIn.Application.ActiveWorkbook.Sheets(2)

     

    Dim paramExportFilePath As String =

    "d:\Test5.xps"

     

    ' Dim paramExportFilePath As String = "d:\Test5.xlsx"

     

    'Dim paramExportFormat As XlFixedFormatType = _

     

    ' XlFixedFormatType.xlTypePDF

     

    Dim paramExportFormat As

    XlFixedFormatType = _

     

    XlFixedFormatType

    .xlTypeXPS

     

    Dim paramExportQuality As XlFixedFormatQuality

    = _

     

    XlFixedFormatQuality

    .xlQualityStandard

     

    Dim paramOpenAfterPublish As Boolean =

    False

     

    Dim paramIncludeDocProps As Boolean =

    True

     

    Dim paramIgnorePrintAreas As Boolean =

    True

     

    Dim paramFromPage As Object = Type

    .Missing

     

    Dim paramToPage As Object = Type

    .Missing

     

    ' Open the source workbook.

     

    ' excelWorkbook = excelApplication.Workbooks.Open(paramSourceBookPath)

     

    ' Save it in the target format.

     

    'VERY IMPORTANT THAT: yo can export the complete workbook as well as the selected sheets to PDF format'

     

    If Not excelWorkbook Is Nothing

    Then

     

    'excelWorkbook.ExportAsFixedFormat(paramExportFormat, _

     

    ' paramExportFilePath, paramExportQuality, _

     

    ' paramIncludeDocProps, paramIgnorePrintAreas, _

     

    ' paramFromPage, paramToPage, paramOpenAfterPublish)

    act.ExportAsFixedFormat(paramExportFormat, _

    paramExportFilePath, paramExportQuality, _

    paramIncludeDocProps, paramIgnorePrintAreas, _

    paramFromPage, paramToPage, paramOpenAfterPublish)

     

    End

    If

     

    Catch ex As

    Exception

     

    ' Respond to the error.

     

    Finally

     

    ' Close the workbook object.

     

    If Not excelWorkbook Is Nothing

    Then

    excelWorkbook.Close(

    False

    )

    excelWorkbook =

    Nothing

     

    End

    If

     

    ' Quit Excel and release the ApplicationClass object.

     

    If Not excelApplication Is Nothing

    Then

    excelApplication.Quit()

    excelApplication =

    Nothing

     

    End

    If

     

    GC

    .Collect()

     

    GC

    .WaitForPendingFinalizers()

     

    GC

    .Collect()

     

    GC

    .WaitForPendingFinalizers()

     

     

    End

    Try

    Thursday, December 8, 2011 11:05 AM
  • Hey,

    i have created a MSexcel plugin created in VS.net , i have implemted the functionality of exporting the excel sheet into PDF but i want to export the same sheet also in protected excel sheet format.

    Please guide me how can i do this?

    For XPS format i have written this code:

     

    Dim paramSourceBookPath As String =

     

     

     

     

     

    Dim excelApplication As New

     

    Dim excelWorkbook As Microsoft.Office.Interop.Excel.Workbook =

     

    excelWorkbook =

    Globals

     

     

     

     

    act =

    Globals

     

    Dim paramExportFilePath As String =

     

     

     

     

     

     

     

     

    Dim paramExportFormat As

     

    XlFixedFormatType

     

    Dim paramExportQuality As XlFixedFormatQuality

     

    XlFixedFormatQuality

     

    Dim paramOpenAfterPublish As Boolean =

     

     

    Dim paramIncludeDocProps As Boolean =

     

     

    Dim paramIgnorePrintAreas As Boolean =

     

     

    Dim paramFromPage As Object = Type

     

    Dim paramToPage As Object = Type

     

     

     

     

     

     

     

     

     

    If Not excelWorkbook Is Nothing

     

     

     

     

     

     

     

     

     

    act.ExportAsFixedFormat(paramExportFormat, _

    paramExportFilePath, paramExportQuality, _

    paramIncludeDocProps, paramIgnorePrintAreas, _

    paramFromPage, paramToPage, paramOpenAfterPublish)

     

    End

     

     

    Catch ex As

     

     

     

     

     

     

     

     

    If Not excelWorkbook Is Nothing

     

    excelWorkbook.Close(

    False

     

     

     

    End

     

     

     

     

    If Not excelApplication Is Nothing

     

    excelApplication.Quit()

    excelApplication =

     

     

    End

     

     

    GC

     

    GC

     

    GC

     

    GC

     

    End Try

    Urgent Help me

     

    .WaitForPendingFinalizers()

     

     

     

    .Collect()

     

     

    .WaitForPendingFinalizers()

     

     

    .Collect()

     

     

     

    If

     

    Nothing

     

    Then

     

    ' Quit Excel and release the ApplicationClass object.

     

    If

     

    Nothing

    )

    excelWorkbook =

     

     

    Then

     

    ' Close the workbook object.

     

    Finally

     

    ' Respond to the error.

     

    Exception

     

    If

     

    ' paramFromPage, paramToPage, paramOpenAfterPublish)

     

    ' paramIncludeDocProps, paramIgnorePrintAreas, _

     

    ' paramExportFilePath, paramExportQuality, _

     

    'excelWorkbook.ExportAsFixedFormat(paramExportFormat, _

     

    Then

     

    'VERY IMPORTANT THAT: yo can export the complete workbook as well as the selected sheets to PDF format'

     

    ' Save it in the target format.

     

    ' excelWorkbook = excelApplication.Workbooks.Open(paramSourceBookPath)

     

    ' Open the source workbook.

    .Missing

     

     

    .Missing

     

     

     

    True

     

    True

     

    False

    .xlQualityStandard

     

     

    = _

     

     

    .xlTypeXPS

     

     

    XlFixedFormatType = _

     

     

     

    ' XlFixedFormatType.xlTypePDF

     

    'Dim paramExportFormat As XlFixedFormatType = _

     

    ' Dim paramExportFilePath As String = "d:\Test5.xlsx"

     

    "d:\Test5.xps"

    .ThisAddIn.Application.ActiveWorkbook.Sheets(2)

     

     

     

    Try

     

    'sheets1 = (Excel.Sheets)Globals.ThisAddIn.Application.ActiveWorkbook.Sheets(0)

    .ThisAddIn.Application.ActiveWorkbook

     

     

     

    Nothing

    Microsoft.Office.Interop.Excel.Application()

     

     

     

    ' Dim sheets1 As Microsoft.Office.Tools.Excel.Worksheet

     

    "d:\Test.xlsx"

    Thursday, December 8, 2011 11:28 AM
  • Hi ArchanaSinghvi,

     

    Thanks for posting in the MSDN Forum.

     

    As far as I know only the workbook instance has the method to export the active worksheet to PDF. This means Excel will not export all of the worksheet in the workbook to PDF file, only the active worksheet (maybe the particular worksheet is you selected if it isn’t please active your selected worksheet). I will show you the snippet which works fine on my side. I hope it can help you.

     

    Imports Microsoft.Office.Tools.Ribbon
    Imports System.Windows.Forms
    
    Public Class Ribbon1
    
        Private Sub Ribbon1_Load(ByVal sender As System.Object, _
    ByVal e As RibbonUIEventArgs) Handles MyBase.Load
    
        End Sub
    
        Private Sub Button1_Click(ByVal sender As System.Object, _
    ByVal e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) _
    Handles Button1.Click
            Dim objApplication As Excel.Application
            Dim objActiveWorkbook As Excel.Workbook
            Dim strSavePath As String
            Dim objSaveFileDialog As SaveFileDialog = New SaveFileDialog
    
            objApplication = Globals.ThisAddIn.Application
            objActiveWorkbook = objApplication.ActiveWorkbook
            Try
                objSaveFileDialog.Filter = "PDF File(*.pdf)|*.pdf"
                objSaveFileDialog.ShowDialog()
                strSavePath = objSaveFileDialog.FileName
                objActiveWorkbook.ExportAsFixedFormat _
    (Excel.XlFixedFormatType.xlTypePDF, Filename:=strSavePath)
            Catch ex As Exception
                MsgBox("Stack:" + Chr(13) + ex.StackTrace + Chr(13) + "Message:" _
    + Chr(13) + ex.Message, Title:="ExcelAddIn10 Exception Message")
            End Try
        End Sub
    End Class
    

     

    Have a good day,

     

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Tuesday, December 13, 2011 3:27 AM
    Moderator
  • Hi ArchanaSinghvi,

     

    Thanks for posting in the MSDN Forum.

     

    Would you please clarify the mean of “protected excel sheet format”? Please show me the extend file name of this kind of file format.

     

    Have a good day,

     

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Tuesday, December 13, 2011 3:33 AM
    Moderator
  • Hye,

    This is the Xlxs that have the normal syntax as the Excel file but it will be readonly so no other user can edit once it is exported..

     

    Tuesday, December 13, 2011 4:58 AM