Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 641
  • Last Modified:

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

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
chtullu135
Asked:
chtullu135
  • 4
  • 4
1 Solution
 
SteveCommented:
Can you find the value of strWorkSheetName with debugging? It seems as though it is not being passed or is being passed incorrectly.
0
 
chtullu135Author Commented:
I took another look and the maximum length of a worksheet tab is 31 characters
0
 
SteveCommented:
I take it the one being passed is more than 31?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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.
0
 
SteveCommented:
Works now?
0
 
chtullu135Author Commented:
Yes it does
0
 
SteveCommented:
Good, glad you figured it out.
0
 
chtullu135Author Commented:
It was being passed incorrectly as it exceeded the maximum length for a tab name
0
 
Khonit 2016Commented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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