Overlord

need help for excel script

46 posts in this topic

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.

Share this post


Link to post
Share on other sites



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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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".

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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...

 

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#12 ·  Posted (edited)

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 by Overlord
forgot something

Share this post


Link to post
Share on other sites

#13 ·  Posted (edited)

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
>"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??

 

 

Share this post


Link to post
Share on other sites

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

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#16 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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??

Share this post


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