Autoit Excel "Requested Object wasnt..."

[xamxxl]

Hello,

im writing a short script for my workplace in germany. I attached 1 USB botton with the commands " Media Next " and " Media Prev ". 

My Script should do some tasks in excel after his , but somehow i will get the same error after 2 or 3 hours  "the requested object wasnt found". 

Do you know how i could fix this ? 

Im using 1 intel atom pc with 4 gb ram and win 10 64bit. Right now i switched to another pc with win 7, intel i5 and 8gb ram. 

Ty for every answer ! 

expandcollapse popup

#include <excel.au3>
#include <File.au3>
#include <Array.au3>
#include <Date.au3>
#include <MsgBoxConstants.au3>
#include <StringConstants.au3>
#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#include <AutoItConstants.au3>
#include <StaticConstants.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#Include <GuiButton.au3>

Global $Counter
$Laufzeitstart = TimerInit()
$oExcel = _Excel_Open()
$oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Infocenter-Fahrerbildschirm.xlsm")
$var =  "C:\Users\Max\Documents\Arbeit\Test.xlsx)"
WinActivate("[CLASS:EXCEL]")

HotKeySet("{MEDIA_NEXT}", "Vor")
HotKeySet("{MEDIA_PREV}", "Zuruck")
;HotKeySet("{F3}", "Tour1")
;HotKeySet("{F4}", "Tour2")
;HotKeySet("{F5}", "Tour3")
;HotKeySet("{F6}", "Tour4")
;HotKeySet("{F7}", "Tour5")
HotKeySet("{ESC}", "Terminate")

Func Vor()
   WinActivate("[CLASS:EXCEL]")
   HotKeySet("{MEDIA_NEXT}")
   _FileWriteLog (@ScriptDir & "\Log.log", $Min & " Media Next")
   if $Counter = 4 Then

_FileWriteLog (@ScriptDir & "\Log.log", $Min & " Morgenexpress")
ToolTip ( "Aktiv: Morgenexpress " & $Min , 0, 0 )
;$oExcel = _Excel_Open()
$oExcel.Sheets("Kunden pro Tour").Select
Sleep(200)
$oExcel.ActiveSheet.Range("B2").Select
Sleep(200)
Send ("1 Morgen-Express")
Send("{Enter}")
Sleep (200)
$oExcel.Sheets("Fahrerinfo2").Select
$oExcel.ActiveSheet.Range("B2").Select
Send ("1 Morgen-Express")
Send("{Enter}")
Sleep (200)
$oExcel.ActiveSheet.Range("K2").Select
Send ("1 Morgen-Express")
Send("{Enter}")
Sleep (200)
$Counter = 0

ElseIf $Counter = 3 Then

_FileWriteLog (@ScriptDir & "\Log.log", $Min & " Abholung")
ToolTip ( "Aktiv: Abholung"& $Min , 0, 0 )
$Counter += 1
;$oExcel = _Excel_Open()
$oExcel.Sheets("Kunden pro Tour").Select
Sleep(200)
$oExcel.ActiveSheet.Range("B2").Select
Sleep(200)
Send ("5 Abholung")
Send("{Enter}")
Sleep (200)
$oExcel.Sheets("Fahrerinfo2").Select
$oExcel.ActiveSheet.Range("B2").Select
Send ("5 Abholung")
Send("{Enter}")
Sleep (200)
$oExcel.ActiveSheet.Range("K2").Select
Send ("5 Abholung")
Send("{Enter}")
Sleep (200)

ElseIf $Counter = 2 Then

_FileWriteLog (@ScriptDir & "\Log.log", $Min & " Mittagstour")
ToolTip ( "Aktiv: Mittagstour" & $Min, 0, 0 )
$Counter += 1
;$oExcel = _Excel_Open()
$oExcel.Sheets("Kunden pro Tour").Select
Sleep(200)
$oExcel.ActiveSheet.Range("B2").Select
Sleep(200)
Send ("4 Mittagstour")
Send("{Enter}")
Sleep (200)
$oExcel.Sheets("Fahrerinfo2").Select
$oExcel.ActiveSheet.Range("B2").Select
Send ("4 Mittagstour")
Send("{Enter}")
Sleep (200)
$oExcel.ActiveSheet.Range("K2").Select
Send ("4 Mittagstour")
Send("{Enter}")
Sleep (200)

ElseIf $Counter = 1 Then

