Sign in to follow this  
Followers 0
kctvt

Errors $oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).Value = $sValue

11 posts in this topic

Hi everybody, I have this code, but dont know how to fix this errors. So, I need help.
 

I want to use file Txt to control , write line number , write link to file ..... etc

But, when this code run, I can open file Excel, but have errors. 

 

This is my code Auto IT :
Test Read Txt - Xls.au3
 
My file Excel :
New.xls
 
File Txt to control :
Control.txt
 
And this is my code :

#include <IE.au3>
#include <File.au3>
#include <Array.au3>
#include <Date.au3>
#Include <Excel.au3>
#include <Word.au3>
#include <FTPEx.au3>





$partData = FileReadLine ("Control.txt",2)
$oExcelData = _ExcelBookOpen($partData)


$CellName = FileReadLine ("Control.txt",17)
_ExcelWriteCell ($oExcelData,"Name Malibu",4,$CellName)

$CellPhone = FileReadLine ("Control.txt",8)
_ExcelWriteCell ($oExcelData,"0012848299124",4,$CellPhone)

And this is my Errors :

D:\AutoIt3\Include\Excel.au3 (451) : ==> The requested action with this object has failed.:
$oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).Value = $sValue
$oExcel.Activesheet.Cells($sRangeOrRow, $iColumn)^ ERROR

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

Hi there!

I have the same problem. I look everywhere without luck for solution.

Today i found a very strange solution. If we want this script to work we must give a fake value to wake up the script

example:

      1. $CellName = FileReadLine ("Control.txt",17) ;your line
        
        $test1 = msgbox(0,"",$CellName,1) ;trick starts
        
        if $test1 = -1 then
        $bla = $CellName +1 -1            ;the wake up trick
        endif                             ;trick ends
        
        _ExcelWriteCell($oExcelData,"Name Malibu",4,$bla) ; not $CellName

        Tell me if is working!!

Edited by gregorkkk

Share this post


Link to post
Share on other sites

What exactly are you attempting to do with this line "$bla = $CellName +1 -1"?


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

as I say "to wake up the variable value"

My script was:

$oExcel = _ExcelBookOpen(@DesktopDir & "\t.xls")
$sLastCell = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address(True, True, $xlR1C1)
$var = StringRight($sLastCell,1)
FOR $i = 1 to 10 Step 1
_ExcelWriteCell($oExcel,$i,$i,$var)
Next

But it doesn't work (I am not an expert script writter, but i tried many things)

$oExcel = _ExcelBookOpen(@DesktopDir & "\t.xls")
$sLastCell = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address(True, True, $xlR1C1)
$var = StringRight($sLastCell,1)
FileOpen(@DesktopDir & "\test.txt",2)
FileWrite(@DesktopDir & "\test.txt",$var)
$test = FileRead(@DesktopDir & "\test.txt",1)
$test1 = MsgBox(0,"",$test,1)
if $test1 = -1 Then
$bla = $test +1 -1
EndIf
FOR $i = 1 to 10 Step 1
_ExcelWriteCell($oExcel,$i,$i,$bla)
Next

That script with the +1 -1 trick working!! (I don't know why exactly. Maybe you can tell me)

Edited by gregorkkk

Share this post


Link to post
Share on other sites

I haven't a clue what you're attempting to do with that line, what it's doing is taking the string returned by FileRead, and then adding 1 to the string (converting the string to a number) and then subtracting 1 from the previous result.

What version of AutoIt are you using, because the updated Excel UDF uses different naming.


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

I use AutoIt v3.3.6.1

see the comments:

$oExcel = _ExcelBookOpen(@DesktopDir & "\t.xls")

$sLastCell = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address(True, True, $xlR1C1) ;this is a trick: How to read the range of t.xls for example "R3C6"

$var = StringRight($sLastCell,1) ;gives me the number of last column for example "6"

FOR $i = 1 to 10 Step 1
_ExcelWriteCell($oExcel,$i,$i,$var) ; here i want to use the value of last column which is "6" into the loop but it doesn't work
Next
 
It not seems logical and I don't know why, but the trick "if" & "msgbox" & "the new variable with +1-1" is working.
Can you try "the above not working script" to make it working?!?
Edited by gregorkkk

Share this post


Link to post
Share on other sites

I don't have that version available, it's also 4 years old. You should probably update to a newer or the newest version. The Excel UDF has been vastly improved over that version.


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites

Ιn my opinion the latest version of AutoIT is much better and stable, but the new excel UDF not.

It has some new func but some older func missing. I think that the combination of both udf versions, they will bring the best results

Share this post


Link to post
Share on other sites

The UDFs can't be combined in a single UDF because the design concept has changed.

The removed functions are only single line functions which became obsolete based on the new design concept or where 90% of the code were used for error checking of the passed parameters.

Please have a look at the wiki how to replace dropped functions.


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

"The UDFs can't be combined in a single UDF because the design concept has changed."

I didn't mean to compine the two versions of UDF. I mean-->

With two PC: (first PC) write a part of script to my older autoit version with old UDF excel, compiled to .exe and (second PC): write another part of script and execute the older func to the new version. 

I haven't read yet the wiki how to replace dropped functions, but i will definitely do that. Thanks!!

Share this post


Link to post
Share on other sites

Let me know if you encounter any problems.


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

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
Sign in to follow this  
Followers 0

  • Similar Content

    • VeryGut
      By VeryGut
      I'm trying to insert the following formula in cell A2 using my script:
      =if(A1=""; "YES"; "NO")
      To my understanding, the line of code should be similar to this:
      _Excel_RangeWrite($MasterFile, Default, "=if(A1=""; "YES"; "NO")", "A2")
      However, it does not work, probably due to the multiple quotation marks that confuse the script :C
      How do I avoid this problem?
    • LoneWolf_2106
      By LoneWolf_2106
      Hi everybody,
      i have a question about Excel, i have to create several charts one below the other dynamically.
      I have thought to use:
       
      $oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell) $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count  
      And then to use it in this way:
      $Graph_position = "=Test1!A"&$iRowCount+2&":K"&$iRowCount+24 But it doesn't work with charts.
      Does anyone have a suggestion?
       
    • LoneWolf_2106
      By LoneWolf_2106
      Hi all,
      i have an empty csv file, i have a non formatted text file.
      What do i want to do?
      I want to automate the process "get external data" in Excel, i want to import the data from the text file and basically create a csv file with a specific character encoding.
      Is it possible with AutoIT?
       
    • breakbadsp
      By breakbadsp
      I  want to create a excel file from my script if it does not exist.
      _ExcelBookOpen throws error=2 if file does not exist, after this error i want to create new file at this point.
      can i use _FileCreate()?
      _Logger($sLogPath, "{INFO}------: Opening Excel File: " & $sExcelPath& "") While 1 Local $oExcelTestResult = _ExcelBookOpen($sExcelPath) If @error = 2 Then If not _FileCreate($sResExcelPath) Then MsgBox(0, "Error", "Error In Opening REsult Excel File: Error: " & String(@error)) _Logger($sLogPath, "{ERROR}------: Result Excel File does not exist.. tried to create new but :ERROR : " & String(@error) & "") ExitLoop Else _Logger($sLogPath, "{INFO}------: Result Excel File does not exist.. **Created New**: ") EndIf Else ExitLoop EndIf WEnd  
    • LoneWolf_2106
      By LoneWolf_2106
      Hi everybody,
      i have to store an entire row of a Excel workbook into an array.  The row index is stored in a variable.
      How can i do it?
      Thanks in advance for your support.