need help for excel script

[water]

Q&D. Needs beautification and some error checking.

#include <excel.au3>

Global $sDate ; date column to process
Global $sNames ; list of names
Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\temp\Test.xlsx")

While 1
    $sInputDate = InputBox("Your title goes here", "Please enter the date to be processed as DD.MM.YYYY")
    If @error = 1 Then ExitLoop ; The Cancel button was pressed
    $sInputNames = InputBox("Your title goes here", "Please enter the users to be processed separated by ';'.")
    If @error = 1 Then ExitLoop ; The Cancel button was pressed
    _Process($sDate, $sNames)
WEnd
_Excel_BookSave($oWorkbook) ; Save workbook
_Excel_Close($oExcel)
Exit


Func _Process($sDate, $sNames)
    Global $aNames = StringSplit($sNames, ";")
    Global $iNamesStartRow = 3 ; row of first name in worksheet
    Global $vDateCol
    Global $aFind = _Excel_RangeFind($oWorkbook, $sDate, Default, $xlFormulas) ; find the date column
    If UBound($aFind, 1) = 0 Then Exit ; if not found, exit
    $vDateCol = $oWorkbook.Activesheet.Range($aFind[0][2]).Column ; Column number where the date was found
    $vDateCol = _Excel_ColumnToLetter($vDateCol) ; Translate to column letter
    $iUsedRows = $oWorkbook.Activesheet.usedrange.rows.count ; # of last used row
    $aNameValues = _Excel_RangeRead($oWorkbook) ; read whole worksheet
    For $i = $iNamesStartRow To $iUsedRows
        For $j = 1 To $aNames[0]
            If $aNameValues[$i - 1][0] = $aNames[$j] Then _Excel_RangeWrite($oWorkbook, Default, "x", $vDateCol & $i)
        Next
    Next
EndFunc   ;==>_Process

 

[Overlord]

no result :/

and I don't even have a clue why

 

also, is there a possibility to have the date set to dd/mm/yyyy instead dd.mm.yyyy?

[water]

My bad
Change line

_Process($sDate, $sNames)

to

_Process($sInputDate, $sInputNames)

 

[water]

6 minutes ago, Overlord said:

is there a possibility to have the date set to dd/mm/yyyy instead dd.mm.yyyy?

You mean the date as entered by the user? Even when it stays dd.mm.yyyy in the Excel workbook?

[Overlord]

Will test tomorrow morning. Thx

regarding the . or / question...both . Our way of datewriting over here is with a /

[water]

You could use StringReplace to change the date as entered by the user to the desired format.
Changing the date format in Excel needs to be tested so that _Excel_RangeFind still works.

[Overlord]

still had some errors in it but could find them based on why it wasn't working and minor experience.

 

#include <excel.au3>

Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\temp\Test.xlsx")

While 1
    $sInputDate = InputBox("Your title goes here", "Please enter the date to be processed as D/MM/YYYY")
    If @error = 1 Then ExitLoop ; The Cancel button was pressed
    $datereplaced = StringReplace($sInputDate, ".", "/")
    $sInputNames = InputBox("Your title goes here", "Please enter the users to be processed separated by ';'.")
    If @error = 1 Then ExitLoop ; The Cancel button was pressed
    _Process($datereplaced, $sInputNames)
WEnd
_Excel_BookSave($oWorkbook) ; Save workbook
_Excel_Close($oExcel)
Exit

Func _Process($datereplaced, $sInputNames)
    Global $aNames = StringSplit($sInputNames, ";")
    Global $iNamesStartRow = 3 ; row of first name in worksheet
    Global $vDateCol
    Global $aFind = _Excel_RangeFind($oWorkbook, $datereplaced, Default, $xlFormulas) ; find the date column
    If UBound($aFind, 1) = 0 Then Exit ; if not found, exit
    $vDateCol = $oWorkbook.Activesheet.Range($aFind[0][2]).Column ; Column number where the date was found
    $vDateCol = _Excel_ColumnToLetter($vDateCol) ; Translate to column letter
    $iUsedRows = $oWorkbook.Activesheet.usedrange.rows.count ; # of last used row
    $aNameValues = _Excel_RangeRead($oWorkbook) ; read whole worksheet
    For $i = $iNamesStartRow To $iUsedRows
        For $j = 1 To $aNames[0]
            If $aNameValues[$i - 1][0] = $aNames[$j] Then _Excel_RangeWrite($oWorkbook, Default, "x", $vDateCol & $i)
        Next
    Next
