Error in $oExcel.Cells.SpecialCells($xlCellTypeLastCell).Row

[KickStarter15]

Hi Experts,

I would like to ask for your help in solving my error with the script "$oExcel.Cells.SpecialCells($xlCellTypeLastCell).Row". I know this is not the right forum but i can't select another option in creating new topic. Anyway, what happened here is that the script is having an error in which I could resolved. I tried checking other forums but seems still having an error. Anyone please help.

I'm using old version v2.

Error:

Variable must be of type "Object".:
Global $aLastRow = $oExcel.Cells.SpecialCells($xlCellTypeLastCell).Row

[water]

How do you set $oExcel?

[KickStarter15]

Water,

Not sure if this is what you mean. I'm just a beginner in autoit. Need your support for this water. Thanks in advance.

   Local $oExcel = ObjCreate("Excel.Application")

[KickStarter15]

Water,

I have posted the script and the recent error I've got in my last attempt.

Error:

D:\Programs\Test.au3 (35) : ==> Variable must be of type "Object".:
Global $aLastRow = $oExcel.Cells.SpecialCells($xlCellTypeLastCell).Row
Global $aLastRow = $oExcel^ ERROR
>Exit code: 1    Time: 4.166

Here's the script that I have:
 

expandcollapse popup

#include <Excel.au3>
#include <File.au3>

Global Const $GUI_EVENT_CLOSE = -3
#region ### START Koda GUI section ### Form=
$Form1 = GUICreate("Test", 580, 115)
$Input1 = GUICtrlCreateInput("", 10, 30, 270, 21)
$Label1 = GUICtrlCreateLabel("Name:", 10, 10, 35, 17)
$Save = GUICtrlCreateButton("Save to excel", 450, 70, 120, 30)
GUISetState(@SW_SHOW)
#endregion ### END Koda GUI section ###

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
         Case $Save
            SaveClick()
             MsgBox(64, @ScriptName, "Record Saved.")
     EndSwitch
WEnd
Func SaveClick()
   Local $oExcel = ObjCreate("Excel.Application")
   Local $sDataFilePath, $oExcel
    IsObj($oExcel)
     $sDataFilePath = @ScriptDir & "\Test.xls"
    If FileExists($sDataFilePath) = 0 Then
       $oExcel = _ExcelBookNew($sDataFilePath)
       _ExcelBookSaveAs($oExcel, $sDataFilePath, "xls")
     FileWriteLine($sDataFilePath, GUICtrlRead($Input1))
      Global $aLastRow = $oExcel.Cells.SpecialCells($xlCellTypeLastCell).Row
   Else
     $oExcel = _ExcelBookOpen($sDataFilePath, 1)
      Global $aLastRow = $oExcel.Cells.SpecialCells($xlCellTypeLastCell).Row
 EndIf
    _ExcelWriteCell($oExcel, GUICtrlRead($Input1), $aLastRow + 1, 1)
    _ExcelBookClose($oExcel, 1, 0)
EndFunc

 

[KickStarter15]

Advise from anyone is open. I just need help.

[KickStarter15]

Ok, I think I just need it to solve it myself since everybody is busy.

[dmob]

_Excel_BookNew returns a workbook object, so your code overwrite oExcel app object with workbook object...

Check help file for correct usage of _Excel_BookNew

[KickStarter15]

Spoiler

_Excel_BookNew returns a workbook object, so your code overwrite oExcel app object with workbook object...

Check help file for correct usage of _Excel_BookNew

Thanks, dmob. I tried removing the code to open workbook directly and instead replacing it with message box for confirmation that data was already saved in the excel file, and it work properly in my PC no error found.

However, when deploying it in production (ready to use application), the program ran with the same error recurred. Production has Windows7 and Windows8 installed with around 100 pc's used.

I already tried checking help file for the usage but seems I did not find the correct coding. Would appreciate your help in modifying my code.

Very much thank you in advance.

[dmob]

What exactly are you trying to accomplish?

[KickStarter15]

Sorry for the less information dmob , I'm actually creating an interface in which production can update toolkit, send information, to create database (in way of attachment using excel file) and to input workaround for automation.

My problem now is the one that creates database which they can't use because of this error . Input data is fine, selecting options are fine as well but when they click "Save to Excel", error will prompt (see below). Others are working perfectly.