_FileWriteLog (@ScriptDir & "\Log.log", $Min & " 11 Uhr Tour")
ToolTip ( "Aktiv: 11 Uhr-Tour" & $Min, 0, 0 )
$Counter += 1
;$oExcel = _Excel_Open()
$oExcel.Sheets("Kunden pro Tour").Select
Sleep(200)
$oExcel.ActiveSheet.Range("B2").Select
Sleep(200)
Send ("3 11Uhr-Tour")
Send("{Enter}")
Sleep (200)
$oExcel.Sheets("Fahrerinfo2").Select
$oExcel.ActiveSheet.Range("B2").Select
Send ("3 11Uhr-Tour")
Send("{Enter}")
Sleep (200)
$oExcel.ActiveSheet.Range("K2").Select
Send ("3 11Uhr-Tour")
Send("{Enter}")
Sleep (200)

ElseIf $Counter = 0 Then

_FileWriteLog (@ScriptDir & "\Log.log", $Min & " Frühtour")
ToolTip ( "Aktiv: Frühtour" , 0, 0 )
$Counter += 1
;$oExcel = _Excel_Open()
Sleep(200)
$oExcel.Sheets("Kunden pro Tour").Select
Sleep(200)
$oExcel.ActiveSheet.Range("B2").Select
Sleep(200)
Send ("2 Frühtour")
Send("{Enter}")
Sleep (200)
$oExcel.Sheets("Fahrerinfo2").Select
$oExcel.ActiveSheet.Range("B2").Select
Send ("2 Frühtour")
Send("{Enter}")
Sleep (200)
$oExcel.ActiveSheet.Range("K2").Select
Send ("2 Frühtour")
Send("{Enter}")
Sleep (200)

EndIf

HotKeySet("{MEDIA_NEXT}", "Vor")

EndFunc

Func Zuruck()

   _FileWriteLog (@ScriptDir & "\Log.log", $Min & " Media zurück")
   ;WinActivate("[CLASS:EXCEL]")
   HotKeySet("{MEDIA_PREV}")

if $Counter = 1 Then

_FileWriteLog (@ScriptDir & "\Log.log", $Min & " Morgenexpress")
ToolTip ( "Aktiv: Morgenexpress" , 0, 0 )
;$oExcel = _Excel_Open()
$oExcel.Sheets("Kunden pro Tour").Select
Sleep(200)
$oExcel.ActiveSheet.Range("B2").Select
Sleep(200)
Send ("1 Morgen-Express")
Send("{Enter}")
Sleep (200)
$oExcel.Sheets("Fahrerinfo2").Select
$oExcel.ActiveSheet.Range("B2").Select
Send ("1 Morgen-Express")
Send("{Enter}")
Sleep (200)
$oExcel.ActiveSheet.Range("K2").Select
Send ("1 Morgen-Express")
Send("{Enter}")
Sleep (200)
$Counter -= 1

ElseIf $Counter = 4 Then

_FileWriteLog (@ScriptDir & "\Log.log", $Min & " Mittagstour")
ToolTip ( "Aktiv: Mittagstour" , 0, 0 )
;$oExcel = _Excel_Open()
$oExcel.Sheets("Kunden pro Tour").Select
Sleep(200)
$oExcel.ActiveSheet.Range("B2").Select
Sleep(200)
Send ("4 Mittagstour")
Send("{Enter}")
Sleep (200)
$oExcel.Sheets("Fahrerinfo2").Select
$oExcel.ActiveSheet.Range("B2").Select
Send ("4 Mittagstour")
Send("{Enter}")
Sleep (200)
$oExcel.ActiveSheet.Range("K2").Select
Send ("4 Mittagstour")
Send("{Enter}")
Sleep (200)
$Counter -= 1

ElseIf $Counter = 3 Then

_FileWriteLog (@ScriptDir & "\Log.log", $Min & " 11 Uhr Tour")
ToolTip ( "Aktiv: 11 Uhr Tour" , 0, 0 )
;$oExcel = _Excel_Open()
$oExcel.Sheets("Kunden pro Tour").Select
Sleep(200)
$oExcel.ActiveSheet.Range("B2").Select
Sleep(200)
Send ("3 11Uhr-Tour")
Send("{Enter}")
Sleep (200)
$oExcel.Sheets("Fahrerinfo2").Select
$oExcel.ActiveSheet.Range("B2").Select
Send ("3 11Uhr-Tour")
Send("{Enter}")
Sleep (200)
$oExcel.ActiveSheet.Range("K2").Select
Send ("3 11Uhr-Tour")
Send("{Enter}")
Sleep (200)
$Counter -= 1

