_Excel_RangeWrite - Worksheet object? - (Moved)

[rawmud]

Hi

 

I am writing a script to get autoit to populate an excel workbook with functions.  I receive an error when it comes to the _Excel_RangeWrite function.  It tells me that the worksheet object is wrong or the sheet name is wrong and the com error code from Microsoft indicates that it is an incorrect function.  I've tried various iterations and can't get it to work.  Would you have any idea as to what I need to get it to work:

Code:

_Excel_RangeWrite($oWorkbook,"x", ClipGet(),$xc,TRUE)

-"x" is where it fails

Error: @error = 2 ; @extended = 1

 

@error:

https://www.autoitscript.com/autoit3/docs/libfunctions/_Excel_RangeWrite.htm

@extended:

https://docs.microsoft.com/en-us/windows/win32/debug/system-error-codes--0-499-

 

Examples attempted:

-I have tried to set the worksheet name into a variable and load it that way

 

#1

_Excel_RangeWrite($oWorkbook,$oWorkbook.Activatesheet, ClipGet(),$xc,TRUE)

#2

Local $vWorksheet = $oWorkbook.Sheets("Data Transfer")

_Excel_RangeWrite($oWorkbook,$oWorkbook.Activatesheet, ClipGet(),$xc,TRUE)

#3

Local $vWorksheet ="Data Transfer"

_Excel_RangeWrite($oWorkbook,$oWorkbook.Activatesheet, ClipGet(),$xc,TRUE)

 

[Melba23]

Moved to the appropriate forum.

Moderation Team

[Nine]

There is a typo in your test it should be :

_Excel_RangeWrite($oWorkbook,$oWorkbook.Activesheet, ClipGet(),$xc,TRUE)

