Jump to content

need help for excel script

Overlord

By Overlord
in Office

 Share

Recommended Posts

Overlord Prodigy

Overlord

Posted
  • Author
Posted

ok updated the script.

 

Can you help me further with the gui also?

problem still remains there... 

#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") ; <======================================= DON'T FORGET TO ADJUST IN FINAL


#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
    $oWorkbook.Sheets(3).Activate ; Makes sheet n the active sheet. <======================================= DON'T FORGET TO ADJUST IN FINAL
    $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

 

  • Replies 45
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Popular Posts

water
water

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

water Universalist

water

Posted
Posted

You need to add some error checking and inform the user when something goes wrong. I have marked some lines with "; >>" 

#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") ; <======================================= DON'T FORGET TO ADJUST IN FINAL

#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
            ; >> Add error checking here <<
            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
    ; >> Do not just exit, inform the user that the date could npt be found <<
    If UBound($aFind, 1) = 0 Then Exit ; if not found, exit
    $oWorkbook.Sheets(3).Activate ; Makes sheet n the active sheet. <======================================= DON'T FORGET TO ADJUST IN FINAL
    ; >> Add error checking here. Maybe sheet n does not exist <<
    $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
    ; >> Add error checking here <<
    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)
              ; >> Add error checking here <<
              $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

 

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.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 (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Overlord Prodigy

Overlord

Posted
  • Author
Posted

the whole thing is that the function _Process used to work until I started working with the GUI.

at this line here you see that for some reason my string doesn't get updated. 

$datereplaced = StringReplace($sInputDate, ".", "/") ; doesn't seem to work???

same problem for inputting the names in that editbox.

If I set a msgbox box behind it I get the exact text entered. Also for the date.

When I use stringstripCR to remove the hard space or use stringreplace to replace the @CR to ";" then nothing happens.

and I have no idea what is causing those errors...

water Universalist

water

Posted
Posted

After GUICtrl... the variable contains the ID of the control. To get the CONTENT of the control you need to use GUICtrlRead. 

#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") ; <======================================= DON'T FORGET TO ADJUST IN FINAL

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

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $idButton1
            $sDatereplaced = StringReplace(GUICtrlRead($idInputDate), ".", "/") ; doesn't seem to work???
            _Process($sDatereplaced, GUICtrlRead($idInputNames))
        Case $GUI_EVENT_CLOSE
            _Excel_BookSave($oWorkbook) ; Save workbook
            ; >> Add error checking here <<
            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
    ; >> Do not just exit, inform the user that the date could npt be found <<
    If UBound($aFind, 1) = 0 Then Exit ; if not found, exit
    $oWorkbook.Sheets(3).Activate ; Makes sheet n the active sheet. <======================================= DON'T FORGET TO ADJUST IN FINAL
    ; >> Add error checking here. Maybe sheet n does not exist <<
    $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
    ; >> Add error checking here <<
    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)
                ; >> Add error checking here <<
                $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

 

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.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 (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

water Universalist

water

Posted
Posted

I know. Happens to me too from time to time :)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.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 (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

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
 Share
Go to topic listing

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...