ElseIf $Counter = 2 Then

_FileWriteLog (@ScriptDir & "\Log.log", $Min & " Frühtour")
ToolTip ( "Aktiv: Frühtour" , 0, 0 )
;$oExcel = _Excel_Open()
$oExcel.Sheets("Kunden pro Tour").Select
Sleep(200)
$oExcel.ActiveSheet.Range("B2").Select
Sleep(200)
Send ("2 Frühtour")
Send("{Enter}")
Sleep (200)
$oExcel.Sheets("Fahrerinfo2").Select
$oExcel.ActiveSheet.Range("B2").Select
Send ("2 Frühtour")
Send("{Enter}")
Sleep (200)
$oExcel.ActiveSheet.Range("K2").Select
Send ("2 Frühtour")
Send("{Enter}")
Sleep (200)
$Counter -= 1

EndIf

HotKeySet("{MEDIA_PREV}", "Zuruck")
EndFunc


;1 Morgenexpress
Func Tour1()
;$oExcel = _Excel_Open()
$oExcel.Sheets("Kunden pro Tour").Select
Sleep(200)
$oExcel.ActiveSheet.Range("B2").Select
Sleep(200)
Send ("1 Morgen-Express")
Send("{Enter}")
Sleep (200)
$oExcel.Sheets("Fahrerinfo2").Select
$oExcel.ActiveSheet.Range("B2").Select
Send ("1 Morgen-Express")
Send("{Enter}")
Sleep (200)
$oExcel.ActiveSheet.Range("K2").Select
Send ("1 Morgen-Express")
Send("{Enter}")
Sleep (200)
EndFunc


;2 Frühtour
Func Tour2()
;$oExcel = _Excel_Open()
$oExcel.Sheets("Kunden pro Tour").Select
Sleep(200)
$oExcel.ActiveSheet.Range("B2").Select
Sleep(200)
Send ("2 Frühtour")
Send("{Enter}")
Sleep (200)
$oExcel.Sheets("Fahrerinfo2").Select
$oExcel.ActiveSheet.Range("B2").Select
Send ("2 Frühtour")
Send("{Enter}")
Sleep (200)
$oExcel.ActiveSheet.Range("K2").Select
Send ("2 Frühtour")
Send("{Enter}")
Sleep (200)
EndFunc

;3 11Uhr-Tour
Func Tour3()
;$oExcel = _Excel_Open()
$oExcel.Sheets("Kunden pro Tour").Select
Sleep(200)
$oExcel.ActiveSheet.Range("B2").Select
Sleep(200)
Send ("11Uhr-Tour")
Send("{Enter}")
Sleep (200)
$oExcel.Sheets("Fahrerinfo2").Select
$oExcel.ActiveSheet.Range("B2").Select
Send ("11Uhr-Tour")
Send("{Enter}")
Sleep (200)
$oExcel.ActiveSheet.Range("K2").Select
Send ("11Uhr-Tour")
Send("{Enter}")
Sleep (200)
EndFunc

;4 Mittagstour
Func Tour4()
$oExcel = _Excel_Open()
$oExcel.Sheets("Kunden pro Tour").Select
Sleep(200)
$oExcel.ActiveSheet.Range("B2").Select
Sleep(200)
Send ("4 Mittagstour")
Send("{Enter}")
Sleep (200)
$oExcel.Sheets("Fahrerinfo2").Select
$oExcel.ActiveSheet.Range("B2").Select
Send ("4 Mittagstour")
Send("{Enter}")
Sleep (200)
$oExcel.ActiveSheet.Range("K2").Select
Send ("4 Mittagstour")
Send("{Enter}")
Sleep (200)
EndFunc

;5 Abholung
Func Tour5()
$oExcel = _Excel_Open()
$oExcel.Sheets("Kunden pro Tour").Select
Sleep(200)
$oExcel.ActiveSheet.Range("B2").Select
Sleep(200)
Send ("5 Abholung")
Send("{Enter}")
Sleep (200)
$oExcel.Sheets("Fahrerinfo2").Select
$oExcel.ActiveSheet.Range("B2").Select
Send ("5 Abholung")
Send("{Enter}")
Sleep (200)
$oExcel.ActiveSheet.Range("K2").Select
Send ("5 Abholung")
Send("{Enter}")
Sleep (200)
EndFunc

Func Terminate()
    Exit
 EndFunc