EndFunc   ;==>_Process

you can now also add the date in d.mm.yyyy or d/mm/yyyy function.

Date will be converted to d/mm/yyyy like we're used to writing it over here.

Edited March 26, 2016 by Overlord
forgot to mention something

[water]

Thanks for the feedback

[Overlord]

how do I get to the GUI now?

[Overlord]

oh I just need to get 1 more thing in the script if possible...

if a name is misspelled or not found that it gives me a box at the end which names where not found and if all names are found and marked a box saying that all names where found.

Edited March 26, 2016 by Overlord
pressed enter to soon

[water]

expandcollapse popup

include <excel.au3>

Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\temp\Test.xlsx")

While 1
    $sInputDate = InputBox("Your title goes here", "Please enter the date to be processed as D/MM/YYYY")
    If @error = 1 Then ExitLoop ; The Cancel button was pressed
    $datereplaced = StringReplace($sInputDate, ".", "/")
    $sInputNames = InputBox("Your title goes here", "Please enter the users to be processed separated by ';'.")
    If @error = 1 Then ExitLoop ; The Cancel button was pressed
    _Process($datereplaced, $sInputNames)
WEnd
_Excel_BookSave($oWorkbook) ; Save workbook
_Excel_Close($oExcel)
Exit

Func _Process($datereplaced, $sInputNames)
    Global $aNames = StringSplit($sInputNames, ";")
    Global $iNamesStartRow = 3 ; row of first name in worksheet
    Global $vDateCol, $bFound = False, $sNotFound = ""
    Global $aFind = _Excel_RangeFind($oWorkbook, $datereplaced, Default, $xlFormulas) ; find the date column
    If UBound($aFind, 1) = 0 Then Exit ; if not found, exit
    $vDateCol = $oWorkbook.Activesheet.Range($aFind[0][2]).Column ; Column number where the date was found
    $vDateCol = _Excel_ColumnToLetter($vDateCol) ; Translate to column letter
    $iUsedRows = $oWorkbook.Activesheet.usedrange.rows.count ; # of last used row
    $aNameValues = _Excel_RangeRead($oWorkbook) ; read whole worksheet
    For $i = $iNamesStartRow To $iUsedRows
        $bFound = False
        For $j = 1 To $aNames[0]
            If $aNameValues[$i - 1][0] = $aNames[$j] Then 
                _Excel_RangeWrite($oWorkbook, Default, "x", $vDateCol & $i)
                $bFound = True 
            EndIf
        Next
        If $bFound = False Then $sNotFound = $sNotFound & $aNames[$j] & ";"
    Next
    If $sNotFound <> "" Then MsgBox(0, "Error", "The following names could not be found:" & @CRLF & $sNotFound) 
EndFunc   ;==>_Process

 

[Overlord]

getting a error on line 36

"e:\Desktop\test2.au3" (36) : ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.:
If $bFound = False Then $sNotFound = $sNotFound & $aNames[$j] & ";"
If $bFound = False Then $sNotFound = $sNotFound & ^ ERROR

[water]

Should be:

If $bFound = False Then $sNotFound = $sNotFound & $aNameValues[$i - 1][0] & ";"

Forget it. That's the other way round. Would post all names not entered by the user.
Will have to think about another solution.

Edited March 27, 2016 by water

[water]

Untested:

expandcollapse popup

include <excel.au3>

Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\temp\Test.xlsx")

While 1
    $sInputDate = InputBox("Your title goes here", "Please enter the date to be processed as D/MM/YYYY")
    If @error = 1 Then ExitLoop ; The Cancel button was pressed
    $datereplaced = StringReplace($sInputDate, ".", "/")
    $sInputNames = InputBox("Your title goes here", "Please enter the users to be processed separated by ';'.")
    If @error = 1 Then ExitLoop ; The Cancel button was pressed
    _Process($datereplaced, $sInputNames)
