Solved

Error message when exporting to multiple excel sheets in a work book

Posted on 2010-09-01
9
502 Views
Last Modified: 2016-11-26
Hello,

I've been working on code to export populate mutliple worksheets in a workbook with grapshs.  I've been testing it piece by piece and am getting the following error on the catch block for the OpenWorkbook subroutine
Conversion from string "System.Runtime.InteropServices.C" to type 'Integer' is not valid.

When I commented out the try catch block in order to see where the error was occuring, I saw that it was occuring on the following line  in the open workbook routine.  
ws.Name = strWorkSheetName
I then looked at the resulting error message and it said the following.  Does that mean a sheet name cannot exceed 31 characters
You typed an invalid name for a sheet or chart. Make sure that:  • The name that you type does not exceed 31 characters. • The name does not contain any of the following characters:  :  \  /  ?  *  [  or  ] • You did not leave the name blank.


Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click

        Dim dgl As New List(Of DatasetGroup)

        'Try





        For Each row As DataGridViewRow In dgvDatasetGroups.Rows

            Dim cell As DataGridViewCell = row.Cells(8)

            If CBool(cell.Value) = True Then

                Dim dg As New DatasetGroup

                Dim indexcell As DataGridViewCell = row.Cells(0)

                dg.DatasetGroupId = indexcell.Value

                cell = row.Cells(1)

                dg.Name = cell.Value

                dgl.Add(dg)

            End If



        Next

        Dim strWorkbookPath As String = Utilities.CreateWorkbook()

        MessageBox.Show(strWorkbookPath, "Returned Workbook Path")

        For Each dg As DatasetGroup In dgl

            Dim strName As String = dg.Name.ToString

            Utilities.OpenWorkbook(strWorkbookPath, strName)

        Next

        'CreateGraphTable(Convert.ToInt32(dg.DatasetGroupId))

        'Catch ex As Exception

        '   MessageBox.Show(ex.Message, ex.GetType.ToString)

        ' End Try



    End Sub



 Public Shared Function CreateWorkbook() As String

        Dim filename As String

        Dim excelApp As Excel.Application

        Dim excelWorkbook As Excel.Workbook

        Dim excelSheet As Excel.Worksheet

        Dim SaveFileDialog As New SaveFileDialog







        If My.Settings.DefaultLocation = "" Then

            SaveFileDialog.InitialDirectory = "C:\"

        Else

            SaveFileDialog.InitialDirectory = My.Settings.DefaultLocation.ToString

        End If



        SaveFileDialog.Filter = "xlsx files (*.xlsx)|*.xlsx"

        SaveFileDialog.FilterIndex = 2

        SaveFileDialog.RestoreDirectory = True



        'Try

        '***************************Creates Spreadsheet*********************************************



        If SaveFileDialog.ShowDialog = Windows.Forms.DialogResult.OK Then



            filename = SaveFileDialog.FileName



            'Validate file name

            If Convert.ToString(filename) = "" Then

                Return Nothing

                Exit Function

            End If



            'Create excel instance

            Dim misValue As Object = System.Reflection.Missing.Value

            excelApp = New Excel.ApplicationClass

            'excelApp.Visible = True



            excelWorkbook = excelApp.Workbooks.Add(misValue)

            excelSheet = excelWorkbook.Sheets("sheet1")

            excelApp.ScreenUpdating = True

            'MessageBox.Show(System.Guid.NewGuid.ToString.ToUpper)

            excelApp.Caption = System.Guid.NewGuid.ToString.ToUpper

            If Not filename.EndsWith(".xlsx") Then

                excelWorkbook.SaveAs(filename, 56)

            Else

                excelWorkbook.SaveAs(filename)

            End If



            MsgBox("File generated successfully at " & filename)



            excelWorkbook.Close()

            excelApp.Quit()

            excelWorkbook = Nothing

            excelApp = Nothing

            Return filename

        Else

            Return Nothing

        End If

        KillExcel(excelApp)

    End Function



  Public Shared Sub OpenWorkbook(ByVal strFilePath, ByVal strWorkSheetName) 'As String



        'declares the variables

        Dim excelApp As New Excel.Application

        Dim excelBook As Excel.Workbook = excelApp.Workbooks.Add(System.Reflection.Missing.Value)

        Dim ws As Excel.Worksheet = Nothing

        'try to open the workbook and a worksheet

        Try

            excelBook = excelApp.Workbooks.Open(strFilePath)

            ws = excelBook.Worksheets.Add

            excelApp.Visible = True

            ws.Name = strWorkSheetName



        Catch ex As Exception

            MsgBox(ex.Message, ex.GetType.ToString)

        Finally

            'MAKE SURE TO KILL ALL INSTANCES BEFORE QUITING! if you fail to do this

            'The service (excel.exe) will continue to run

            excelBook.Close()

            excelApp.Quit()



            excelBook = Nothing

            excelApp = Nothing



            NAR(excelApp)

            'VERY IMPORTANT

            GC.Collect()

        End Try

    End Sub

Open in new window

0
Comment
Question by:chtullu135
  • 4
  • 4
9 Comments
 
LVL 7

Accepted Solution

by:
Steve earned 500 total points
ID: 33579355
Can you find the value of strWorkSheetName with debugging? It seems as though it is not being passed or is being passed incorrectly.
0
 

Author Comment

by:chtullu135
ID: 33579398
I took another look and the maximum length of a worksheet tab is 31 characters
0
 
LVL 7

Expert Comment

by:Steve
ID: 33579837
I take it the one being passed is more than 31?
0
 

Author Comment

by:chtullu135
ID: 33580908
Yes it is so I added a test for a character length greater than 31.  If it was greater, then an imput box would appear prompting the user to enter a new name that was no larger than 31 characters.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 7

Expert Comment

by:Steve
ID: 33581192
Works now?
0
 

Author Comment

by:chtullu135
ID: 33581313
Yes it does
0
 
LVL 7

Expert Comment

by:Steve
ID: 33581433
Good, glad you figured it out.
0
 

Author Closing Comment

by:chtullu135
ID: 33581591
It was being passed incorrectly as it exceeded the maximum length for a tab name
0
 

Expert Comment

by:Khonit 2016
ID: 41902272
My troubleshooting is move or copy all sheets to new worksheets. I can create and rename sheets.  everything is OK. have fun!!!!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now