Jump to content
Sign in to follow this  
kctvt

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

Recommended Posts

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

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

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

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 (2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-03-02 - Version 1.3.5.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-01-22 - Version 0.1.0.0) - Download - General Help & Support
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
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 (2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-03-02 - Version 1.3.5.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-01-22 - Version 0.1.0.0) - Download - General Help & Support
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
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  

  • Similar Content

    • By Rhidlor
      Quick question, when working with Excel, does each workbook require its own Excel instance, or can multiple workbooks be opened off of the same Excel instance? I tested both ways, on the surface they both seemed to work, just thought I'd ask here to make sure before proceeding any further. Thanks!
      Disclaimer: The following is pseudo code
      $excel_instance1 = _Excel_Open() $workbook1 = _Excel_BookOpen($excel_instance1, @ScriptDir & "\book1.xlsx") $workbook2 = _Excel_BookOpen($excel_instance1, @ScriptDir & "\book2.xlsx") Or
      $excel_instance1 = _Excel_Open() $workbook1 = _Excel_BookOpen($excel_instance1, @ScriptDir & "\book1.xlsx") $excel_instance2 = _Excel_Open() $workbook2 = _Excel_BookOpen($excel_instance2, @ScriptDir & "\book2.xlsx")  
    • By TheDcoder
      Hello everyone, I am working on a project which requires reading a few values from Excel, the catch is that I need it to be very fast... unfortunatley I found out that read operations using the supplied Excel UDF are very slow, more than 150 ms for each operation on average
      Here is my testing setup that I made:
      #include <Excel.au3> #include <MsgBoxConstants.au3> Global $iTotalTime = 0 Test() Func Test() Local $oExcel = _Excel_Open() Local $oBook = _Excel_BookAttach("Test.xlsx", "FileName", $oExcel) Local $sSheet = "Sheet1" If @error Then Return MsgBox($MB_ICONERROR, "Excel Failed", "Failed to attach to Excel") Local $iNum For $iRow = 1 To 6 Time() Local $iNum = Number(_Excel_RangeRead($oBook, $sSheet, "A" & $iRow)) If ($iNum = 1) Then ConsoleWrite("Row " & $iRow & " is 1 and value of column B is " & _Excel_RangeRead($oBook, $sSheet, "B" & $iRow)) Else ConsoleWrite("Row " & $iRow & " is not 1") EndIf ConsoleWrite(". Reading took: ") Time() Next ConsoleWrite("The whole operation took " & $iTotalTime & " milliseconds." & @CRLF) EndFunc Func Time() Local Static $hTimer Local Static $bRunning = False If $bRunning Then Local $iTime = Round(TimerDiff($hTimer), 2) $iTotalTime += $iTime ConsoleWrite($iTime & @CRLF) Else $hTimer = TimerInit() EndIf $bRunning = Not $bRunning EndFunc And Test.xlsx in CSV format:
      1,-1 -1,1 1,-1 1,1 -1,-1 1,1 Here is the actual xlsx but it should expire in a week: https://we.tl/t-EVkxGp1kc6
      And finally output from my script:
      Row 1 is 1 and value of column B is -1. Reading took: 276.06 Row 2 is not 1. Reading took: 163.36 Row 3 is 1 and value of column B is -1. Reading took: 302.58 Row 4 is 1 and value of column B is 1. Reading took: 294.65 Row 5 is not 1. Reading took: 152.33 Row 6 is 1 and value of column B is 1. Reading took: 284.92 The whole operation took 1473.9 milliseconds.  
      Taking ~1.5 seconds for reading 6 rows of data is bad for my script, which needs to run as fast as possible . It would be nice if I can bring this down to 100 ms somehow, I am not very experienced working with MS office so I thought about asking you folks for help and advice on how I can optimize my script to squeeze out every bit of performance that I can get from this script
       
      Thanks for the help in advance!
    • By SlackerAl
      I have been using some AutoIt scripts to manipulate Excel for a few weeks now. Today they stopped working. I have rebooted the PC and I'm not aware of any other significant changes. I can start and use Excel conventionally without a problem, but any attempt to create an excel object from AutoIt fails. E.g
      #include <MsgBoxConstants.au3> #include <Excel.au3> Local $oExcel = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_Open Example 1", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Produces:
      @error = 1
      @extended = -2147221005
      I can't find that COM error listed anywhere... Anyone have any ideas?
    • By Eggie6
      Hi,
      I'm trying to write an formula to the excel, but it actually does not write anything into the cell, any ideas?
      $formula1="=MID(B"&$utakmice&",SEARCH("-",B"&$utakmice&")-1,1)" _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $formula1, "C"&$smth)  
    • By XinYoung
      Hello all,
      Preface: Column A is full of courses. Column B is full of usernames. If course (A1) exists, we check to see if username (B1) is enrolled. If user is found, the user is un-enrolled. Then Loop.
      I am working on a script that loops through an Excel file and pastes the content from A1 into a textbox in IE, does some stuff, then pastes the content from B1 into a different textbox. Then it loops around until all used rows in columns A and B have been accounted for.
      For some reason, column A loops properly but column B doesn't. B1 is pasted over and over again. So, as it loops, B1 is constantly being pasted, first accompanying A1's loop, then A2, and so on.
      The OpenExcel() func opens the Excel file the user specifies in an earlier function. It's supposed to gather the entire used range of columns A and B. The SearchCourse() func only uses column A, pasting its content into a Search tool in IE. This seems to be working fine. SearchResult() puts "Course Not Found" into column C if the search fails. If the search is successful, however, we move onto... EnterCourse(). This simply gets us to the place where column B's content comes into play. UnenrollNow(). Here, we paste the variable $_userName into a textbox. I don't know why it's always B1  Func OpenExcel() If Not WinExists($hWnd) Then MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.") _Exit() Else $oExcel = _Excel_Open() Global $oWorkbook = _Excel_BookOpen($oExcel, $ChosenFileName, Default, True, True) $oExcel.Sheets("CopyCourses").Activate ;~ Get all used cells in columns A and B $aSearchItems = _Excel_RangeRead($oWorkbook, 1, $oWorkbook.Sheets("CopyCourses").Usedrange.Columns("A:B")) ;~ Create the $aSearchResult array ReDim $aSearchResult[UBound($aSearchItems)] ;~ Loop through the array starting at 0 until the end of the array which is (Ubound($aSearchItems) - 1) For $i = 0 To UBound($aSearchItems) - 1 ;~ Column 0 ConsoleWrite($aSearchItems[$i][0] & @CRLF) ;~ Column 1 ConsoleWrite($aSearchItems[$i][1] & @CRLF) $aSearchResult[$i] = SearchCourse($aSearchItems[$i][0], $aSearchItems[$i][1]) Next _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") ;~ When the loop is complete, run the Finished function. Finished() EndIf EndFunc ;==>OpenExcel Func SearchCourse($_sSearchResult, $_userName) If Not WinExists($hWnd) Then MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.") _Exit() Else _IENavigate($oIE, $urlBBCourseSearch) _IELoadWait($oIE) Local $oForm = _IEGetObjByName($oIE, "courseManagerFormSearch") Local $oSearchString = _IEFormElementGetObjByName($oForm, "courseInfoSearchText") _IEFormElementSetValue($oSearchString, $_sSearchResult) _IEFormSubmit($oForm) _IELoadWait($oIE) Local $oBBTable = _IETableGetCollection($oIE, 2) $aBBTableData = _IETableWriteToArray($oBBTable) Return SearchResult() EndIf EndFunc ;==>SearchCourse Func SearchResult() If Not WinExists($hWnd) Then MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.") _Exit() Else Local $sResult $iSearchIndex = _ArraySearch($aBBTableData, "Course ID", 0, 0, 0, 1, 1, 0) If $iSearchIndex = -1 Then $sResult = "Course Not Found" _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") Else EnterCourse() $sResult = "UnEnrolled!" _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") EndIf Return $sResult EndIf EndFunc ;==>SearchResult Func EnterCourse() If Not WinExists($hWnd) Then MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.") _Exit() Else Local $clickFail = "DATE CREATED" _IELinkClickByIndex($oIE, 34) _IELoadWait($oIE) $sourceCode = _IEBodyReadHTML($oIE) If StringInStr($sourceCode, $clickFail) <> 0 Then _IELinkClickByIndex($oIE, 35) _IELoadWait($oIE) EndIf $oLink = _IEGetObjById($oIE, "controlpanel.users.and.groups_groupExpanderLink") _IEAction($oLink, "click") Sleep(500) _IELinkClickByText($oIE, "Users") _IELoadWait($oIE) $aSearchResult[$i] = UnenrollNow($aSearchItems[$i][0], $aSearchItems[$i][1]) EndIf EndFunc ;==>EnterCourse Func UnenrollNow($_sourceCourseId, $_userName) If Not WinExists($hWnd) Then MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.") _Exit() Else Local $UserError = "No users found" Local $sResult $criteriaUsername = _IEGetObjById($oIE, "userInfoSearchKeyString") _IEFormElementOptionSelect($criteriaUsername, 0, 1, 'byIndex') $criteriaUsername = _IEGetObjById($oIE, "userInfoSearchOperatorString") _IEFormElementOptionSelect($criteriaUsername, 0, 1, 'byIndex') ;Paste whats copied from column B into the Username text box. Local $oForm = _IEGetObjByName($oIE, "userManagerSearchForm") Local $oSearchString = _IEFormElementGetObjByName($oForm, "userInfoSearchText") ;PROBLEM HERE... _IEFormElementSetValue($oSearchString, $_userName) ;^^^^^^ WHY IS $_userName ALWAYS B1 ??? Sleep(1000) _IEFormSubmit($oForm) _IELoadWait($oIE) $sourceCode = _IEBodyReadHTML($oIE) If StringInStr($sourceCode, $UserError) <> 0 Then $sResult = "User Not Found" _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") Return $sResult Else MsgBox(0, "Unenrollment READY!", "We're ready to unenroll foreal") $sResult = "DUN" _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") Return $sResult EndIf EndIf EndFunc ;==>UnenrollNow Please let me know if any further information is needed.
      If you see other problems or redundancies in my code, please let me know.
      Thank you!
       
×
×
  • Create New...