Rename excel file based on values in the form field vb.net

In vb.net I am using excel parameters to convert my excel sheets to pdf. This works fine but now last minute I am to change my naming convention based on values that will be standardized in the cells of the excel forms.  I was wondering how I would do this, the naming convention would be like:

 "EMPLOYERNAME_excelFormValueD1_STATICNUMBER_excelFormValueL1"

Just to note I am writing in vb.net in visual studio NOT in excel vb. (sometimes my wording gets experts confused cause I am a novice developer)

Here is an example of my excel to pdf conversion:

   Dim dir As New IO.DirectoryInfo("C:\xxxx_Projects\Testing")
        Dim files As IO.FileInfo() = dir.GetFiles("*.xls")

        ProgressBar1.PerformStep()

        For Each file In files
            Dim excelApplication As ApplicationClass = New ApplicationClass()
            Dim excelWorkbook As Workbook = Nothing
            Dim paramSourceBookPath As String = file.FullName
            Dim paramExportFilePath As String = file.DirectoryName & "\OUTPUT_TEST\" & Path.GetFileNameWithoutExtension(file.Name) & ".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


            Try
                ' Open the source workbook.
                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

        Next

        
        MessageBox.Show("The Process is Complete", "Completed", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly, False)

        Me.Close()

Open in new window



Thanks in advance experts.
thomashospitalAsked:
Who is Participating?
 
FamousMortimerConnect With a Mentor Commented:
Its no problem.  How many sheets are in each workbook?

If there are multiple sheets (or the sheets may be in different orders) this will require more work, but this should do it if the first sheet contains the information you need.

Try
                ' Open the source workbook.
                excelWorkbook = excelApplication.Workbooks.Open(paramSourceBookPath)
                Dim excelWorksheet As Excel.Worksheet = excelWorkbook.Sheets(1)
                paramExportFilePath = file.DirectoryName & "\OUTPUT_TEST\" & "EMPLOYERNAME_" & excelWorksheet.Cells(1, 3) & "_STATICNUMBER_" & excelSheet.cells(1, 12) & ".pdf"

Open in new window

0
 
FamousMortimerCommented:
Hi Thomas,

I haven't tested this but it should work for you...

        For Each file In files
            Dim excelApplication As ApplicationClass = New ApplicationClass()
            Dim excelWorkbook As Workbook = Nothing
            Dim paramSourceBookPath As String = file.FullName
            Dim paramExportFilePath As String 
            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


            Try
                ' Open the source workbook.
                excelWorkbook = excelApplication.Workbooks.Open(paramSourceBookPath)
		dim excelSheet as excelworkbook.sheets(1) 'or excelWorkbook.ActiveSheet
		paramExportFilePath=file.DirectoryName & "\OUTPUT_TEST\" & "EMPLOYERNAME_" & excelSheet.Cells(1,3) & "_STATICNUMBER_" & excelSheet.cells(1,12) & ".pdf"
                ' Save it in the target format.
                If Not excelWorkbook Is Nothing Then
                    excelWorkbook.ExportAsFixedFormat(paramExportFormat, _
                        paramExportFilePath, paramExportQuality, _
                        paramIncludeDocProps, paramIgnorePrintAreas, _
                        paramFromPage, paramToPage, paramOpenAfterPublish)
                End If

Open in new window

0
 
thomashospitalAuthor Commented:
Hello Famous and thanks for the help. Im getting a couple errors, and I am just now starting to trouble shoot them. If by any chance your reading this and want to throw in your input, its appreciated.

dim excelSheet as excelworkbook.sheets(1) 'or excelWorkbook.ActiveSheet

Open in new window

--With excelworkbook.sheets(1) it tells me Array bounds cannot appear in the type specifiers.


dim excelSheet as excelworkbook.sheets(1) 'or excelWorkbook.ActiveSheet
                paramExportFilePath = file.DirectoryName & "\OUTPUT_TEST\" & "EMPLOYERNAME_" & excelSheet.Cells(1, 3) & "_STATICNUMBER_" & excelSheet.cells(1, 12) & ".pdf"

Open in new window

 --This keeps telling me that excelSheet is not declared
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
FamousMortimerConnect With a Mentor Commented:
Sorry that was my mistake.

You will need to add a reference to the Microsoft Excel Object Library.  See here http://support.microsoft.com/kb/301982

Then we can rework a bit of the code.

Edit:  Also Import the following
Imports Microsoft.Office.Interop

Open in new window



        For Each file In files
            Dim excelApplication As Excel.Application
            Dim excelWorkbook As Excel.Workbook = Nothing
            Dim excelWorksheet As Excel.Worksheet = Nothing
            Dim paramSourceBookPath As String = file.FullName
            Dim paramExportFilePath As String = file.DirectoryName & "\OUTPUT_TEST\" & Path.GetFileNameWithoutExtension(file.Name) & ".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


            Try
                ' Open the source workbook.
                excelWorkbook = excelApplication.Workbooks.Open(paramSourceBookPath)
                excelWorksheet = excelWorkbook.Sheets(1)
                paramExportFilePath = file.DirectoryName & "\OUTPUT_TEST\" & "EMPLOYERNAME_" & excelSheet.Cells(1, 3) & "_STATICNUMBER_" & excelWorkSheet.cells(1, 12) & ".pdf"

                ' 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

Open in new window

0
 
thomashospitalAuthor Commented:
Thanks Famous for the help, I think we are getting closer.  I had the imports in already as far as the code I am getting a null reference exception thrown at
 excelWorkbook = excelApplication.Workbooks.Open(paramSourceBookPath)

Open in new window

Object reference not set to an instance of an object.

I really do appreciate all the help.
0
 
FamousMortimerConnect With a Mentor Commented:
Try adding New to the declaration
Dim excelApplication As New Excel.Application
Dim excelWorkbook As New Excel.Workbook

Open in new window


Silly question, but you do have Excel installed on the PC that is running this program?

If that doesn't work, put a breakpoint on that line, and check each variables value at run time to see which one is causing the object reference error.

Sorry, I would test it myself, but I won't be able to for a few hours.
0
 
thomashospitalAuthor Commented:
I do have excel installed on this machine.  I also have the COMs added in as a reference.  Right now the original functions correctly as it will loop through the source folder grab the name of all excel files there, and then convert them to pdf keeping the same name.  This latest naming requirement was sprung on me last minute, and Im not that strong of a vb.net programmer.

Famous dont apologize for anything, if your willing to put up with my newbie questions, I will take all the help I can get.

I will try the new declarations, and keep troubleshooting this.  :)
0
 
thomashospitalAuthor Commented:
Tried using the above code, first error I get is that it hides a variable in the enclosing block.  I changed workSheet to workSheets and get the following null references...

 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

Open in new window

excelWorkbook AND excelApplication both are giving me this error Variable is used before it has been assigned a value. A null reference exception could result at runtime
0
 
thomashospitalAuthor Commented:
Actually Famous I figured it out and you were right.  I had to go back and look at where I was making my declarations.  I had them out of order which in essence what was throwing errors.  After a deep breath and a calm mind, I got it.  THANK YOU.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.