Solved

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

Posted on 2010-09-01
9
491 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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
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…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

707 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

10 Experts available now in Live!

Get 1:1 Help Now