Excel help (Solved)

[Deye]

I want to draw a two column table in excel that starts with a given date, every date will span into a 3 row block, then have 1 empty row (or an extra unused row) and continue having the same done with the next dates up to a given date

any ideas how this can be drawn up?

Edited April 20, 2021 by Deye

[Subz]

Can you post a demo Excel spreadsheet?

[Deye]

could have done something better, but this will have to do

Book.xlsx

[water]

As a start I suggest something like this. To be more flexible you could create a template holding a single block of data. This could then be duplicated in the target workbook as often as needed.

#include <Excel.au3>
#include <Date.au3>

Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookNew($oExcel)

Global $sStartDate = "2021/04/20"
Global $sEndDate = "2021/04/25"
Global $iStartRow = 1
Global $iBlockSize = 3
Global $iEmptyRows = 1
Global $sCurrentDate = $sStartDate
Global $iCurrentRow = $iStartRow

While $sCurrentDate <= $sEndDate
    ; Write Date
    _Excel_RangeWrite($oWorkbook, 1, $sCurrentDate, "A" & $iCurrentRow)
    ; calculate next date and next row to write to
    $sCurrentDate = _DateAdd("D", 1, $sCurrentDate)
    $iCurrentRow = $iCurrentRow + $iBlockSize + $iEmptyRows
WEnd

 

Edited April 20, 2021 by water

[water]

The template solution could look like this:

#include <Excel.au3>
#include <Date.au3>

Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\TEST Template.xlsx") ; Open the template
_Excel_BookSaveAs($oExcel, @ScriptDir & "\TEST Result.xlsx") ; Save as a new workbook

Global $sStartDate = "2020/10/24"
Global $sEndDate = "2020/10/27"
Global $iStartRow = 1
Global $iBlockRows = 4
Global $iBlockColumns = 3
Global $iEmptyRows = 1
Global $sCurrentDate = $sStartDate
Global $iCurrentRow = $iStartRow

While 1
    ; Write Date to the current block
    _Excel_RangeWrite($oWorkbook, 1, $sCurrentDate, "A" & $iCurrentRow)
    ; calculate next date and next row to write to
    $sCurrentDate = _DateAdd("D", 1, $sCurrentDate)
    $iCurrentRow = $iCurrentRow + $iBlockRows + $iEmptyRows
    If $sCurrentDate > $sEndDate Then ExitLoop ; Exit loop if all dates have been processed
    _Excel_RangeCopyPaste($oWorkbook.Sheets(1), "A" & $iStartRow & ":" & _Excel_ColumnToLetter($iBlockColumns) & ($iStartRow + $iBlockRows), "A" & $iCurrentRow) ; Copy the first block to the calculated row
WEnd

This way you have separated the layout and the code.

Edited April 20, 2021 by water

[Deye]

Not all clear yet

with the first example then how can I format the second column to summarize the values into the third column of the final row in each block.

getting an error with the second example, can you post your template ..looking ..

(233) : ==> The requested action with this object has failed.:
Local $oWindow = $oExcel.Windows($oWorkbook.Name)
Local $oWindow = $oExcel.Windows($oWorkbook^ ERROR

 

Edited April 20, 2021 by Deye

[water]

According to this thread this is caused by the inability of Excel to open two workbooks with the same name. Unfortunately Excel doesn't raise a COM error. So this error goes unnoticed. Is fixed in the next release.
So please make sure you do not try to open an already open workbook.

 

[Deye]

Nice, The concept of creating a block with the settings and then duplicating it worked as expected.

Thanks again Water

[water]