WEnd
_Excel_BookSave($oWorkbook) ; Save workbook
_Excel_Close($oExcel)
Exit

Func _Process($datereplaced, $sInputNames)
    Global $aNames = StringSplit($sInputNames, ";")
    Global $iNamesStartRow = 3 ; row of first name in worksheet
    Global $vDateCol, $bFound = False, $sNotFound = ""
    Global $aFind = _Excel_RangeFind($oWorkbook, $datereplaced, Default, $xlFormulas) ; find the date column
    If UBound($aFind, 1) = 0 Then Exit ; if not found, exit
    $vDateCol = $oWorkbook.Activesheet.Range($aFind[0][2]).Column ; Column number where the date was found
    $vDateCol = _Excel_ColumnToLetter($vDateCol) ; Translate to column letter
    $iUsedRows = $oWorkbook.Activesheet.usedrange.rows.count ; # of last used row
    $aNameValues = _Excel_RangeRead($oWorkbook) ; read whole worksheet
    For $j = 1 To $aNames[0]
        $bFound = False        
        For $i = $iNamesStartRow To $iUsedRows
            If $aNameValues[$i - 1][0] = $aNames[$j] Then 
              _Excel_RangeWrite($oWorkbook, Default, "x", $vDateCol & $i)
              $bFound = True 
            EndIf
        Next
        If $bFound = False Then $sNotFound = $sNotFound & $aNames[$j] & ";"
    Next
    If $sNotFound <> "" Then MsgBox(0, "Error", "The following names could not be found:" & @CRLF & $sNotFound) 
EndFunc   ;==>_Process

 

[Overlord]

1 hour ago, water said:

Untested:

expandcollapse popup

include <excel.au3>

Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\temp\Test.xlsx")

While 1
    $sInputDate = InputBox("Your title goes here", "Please enter the date to be processed as D/MM/YYYY")
    If @error = 1 Then ExitLoop ; The Cancel button was pressed
    $datereplaced = StringReplace($sInputDate, ".", "/")
    $sInputNames = InputBox("Your title goes here", "Please enter the users to be processed separated by ';'.")
    If @error = 1 Then ExitLoop ; The Cancel button was pressed
    _Process($datereplaced, $sInputNames)
WEnd
_Excel_BookSave($oWorkbook) ; Save workbook
_Excel_Close($oExcel)
Exit

Func _Process($datereplaced, $sInputNames)
    Global $aNames = StringSplit($sInputNames, ";")
    Global $iNamesStartRow = 3 ; row of first name in worksheet
    Global $vDateCol, $bFound = False, $sNotFound = ""
    Global $aFind = _Excel_RangeFind($oWorkbook, $datereplaced, Default, $xlFormulas) ; find the date column
    If UBound($aFind, 1) = 0 Then Exit ; if not found, exit
    $vDateCol = $oWorkbook.Activesheet.Range($aFind[0][2]).Column ; Column number where the date was found
    $vDateCol = _Excel_ColumnToLetter($vDateCol) ; Translate to column letter
    $iUsedRows = $oWorkbook.Activesheet.usedrange.rows.count ; # of last used row
    $aNameValues = _Excel_RangeRead($oWorkbook) ; read whole worksheet
    For $j = 1 To $aNames[0]
        $bFound = False        
        For $i = $iNamesStartRow To $iUsedRows
            If $aNameValues[$i - 1][0] = $aNames[$j] Then 
              _Excel_RangeWrite($oWorkbook, Default, "x", $vDateCol & $i)
              $bFound = True 
            EndIf
        Next
        If $bFound = False Then $sNotFound = $sNotFound & $aNames[$j] & ";"
    Next
    If $sNotFound <> "" Then MsgBox(0, "Error", "The following names could not be found:" & @CRLF & $sNotFound) 
EndFunc   ;==>_Process

 

works!!!

