Excel isn't playing nice

[Anonymous]

Hello everybody,

I made a rule that extracts information from an Excel file, and it works great for everybody in our team, except for this 1 guy that uses Office 2013, whereas the rest of us use Office 2010. His Windows Task manager shows that every time he runs this rule, an additional EXCEL instance is opened and stays open. He gets 10-12 of these after a few hours. I have 1 or none at all. 

Needless to say I'm looking for a way to close his excessive Excels excellently. 

I tried using the line GoExcel.Close, to no avail. Here is the relevant code, if it can help: 

SyntaxEditor Code Snippet

' --- Trouve la cellule contenant le Materiel actif
FichierExcel = "T:\Inventor Normand\Design Data\iLogic\Mat-Actif.xls"
Dim NumMateriel as String
Dim rowPN as Integer
For rowPN = 1 To 2000
 If (GoExcel.CellValue(FichierExcel, "Feuil1", "A" & rowPN)) = NomMaterielSeul Then
 NumMateriel = GoExcel.CellValue(FichierExcel, "Feuil1", "B" & rowPN)
 iProperties.Value("Custom", "NumMateriel") = NumMateriel
 iProperties.Value("Project", "Description") = NomMaterielSeul
  'Info1 = GoExcel.CellValue("C" & rowPN)
    Exit For
  ' ElseIf NumMateriel = ""'  Exit Sub
 End If
Next

'GoExcel.Close    Commented out, but does nothing if re-inserted

If NumMateriel = Nothing Then
MessageBox.Show("Le materiel actif n'existe pas dans la librairie des materiaux." & vbLf & _
"SVP choisir un materiel dans la liste en haut.", "Erreur - iLogic"
End If

Thank you to any and all who chime in to help.

[Owner2229]

Hi, try it like this below. The "NomMaterielSeul" variable is not declared in this rule, so i assume you have it declared somewhere outside of this code, right? If not, do so.

The changes are red highlighted.

' --- Trouve la cellule contenant le Materiel actif
GoExcel.Open("T:\Inventor Normand\Design Data\iLogic\Mat-Actif.xls", "Feuil1")

Dim NumMateriel As String
Dim rowPN As Integer
For rowPN = 1 To 2000
    If GoExcel.CellValue("A" & rowPN) = NomMaterielSeul Then
        NumMateriel = GoExcel.CellValue("B" & rowPN)
        iProperties.Value("Custom", "NumMateriel") = NumMateriel
        iProperties.Value("Project", "Description") = NomMaterielSeul
        Exit For
    End If
Next

GoExcel.Save
GoExcel.Close

If NumMateriel = Nothing Then
    MessageBox.Show("Le materiel actif n'existe pas dans la librairie des materiaux." & vbLf & _
    "SVP choisir un materiel dans la liste en haut.", "Erreur - iLogic")
End If

[Anonymous]

Hi Owner2229, 

thank you for your response, but I get the error : "Server has generated an exception: (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT))"

I see what you did, but I don't see how this would avoid opening extra Excel instances. If anything, it made me open more instances of Excel as well, whereas I did not have this problem before. Weird.

I'll keep digging and playing with it a bit. I'll post any improvement I make.

[Owner2229]

Hi, try to run this first:

GoExcel.Open("T:\Inventor Normand\Design Data\iLogic\Mat-Actif.xls", "Feuil1")
GoExcel.Close

And if it runs without issues, try this one to see where is the problem:

GoExcel.Open("T:\Inventor Normand\Design Data\iLogic\Mat-Actif.xls", "Feuil1")

Try
    Dim NumMateriel As String
    Dim rowPN As Integer
    For rowPN = 1 To 2000
        If GoExcel.CellValue("A" & rowPN) = NomMaterielSeul Then
            NumMateriel = GoExcel.CellValue("B" & rowPN)
            iProperties.Value("Custom", "NumMateriel") = NumMateriel
            iProperties.Value("Project", "Description") = NomMaterielSeul
            Exit For
        End If
    Next
Catch ex As Exception
    MsgBox(ex.Message)
End Try

GoExcel.Save
GoExcel.Close

If not, try it like this:

Try
    GoExcel.Open("T:\Inventor Normand\Design Data\iLogic\Mat-Actif.xls", "Feuil1")
    GoExcel.Close
Catch ex As Exception
    MsgBox(ex.Message)
End Try

[Anonymous]

Here's what I've found: 

GoExcel.Close works perfectly.

GoExcel.Open ("Filename.xls", "SheetName") does not. 

I have to use a declared variable and use it to dig into the Excel file. For example, this works fine:

SyntaxEditor Code Snippet

FichierExcel = "T:\Inventor Normand\Design Data\iLogic\Mat-Actif.xls"
GoExcel.CellValue(FichierExcel, "Feuil1", "A" & rowPN) = NomMaterielSeul

This does not:

SyntaxEditor Code Snippet

GoExcel.Open("T:\Inventor Normand\Design Data\iLogic\Mat-Actif.xls", "Feuil1")
GoExcel.CellValue("A" & rowPN) = NomMaterielSeul

I keep getting the server error as described in a previous post. Apparently this has to do with how Excel 2013 deals with multiple instances of itself (MDI) vs the  2010 version (SDI). I'm waiting a call from a service tech who should tell me how to make Excel 2013 act like it's 2010. 

The soggy saga continues....

[Owner2229]

Alright then, can you try this one instead? It might help as it is ussing a bit different approach to the document.

excelApp = CreateObject("Excel.Application")
excelApp.Visible = False
excelApp.DisplayAlerts = False
excelWorkbook = excelApp.Workbooks.Open("T:\Inventor Normand\Design Data\iLogic\Mat-Actif.xls")
excelWorkbook.Save
excelWorkbook.Close

Here is how to use it to write data.

Remember that in this method is row first and column second. And you don't reffer to columns with letters, but with numbers (their possition). So, (5, 4) is "D5"

Dim oExcel As Object = CreateObject("Excel.Application")
oExcel.Visible = False
oExcel.DisplayAlerts = False
Dim oWB As Object = oExcel.Workbooks.Open("T:\Inventor Normand\Design Data\iLogic\Mat-Actif.xls")
Dim oWS As Object = oWB.Sheets(1)
oWS.Cells(5, 4).Value = "some text"
oWS.Cells(7, 4).Value = "more text"
oWS.Cells(8, 4).Value = "even more text?"
oWB.Save
oWB.Close (True)
oExcel.Quit