Solved

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

Posted on 2010-09-01
9
550 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

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…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
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 will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

828 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