dersiniar Posted June 8, 2022 Share Posted June 8, 2022 (edited) Hi, I have about 205 lines in Array. I want to write this data to Excel. But it writes random amount rows each time. usually 90rows. At same time I use also FileWriteLine and this works perfect, all 205 lines will be there. Here's my code for excel Func _ExportData($aArray) Sleep(100) Global $oExcel = _Excel_Open(False, False, True, True, True) Global $oWorkBook If Not FileExists($sDataFilePath) Then $oWorkBook = _Excel_BookNew($oExcel) Else $oWorkBook = _Excel_BookOpen($oExcel, $sDataFilePath) EndIf Local $sRange = "A1:F600" _Excel_RangeDelete($oWorkbook.Activesheet, $sRange) Sleep(1000) _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aArray) Sleep(1000) _Excel_BookSaveAs($oWorkBook, $sDataFilePath) _Excel_Close($oExcel, Default, True) killEXCEL() EndFunc ;==>_ExportData Edited June 8, 2022 by dersiniar Link to comment Share on other sites More sharing options...
Subz Posted June 8, 2022 Share Posted June 8, 2022 Can you try setting $bForceFunc to True within _Excel_RangeWrite? Recall had to do this for a script I wrote a few years back, forget the reason why. Link to comment Share on other sites More sharing options...
water Posted June 8, 2022 Share Posted June 8, 2022 Why $bForceFunc is needed in some cases is described in the Wiki. Subz 1 My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
dersiniar Posted June 9, 2022 Author Share Posted June 9, 2022 18 hours ago, Subz said: Can you try setting $bForceFunc to True within _Excel_RangeWrite? Recall had to do this for a script I wrote a few years back, forget the reason why. Umm, can you help me with it? im not sure how to add this Link to comment Share on other sites More sharing options...
Subz Posted June 9, 2022 Share Posted June 9, 2022 _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aArray, "A1", True, True) Link to comment Share on other sites More sharing options...
dersiniar Posted June 9, 2022 Author Share Posted June 9, 2022 47 minutes ago, Subz said: _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aArray, "A1", True, True) It didn't work, got this time 105lines into Excel Link to comment Share on other sites More sharing options...
water Posted June 9, 2022 Share Posted June 9, 2022 Please post the values of @error and @extended after calling _Excel_RangeWrite. $forceFunc does not solve all problems described in the Wiki (see my link above). Add your array to the example script and post the result. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
dersiniar Posted June 9, 2022 Author Share Posted June 9, 2022 6 minutes ago, water said: Please post the values of @error and @extended after calling _Excel_RangeWrite. $forceFunc does not solve all problems described in the Wiki (see my link above). Add your array to the example script and post the result. I do not have "@error and @extended" Array Global $aArray[1][5] = [["A","B","C","D","E"]] ArrayAdd _ArrayAdd($aArray, $First & "|" & $Second & "|" & $Third & "|" & $Forth& "|" & $Fifth) And Write to excel Func _ExportData($aArray) Sleep(100) Global $oExcel = _Excel_Open(False, False, True, True, True) Global $oWorkBook If Not FileExists($sDataFilePath) Then $oWorkBook = _Excel_BookNew($oExcel) Else $oWorkBook = _Excel_BookOpen($oExcel, $sDataFilePath) EndIf Local $sRange = "A1:F600" _Excel_RangeDelete($oWorkbook.Activesheet, $sRange) Sleep(1000) _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aArray, "A1", True, True) Sleep(1000) _Excel_BookSaveAs($oWorkBook, $sDataFilePath) _Excel_Close($oExcel, Default, True) killEXCEL() EndFunc ;==>_ExportData All cells have max value 30caracters and there must be 205 lines and 5 columns. Array works good, cos i have same outbut in txt file, but i need csv not txt. FileWriteLine($file1, $First & "|" & $Second & "|" & $Third & "|" & $Forth& "|" & $Fifth) Sample from csv A B C D E F €10 Artur 5h €9.99 June 16 €10 Juhan 5h Not Found Not Found €12 Artur 1.2h Not Found Not Found €12 Enn 1.2h Not Found Not Found Link to comment Share on other sites More sharing options...
dersiniar Posted June 9, 2022 Author Share Posted June 9, 2022 One solution can be like this, instead using array, Maybe I can write lines directly to excel using same system as i write to txt file?FileWriteLine($file1, $First & "|" & $Second & "|" & $Third & "|" & $Forth& "|" & $Fifth) Link to comment Share on other sites More sharing options...
water Posted June 9, 2022 Share Posted June 9, 2022 (edited) 25 minutes ago, dersiniar said: I do not have "@error and @extended" This are AutoIt macros. Add ConsoleWrite("@error=" & @error & ", @extended=" & @extended & @CRLF) after _Excel_RangeWrite and post the result. Edited June 9, 2022 by water My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
dersiniar Posted June 9, 2022 Author Share Posted June 9, 2022 40 minutes ago, water said: This are AutoIt macros. Add ConsoleWrite("@error=" & @error & ", @extended=" & @extended & @CRLF) after _Excel_RangeWrite and post the result. This is result i Got @error=5, @extended=-2147352567 Link to comment Share on other sites More sharing options...
water Posted June 9, 2022 Share Posted June 9, 2022 2 hours ago, water said: Add your array to the example script and post the result. Run the example script from the wiki with your data and post the result. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now