also updatedthe not found namelist so it shows names below eachother.

Thx again Water!

The GUI is done now. how do I get it to work with the GUI?

Here's the GUI I'm gonna use.

expandcollapse popup

#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#Region ### START Koda GUI section ### Form=e:\Desktop\G4S.kxf
$Form1_1 = GUICreate("G4S", 637, 407, 192, 124)
GUISetBkColor(0xC0DCC0)
$Pic1 = GUICtrlCreatePic("E:\Downloads\g4s fire + safety.jpg", 288, 80, 313, 73)
$Label1 = GUICtrlCreateLabel("Developed By", 464, 176, 134, 28)
GUICtrlSetFont(-1, 15, 800, 4, "MS Sans Serif")
GUICtrlCreateInput("", 40, 112, 177, 21)
GUICtrlCreateEdit("", 40, 168, 177, 209)
$Label2 = GUICtrlCreateLabel("G4S Datumscript voor ExxonMobil Anwerup", 40, 24, 557, 36)
GUICtrlSetFont(-1, 20, 800, 0, "MS Sans Serif")
GUICtrlSetColor(-1, 0x3399FF)
$Label3 = GUICtrlCreateLabel("Datum (invullen als d/mm/yyyy)", 40, 80, 217, 24)
GUICtrlSetFont(-1, 12, 400, 0, "MS Sans Serif")
$Label4 = GUICtrlCreateLabel("Namenlijst", 40, 136, 77, 24)
GUICtrlSetFont(-1, 12, 400, 0, "MS Sans Serif")
$Label5 = GUICtrlCreateLabel("Water (Autoitscript MVP)", 391, 207, 207, 28)
GUICtrlSetFont(-1, 15, 400, 0, "MS Sans Serif")
$Label6 = GUICtrlCreateLabel("GUI Developed By", 424, 285, 174, 28)
GUICtrlSetFont(-1, 15, 800, 4, "MS Sans Serif")
$Label7 = GUICtrlCreateLabel("Bart Cuenen (G4S)", 436, 243, 162, 28)
GUICtrlSetFont(-1, 15, 400, 0, "MS Sans Serif")
$Label8 = GUICtrlCreateLabel("Bart Cuenen (G4S)", 436, 323, 162, 28)
GUICtrlSetFont(-1, 15, 400, 0, "MS Sans Serif")
$Button1 = GUICtrlCreateButton("ONE G4S", 281, 360, 75, 25)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit

    EndSwitch
WEnd

 

[water]

Add a GUICtrlCreateDate control for the date, a GUICtrlCreateInput for the names and a GUICtrlCreateButton to start processing to your GUI.
If the button is presses call function _Process and pass the dat and the names.

[Overlord]

how do I select the sheet?

In the test there was always only 1 sheet while my workbook has multiple sheets...

[water]

To specify the sheet to read write from use

$oWorkbook.Sheets(n)

where n is the number of the sheet.

[Overlord]

2 minutes ago, water said:

To specify the sheet to read write from use

$oWorkbook.Sheets(n)

where n is the number of the sheet.

I have to use that in "Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\temp\Test.xlsx")" or in "Global $aFind = _Excel_RangeFind($oWorkbook, $datereplaced, Default, $xlFormulas) ; find the date column"

I assume the 2nd one?

Meanwhile I tried to get it into a gui but I'm having some errors (not surprised :-) )

expandcollapse popup

#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#include <excel.au3>

Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\temp\Test.xlsx")


