need help for excel script

[Overlord]

Hi guys,

 

I need help with a excel script (obvious, otherwise I wouldn't be asking here)

What It needs to do:

start a gui with 2 inputboxes. (inputbox 1 = date, inputbox 2 = a list of names)  > Got that done with Koda.

open a excel file >Got that done...

find the corresponding date from inputbox 1 and use that column

read all names in the list from inputbox 2 and place a "X" in their row and the column from inputbox1

if a name is not recognized then it should give a msgbox which names where missing.

[water]

Could you please provide an example workbook so we know how it looks like?

[Overlord]

hi Water,

I attached a very small part from the matrix I got for our people.

For safety reasons I cannot send the full file but this should give a idea what I need...

My full matrix has several more sheets but this sheet (#days on site) is the one where I need to set the marks.

This is the code for the GUI I created with KODA.

#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#Region ### START Koda GUI section ### Form=
$Form1 = GUICreate("G4S Datumscript", 202, 331, 192, 124)
$Label1 = GUICtrlCreateLabel("DATUM", 40, 16, 43, 17)
GUICtrlCreateInput("", 40, 40, 121, 21)
$Label2 = GUICtrlCreateLabel("NAAM", 40, 80, 35, 17)
GUICtrlCreateEdit("", 40, 112, 121, 129)
$Button1 = GUICtrlCreateButton("START", 56, 264, 75, 25)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###

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

    EndSwitch
WEnd

One last detail...after the script it should not close the excel file but save it

test.xlsx

[water]

Will check tomorrow but shouldn't be too hard.
As a start you could use _Excel_RangeFind for the range C1:K1 and search for the date using the format as displayed "DD.MM.YYYY".

 

[Overlord]

ok, happy to hear that.

So if C1:K1 is the dates I need it to search then you might be happy to hear that the project I'm working on is planned until december 2018. which broadens the range  from C1:K1 to C1:ASZ1?

I'm allready lost now between the guictrlread where I need a controlID and the _excel_rangefind which is a string?

[Overlord]

Actually I've been thinking about the GUI... If I can get around it with a inputbox....

[water]

At the moment I'm working on the Excel part. If this works then a GUI/Inputbox should be easy

[Overlord]

thank you so much for your help water.

also regarding the namelist....right now we have approx 150 persons on site.  Like any other company people will come and leave the project so I'm guessing that list will be double the size pretty soon.

isn't there some formula to use each name in the list without limitating a size like A1:A55?

[water]

Something for you to play with:

#include <excel.au3>

Global $sDate = "03.10.2015" ; date column to process
Global $sNames = "fred flinstone|barnie rumble|birgit caledi" ; list of names
Global $aNames = StringSplit($sNames, "|")
Global $iNamesStartRow = 3 ; row of first name in worksheet
Global $vDateCol
Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\temp\Test.xlsx")
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

 

[Overlord]

somehow this script isn't doing anything for me...

 

but from my understanding from autoit I see things that I do need.

now to implement them...

 

[water]

Your script does nothing because it exits as soon as it doesn't find the date.
I had the same problem with your example Excel file. After reformatting the date cells C1:K1 as "date" the script worked as designed.

[Overlord]

does it matter that I have a dutch version from office? Meaning Date = Datum for me??

 

The script opens the excel file for me but then it stops?

and yes: top row is marked as datum(date)

 

Edited March 23, 2016 by Overlord
forgot something

[water]

Try this version. It displays what was found:

#include <excel.au3>

Global $sDate = "03.10.2015" ; date column to process
Global $sNames = "fred flinstone|barnie rumble|birgit caledi" ; list of names
Global $aNames = StringSplit($sNames, "|")
Global $iNamesStartRow = 3 ; row of first name in worksheet
Global $vDateCol
Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\temp\Test.xlsx")
Global $aFind = _Excel_RangeFind($oWorkbook, $sDate, Default, $xlFormulas) ; find the date column
_ArrayDisplay($aFind)
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

 

Edited March 23, 2016 by water

[Overlord]

>"C:\Program Files (x86)\AutoIt3\SciTE\..\AutoIt3.exe" "C:\Program Files (x86)\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.au3" /run /prod /ErrorStdOut /in "C:\Users\Ann\Desktop\test.au3" /UserParams    
+>18:31:28 Starting AutoIt3Wrapper v.16.306.1237.0 SciTE v.3.6.2.0   Keyboard:00000813  OS:WIN_10/  CPU:X64 OS:X64  Environment(Language:0413)  CodePage:0  utf8.auto.check:4    # detect ascii high characters and if none found set default encoding to UTF8 and do not add BOM
+>         SciTEDir => C:\Program Files (x86)\AutoIt3\SciTE   UserDir => C:\Users\Ann\AppData\Local\AutoIt v3\SciTE\AutoIt3Wrapper   SCITE_USERHOME => C:\Users\Ann\AppData\Local\AutoIt v3\SciTE 
>Running AU3Check (3.3.14.2)  from:C:\Program Files (x86)\AutoIt3  input:C:\Users\Ann\Desktop\test.au3
+>18:31:28 AU3Check ended.rc:0
>Running:(3.3.14.2):C:\Program Files (x86)\AutoIt3\autoit3.exe "C:\Users\Ann\Desktop\test.au3"    
--> Press Ctrl+Alt+Break to Restart or Ctrl+Break to Stop
"C:\Program Files (x86)\AutoIt3\Include\excel.au3" (670) : ==> The requested action with this object has failed.:
$oSheet = $oWorkbook.Sheets($iIndexSheets)
$oSheet = $oWorkbook^ ERROR
->18:31:29 AutoIt3.exe ended.rc:1
+>18:31:29 AutoIt3Wrapper Finished.
>Exit code: 1    Time: 1.588

still no result.

it opens the excel file but it stops there.

I'm getting a message on excel.au3 giving a error??

 

 

[water]

That's a bug in Autoit 3.3.14.2. How to solve is described here:

 

[Overlord]

U said in that topic in post #6:

Quote

Use function _Excel_RangeFind as described in the help file after you have modified the following line in the UDF

what is the UDF? excel.au3 is that it? I saved and renamed to excel2.au3 and adjusted it in the script since I couldn't save it in the original.

I'm still getting a error in line 670 from excel.au3

 

or rather said.. in excel2.au3

Edited March 23, 2016 by Overlord

[water]

Another idea: Would it be possible for you to downgrade to AutoIt 3.3.12.0?

[Overlord]

ok, I downgraded like you said.

Now the script runs without any issues

from what I understand from the script it should place a cross for fred flinstone, barnie rumble and birgit caledi on date 03/10/2015

however the arraydisplay is returning [0] to me and doesn't place crosses.

[water]

Then there is a problem with the formatting of the date column headers.
I formatted all date cells as date - "*TT.MM.YYYY" - note the start at the beginning. Then it worked.

[Overlord]

ok. gotten it to work.

dateformat in excel  = *DD/MM/YYYY

test done:

Global $sDate = "03.10.2015" ; date column to process

excel date 03/10/2015 = no result

excel date 03.10.2015 = positive result

Global $sDate = "03/10/2015" ; date column to process

excel date 03/10/2015 = no result

excel date 03.10.2015 = no result

 

that means I need to reformat my date from dd/mm/yyyy to dd.mm.yyyy

 

how do you get it into a gui now??