Jump to content

Recommended Posts

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

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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (NEW 2021-06-14 - Version 1.6.5.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 (NEW 2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - 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

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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (NEW 2021-06-14 - Version 1.6.5.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 (NEW 2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - 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
Posted (edited)

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

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.

 

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (NEW 2021-06-14 - Version 1.6.5.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 (NEW 2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - 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

:)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (NEW 2021-06-14 - Version 1.6.5.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 (NEW 2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - 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...