Improve company productivity with a Business Account.Sign Up

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 680
  • Last Modified:

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


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)

        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
            End If

        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)
        '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:\"
            SaveFileDialog.InitialDirectory = My.Settings.DefaultLocation.ToString
        End If

        SaveFileDialog.Filter = "xlsx files (*.xlsx)|*.xlsx"
        SaveFileDialog.FilterIndex = 2
        SaveFileDialog.RestoreDirectory = True

        '***************************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
            excelApp.Caption = System.Guid.NewGuid.ToString.ToUpper
            If Not filename.EndsWith(".xlsx") Then
                excelWorkbook.SaveAs(filename, 56)
            End If

            MsgBox("File generated successfully at " & filename)

            excelWorkbook = Nothing
            excelApp = Nothing
            Return filename
            Return Nothing
        End If
    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
            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)
            'MAKE SURE TO KILL ALL INSTANCES BEFORE QUITING! if you fail to do this
            'The service (excel.exe) will continue to run

            excelBook = Nothing
            excelApp = Nothing

            'VERY IMPORTANT
        End Try
    End Sub

Open in new window

  • 4
  • 4
1 Solution
Can you find the value of strWorkSheetName with debugging? It seems as though it is not being passed or is being passed incorrectly.
chtullu135Author Commented:
I took another look and the maximum length of a worksheet tab is 31 characters
I take it the one being passed is more than 31?
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

chtullu135Author Commented:
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.
Works now?
chtullu135Author Commented:
Yes it does
Good, glad you figured it out.
chtullu135Author Commented:
It was being passed incorrectly as it exceeded the maximum length for a tab name
Khonit 2016Commented:
My troubleshooting is move or copy all sheets to new worksheets. I can create and rename sheets.  everything is OK. have fun!!!!
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.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now