VB.Net Excel Exception

zoidi
zoidi used Ask the Experts™
on
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
        exAppExporter.Workbooks.Add()
        '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
            .Cells.Merge()
            .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
        Next
        '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)
            Next
        Next
        '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)
            .Columns("A:Z").AutoFit()
        End With
        'On supprime les sheet non-utilisés
        exAppExporter.Sheets(3).Delete()
        exAppExporter.Sheets(2).Delete()
        'Rendre le mode d'affichage d'Excel sur maximisé et rendre le visible
        exAppExporter.WindowState = Excel.XlWindowState.xlMaximized
        exAppExporter.Visible = True
        ReleaseComObject(exAppExporter.Workbooks)
        ReleaseComObject(exAppExporter)
        exAppExporter = Nothing
    End Sub

Open in new window

errorlog.txt
Comment
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?

http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_22459541.html
http://www.xtremevbtalk.com/showthread.php?t=300227

NY

Author

Commented:
Hi,

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.

Regards,
Zoidi

Author

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