Jump to content
Sign in to follow this  
Relive

Writing to Excel sheet fails during script run

Recommended Posts

Relive

I wrote a script that is designed to log at least 10,000 lines of data. While timing it in the beginning, I figured this will take a while so I decided to afk. I came back shortly and found an error came up in the script

C:\Program Files (x86)\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).Value = $sValue^ ERROR

About six thousand lines were produced in the file successfully, but I was wondering what could have caused this error?

Share this post


Link to post
Share on other sites
water

Add a COM error handler to get more detailed information about the problem.

See ObjEvent in the help file for an example.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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

Which version of Excel do you run?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
Relive

What was the value of $sRangeOrRow?

The variable for $sRangeOrRow get iterated.

Which version of Excel do you run?

2007

Share this post


Link to post
Share on other sites
Relive

I shrugged off the problem and decided to continue where I left off. After several minutes of running, the script stopped unexpectedly with the same error. Luckily I put ConsoleWrite() to check my variables and here's where my script stopped:

>===[TyrantAA]=== 18% abc Efghijk
C:\Program Files (x86)\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).Value = $sValue^ ERROR

At the same time, _ ===[TyrantAA]=== 18% abc Efghijk _ was supposed to be filled into the next iterated cell but it didn't. If I remember correctly, Excel reads the = sign as initiating a function statement. Unless you put a ' in front of = to tell it is plain text, it will take it as a function. I might need to add some if-then statements to combat this but instead of going through the debugging process again, can I disable such Excel feature just for this spreadsheet?

 

 

Share this post


Link to post
Share on other sites
Relive

Can someone tell me why this is false?

$ShopDesc = "===[TyrantAA]=== "

Msgbox(0, '', NoXLFunc($ShopDesc))

Func NoXLFunc($ShopDesc)
    if StringLeft($ShopDesc, 1) = "=" then return $ShopDesc = "'" & $ShopDesc
EndFunc

Share this post


Link to post
Share on other sites
BrewManNH