or (assuming sheet #1)

_Excel_RangeWrite($oWorkbook,1, ClipGet(),$xc,TRUE)

or (assuming name is right)

_Excel_RangeWrite($oWorkbook,"Data Transfer", ClipGet(),$xc,TRUE)

 

[rawmud]

So I had tried all three versions after fixing the typo and I still receive the same error.  It looks like the Excel functions were changed recently and I think the worksheet reference does not work correctly?  Earlier this week, it worked fine - it wasn't until Thursday that it started to go down.

 

Below is a screenshot - typo corrected and sheet name is correct

 

 

 

 

[Nine]

Upload the file, I'll check it out.

[water]

Which version of Excel do you run?
Which version of AutoIt do you run?
How do you create the workbook (use _Excel_BookOpen, _Excel_BookNew, _Excel_BookAttach)?

[rawmud]

Microsoft Office Home & Student 2019

AutoIT:  3.3.14.5

I am opening up an existing worksheet.  I take values in a text file and copy it over to an excel spreadsheet.  I had updated my windows office mid-week, that may be why it is being thrown off.

excel open(), then excel book open

below is the code that I am using.

 

Thanks for the help!

 

#include <AutoItConstants.au3>
#include <WinAPI.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>
#include <Date.au3>
#include <String.au3>

;;EXIT OPTION
HotKeySet("{ESC}", "Terminate")
Func Terminate()
    Exit
 EndFunc   ;==>Terminate

Run("NOTEPAD_FILE")

Local $var = "EXCEL_FILE_LOCATION"
Local $oExcel_1= _Excel_Open()
Local $oWorkbook=_Excel_BookOpen($oExcel_1,$var)

ClipPut("")

WinSetState("NOTEPAD FILE", "", @SW_MAXIMIZE)
WinActivate ("EXCEL FILE")
WinSetState("EXCEL FILE","",@SW_RESTORE)
WinSetState("EXCEL FILE", "", @SW_MAXIMIZE)

For $i = 1 to 27 Step 1

IF $i <15 Then
   $h1 = 30+ 109*($i-1)  ;50, 155, 210, 265,320, 375,430,485,540,595,650,705,760,815,870,925,980
   ;Horizontal spacing between hourly cells: 0    109.43    218.86    328.29    437.72    547.15    656.58    766.01    875.44    984.87    1094.3    1203.73    1313.16    1422.59

   $v1 = 182
   $v2 = 497
   $v3 = 474
   $h2 = $h1+45
   $h3 = $h1+55
Else
   $h1 = 50+ 105*($i-15)
   $v1 = 660
   $v2 = 775
   $v3 = 475
   $h2 = $h1+45
   $h3 = $h1+55
endif

  MouseClick($MOUSE_CLICK_RIGHT, $h1, $v1, 1,25) ;RIGHT cLICK
   MouseClick($MOUSE_CLICK_LEFT, $h2, $v2, 1,25) ; LEFT CLICK - COPY

   $letter = $i+64
   $xc = Chr($letter)&"5"

if $i = 27 Then
   $xc = "AA5"
endif

WinActivate ("EXCEL_FILE")

_Excel_RangeWrite($oWorkbook,"SHEET1", ClipGet(),$xc,TRUE)
;_Excel_RangeWrite($oWorkbook,$oWorkbook.Activesheet, ClipGet(),$xc,TRUE)
;_Excel_RangeWrite($oWorkbook,3, ClipGet(),$xc,TRUE)

If @error Then MsgBox(0, "Range Write Error", "@error = " & @error & ", @extended = " & @extended)

ClipPut("")

next

[JoHanatCent]

Can you provide an example of your notepad files?

I will be a lot easier to use FileReadToArray

and the write to the exel file?

[water]

Please check @error and @extended after each call to an _Excel_* function.
Example:

Local $oExcel_1= _Excel_Open()
ConsoleWrite("_Excel_Open: @error=" & @error & ", @extended=" & @extended & @CRLF)

 

Edited April 20, 2020 by water

[rawmud]

I thought it'd be easier to understand with an notepad file.  I can't figure why it wouldn't work, I guess it's something deeper.  I am actually trying to generate a excel report from watch lists in TC2000 - a stock software.  I open up the TC2000, copy symbols from a watch list and paste them into excel.  Except the past function into excel stopped working.  I was going to then run an excel macro to do some data manipulation.  So maybe because it is an xlsm sheet, that's why? Except that it worked earlier in the week and stopped working after I updated to 2019?

 

BELOW IS THE ACTUAL CODE.  I also attached a screenshot to show you the TC2000 screen I am clicking from.  I wish there was a TC2000 API, for now Autotit will work.

 

#include <AutoItConstants.au3>
#include <WinAPI.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>
#include <Date.au3>
#include <String.au3>

;EXIT OPTION
HotKeySet("{ESC}", "Terminate")
Func Terminate()
    Exit
 EndFunc   ;==>Terminate

;OPEN PROGRAMS (TC2000,EXCEL)
Run("TC2000 EXE LOCATION")

 

Local $var = "EXCEL FILE LOCATION"
Local $oExcel_1= _Excel_Open()

If @error Then MsgBox(0, "Excel Open", "@error = " & @error & ", @extended = " & @extended & @CRLF)

 Local $oWorkbook=_Excel_BookOpen($oExcel_1,$var)

If @error Then MsgBox(0, "Excel book bopen", "@error = " & @error & ", @extended = " & @extended & @CRLF)

 

ClipPut("")

WinSetState("EXCEL FILE", "", @SW_MAXIMIZE)
WinActivate ("TC2000")
WinSetState("TC2000","",@SW_RESTORE)
WinSetState("TC2000", "", @SW_MAXIMIZE)

MouseClick($MOUSE_CLICK_LEFT, 291, 815, 1,5)

 

;$oWorkbook.Sheets("EXCEL SHEET").Activate

For $i = 1 to 27 Step 1

   WinActivate ( "TC2000")

IF $i <15 Then
   $h1 = 30+ 109*($i-1)  ;50, 155, 210, 265,320, 375,430,485,540,595,650,705,760,815,870,925,980
   ;Horizontal spacing between hourly cells: 0    109.43    218.86    328.29    437.72    547.15    656.58    766.01    875.44    984.87    1094.3    1203.73    1313.16    1422.59

   $v1 = 182
   $v2 = 497
   $v3 = 474
   $h2 = $h1+45
   $h3 = $h1+55
Else
   $h1 = 50+ 105*($i-15)
   $v1 = 660
   $v2 = 775
   $v3 = 475
   $h2 = $h1+45
   $h3 = $h1+55
endif

   MouseClick($MOUSE_CLICK_RIGHT, $h1, $v1, 1,25) ;MOVE TO TIME SYMBOL
   MouseClick($MOUSE_CLICK_LEFT, $h2, $v2, 1,25) ; COPY ALL SYMBOLS
   MouseClick($MOUSE_CLICK_LEFT, $h3, $v3, 1,25) ; COPY TO CLIPBOARD

   $letter = $i+64
   $xc = Chr($letter)&"5"

if $i = 27 Then
   $xc = "AA5"
endif

WinActivate ("EXCEL SHEET")
MouseClick($MOUSE_CLICK_LEFT, 257, 786, 1,5)

;_Excel_RangeWrite($oWorkbook,"EXCEL SHEET", ClipGet(),$xc,TRUE)
_Excel_RangeWrite($oWorkbook,"EXCEL SHEET", ClipGet(),$xc,TRUE)
;_Excel_RangeWrite($oWorkbook,$oWorkbook.Activesheet, ClipGet(),$xc,TRUE)
;_Excel_RangeWrite($oWorkbook,1, ClipGet(),$xc,TRUE)

If @error Then MsgBox(0, "Range Write Error", "@error = " & @error & ", @extended = " & @extended)

 

ClipPut("")

next

 

[rawmud]

I added in the ConsoleWrite code once I figured out what it does.  Below is the output, it errors on the _Excel_Open() but still works and then throws errors on the _Excel_rangewrite.

 

_Excel_Open: @error=0, @extended=1
_Excel_bookOpen: @error=0, @extended=0
_Excel_rangewrite: @error=2, @extended=1
_Excel_rangewrite: @error=2, @extended=1
_Excel_rangewrite: @error=2, @extended=1
_Excel_rangewrite: @error=2, @extended=1
_Excel_rangewrite: @error=2, @extended=1
_Excel_rangewrite: @error=2, @extended=1
_Excel_rangewrite: @error=2, @extended=1
_Excel_rangewrite: @error=2, @extended=1
_Excel_rangewrite: @error=2, @extended=1
_Excel_rangewrite: @error=2, @extended=1
_Excel_rangewrite: @error=2, @extended=1
_Excel_rangewrite: @error=2, @extended=1
_Excel_rangewrite: @error=2, @extended=1
_Excel_rangewrite: @error=2, @extended=1
_Excel_rangewrite: @error=2, @extended=1
_Excel_rangewrite: @error=2, @extended=1
_Excel_rangewrite: @error=2, @extended=1
_Excel_rangewrite: @error=2, @extended=1
_Excel_rangewrite: @error=2, @extended=1
_Excel_rangewrite: @error=2, @extended=1
_Excel_rangewrite: @error=2, @extended=1
_Excel_rangewrite: @error=2, @extended=1
_Excel_rangewrite: @error=2, @extended=1
_Excel_rangewrite: @error=2, @extended=1
_Excel_rangewrite: @error=2, @extended=1
_Excel_rangewrite: @error=2, @extended=1
_Excel_rangewrite: @error=2, @extended=1

 

 

Why is this not just simple copy/paste?

[water]

Could you please replace _Excel_BookOpen with _Excel_BookNew?
If it works then we know that the existing workbook causes the problem.

[rawmud]

The existing workbook caused the problem.  When I replaced _Excel_BookOpen with _Excel_BookNew, it fixed it.  I tried to create a second version by save as and it did not work.  I rebuilt the workbook and voilà, it works!

 

Thanks everyone for your help!

[water]