#Region ### START Koda GUI section ### Form=e:\Desktop\G4S.kxf
$Form1_1 = GUICreate("G4S", 637, 407, 192, 124)
GUISetBkColor(0xC0DCC0)
$Pic1 = GUICtrlCreatePic("E:\Downloads\g4s fire + safety.jpg", 288, 80, 313, 73)
$Label1 = GUICtrlCreateLabel("Developed By", 464, 176, 134, 28)
GUICtrlSetFont(-1, 15, 800, 4, "MS Sans Serif")
$sInputDate = GUICtrlCreateInput("", 40, 112, 177, 21) ; DATUM INPUT
$sInputNames = GUICtrlCreateEdit("", 40, 168, 177, 209) ; NAME INPUT
$Label2 = GUICtrlCreateLabel("G4S Datumscript voor ExxonMobil Anwerup", 40, 24, 557, 36)
GUICtrlSetFont(-1, 20, 800, 0, "MS Sans Serif")
GUICtrlSetColor(-1, 0x3399FF)
$Label3 = GUICtrlCreateLabel("Datum (invullen als d/mm/yyyy)", 40, 80, 217, 24)
GUICtrlSetFont(-1, 12, 400, 0, "MS Sans Serif")
$Label4 = GUICtrlCreateLabel("Namenlijst", 40, 136, 77, 24)
GUICtrlSetFont(-1, 12, 400, 0, "MS Sans Serif")
$Label5 = GUICtrlCreateLabel("Water (Autoitscript MVP)", 391, 207, 207, 28)
GUICtrlSetFont(-1, 15, 400, 0, "MS Sans Serif")
$Label6 = GUICtrlCreateLabel("GUI Developed By", 424, 285, 174, 28)
GUICtrlSetFont(-1, 15, 800, 4, "MS Sans Serif")
$Label7 = GUICtrlCreateLabel("Bart Cuenen (G4S)", 436, 243, 162, 28)
GUICtrlSetFont(-1, 15, 400, 0, "MS Sans Serif")
$Label8 = GUICtrlCreateLabel("Bart Cuenen (G4S)", 436, 323, 162, 28)
GUICtrlSetFont(-1, 15, 400, 0, "MS Sans Serif")
$Button1 = GUICtrlCreateButton("ONE G4S", 281, 360, 75, 25) ; STARTBUTTON
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $Button1
            $datereplaced = StringReplace($sInputDate, ".", "/") ; doesn't seem to work???
            _Process($datereplaced, $sInputNames)
        Case $GUI_EVENT_CLOSE
            _Excel_BookSave($oWorkbook) ; Save workbook
            Exit

    EndSwitch
WEnd

Func _Process($datereplaced, $sInputNames)
    Global $aNames = StringSplit($sInputNames, ";")
    Global $iNamesStartRow = 3 ; row of first name in worksheet
    Global $vDateCol, $bFound = False, $sNotFound = ""
    Global $aFind = _Excel_RangeFind($oWorkbook, $datereplaced, Default, $xlFormulas) ; find the date column
    If UBound($aFind, 1) = 0 Then Exit ; if not found, exit
    $vDateCol = $oWorkbook.Activesheet.Range($aFind[0][2]).Column ; Column number where the date was found
    $vDateCol = _Excel_ColumnToLetter($vDateCol) ; Translate to column letter
    $iUsedRows = $oWorkbook.Activesheet.usedrange.rows.count ; # of last used row
    $aNameValues = _Excel_RangeRead($oWorkbook) ; read whole worksheet
    For $j = 1 To $aNames[0]
        $bFound = False
        For $i = $iNamesStartRow To $iUsedRows
            If $aNameValues[$i - 1][0] = $aNames[$j] Then
              _Excel_RangeWrite($oWorkbook, Default, "x", $vDateCol & $i)
              $bFound = True
            EndIf
        Next
        If $bFound = False Then $sNotFound = $sNotFound & $aNames[$j] & @CRLF
    Next
    If $sNotFound <> "" Then MsgBox(0, "Error", "The following names could not be found:" & @CRLF & @CRLF & $sNotFound)
EndFunc   ;==>_Process

$datereplaced = StringReplace($sInputDate, ".", "/") ; doesn't seem to work??? so my date doesn't get changed from . to / when I input it wrong.

For the names, I want a editbox where I can copy/paste all names from another excel.

using stringreplace to remove the @CRLF aint working and stringstripCR doesn't work either.

 

what am I missing?

[water]

Either us the sheet numbr inf RangeFind/ReangeRead/RangeWrite or (much easier) switch to the needed sheet after you opened the workbook by using.

$oWorkbook.Sheets(n).Activate ; Makes sheet n the active sheet.