?
Solved

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

Posted on 2012-09-06
9
Medium Priority
?
562 Views
Last Modified: 2012-09-10
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.
0
Comment
Question by:thomashospital
  • 5
  • 4
9 Comments
 
LVL 10

Expert Comment

by:FamousMortimer
ID: 38375900
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
 

Author Comment

by:thomashospital
ID: 38375997
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
 
LVL 10

Assisted Solution

by:FamousMortimer
FamousMortimer earned 2000 total points
ID: 38376304
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:thomashospital
ID: 38376665
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
 
LVL 10

Assisted Solution

by:FamousMortimer
FamousMortimer earned 2000 total points
ID: 38376796
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
 

Author Comment

by:thomashospital
ID: 38376814
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
 
LVL 10

Accepted Solution

by:
FamousMortimer earned 2000 total points
ID: 38376981
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
 

Author Comment

by:thomashospital
ID: 38382808
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
 

Author Comment

by:thomashospital
ID: 38383059
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question