Solved

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

Posted on 2010-09-01
9
585 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

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…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

635 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