Jump to content

Fail to add info fully from array to excel


Recommended Posts

Posted (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 by dersiniar
Link to post
Share on other sites

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 post
Share on other sites

Why $bForceFunc is needed in some cases is described in the Wiki.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites
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 post
Share on other sites

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 - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites
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 post
Share on other sites
Posted (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 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 - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites
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 - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

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
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...