Jump to content

Excel add array to last cell


Go to solution Solved by junkew,

Recommended Posts

Hi i want to add array to to excel file that has data allready in. I want to add to end to data that is allready there. Example if existing data endas A:45 then i want array to go A46
But func i have still dont do this. It still populates A:1 cell

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)
    ;ConsoleWrite($sDataFilePath & @CRLF)
    ;ConsoleWrite($oWorkBook & @CRLF)
EndIf
$lastline=_Excel_RangeRead ($oWorkbook,Default,Default,Default)
_Excel_RangeWrite($oWorkBook, $lastline, $aArray)

    If FileExists($sDataFilePath) Then
         _Excel_BookSaveAs($oWorkBook, $sDataFilePath)
    Else
        _Excel_BookSaveAs($oWorkBook, $sDataFilePath)
    EndIf
   _Excel_Close($oExcel, Default, True)
   Sleep(1000)
   killEXCEL()
EndFunc   ;==>_ExportData

 

Link to comment
Share on other sites

12 minutes ago, junkew said:

Why do you think $lastline has the lastline? did you check that value with a consolewrite/msgbox

You probably need something like


$LastRow = $oWorkbook.ActiveSheet.Range("A1").SpecialCells($xlCellTypeLastCell).Row

Hmm, now it didn't overwrite data. That's good, but, it didn't add array at all now lol

$LastRow = $oWorkbook.ActiveSheet.Range("A1").SpecialCells($xlCellTypeLastCell).Row
Consolewrite($LastRow & @crlf)
_Excel_RangeWrite($oWorkbook.Activesheet, $LastRow, $aArray)

 

Link to comment
Share on other sites

Please have a look at the wiki: https://www.autoitscript.com/wiki/Excel_Range about the last used cell.

Please check the value of @error after calling  _Excel_RangeWrite.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.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 (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 comment
Share on other sites

1 hour ago, dersiniar said:

This is what I tried, but still no luck

$LastRow = $oWorkbook.ActiveSheet.Range("A1").SpecialCells($xlCellTypeLastCell).Row
$Rowrange = "A"&$LastRow+1
Consolewrite($Rowrange & @crlf)
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aArray, $Rowrange)

 

It worked like a charm... I had excel opened in task manager.. So it was not able to save it

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...