It's returning false because your return statement tells it to evaluate whether $ShopDesc is equal to ' & $ShopDesc, which it isn't equal to, so it returns false.


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

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

    • nooneclose
      By nooneclose
      How to use _Excel_RangeSort to sort my excel file by three different headers Column A1, B1, and C1 have headers on which I want to sort by. The headers on which I want to sort are department, employee type, and name.
      I still really new to AutoIt so I do not actually know how to properly start this line or lines of code, to be honest. The example code is the best I can do.
      _Excel_RangeSort($OpenWorkbook, Default, "A1:C1", "1:1", $xlDescending, Default, $xlYes, Default, $xlSortRows) I just need to sort by those three headers in that order of department, employee type, and name, plus in descending order.
       
      any and all help would be greatly appreciated.  Thank you!
    • Daniza
      By Daniza
      It works fine on my Excel 2007, but after I Emailed My Area Manager he told me after he Enable Macro Security nothing happen's, can someone test this to run on Excel 2016? My AM can't provide me more information 

      Thank You in Advance,
      Please see attachment.
      <snip>
    • tuffgong
      By tuffgong
      Good morning. I have a system I am trying to automate that works like this: user fills a column in an Excel spreadsheet with values they would like printed and saves it to a folder on their desktop, they start the script and it formats their data into a text file (adding a prefix) and sends the text file as a .bch file where it needs to go. This is working:
      #include <Array.au3> #include <Excel.au3> #include <File.au3> #include <MsgBoxConstants.au3> Global $sSTCArray Global $sFilename = @DesktopDir & "\Labels\print.txt" Global $sWorkbook = @DesktopDir & "\Labels\Labels.xlsx" Global $oExcel = _Excel_Open(False,False,False,False,True) barcodePrint() Func barcodePrint() $Read = _Excel_BookOpen($oExcel, $sWorkbook, True, False, Default, Default, Default) FileOpen($sFilename, $FO_OVERWRITE) ;Global $oWorkbook = _Excel_BookAttach($oExcel) Global $sSTCArray = _Excel_RangeRead($Read) For $i = 0 to UBound($sSTCArray, 1) - 1 FileWriteLine($sFilename, "!StaticShelving1x3_ZPL," & $sSTCArray[$i]) Next _Excel_Close($oExcel) FileMove($sFilename, "***file path***\print.bch") EndFunc However, it only works if the user first formats the spreadsheet to text. I want to automate that. From what I have read it appears AutoIt does not like formatting cells that already have values. True? Can I pull the values from an un-formatted (default GENERAL format) spreadsheet and go straight to my text file? I have also considered opening a second spreadsheet, formatting it, and copying the values over. Like this:
        
      Global $sPrefix Global $oPath Global $sSTCArray Global $sFilename = @DesktopDir & "\Labels\print.txt" Global $sWorkbook = @DesktopDir & "\Labels\Labels.xlsx" Global $oExcel = _Excel_Open(False,False,False,False,True) Global $aArray Global $bExcel _Excel_BookNew($bExcel) While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit Case $Button1 $aArray = IniReadSection("***File path***\barcode.ini", "stc/rvt/mgm") $sPrefix = $aArray[1][1] $oPath = $aArray[2][1] barcodePrint() EndSwitch WEnd Func barcodePrint() $oWorkbook = _Excel_BookOpen($bExcel, @DesktopDir & "\Labels\print.xlsx") $oWorkbook.ActiveSheet.Columns("A").NumberFormat = "@" Local $Read = _Excel_BookOpen($oExcel, $sWorkbook, True, False, Default, Default, Default) $oCopy = _Excel_RangeRead($sWorkbook) _Excel_RangeWrite($oWorkbook, Default, $oCopy) FileOpen($sFilename, $FO_OVERWRITE) $sSTCArray = _Excel_RangeRead($Read,"Default","Default",3) ;_ArrayDisplay($sSTCArray) For $i = 0 to UBound($sSTCArray, 1) - 1 FileWriteLine($sFilename, $sPrefix & $sSTCArray[$i]) Next _Excel_Close($oExcel) ;FileMove($sFilename, $oPath) Exit EndFunc This does not like the formatting of $oWorkbook: "Variable must be of type 'Object'".  Do I need this second sheet? If so, how can I format it? Is there a better way to get the Excel values into a .txt file? Any ideas would be appreciated. Thanks!
    • Moonscarlet
      By Moonscarlet
      Hello,
      I am trying to keep only the filtered rows in an excel sheet but couldn't find a fast way to do it for a 1k+ rows file.
      Tried going through each row to see if it's hidden or not and if it is, delete it but it's not fast:
      Local $oExcel = _Excel_Open() Local $File = _Excel_BookOpen($oExcel, $Filepath) Local $LastRow = $File.Activesheet.Range("A1000000").End(-4162).Row _Excel_FilterSet($File, Default, Default, 1, "Test") For $j=1 To $LastRow If $File.Activesheet.Range("A"&$j).EntireRow.Hidden Then _Excel_RangeDelete($File.Activesheet,$j&":"&$j) Next I found a VBA macro that works great but I am having a hard time converting it to be able to use it in my script:
      Sub RemoveHiddenRows() Dim oRow As Range, rng As Range Dim myRows As Range Set myRows = Intersect(Sheets("Sheet1").Range("A:A").EntireRow, Sheets("Sheet1").UsedRange) For Each oRow In myRows.Columns(1).Cells If oRow.EntireRow.Hidden Then If rng Is Nothing Then Set rng = oRow Else Set rng = Union(rng, oRow) End If End If Next If Not rng Is Nothing Then rng.EntireRow.Delete End Sub I would really appreciate it if anyone can help me so I can use this part in my autoit script.
      Thanks.
    • Ahmed101
      By Ahmed101
      I have more than 12 workbooks opened together, if i wanted to attach to the last workbook opened it will take more than 1 minute !
      Is there any solution for that ?
×