Convert Excel File To XPS and PDF

Convert Excel File To XPS and PDF

Imports Microsoft.Office.Interop.Excel
Public Class XlscDoc
    Dim oXL As Application
    Dim oWB As Workbook
    Dim oSheet As Worksheet
    Dim oRng As Range
    Dim path As String
    Dim path1 As String
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
       
        ' If either required string is null or empty, stop and bail out
        If String.IsNullOrEmpty(TextBox1.Text) OrElse String.IsNullOrEmpty(TextBox2.Text) Then
            MsgBox("Path Empty")
        End If

        ' Create COM Objects
        Dim excelApplication As Microsoft.Office.Interop.Excel.Application
        Dim excelWorkbook As Microsoft.Office.Interop.Excel.Workbook

        ' Create new instance of Excel
        excelApplication = New Microsoft.Office.Interop.Excel.Application()

        ' Make the process invisible to the user
        excelApplication.ScreenUpdating = False

        ' Make the process silent
        excelApplication.DisplayAlerts = False

        ' Open the workbook that you wish to export to PDF
        excelWorkbook = excelApplication.Workbooks.Open(TextBox1.Text)

        ' If the workbook failed to open, stop, clean up, and bail out
        If excelWorkbook Is Nothing Then
            excelApplication.Quit()

            excelApplication = Nothing
            excelWorkbook = Nothing

            MsgBox("Path Empty")
        End If

        Dim exportSuccessful = True
        Dim a As String
        a = TextBox2.Text & "\" & TextBox3.Text & ".pdf"
        Try
            ' Call Excel's native export function (valid in Office 2007 and Office 2010, AFAIK)
            excelWorkbook.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF, a)
        Catch ex As System.Exception
            ' Mark the export as failed for the return value...

            ' Do something with any exceptions here, if you wish...
            ' MessageBox.Show...       
            exportSuccessful = False
        Finally
            ' Close the workbook, quit the Excel, and clean up regardless of the results...
            excelWorkbook.Close()
            excelApplication.Quit()

            excelApplication = Nothing
            excelWorkbook = Nothing
        End Try

        ' You can use the following method to automatically open the PDF after export if you wish
        ' Make sure that the file actually exists first...
        If System.IO.File.Exists(TextBox2.Text) Then
            System.Diagnostics.Process.Start(TextBox2.Text)
        End If
        MsgBox("Export succesful")
    End Sub

    Private Sub XlscDoc_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        path = docpath
        TextBox1.Text = path
        path1 = pdfpath
        TextBox2.Text = path1
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim d As String
        Dim f As String
        OpenFileDialog1.Title = "Please select a MicroSoft Excel file"
        OpenFileDialog1.InitialDirectory = docpath
        OpenFileDialog1.Filter = "Word files (*.xls)|*.xls"
        If OpenFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
            Dim wordFileName As String = OpenFileDialog1.FileName
            TextBox1.Text = wordFileName
        End If
        d = System.IO.Path.GetFileName(OpenFileDialog1.FileName)
        f = ".xlsx"
        TextBox3.Text = Replace(d, f, "")
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        FolderBrowserDialog1.ShowDialog()
        TextBox2.Text = FolderBrowserDialog1.SelectedPath
    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        TextBox1.Clear()
        TextBox2.Clear()
        TextBox3.Clear()
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        If String.IsNullOrEmpty(TextBox1.Text) OrElse String.IsNullOrEmpty(TextBox2.Text) Then
            MsgBox("Path Empty")
        End If

        ' Create COM Objects
        Dim excelApplication As Microsoft.Office.Interop.Excel.Application
        Dim excelWorkbook As Microsoft.Office.Interop.Excel.Workbook

        ' Create new instance of Excel
        excelApplication = New Microsoft.Office.Interop.Excel.Application()

        ' Make the process invisible to the user
        excelApplication.ScreenUpdating = False

        ' Make the process silent
        excelApplication.DisplayAlerts = False

        ' Open the workbook that you wish to export to PDF
        excelWorkbook = excelApplication.Workbooks.Open(TextBox1.Text)

        ' If the workbook failed to open, stop, clean up, and bail out
        If excelWorkbook Is Nothing Then
            excelApplication.Quit()

            excelApplication = Nothing
            excelWorkbook = Nothing

            MsgBox("Path Empty")
        End If

        Dim exportSuccessful = True
        Dim a As String
        a = TextBox2.Text & "\" & TextBox3.Text & ".xps"
        Try
            ' Call Excel's native export function (valid in Office 2007 and Office 2010, AFAIK)
            excelWorkbook.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypeXPS, a)
        Catch ex As System.Exception
            ' Mark the export as failed for the return value...

            ' Do something with any exceptions here, if you wish...
            ' MessageBox.Show...       
            exportSuccessful = False
        Finally
            ' Close the workbook, quit the Excel, and clean up regardless of the results...
            excelWorkbook.Close()
            excelApplication.Quit()

            excelApplication = Nothing
            excelWorkbook = Nothing
        End Try

        ' You can use the following method to automatically open the PDF after export if you wish
        ' Make sure that the file actually exists first...
        If System.IO.File.Exists(TextBox2.Text) Then
            System.Diagnostics.Process.Start(TextBox2.Text)
        End If
        MsgBox("Export succesful")
    End Sub
End Class