While 1
 If @error Then

    _FileWriteLog (@ScriptDir & "\Log.log", $Min & " Error")

   Else
   $Min = Round(TimerDiff($Laufzeitstart)/1000,2)
   ;$Min = $Laufzeit / 10^5
   $nMsg = GUIGetMsg()
  ; _FileWriteLog (@ScriptDir & "\Log.log", $Min)
   ;Sleep (120000)
  ; If @error Then
    ;   _FileWriteLog (@ScriptDir & "\Log.log", "Fehler" & $Min)
   EndIf
Switch $nMsg

Case $GUI_EVENT_CLOSE
   _FileWriteLog (@ScriptDir & "\Log.log", $Min & " Geschlossen")
   Exit

EndSwitch
WEnd

 

[Nine]

Is it always the same line or do you get this error randomly situated ?  If it is the same line, which one is it ?

Have tried to run it x64 ?  Cause I have a similar problem with one of my script and it solves the issue.

[xamxxl]

I tried 32 and 64, but it always crashes after 3 hours.. could a error handler fix this  ( repeat the func. after @error ) ? 

The error appeares everytime for §oExcel :s 

[Nine]

3 minutes ago, xamxxl said:

could a error handler fix this

Probably not.  When the object is lost, re-executing the same line won't solve the issue.

You could try recreating the object every so often after some number of iterations.

Have you check for memory leaks ?

[Nine]

I see you have multiple _Excel_Open but no Excel_Close, could you try adding it ?

[xamxxl]

the mem process  had always the same ID , but the script crashed serveral times with the same error ..

i tried to use a custom excel.udf to compile, but its always the same result ..
 

[water]

You are mixing a few very different techniques in your script.

1. _Excel* functions use COM to communicate with Excel
2. Win* functions work with the Excel GUI
3. You are using Excel's Select method. This automates the Excel GUI as well
4. Send etc. sends keystrokes to the active window
5. Your script is slowed down by the Sleep statements

#4 is prone to errors because of user interaction.

I suggest to do everything with the _Excel* functions. If none is available then you could use pure COM in additon to work with Excel.
First step would be to grab one of the steps you do (Counter = 1, 2, 3 or 4) and explain what you want to do so we can help you to replace it with COM.

Gerne zusätzlich auch in Deutsch

[xamxxl]

ty for the replies, 

8 hours ago, Nine said:

I see you have multiple _Excel_Open but no Excel_Close, could you try adding it ?

the sheet is a overview about the articles that has to be delievered to different customers. Its refreshing the actual orders every 30 seconds from our database. But it has to be always opened .. 

5 hours ago, water said:

You are mixing a few very different techniques in your script.

1. _Excel* functions use COM to communicate with Excel
2. Win* functions work with the Excel GUI
3. You are using Excel's Select method. This automates the Excel GUI as well
4. Send etc. sends keystrokes to the active window
5. Your script is slowed down by the Sleep statements

#4 is prone to errors because of user interaction.

I suggest to do everything with the _Excel* functions. If none is available then you could use pure COM in additon to work with Excel.
First step would be to grab one of the steps you do (Counter = 1, 2, 3 or 4) and explain what you want to do so we can help you to replace it with COM.

Gerne zusätzlich auch in Deutsch

The PC is connected to one button. It is just allowing 2 commands to set the sheet to different delievery tours. The counter is for going from "morning tour" to the last tour. The sleep statement is for the excel refresh, because it takes some seconds. But the script does his job for 2 or 3 hours, mostly after this time the error appears.. 

So i should change the excel.select command ?  Im sending the keystrokes to select 1/5 options in 1 pivot table in excel.

Thank you so far! 

 

Und einmal auf Deutsch  : 
Wir haben im Lager einen großen Bildschirm aufgestellt. Daran ist der PC angeschlossen um unseren Fahren zu zeigen, wie viele Kunden sie wo anfahren müssen und welche Artikel aus welchem Lager noch mit müssen. 

Damit das umstellen möglichst einfach ist habe ich einen USB Button angeschlossen. Dieser sendet nur 2 Befehle um das Script zu aktivieren.  Leider kommt es da immer nach der selben Zeit zum genannten Error. Um die richtigen Pivot Tabellen auszuwählen nutze ich select. 

MfG Max

[xamxxl]

Any ideas ?

[Nine]

Well we are stabbing in the dark here.  If you would like us to help you, you will need to help us too.  We gave you multiple suggestions, you could apply them.  If it is still crashing, one thing you could do is make a small script replicate of the problem you are facing.  A replicate that we can run ourselves with an actual .xls.

Edited November 25, 2019 by Nine

[water]

The line number where the error occurs would help