Line 2571(File "D:\Programs\Template\Standards\AIO.exe):

Error: Variable must be of type "Object".

[dmob]

Please show Scite output so we can see line with error.

What does this line do: 

FileWriteLine($sDataFilePath, GUICtrlRead($Input1))

Also insert error checking after every command to help debug.

[KickStarter15]

Error line directs here:

D:\Programs\Test.au3 (35) : ==> Variable must be of type "Object".:
Global $aLastRow = $oExcel.Cells.SpecialCells($xlCellTypeLastCell).Row
Global $aLastRow = $oExcel^ ERROR

This line is the one that reads data from $Input1 and insert text in next column. Actually it's a duplicate function with this "_ExcelWriteCell($oExcel, GUICtrlRead($Input1), $aLastRow + 1, 1)". You can ignore this one.

FileWriteLine($sDataFilePath, GUICtrlRead($Input1))

 

[dmob]

1 hour ago, KickStarter15 said:

This line is the one that reads data from $Input1 and insert text in next column. Actually it's a duplicate function with this "_ExcelWriteCell($oExcel, GUICtrlRead($Input1), $aLastRow + 1, 1)". You can ignore this one.

FileWriteLine($sDataFilePath, GUICtrlRead($Input1))

 

Almost sure that will corrupt your excel file, but I may be wrong.

 

Anyways, try this (untested):

Func SaveClick()
    Local $oExcel, $oWorkbook, $bFileExist, $iLastRow
    Local $bVisible = False ; set to True to show Excel window
    $oExcel = _Excel_Open($bVisible, False, False, False)
    If @error Then
        ConsoleWrite("! Err: " & @error & " - Failed to Create Excel Object" & @CRLF)
        Return SetError(1, 0, False)
    EndIf

    $bFileExist = FileExists($sDataFilePath)
    If $bFileExist Then
        $oWorkbook = _Excel_BookOpen($oExcel, $sDataFilePath, False, True)
    Else
        $oWorkbook = _Excel_BookNew($oExcel)
    EndIf
    If @error Then
        ConsoleWrite("! Err: " & @error & " -  Failed to " & ($bFileExist ? "Open" : "Create new") & " Excel file" & @CRLF)
        Return SetError(2, 0, False)
    EndIf

    $iLastRow = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Row
    Local $oRange = _Excel_RangeWrite($oWorkbook, Default, GUICtrlRead($Input1), "A" & $iLastRow + 1)
    ; insert error check here

    _Excel_BookSaveAs($oExcel, $sDataFilePath, $xlExcel8, True) ; $xlExcel8 = .xls format
    ; insert error check here

    Return 1
EndFunc   ;==>SaveClick

 

Edited January 2, 2017 by dmob
Code correction

[KickStarter15]

dmob, error occurred in checking your codes:

D:\Programs\Test.au3 (39) : ==> Unable to parse line.:
ConsoleWrite("! Err: " & @error & " -  Failed to " & ($bFileExist ? "Open" : "Create new") & " Excel file" & @CRLF)
ConsoleWrite("! Err: " & @error & " -  Failed to " & ($bFileExist ^ ERROR

 

When removing characters "?" and ":" below will flagged:

D:\Programs\Test.au3 (26) : ==> Unknown function name.:
$oExcel = _Excel_Open($bVisible, False, False, False)
$oExcel = ^ ERROR
>Exit code: 1    Time: 2.744

[KickStarter15]

dmob,

After checking, I replaced "_Excel_Open" to "_ExcelBookOpen" I used old UDF function but have error found:

>"D:\Programs\AutoIT Sample GUI\autoit\AutoIT\SciTe\..\autoit3.exe" /ErrorStdOut "D:\Programs\Test.au3"
! Err: 2 - Failed to Create Excel Object
>Exit code: 0    Time: 4.703

 

Thanks, much.

[KickStarter15]

 

Here's the script you gave and I only changed "_Excel_Open" to "_ExcelBookOpen".

Func SaveClick()
    Local $oExcel, $oWorkbook, $bFileExist, $iLastRow
    Local $bVisible = False
    $oExcel = _ExcelBookOpen($bVisible, False, False, False)
    $sDataFilePath = @ScriptDir & "\Test.xls"
    If @error Then
        ConsoleWrite("! Err: " & @error & " - Failed to Create Excel Object" & @CRLF)
        Return SetError(1, 0, False)
    EndIf

    $bFileExist = FileExists($sDataFilePath)
    If $bFileExist Then
        $oWorkbook = _ExcelBookOpen($oExcel, $sDataFilePath, False, True)
    Else
        $oWorkbook = _Excel_BookNew($oExcel)
    EndIf
    If @error Then
        ConsoleWrite("! Err: " & @error & " -  Failed to " & ($bFileExist) & " Excel file" & @CRLF)
        Return SetError(2, 0, False)
    EndIf

    $iLastRow = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Row
    Local $oRange = _Excel_RangeWrite($oWorkbook, Default, GUICtrlRead($Input1), "A" & $iLastRow + 1)
    _Excel_BookSaveAs($oExcel, $sDataFilePath, $xlExcel8, True)
    Return 1
EndFunc

 

[Juvigy]

Why dont you use the latest version of Autoit? Do you have #requireadmin ?

 

[KickStarter15]

Yup, I was thinking of that many times but it's hard for me in my current position right now. I was assigned in old version application for enhancement with our tool, with it they gave my v2 for autoit and I already requested to them to let have me the latest version.

Sadly, they are not replying in my request.

So, stuck on it for a while.

 

[KickStarter15]

Thank you guys for the support in looking to this post.

I figured it out and error was already fixed.

What I did is I only removed the below code and declared IsObj() in the script. Also, I've gave them the access right of the server but limited to folder specification. And all runs perfectly.

     FileWriteLine($sDataFilePath, GUICtrlRead($Input1))

Thanks, everyone for the good ideas.