VB.Net Excel Exception

zoidi used Ask the Experts™
Hi Experts,

I'm getting some exception when I try to automate Excel from VB.Net. I tried it first on my machine (Windows 7 English) and then on another machine (Windows Server 2003 French). On the server machine it didn't work. I'll attach the error log in a text file. Does anybody have an idea what's wrong? By the way, on the Windows 7 machine it worked just fine. Here is my code:
Private Sub procExporterVersExcel(ByVal intDepart As Integer, ByVal intEnregistrement As Integer)
        'D'abord on défine les composant Excel
        Dim exAppExporter As Excel.Application = New Excel.Application
        'Puis on ajoute un workbook dans le fichier
        'On écrit le titre de la statistique au worksheet et on l'aligne
        exAppExporter.Cells(2, 2) = cmbStatistique.Text
        exAppExporter.ActiveSheet.Name = "Statistique"
        exAppExporter.Cells(2, 2).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
        exAppExporter.Cells(2, 2).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
        'Formattage du titre
        With exAppExporter.ActiveSheet.Range(exAppExporter.ActiveSheet.Cells(2, 2), exAppExporter.ActiveSheet.Cells(3, dgvResultat.ColumnCount + 1))
            'On "merge" les cellules
            .Font.Bold = True
            .Font.Size = 16
            .Interior.Color = System.Drawing.ColorTranslator.ToWin32(Color.DarkGray)
            'On met une bordure
            .BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)
        End With
        'On écrit les "ColumnHeader" du DataGridView dans le fichier Excel et on met un formattage approprié
        For intColTitre As Integer = 0 To dgvResultat.ColumnCount - 1
            With exAppExporter.Cells(4, intColTitre + 2)
                .Value = dgvResultat.Columns(intColTitre).Name
                .Font.Bold = True
                .Interior.Color = System.Drawing.ColorTranslator.ToWin32(Color.LightGray)
                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)
            End With
        'Maintenant on peut remplir le tableau avec les données de la statistique affichées dans le DataGridView
        For i As Integer = 0 To intEnregistrement - 1 'Index pour les lignes 
            For j As Integer = 0 To dgvResultat.ColumnCount - 1 'Index pour les colonnes
                exAppExporter.Cells(i + 5, j + 2) = dgvResultat.Rows(i + intDepart).Cells(j).Value.ToString '(On commence dans la ligne 5 et dans la colonne 2)
                exAppExporter.Cells(i + 5, j + 2).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)
        'On met une bordure autour du tableau
        With exAppExporter.ActiveSheet.Range(exAppExporter.ActiveSheet.Cells(4, 2), exAppExporter.ActiveSheet.Cells(intEnregistrement + 4, dgvResultat.ColumnCount + 1))
            .BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic)
        End With
        'On supprime les sheet non-utilisés
        'Rendre le mode d'affichage d'Excel sur maximisé et rendre le visible
        exAppExporter.WindowState = Excel.XlWindowState.xlMaximized
        exAppExporter.Visible = True
        exAppExporter = Nothing
    End Sub

Open in new window

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Must be a difference in the Excel installation on the failing computer.

In particular, I suspect it's the default # worksheets for a new workbook.

Rather than assuming it's a certain #, after creating the new workbook, I suggest you determine if there are the correct # worksheets in it and if not, add any missing worksheets before trying to access them.
Why don't you debug your code before building the executable, that way you will know exactly what line is giving you problems.

I think ToddBeaulieu is on the right track here. Maybe it has to do with the number of default sheets on a workbook. As a user of Excel, you can change this, so it makes your code less robust if you assume Sheet(2) and (3) exist on the target computer, much less to issue a delete command. In any case, you should be using Try...Catch... statements, to try and handle the errors on your code.

Do these links help?





Thank you for your answers, I think this might be the solution. I'll try this out, but I can't report today. Will tell you on Monday if it worked.



Hi Experts,

It's exactly what you said. It is because of the default number of sheets. I did it now with a loop.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial