Unable to Create the Excel Object

[broliukaz]

Hi,

in task manager exe uses 6.0MB memory. 0% CPU

[trancexx]

What version of AutoIt you use?

[NewPlaza]

Do you have any leftover excel.exe process running after the crash? I would also unhide the workbook to help with possible debuging.

This question is more for the dev, does the currect build(not beta) support the new ofc2013?

[water]

Some users reported to run Offcie 2013 and automate it using AutoIt. There seemed only to be minor problems so far.

[broliukaz]

I am using AutoIt version 3.3.8.1
yes there is excel.exe process left after crash.

[broliukaz]

So then why everything is working fine when I launch program from editor window (with F5 button)?

[water]

Hard to tell!

Can you post the whole script so we can have a look?

[broliukaz]

Hard to tell!

Can you post the whole script so we can have a look?

hi,

ok, here is whole script:

expandcollapse popup

#include <GUIConstantsEx.au3>
#include <File.au3>
#include <Excel.au3>
#include <Array.au3>
#include "GUIHyperLink.au3"
#include <WinAPI.au3>
#include <ComboConstants.au3>
#include <WindowsConstants.au3>
#include <EditConstants.au3>
    Local $msg
    Global $realNumber, $ComboValue, $year2dig, $fullEQRMA, $avArray[4], $readEQID
    Global $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")
    
;~  ------------========== v8.1 ==========--------------------
Aprasymai()
Func Aprasymai(); ------------------------------ Aprasomi adresai ir failai ----------------------------------------
   Global $fVisible = 1 ; <====== xls matomas =1, nematomas =0
   Global $xlsLocation = "D:\WORK\HHT\Hardware\Repairs\" ; <====== source'as iš kur paimti failus, filename numerio skaičiavimui
   Global $xlsLocationSent = "D:\WORK\HHT\Hardware\Repairs\Sent\" 
   Global $xlsTemplate = "C:\EQ\Template.xls" ; <======  exelio formos template, atidaromas automatiniam užpildymui  
   Global $xlsDestination = "D:\WORK\HHT\Hardware\Repairs\" ; <====== užpildyto xls destination folder 
   Global $RFIrangosRemontai = "D:\WORK\HHT\Hardware\Repairs\RF_irangos_remontai (EQ).xls" ; <====== only open for enter data
   Global $dirPath = "D:\WORK\HHT\Hardware\Received\Projects\"  ; <======  Received projects direktorija
EndFunc; ------------------------------ Aprasomi adresai ir failai pabaiga----------------------------------

GUI()
Func GUI(); ---------------------------------------- GUI input/output laukai ---------------------------------
    Global $GUI = GUICreate("EQRMA Registration ...v8.1...",800,550) ; will create a dialog box that when displayed is centered
    Global $Button_1 = GUICtrlCreateButton("Register New Item", 10, 30) ; Mygtukas "Create New Form"
    Global $Button_2 = GUICtrlCreateButton("Close", 10, 80, 95, 26) ; Close mygtukas
    GUICtrlCreateLabel("      Enter Device", 120,25,150)
    Global $device = GUICtrlCreateInput("Hand Held Terminal ",120,40,150)
    GUICtrlCreateLabel("      Enter EQ ID", 120,75,150)
    Global $eqid = GUICtrlCreateInput("",120,90,150) ; EQID ivedimo laukas
    GUICtrlCreateLabel("      Enter Product number", 120,125,150)
    Global $pn =  GUICtrlCreateInput("",120,140,150) ; pn ivedimo laukas
    GUICtrlCreateLabel("      Enter Seriall number", 120,175,150)
    Global $sn = GUICtrlCreateInput("",120,190,150) ; sn ivedimo laukas
    GUICtrlCreateLabel("      Enter Accessories:", 120,230,150)
    Global $accessories = GUICtrlCreateInput("",120,245,150) ; Accessories ivedimo laukas
    GUICtrlCreateLabel("      Enter problem", 120,275,200)
    Global $problem = GUICtrlCreateInput("",120,290,150) ; problem ivedimo laukas
    GUICtrlCreateLabel("      Enter Solution", 120,320,200)
    Global $solution = GUICtrlCreateInput("",120,335,150) ; solution ivedimo laukas
    GUICtrlCreateLabel("      Enter Decission", 120,365,200)
    Global $decission = GUICtrlCreateInput("",120,380,150)   ; Decission ivedimo laukas
    GUICtrlCreateLabel("      Enter Price", 120,410,200)
    Global $price = GUICtrlCreateInput("",120,425,150)   ; Price ivedimo laukas
    GUICtrlCreateLabel("Enter notes", 120,455,200)
    Global $notes = GUICtrlCreateEdit("",20,470,350,70) ; notes ivedimo laukas
    Global $eqidLeave = GUICtrlCreateCheckbox("Leave",275,90)
    Global $pnLeave = GUICtrlCreateCheckbox("Leave",275,140)
    Global $accLeave = GUICtrlCreateCheckbox("Leave",275,245)
    GUICtrlCreateLabel("Select project:", 370,25,150) 
;~ ---------------------------------- make an combo box with dir list -------------------------------
$dirArray = _FileListToArray($dirPath , "*" , 2)
      If @Error=1 Then
         MsgBox (0,"","No Files\Folders Found.")
         Exit
      EndIf
;_ArrayDisplay($dirArray, "Directories:")              ;Show directory list
Global $ComboChoose = GUICtrlCreateCombo("Select project", 370, 40,150,0,BitOR($CBS_DROPDOWN, $WS_VSCROLL)) ; ComboBox with vertical scrollbar
$cData = ""
      For $i = 1 To Ubound($dirArray)-1
         $cData &= "|" & $dirArray[$i]
      Next
      GUICtrlSetData($ComboChoose, $cData)
;~ -------------------------------- end make an combo box with dir list --------------------------
GUICtrlCreateLabel("Write info: ", 600,25,150) 
Global $infoBox = GUICtrlCreateEdit("",530, 40, 260, 450,BitOR($ES_READONLY,$WS_VSCROLL, $WS_HSCROLL)) ; Sukuria Edit lauka ishvedamai informacijai rodyti
Global $Checkbox = GUICtrlCreateCheckbox ("Golden Service", 370, 70) ; sukuriamas CheckBox
GUICtrlSetState ( $eqid, $GUI_FOCUS ) ; set focus on input EQID
EndFunc ; ---------------------------------------- end GUI input/output laukai ---------------------------  

GUISetState() ; will display an empty dialog box
; Run the GUI until the dialog is closed
program()
Func program()
   Do
      $msg = GUIGetMsg()
      ; ---------------------------------------- Info irasymas  ---------------------------------
      If $msg = $Button_1 Then ; jei paspaustas mygtukas "register new item"
            ;------------------- ComboBox pasirinkimo identifikavimas ----------------------
            $ComboValue = ""
            $ComboValue = GUICtrlRead($ComboChoose)
            If $ComboValue = "" Then ; jei nepasirinktas projektas
               MsgBox(48,"- Error! No project Selected -","Please select project!")
            Else
               ;MsgBox(0, "", "Info: "& $ComboValue)
               ;----------------- end ComboBox pasirinkimo identifikavimas --------------------
               ReadInputFields()
               If $readEQID = "" Then
                  MsgBox(0,"Empty EQID","Please enter EQID!")  ; Tikrinam "ar EQID ne tuscias" veikia
               ElseIf $readPN = "" Then
                  MsgBox(0,"Empty PN field","Please enter Product Number!")  ; Tikrinam ar ivestas pn
                  
               ElseIf $readSN = "" Then
                  MsgBox(0,"Empty SN field","Please enter Serial Number!")  ; Tikrinam ar ivestas sn
                  
               Else
                  Global $eqidArray = StringSplit($readEQID, "-")
                  Global $ArrayElements = UBound($eqidArray)
                  If $ArrayElements = 4 Then ; tikrinam ar EQID teisingas
                     
                     SearchForFilenameNumber() ; EQRMA xls failo numerio paieshka
                     WriteToTxt() ; irashymas i txt failiuka
                     WriteToXls() ; irashymas i xls faila
                     WriteToTextBox() ; info pavaizdavimas TextBox'e
                     hyperlink() ;  hyperlink'ai
                     LaukuIstrynimas()
                  Else
                     MsgBox(0,"Wrong EQID Number","Wrong EQID number, please enter a valid number!"&@CRLF&"EQID number format: AB-CD-XX") ; tikrinam ar EQID teisingas
                  EndIf
                  
               EndIf
            EndIf
         
      EndIf 
      ; ----------------------------------------------- Info irasymo pabaiga -----------------------------------------------
      
      sleep(10) ; reducing CPU usage
   Until $msg = $GUI_EVENT_CLOSE or $msg = $Button_2 
   
     ;------------------------Close button------------------
      If $msg = $Button_2 then 
         Exit
      EndIf
     ;--------------------Close button end ------------------
     
EndFunc ;==> program


Func hyperlink() ;------------------------ hyperlink -----------------------------------------------------------------------------
   $nAutoItScript_Com_HyperLink = _GUICtrlHyperLink_Create("RF_irangos_remontai (EQ).xls", 350, 170, 110, 15, 0x0000FF, 0x551A8B, _
   -1, $RFIrangosRemontai, 'Redaguoti: RF_irangos_remontai (EQ).xls', $GUI) ;HyperLink'as
   $nAutoItScript_Com_HyperLink = _GUICtrlHyperLink_Create("txt failiukas", 380, 200, 110, 15, 0x0000FF, 0x551A8B, _
   -1, $dirPath&$ComboValue&"\"&$year2dig&@MON&@MDAY&"."&$ComboValue&".Repair.txt", 'Redaguoti: txt failą', $GUI) ;HyperLink'as
EndFunc  ;--------------------------------- hyperlink  end -----------------------------------------------------------------------

Func WriteToXls() ; ---------------------------------- Write to xls ----------------------------------------------------- 
  If GUICtrlRead($CheckBox) = $GUI_UNCHECKED Then ; <===== jei nepažymėta Golden servie, kuriam xls failą
      Local $oExcel = _ExcelBookOpen($xlsTemplate, $fVisible)
      If @error = 1 Then
         MsgBox(0, "Error!", "Unable to Create the Excel Object")
         Exit
      ElseIf @error = 2 Then
         MsgBox(0, "Error!", "Template.xls does not exist!")
         Exit
      EndIf
      _ExcelWriteCell($oExcel, $readEQID, 5, 3) ; y (eil nr) , x (raide)
      _ExcelWriteCell($oExcel, $readPN, 8, 3) 
      _ExcelWriteCell($oExcel, $readSN, 11, 3) 
      _ExcelWriteCell($oExcel, $fullEQRMA, 3, 3) 
      _ExcelWriteCell($oExcel, $readProblem , 19, 3) 
      _ExcelWriteCell($oExcel, $readSolution, 20, 3) 
      _ExcelWriteCell($oExcel, $readDecission, 21, 3) 
      _ExcelWriteCell($oExcel, $readNotes, 34, 3) 
      _ExcelWriteCell($oExcel, $readAccessories, 3, 6) 
      _ExcelWriteCell($oExcel, $readPrice, 21, 6) 
      
      if _ExcelBookSaveAs($oExcel,$xlsDestination&$fullEQRMA, "xls", 1, 1) then ; Now we save it into directory
         _ExcelBookClose($oExcel) ; And finally we close out
      Else
         MsgBox(4096, "Error", " Error Creating xls file.    error:"&@error)
      EndIf
      
      MsgBox(0,"","Duomenys įvesti į: "&@CRLF&$dirPath&$ComboValue&"\"&$year2dig&@MON&@MDAY&"."&$ComboValue&".Repair.txt"&@CRLF&$xlsDestination&$fullEQRMA&".xls"&@CRLF&"užpildykite xls: RF_irangos_remontai (EQ).xls")
      ; ---------------------------------- end Irasymas i xls ----------------------------------------------------- 
      
      ; -------------------------------- jei rashoma i xls tai create hyperlink: ------------------------------------------
      $nAutoItScript_Com_HyperLink = _GUICtrlHyperLink_Create($fullEQRMA&".xls failas", 360, 230, 110, 15, 0x0000FF, 0x551A8B, _
      -1, $xlsDestination&$fullEQRMA&".xls", 'Redaguoti: xls failą', $GUI) ;HyperLink'as
      ; -------------------------------- END jei rashoma i xls tai create hyperlink: ------------------------------------------
      
   Else
      $fullEQRMA = "-"
      MsgBox(0,"","Duomenys įvesti į: "&@CRLF&$dirPath&$ComboValue&"\"&$year2dig&@MON&@MDAY&"."&$ComboValue&".Repair.txt"&@CRLF&"užpildykite xls: RF_irangos_remontai (EQ).xls")
   EndIf
   
EndFunc ; ---------------------------------- end Write to xls ----------------------------------------------------- 

Func WriteToTextBox() ; -------------------------------- Write to textBox ------------------------------------------------
            GUICtrlSetData($infoBox, "Įvesti duomenys:"&@CRLF& _
            $readDevice&" "&$readAccessories&@CRLF& _
            "EQ ID:     "&$readEQID&@CRLF& _
            "P/N:       "&$readPN&@CRLF& _
            "S/N:       "&$readSN&@CRLF& _
            "EQRMA:     "&$fullEQRMA&@CRLF& _
            "Problem:       "&$readProblem&@CRLF& _
            "Solution:      "&$readSolution&@CRLF& _
            "Decission: "&$readDecission&@CRLF& _
            "Price:     "&$readPrice&@CRLF& _
            "Notes      "&$readNotes&@CRLF&@CRLF)
EndFunc     ; ----------------------------------- end Write to textBox -------------------------------------------

Func WriteToTxt() ; ------------------------------------ write info to txt file ----------------------------------------------------
   $year2dig= StringRight(@YEAR, 2) ; <== atrenkamas metų 2sk formatas.
   If FileWrite ($dirPath&$ComboValue&"\"&$year2dig&@MON&@MDAY&"."&$ComboValue&".Repair.txt","" & @CRLF)        Then
      FileWrite ($dirPath&$ComboValue&"\"&$year2dig&@MON&@MDAY&"."&$ComboValue&".Repair.txt",$readDevice&" "&$readAccessories & @CRLF)  
      FileWrite ($dirPath&$ComboValue&"\"&$year2dig&@MON&@MDAY&"."&$ComboValue&".Repair.txt","   EQ ID:         " &$readEQID & @CRLF)                 
      FileWrite ($dirPath&$ComboValue&"\"&$year2dig&@MON&@MDAY&"."&$ComboValue&".Repair.txt","   P/N:               "&$readPN & @CRLF)                   
      FileWrite ($dirPath&$ComboValue&"\"&$year2dig&@MON&@MDAY&"."&$ComboValue&".Repair.txt","   S/N:               "&$readSN & @CRLF)                   
         
      If GUICtrlRead($CheckBox) = $GUI_UNCHECKED Then
         FileWrite ($dirPath&$ComboValue&"\"&$year2dig&@MON&@MDAY&"."&$ComboValue&".Repair.txt","   EQRMA:          "&$fullEQRMA & @CRLF)                            
      Else
         FileWrite ($dirPath&$ComboValue&"\"&$year2dig&@MON&@MDAY&"."&$ComboValue&".Repair.txt","   EQRMA:          "& "-" &@CRLF)  
      EndIf
         FileWrite ($dirPath&$ComboValue&"\"&$year2dig&@MON&@MDAY&"."&$ComboValue&".Repair.txt","   Problem:            "&$readProblem & @CRLF)                              
         FileWrite ($dirPath&$ComboValue&"\"&$year2dig&@MON&@MDAY&"."&$ComboValue&".Repair.txt","   Solution:           "&$readSolution & @CRLF)                             
         FileWrite ($dirPath&$ComboValue&"\"&$year2dig&@MON&@MDAY&"."&$ComboValue&".Repair.txt","   Decission:          "&$readDecission & @CRLF)                            
         FileWrite ($dirPath&$ComboValue&"\"&$year2dig&@MON&@MDAY&"."&$ComboValue&".Repair.txt","   Price:          "&$readPrice & @CRLF)                            
         FileWrite ($dirPath&$ComboValue&"\"&$year2dig&@MON&@MDAY&"."&$ComboValue&".Repair.txt","   Notes           "&$readNotes & @CRLF) 
         FileWrite ($dirPath&$ComboValue&"\"&$year2dig&@MON&@MDAY&"."&$ComboValue&".Repair.txt","" & @CRLF) 
         ; MsgBox(0, "", "Duomenys įvesti: "& $dirPath&$ComboValue&"\"&$year2dig&@MON&@MDAY&"."&$ComboValue&".Repair.txt") ; display the eq id entry
   Else                 ;If Not _FileCreate("file.txt") Then
      MsgBox(4096, "Error", " Error Creating txt file.    error:"&@error)
   EndIf
EndFunc ; ------------------------------------end write info to txt file ---------------------------------------------------------

Func SearchForFilenameNumber() ; --------------------------Search for filename number -------------------------------------
   Local $xlsFileList = _FileListToArray($xlsLocation, "EQRMA*.*", 1) ;repairs dir
   $ArrayRepairs=_ArrayMax($xlsFileList, 0, 1)
   $numbR = StringSplit($ArrayRepairs,"A")
   $numbR2 = StringSplit($numbR[2],".")
;~             _ArrayDisplay($numbR2, "")              ; Repairs array display             
   Local $xlsFileList = _FileListToArray($xlsLocationSent, "EQRMA*.*", 1) ; repairs\sent dir
   $ArraySent=_ArrayMax($xlsFileList, 0, 1)
   $numbS = StringSplit($ArraySent,"A")
   $numbS2 = StringSplit($numbS[2],".")
;~             _ArrayDisplay($numbS2, "")          ; Sent array
   If $numbS2[1] > $numbR2[1] Then     ; atrenkamas kuris didesnis skaicius Repairs ar sent kataloge
      $realNumber = $numbS2[1]
      $realNumber = $realNumber + 1
   Else
      $realNumber = $numbR2[1]
      $realNumber = $realNumber + 1
   EndIf
   
   $fullEQRMA = "EQRMA000"&$realNumber
EndFunc ;-------------------------------End Search for filename number ----------------------------
   
Func LaukuIstrynimas() ;  ----------------------------- blank input fields ---------------------------------------------
   If Not($ComboValue = "") Then ; jei pasirinktas projektas
      If GUICtrlRead($eqidLeave) = $GUI_CHECKED Then ; jeigu pazhymetas "Leave EQID" tada:
         ;MsgBox(0,"","EQID Leave") ; Tikrinam ar EQID Leave checkbox veikia
         If $readEQID = "" Then
            MsgBox(0,"Empty EQID","Please enter EQID!")  ; Tikrinam "ar EQID ne tuscias" veikia
         Else ; Jeigu EQID laukas ne tushchias
;~          $eqidArray = StringSplit($readEQID, "-")
;~          $ArrayElements = UBound($eqidArray)
            ;MsgBox(0,"","Array elements: "&$ArrayElements) ; show array element count
;~          If $ArrayElements = 4 Then ; tikrinam ar EQID teisingas
               ;_ArrayDisplay($eqidArray, "if=4")      ; display array elements (must be 4)
               $eqidBegin = $eqidArray[1]&"-"&$eqidArray[2]&"-" ; padaro EQID be skaichiaus
               GUICtrlSetData($eqid,$eqidBegin)
   
;~          Else
               ;_ArrayDisplay($eqidArray, "else")  
;~             MsgBox(0,"Wrong EQID Number","Wrong EQID number, please enter a valid number!"&@CRLF&"EQID number format: AB-CD-XX") ; tikrinam ar EQID teisingas
;~          EndIf
         EndIf
      Else
         GUICtrlSetData($eqid,"")
      EndIf
      
      If GUICtrlRead($pnleave) = $GUI_UNCHECKED Then 
         GUICtrlSetData($pn,"")
      EndIf
      If GUICtrlRead($accLeave) = $GUI_UNCHECKED Then 
         GUICtrlSetData($accessories,"")
      EndIf
      GUICtrlSetData($sn,"")
      GUICtrlSetData($problem ,"")
      GUICtrlSetData($solution ,"")
      GUICtrlSetData($decission ,"")
      GUICtrlSetData($price ,"")
      GUICtrlSetData($notes ,"")
      GUICtrlSetState ( $eqid, $GUI_FOCUS ) ; set focus on input EQID
      Send("{RIGHT}")
   EndIf
EndFunc ; ----------------------------- end blank input fields ---------------------------------------------            

Func ReadinputFields() ; -------------------------------------- read input fields --------------------------------------
   Global $readDevice = GUICtrlRead($device)
   Global $readEQID = GUICtrlRead($eqid)
   Global $readPN = GUICtrlRead($pn)
   Global $readSN = GUICtrlRead($sn)
   Global $readAccessories = GUICtrlRead($accessories)
   Global $readProblem = GUICtrlRead($problem)
   Global $readSolution = GUICtrlRead($solution)
   Global $readDecission = GUICtrlRead($decission)
   Global $readPrice = GUICtrlRead($price)
   Global $readNotes = GUICtrlRead($notes)
EndFunc ; --------------------------------------------------- end read input fields --------------------------------------

Func _ErrFunc($oError)
    MsgBox(0, "COM error", "err.number is: " & @TAB & $oError.number & @CRLF & _
            "err.windescription:" & @TAB & $oError.windescription & @CRLF & _
            "err.description is: " & @TAB & $oError.description & @CRLF & _
            "err.source is: " & @TAB & $oError.source & @CRLF & _
            "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
            "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
            "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
            "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
            "err.retcode is: " & @TAB & $oError.retcode)
EndFunc   ;==>_ErrFunc

[water]

Function WriteToXls is called only once in the script.

Can you deactivate line

WriteToXls() ; irashymas i xls faila

and check if the script works?

[broliukaz]

If I deactivate this line, everything works, but doesn't write to xls file. This is the most important function for me

[water]

Now we know it is related to this function and maybe Excel.

Can you test this script on a Windows 7 machine or with Excel 2010? Just to see if this is Excel or Windows related.

[broliukaz]

I Can test it with Windows 7 32bit with Office 2013.

[broliukaz]

i tested with Windows 7 32bit and Office 2013 on other computer. It works well, there is no errors.

[water]

So it looks like to be a problem with Windows 8.

I searched the web but couldn't find an answer. Very strange.

[broliukaz]

I try to update to windows 8.1 then I will let you know the situation.

[water]

Thanks for the info.

[broliukaz]

I updated to Windows 8.1 and the problem is gone
Thank you for help!

Edited November 21, 2013 by broliukaz

[water]

Glad the problem could be solved