KickStarter15

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

19 posts in this topic

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

Share this post


Link to post
Share on other sites



How do you set $oExcel?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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")

Share this post


Link to post
Share on other sites

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:
 

#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

 

Share this post


Link to post
Share on other sites

Advise from anyone is open. I just need help. :sweating:

Share this post


Link to post
Share on other sites

#6 ·  Posted

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

Share this post


Link to post
Share on other sites

#7 ·  Posted

_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

Share this post


Link to post
Share on other sites

#8 ·  Posted

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.

Share this post


Link to post
Share on other sites

#9 ·  Posted

What exactly are you trying to accomplish?

Share this post


Link to post
Share on other sites

#10 ·  Posted

Sorry for the less information dmob :D, 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".

Share this post


Link to post
Share on other sites

#11 ·  Posted

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.

Share this post


Link to post
Share on other sites

#12 ·  Posted

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))

 

Share this post


Link to post
Share on other sites

#13 ·  Posted (edited)

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 by dmob
Code correction
1 person likes this

Share this post


Link to post
Share on other sites

#14 ·  Posted

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

Share this post


Link to post
Share on other sites

#15 ·  Posted

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.

Share this post


Link to post
Share on other sites

#16 ·  Posted

 

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

 

Share this post


Link to post
Share on other sites

#17 ·  Posted

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

 

Share this post


Link to post
Share on other sites

#18 ·  Posted

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.:D

 

Share this post


Link to post
Share on other sites

#19 ·  Posted

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

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.

1 person likes this

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now