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

    • 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.
    • LoneWolf_2106
      By LoneWolf_2106
      Hi everybody,
      i have to write a value into an excel column.
      I know where it starts from, but i don't know what the end is, last non-empty cell.
      How can i get the number of last non-empty cell?
      Thanks in advance.
      Regards 
    • Nareshm
      By Nareshm
      Hi All,
      I have excel file like this
      and i want to cut cell/text from excel to other software.

       
      I have to cut the cell of B column one by one and past into other software
      If Winexists("No Data Found")
      then restore cuted cell and goto next/down side cell
      How to do it ?
    • water
      By water
      Extensive library to control and manipulate Microsoft Excel charts.
      Theads: General Help & Support - Example Scripts
      BTW: If you like this UDF please click the "I like this" button. This tells me where to next put my development effort

      KNOWN BUGS (last changed: 2017-07-21)
      None. The COM error handling related bugs have been fixed.
       
    • Mag91
      By Mag91
      Hey Community,
      cause im too new in the Auto it world i will try it with the your help. hopefully.
      I woud like to know how i can handle my Problem.
      ----
      I have a Excel Data with 362 random numbers.
      For Example:
      1166642335374 1172899897343
      .....
      this numbers are a part of the filepath ...example
      D:\Projekte\1166_64233_5374
      as u can see its the first number of the Excel data. After the first 4 numbers it shoud make a "_" than another 5 "_"
      This is my first question. How can i handle this to make it Shell execute.
       
      --------
      Second question:
      If i am in the path.
      For Example:
      D:\Projekte\1166_64233_5374
      the code shoud search for specific PDF Files.
      They are named like: 0050569E364B1ED79B900F73E62660EC.pdf
      the first 15 letters are always the same
      0050569E364B1ED
      when he found this data he has to copy it on a Folder on the Desktop.
      (There can also be 2 or 3 pdfs in one Folder with this letters)
      ----
      Please give me some help :-)