- EXCEL TO PDF - CODE

 

'Imports Excel = Microsoft.Office.Interop.Excel

Public Class frmExcelToPDF

Dim ConfigFileIn As System.IO.StreamReader
Dim ConfigFileOut As System.IO.StreamWriter
Dim MyExcel As New Microsoft.Office.Interop.Excel.Application
Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
Dim FileDialog As OpenFileDialog = New OpenFileDialog()
Dim SelectedFile As String
Dim WorkSheetName As String
Dim SelectedCellColumn As Int32
Dim SelectedCellColumnChar() As Char
Dim SelectedCellRow As Int32
Dim SelectedCell As Excel.Range
Dim OutputFileName As String
Dim DateAndTimeWindows As String
Dim DateAndTimePre As String
Dim DateAndTime As String
Dim FolderName As String
Dim StoredNameOption As String

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

ConfigFileRead()

End Sub
Private Sub ConfigFileRead()

If Not System.IO.Directory.Exists("C:\PrintToPDF") Then
System.IO.Directory.CreateDirectory("C:\PrintToPDF")
End If
If System.IO.File.Exists("C:\PrintToPDF\ConfigFile.txt") Then
Else
ConfigFileOut = My.Computer.FileSystem.OpenTextFileWriter("C:\PrintToPDF\ConfigFile.txt", True)
ConfigFileOut.WriteLine("")
ConfigFileOut.WriteLine("0")
ConfigFileOut.WriteLine("0")
ConfigFileOut.WriteLine("0")
ConfigFileOut.Close()
End If
ConfigFileIn = My.Computer.FileSystem.OpenTextFileReader("C:\PrintToPDF\ConfigFile.txt")
SelectedFile = ConfigFileIn.ReadLine()
StoredNameOption = ConfigFileIn.ReadLine()
cmbSelectColumn.SelectedIndex = ConfigFileIn.ReadLine()
cmbSelectRow.SelectedIndex = ConfigFileIn.ReadLine()
ConfigFileIn.Close()
txtSelectFile.Text = SelectedFile
If StoredNameOption = "0" Then
rdoTabName.Checked = True
Else : rdoCellName.Checked = True
End If

End Sub
Private Sub cmdSelectFile_Click(sender As Object, e As EventArgs) Handles cmdSelectFile.Click

FileDialog.Title = "Open File Dialog"
FileDialog.InitialDirectory = (My.Computer.FileSystem.SpecialDirectories.Desktop)
FileDialog.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm"
FileDialog.FilterIndex = 2
FileDialog.RestoreDirectory = True
If FileDialog.ShowDialog() = DialogResult.OK Then
SelectedFile = FileDialog.FileName
End If
txtSelectFile.Text = SelectedFile

End Sub
Private Sub cmdPrintToPDF_Click(sender As Object, e As EventArgs) Handles cmdPrintToPDF.Click

PrintToPDF()

End Sub
Public Sub PrintToPDF()

CreateFolderName()
System.IO.Directory.CreateDirectory(My.Computer.FileSystem.SpecialDirectories.Desktop & FolderName)
Process.Start("explorer.exe", String.Format("/n, /e, {0}", (My.Computer.FileSystem.SpecialDirectories.Desktop & FolderName)))
SelectedCellColumnChar = cmbSelectColumn.Text.ToCharArray
SelectedCellColumn = Convert.ToInt32(SelectedCellColumnChar(0)) - 64
SelectedCellRow = Val(cmbSelectRow.Text)
Try
MyExcel.Workbooks.Open(SelectedFile)
MyExcel.DisplayAlerts = False
For Each xlWorkSheet In MyExcel.Sheets
WorkSheetName = (xlWorkSheet.Name)
MyExcel.Sheets(WorkSheetName).activate()
If rdoTabName.Checked = True Then : If WorkSheetName <> "" Then
OutputFileName = WorkSheetName : End If
Else
SelectedCell = CType(MyExcel.Cells(SelectedCellRow, SelectedCellColumn), Excel.Range)
OutputFileName = SelectedCell.Value
End If
If OutputFileName <> "" Then
MyExcel.ActiveSheet.ExportAsFixedFormat(0, My.Computer.FileSystem.SpecialDirectories.Desktop & FolderName & "\" & OutputFileName & ".pdf") : End If
Next : MyExcel.Workbooks.Close()
MyExcel.Quit()
Catch ex As Exception
MsgBox("No Excel File Found At This Location", MsgBoxStyle.Information, "")
End Try

End Sub
Private Sub CreateFolderName()

DateAndTimeWindows = System.DateTime.Now
DateAndTimePre = ""
DateAndTime = ""
Do While DateAndTimeWindows <> ""
DateAndTimePre = DateAndTimeWindows.Substring(0, 1)
DateAndTimeWindows = DateAndTimeWindows.Remove(0, 1)
If DateAndTimePre = "/" Or DateAndTimePre = "\" Or DateAndTimePre = ":" Then
DateAndTimePre = "_"
End If
If DateAndTimePre = " " Then
DateAndTimePre = " - " : End If
DateAndTime = DateAndTime & DateAndTimePre
Loop
FolderName = "\PrintToPDF - " & DateAndTime

End Sub
Private Sub cmdSaveConfig_Click(sender As Object, e As EventArgs) Handles cmdSaveConfig.Click

If rdoTabName.Checked = True Then
StoredNameOption = "0"
Else
StoredNameOption = "1"
End If
If System.IO.File.Exists("C:\PrintToPDF\ConfigFile.txt") = True Then
System.IO.File.Delete("C:\PrintToPDF\ConfigFile.txt")
End If
ConfigFileOut = My.Computer.FileSystem.OpenTextFileWriter("C:\PrintToPDF\ConfigFile.txt", True)
ConfigFileOut.WriteLine(SelectedFile)
ConfigFileOut.WriteLine(StoredNameOption)
ConfigFileOut.WriteLine(cmbSelectColumn.SelectedIndex)
ConfigFileOut.WriteLine(cmbSelectRow.SelectedIndex)
ConfigFileOut.Close()

End Sub
Private Sub cmdExit_Click(sender As Object, e As EventArgs) Handles cmdExit.Click

MyExcel = Nothing : End

End Sub
Private Sub Form1_FormClosing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles MyBase.FormClosing

MyExcel = Nothing

End Sub
Private Sub lblLinkLabel_Click(sender As Object, e As EventArgs) Handles lblLinkLabel.Click

Dim webAddress As String = "http://www.murraypurdon.co.za/" : Process.Start(webAddress)

End Sub

End Class