robertocm

WaitForSingleObject function example

4 posts in this topic

#1 ·  Posted (edited)

Help file doesn't seems to have an example for _WinAPI_WaitForSingleObject

Below a simple adaptation from the _WinAPI_FindNextChangeNotification help example (also posted in the russian forum)

i'm using something similar as a way of data transfer between computers having a shared folder

#include <WinAPIEx.au3>

Local Const $g_sPath = @ScriptDir

Global $hDir = _WinAPI_FindFirstChangeNotification($g_sPath, $FILE_NOTIFY_CHANGE_LAST_WRITE)

Local $iID, $bBoolean = False
While 1
    Sleep(1000)
    ;Adapted from Help File example for _WinAPI_FindFirstChangeNotification function
    ;My tests without the second parameter seems to have the issue 'can't exit normally' described here:
    ;https://www.autoitscript.com/forum/topic/180602-readdirectorychangesw-exit/
    $iID = _WinAPI_WaitForSingleObject($hDir, 0)
    Switch $iID
        Case 0 ; WAIT_OBJECT_0
            If $bBoolean = True Then
               $bBoolean = False
            Else
               $bBoolean = True
               ConsoleWrite('A file was changed in the directory.' & @CRLF)
               Beep(400,50)
            EndIf
        Case Else
            ContinueLoop
    EndSwitch
    If Not _WinAPI_FindNextChangeNotification($hDir) Then
        MsgBox(0, 'Error', 'Unexpected error.')
        Exit
    EndIf
WEnd

Func OnAutoItExit()
    _WinAPI_FindCloseChangeNotification($hDir)
    ConsoleWrite('Bye-bye!' & @CRLF)
EndFunc

For those interested in the data transfer application, this is what i'm using:

Main.au3

#pragma compile(AutoItExecuteAllowed, True)

#include <WinAPIEx.au3>

;test directory:
Global Const $g_sPath = "C:\Documents and Settings\XP\Escritorio\InterPC\bridge"

Global $hDir = _WinAPI_FindFirstChangeNotification($g_sPath, $FILE_NOTIFY_CHANGE_LAST_WRITE)

Global $sFileRead
Local $aArray

Local $iID, $bBoolean = False

While 1
    Sleep(1000)
    $iID = _WinAPI_WaitForSingleObject($hDir, 0)
    Switch $iID
        Case 0 ; WAIT_OBJECT_0
            If $bBoolean = True Then
               $bBoolean = False
            Else
               $bBoolean = True
               ;ConsoleWrite('A file was changed in the directory.' & @CRLF)
               ReadLine()
               ;_RunAU3(".\scripts\AU3_Example.au3", $sFileRead, "", @SW_SHOW, 8)
               ;in $sFileRead there is a string with this format: AU3_Example.au3|parameter1 'parameter 2' ""parameter 3""
               $aArray = StringSplit($sFileRead, "|")
               _RunAU3(".\scripts\" & $aArray[1], $aArray[2], "", @SW_SHOW, 8)
            EndIf
        Case Else
            ContinueLoop
    EndSwitch
    If Not _WinAPI_FindNextChangeNotification($hDir) Then
        MsgBox(0, 'Error', 'Unexpected error.')
        Exit
    EndIf
WEnd

Func OnAutoItExit()
    _WinAPI_FindCloseChangeNotification($hDir)
    ;ConsoleWrite('Bye-bye!' & @CRLF)
EndFunc

Func ReadLine()
   Local Const $sFilePath = $g_sPath & "\line.txt"
   ;Open the file for reading and store the handle to a variable.
   Local $hFileOpen = FileOpen($sFilePath, $FO_READ)
   If $hFileOpen = -1 Then
     MsgBox(0, "", "An error occurred when reading the file.")
     Return False
   EndIf

   ;Read the fist line of the file using the handle returned by FileOpen.
   $sFileRead = FileReadLine($hFileOpen, 1)

   ;Close the handle returned by FileOpen.
   FileClose($hFileOpen)
   ;Display the first line of the file.
   ;MsgBox($MB_SYSTEMMODAL, "", $sFileRead)
EndFunc

;https://www.autoitscript.com/forum/topic/135203-call-another-script/?do=findComment&comment=943199
;guinness
Func _RunAU3($sFilePath, $sParamet="", $sWorkingDir = "", $iShowFlag = @SW_SHOW, $iOptFlag = 0)
   Return Run('"' & @AutoItExe & '" /AutoIt3ExecuteScript "' & $sFilePath & '" "' & $sParamet & '"', $sWorkingDir, $iShowFlag, $iOptFlag)
   ;Return Run('"' & @AutoItExe & '" /AutoIt3ExecuteScript "' & $sFilePath & '"' & ' "Bos Días"', $sWorkingDir, $iShowFlag, $iOptFlag)
EndFunc   ;==>_RunAU3

UPDATE: I'm finally using a slightly different version of the 'Main' script. It seems more simple: without the boolean variable:

#pragma compile(AutoItExecuteAllowed, True)
;#pragma compile(Icon, local_gray.ico)

#include <WinAPIEx.au3>

#include <Misc.au3>
If _Singleton("test", 1) = 0 Then
   Msgbox(48,"Warning","already running." & @LF & "This instance will close.", 5)
   Exit
EndIf

OnAutoItExitRegister("OnAutoItExit")

Global $g_sPath, $sFileRead, $aArray, $iID
$g_sPath = "C:\Documents and Settings\XP\Escritorio\InterPC\bridge"

Global $hDir = _WinAPI_FindFirstChangeNotification($g_sPath, $FILE_NOTIFY_CHANGE_LAST_WRITE)

While 1
   Sleep(500)
   CheckFile()
WEnd

Func CheckFile()
   $iID = _WinAPI_WaitForSingleObject($hDir, 0)
   If $iID = 0 Then ; WAIT_OBJECT_0
      ;ConsoleWrite('A file was changed in the directory.' & @CRLF)
      Local $sFilePath = $g_sPath & "\line.txt"
      ;Open the file for reading and store the handle to a variable.
      Local $hFileOpen = FileOpen($sFilePath, $FO_READ)
      If $hFileOpen = -1 Then
        MsgBox(0, "", "An error occurred when reading the file.")
        Return False
      EndIf
      ;Read the fist line of the file using the handle returned by FileOpen.
      $sFileRead = FileReadLine($hFileOpen, 1)
      ;Close the handle returned by FileOpen.
      FileClose($hFileOpen)

      ;_RunAU3(".\scripts\AU3_Example.au3", $sFileRead, @ScriptDir, @SW_SHOW, 8)
      ;in $sFileRead there is a string with this format: AU3_Example.au3|parameter1 'parameter 2' ""parameter 3""
      $aArray = StringSplit($sFileRead, "|")
      _RunAU3(".\scripts\" & $aArray[1], $aArray[2], @ScriptDir, @SW_SHOW, 8)
      ;SoundPlay(@WindowsDir & "\media\tada.wav", 0) ; Chimes.wav
   EndIf
   If Not _WinAPI_FindNextChangeNotification($hDir) Then
      MsgBox(48, 'Error', 'Bridge folder not found: revise settings.ini')
      Exit
   EndIf
EndFunc

;https://www.autoitscript.com/forum/topic/135203-call-another-script/?do=findComment&comment=943199
;guinness
Func _RunAU3($sFilePath, $sParamet="", $sWorkingDir = "", $iShowFlag = @SW_SHOW, $iOptFlag = 0)
   Return Run('"' & @AutoItExe & '" /AutoIt3ExecuteScript "' & $sFilePath & '" "' & $sParamet & '"', $sWorkingDir, $iShowFlag, $iOptFlag)
   ;Return Run('"' & @AutoItExe & '" /AutoIt3ExecuteScript "' & $sFilePath & '"' & ' "Bos Días"', $sWorkingDir, $iShowFlag, $iOptFlag)
EndFunc   ;==>_RunAU3

Func OnAutoItExit()
    _WinAPI_FindCloseChangeNotification($hDir)
EndFunc

'bridge' is the folder being monitored, and 'scripts' is another folder containing the scripts to execute when a change is detected

Here an example in the scripts folder:

AU3_Example.au3

MsgBox(0, "It Works", "Hello World, " & $CmdLine[1])

ConsoleWrite("Hello World from ConsoleWrite!, " & $CmdLine[1])

This is an example of an UPDATE statement with a MS ACCESS database in the server computer using the ADODB command method

;Help: COM Error Handling
Global $errADODB = ObjEvent("AutoIt.Error","_ErrADODB")

If $CmdLine[0] = 0 Then Exit ;there is no parameter passed to the executable

Local $sFilename = "_____"

Global Const $iCursorType = 0 ; adOpenForwardOnly
Global Const $iLockType = 1 ;1 adLockReadOnly, 3 adLockOptimistic
Global Const $iOptions = 1 ; Options, 1 Evaluates as a textual definition of a command or stored procedure call ; 2 adCmdTable
Global $cn = ObjCreate("ADODB.Connection") ; Create a connection object
;Global $sADOConnectionString = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & $sFilename
Global $sADOConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & $sFilename & ";Jet OLEDB:Database Password=___;"
$cn.Open($sADOConnectionString) ; Open the connection

Global $cmd = ObjCreate("ADODB.Command")
$cmd.ActiveConnection = $cn

Global $sSQL = $CmdLine[1]
$cmd.CommandText = $sSQL
$cmd.CommandType = 1 ;adCmdText

;https://www.w3schools.com/asp/ado_ref_command.asp
;https://www.autoitscript.com/forum/topic/80351-adodb-command-method/
$cmd.Execute(Default,Default, 0x80)

$cmd = 0
$cn.Close ; Close the connection
$cn = 0 ; Release the connection object

Func _ErrADODB()
   Msgbox(0,"ADODB COM Error","We intercepted a COM Error !"      & @CRLF  & @CRLF & _
       "err.description is: "    & @TAB & $errADODB.description    & @CRLF & _
       "err.windescription:"     & @TAB & $errADODB.windescription & @CRLF & _
       "err.number is: "         & @TAB & hex($errADODB.number,8)  & @CRLF & _
       "err.lastdllerror is: "   & @TAB & $errADODB.lastdllerror   & @CRLF & _
       "err.scriptline is: "     & @TAB & $errADODB.scriptline     & @CRLF & _
       "err.source is: "         & @TAB & $errADODB.source         & @CRLF & _
       "err.helpfile is: "       & @TAB & $errADODB.helpfile       & @CRLF & _
       "err.helpcontext is: "    & @TAB & $errADODB.helpcontext _
      )
   Local $err = $errADODB.number
   If $err = 0 Then $err = -1
EndFunc

An example of a SELECT query, saving the recordset as xml in a shared folder

;Help: COM Error Handling
Global $errADODB = ObjEvent("AutoIt.Error","_ErrADODB")

If $CmdLine[0] = 0 Then Exit ;there is no parameter passed to the executable

Local $sFilename = "_somepath_\MyDataBase.mdb"
Local $sFilename2 = "_somepath_\InterPC\out\example.xml"
FileDelete($sFilename2)

Global Const $iCursorType = 0 ; adOpenForwardOnly
Global Const $iLockType = 1 ;1 adLockReadOnly, 3 adLockOptimistic
Global Const $iOptions = 1 ; Options, 1 Evaluates as a textual definition of a command or stored procedure call ; 2 adCmdTable
Global $cn = ObjCreate("ADODB.Connection") ; Create a connection object
Global $rst = ObjCreate("ADODB.Recordset") ; Create a recordset object
;Global $sADOConnectionString = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & $sFilename
Global $sADOConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & $sFilename & ";Jet OLEDB:Database Password=__;Mode=Read"
$cn.Open($sADOConnectionString) ; Open the connection

Local $sSQL = "SELECT ____" _
  & " FROM __" _
  & " WHERE " & $CmdLine[1] _
  & " ORDER BY __;"

$rst.Open($sSQL, $cn, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query
$rst.Save($sFilename2, 1)
$rst.Close

$rst = 0 ; Release the recordset object
$cn.Close ; Close the connection
$cn = 0 ; Release the connection object

Func _ErrADODB()
   Msgbox(0,"ADODB COM Error","We intercepted a COM Error !"      & @CRLF  & @CRLF & _
       "err.description is: "    & @TAB & $errADODB.description    & @CRLF & _
       "err.windescription:"     & @TAB & $errADODB.windescription & @CRLF & _
       "err.number is: "         & @TAB & hex($errADODB.number,8)  & @CRLF & _
       "err.lastdllerror is: "   & @TAB & $errADODB.lastdllerror   & @CRLF & _
       "err.scriptline is: "     & @TAB & $errADODB.scriptline     & @CRLF & _
       "err.source is: "         & @TAB & $errADODB.source         & @CRLF & _
       "err.helpfile is: "       & @TAB & $errADODB.helpfile       & @CRLF & _
       "err.helpcontext is: "    & @TAB & $errADODB.helpcontext _
      )
   Local $err = $errADODB.number
   If $err = 0 Then $err = -1
EndFunc

Finally an example of an INSERT query:

;Help: COM Error Handling
Global $errADODB = ObjEvent("AutoIt.Error","_ErrADODB")

If $CmdLine[0] = 0 Then Exit ;there is no parameter passed to the executable
Local $aArray
$aArray = StringSplit($CmdLine[1], Chr(1))

Local $sSQL, $IdCab, $sYear
$sYear = @YEAR

Global Const $iCursorType = 0 ; adOpenForwardOnly, 3 adOpenStatic
Global Const $iLockType = 3 ;1 adLockReadOnly, 3 adLockOptimistic
Global Const $iOptions = 1 ; Options, 1 Evaluates as a textual definition of a command or stored procedure call ; 2 adCmdTable
Global $cn = ObjCreate("ADODB.Connection") ; Create a connection object
Global $rst = ObjCreate("ADODB.Recordset") ; Create a recordset object
;Global $sADOConnectionString = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & $sFilename
;Global $sADOConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & $sFilename & ";Jet OLEDB:Database Password=__"
$cn.CursorLocation = 3 ; adUseClient
$cn.Open($aArray[1]) ; Open the connection

;this ok
;$sSQL = "CREATE TABLE AutoIncrementTest " _
; & "(ID int identity, Description varchar(40), " _
; & "CONSTRAINT AutoIncrementTest_PrimaryKey PRIMARY KEY (ID))"
;$cn.Execute($sSQL, Default, 1 + 0x80)  ;adCmdText = 1 , adExecuteNoRecords = 0x80

;this ok
;Global $cmd = ObjCreate("ADODB.Command")
;$cmd.ActiveConnection = $cn
;$cmd.CommandText = $sSQL
;$cmd.CommandType = 1 ;adCmdText
;https://www.w3schools.com/asp/ado_ref_command.asp
;https://www.autoitscript.com/forum/topic/80351-adodb-command-method/
;$cmd.Execute(Default,Default, 1 + 0x80)

$sSQL = "SELECT MAX(Val(SOMEFIELD)) FROM SOMETABLE"
$rst.Open($sSQL, $cn, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query
Local $CodAlb = StringFormat("%06i", $rst(0).Value + 1)
;MsgBox(0,"","New code Nº: " & $CodAlb)
$rst.Close

;$sSQL = "SELECT ID, Description FROM AutoIncrementTest"
$sSQL = "SELECT ID, YEAR, FIELD2, FIELD3, ____________ FROM MYTABLE"
$rst.Open($sSQL, $cn, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query
$cn.BeginTrans
$rst.AddNew
;$rst("Description").Value = "AutoIncrement Test"
;$rst.Fields("Description") = "AutoIncrement Test"
$rst("YEAR").Value = $sYear
$rst("CODIGO").Value = $CodAlb
$rst("FIELD2").Value = $aArray[2]
$rst("FIELD3").Value = $aArray[5]
;...
$rst.Update
$IdCab = $rst(0).Value
;MsgBox(0,"","New Auto-increment value is: " & $IdCab)
$rst.Close

$sSQL = "UPDATE OTHERTABLE SET SOMEFIELD = " & $aArray[15] & " WHERE LINEASPEDIDO.ID = " & $aArray[14]
$cn.Execute($sSQL, Default, 1 + 0x80)  ;adCmdText = 1 , adExecuteNoRecords = 0x80

$cn.CommitTrans
;$cmd = 0
$rst = 0 ; Release the recordset object
$cn.Close ; Close the connection
$cn = 0 ; Release the connection object

Func _ErrADODB()
   Msgbox(0,"ADODB COM Error","We intercepted a COM Error !"      & @CRLF  & @CRLF & _
       "err.description is: "    & @TAB & $errADODB.description    & @CRLF & _
       "err.windescription:"     & @TAB & $errADODB.windescription & @CRLF & _
       "err.number is: "         & @TAB & hex($errADODB.number,8)  & @CRLF & _
       "err.lastdllerror is: "   & @TAB & $errADODB.lastdllerror   & @CRLF & _
       "err.scriptline is: "     & @TAB & $errADODB.scriptline     & @CRLF & _
       "err.source is: "         & @TAB & $errADODB.source         & @CRLF & _
       "err.helpfile is: "       & @TAB & $errADODB.helpfile       & @CRLF & _
       "err.helpcontext is: "    & @TAB & $errADODB.helpcontext _
      )
   Local $err = $errADODB.number
   If $err = 0 Then $err = -1
EndFunc

 

Edited by robertocm
Add an example
2 people like this

Share this post


Link to post
Share on other sites



Thank you for posting this.  I've been thinking about a method where a script would monitor a shared directory for new content.  But I wasn't aware of the OS-level directory monitoring that's available with _WinAPI_FindNextChangeNotification().  My plan is to build an in-house messaging capability, where a message can be a new document or a new image or even a new executable ... and let the receiving script decide what to do with it.

Your example scripts look like an excellent starting point.

 

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

I'm usign for sending automated reports from excel:

  1. Windows Task Scheduler runs a script in client computer to start the server 'main.exe' (because it's closed after hours of inactivity).
  2. Excel macro in client edits 'line.txt', check until xml recordset file is released, reads the xml recordset and send html formated mail with cdo

Here an example for the first step:

#include <Excel.au3>
#include <FileConstants.au3>
#include <Clipboard.au3>
;#include <Debug.au3>

Local $sFilePath2 = "\\MyServer\_somepath_\bridge\line.txt"
Local $sFilePath4 = "\\MyServer\_somepath_\Main.exe"
Local $sFilePath5 = "C:\Users\myusername\Desktop\log.txt"

Local $aFileOpen
$aFileOpen = _FileInUse($sFilePath4, 1)
If $aFileOpen = 0 Then
   _ClipBoard_SetData("A:\_somelocalpathinserver_\Main.exe")
   Run("C:\windows\system32\mstsc.exe /v:" & "__.__.__.__")
   Sleep(15000)
   Send ("#r")
   Sleep(1000)
   Send("{CTRLDOWN}v{CTRLUP}")
   Sleep(1000)
   Send("{ENTER}")
   Sleep(1000)
   ;Alt+F4
   Send("!{F4}")
   Sleep(1000)
   Send("d")
   Sleep(1000)
   Send("{ENTER}")
   Sleep(2000)

  ;log file
  ;Open the file for write access.
  Local $hFileOpen0 = FileOpen($sFilePath5, $FO_OVERWRITE)
  If $hFileOpen0 = -1 Then
    MsgBox(0, "", "An error occurred when reading/writing the file.")
  EndIf
  ;Write some data.
  FileWrite($hFileOpen0, "Started")
  ;Close the handle returned by FileOpen.
  FileClose($hFileOpen0)
EndIf

;https://www.autoitscript.com/forum/topic/168939-run-excel-vba-macro-via-autoit/?do=findComment&comment=1233975
;_DebugSetup()
;_DebugCOMError()
Local $oAppl = _Excel_Open()
;If @error Then Exit MsgBox(0, "Error", "Error _Excel_Open" & @CRLF & "@error = " & @error & ", @extended = " & @extended)

;Local $oAppl = _Excel_Open(False)  ;open Excel hideen
Local $sFilePath = "_somepath_\somefile.xlsm"
Local $oWorkbook = _Excel_BookOpen($oAppl, $sFilePath)
;If @error Then Exit MsgBox(0, "Error", "Error _Excel_BookOpen: " & $sFilePath & @CRLF & "@error = " & @error & ", @extended = " & @extended)

;https://www.autoitscript.com/forum/topic/162796-having-an-issue-with-the-excel-run-command-returning-weird-error/
;Local $sSheet = $CmdLine[2]
;$oWorkbook.Sheets.Item($sSheet).Activate
;If @error Then Exit MsgBox(0, "Error", "Error Excel.Sheets.Item.Activate: " & $sSheet & @CRLF & "@error = " & @error & ", @extended = " & @extended)

;$oAppl.Run("DEFAULT_MACRO")
;$oAppl.Run("Módulo1.DEFAULT_MACRO")
;$oAppl.Run("reports.xlsm!Actual.Tanques") ;descartada

$oAppl.Run("somefile.xlsm!SomeMacroName1")
;If @error Then Exit MsgBox(0, "Error", "Error Excel.Run: SomeMacroName1" & @CRLF & "@error = " & @error & ", @extended = " & @extended)
$oAppl.Run("somefile.xlsm!SomeMacroName2")
;EndIf

_Excel_BookClose($oWorkbook, True)
;If @error Then Exit MsgBox(0, "Error", "Error _Excel_BookClose: " & $sFilePath & @CRLF & "@error = " & @error & ", @extended = " & @extended)

_Excel_Close($oAppl)
;If @error Then Exit MsgBox(0, "Error", "Error _Excel_Close" & @CRLF & "@error = " & @error & ", @extended = " & @extended)

;from this post:
;Need help with copy verification
;http://www.autoitscript.com/forum/index.php?showtopic=53994
;===============================================================================
;
; Function Name:    _FileInUse()
; Description:      Checks if file is in use
; Syntax.........: _FileInUse($sFilename, $iAccess = 1)
; Parameter(s):     $sFilename = File name
; Parameter(s):     $iAccess = 0 = GENERIC_READ - other apps can have file open in readonly mode
;                   $iAccess = 1 = GENERIC_READ|GENERIC_WRITE - exclusive access to file,
;                   fails if file open in readonly mode by app
; Return Value(s):  1 - file in use (@error contains system error code)
;                   0 - file not in use
;                   -1 dllcall error (@error contains dllcall error code)
; Author:           Siao
; Modified          rover - added some additional error handling, access mode
; Remarks           _WinAPI_CreateFile() WinAPI.au3
;===============================================================================
Func _FileInUse($sFilename, $iAccess = 0)
    Local $aRet, $hFile, $iError, $iDA
    Local Const $GENERIC_WRITE = 0x40000000
    Local Const $GENERIC_READ = 0x80000000
    Local Const $FILE_ATTRIBUTE_NORMAL = 0x80
    Local Const $OPEN_EXISTING = 3
    $iDA = $GENERIC_READ
    If BitAND($iAccess, 1) <> 0 Then $iDA = BitOR($GENERIC_READ, $GENERIC_WRITE)
    $aRet = DllCall("Kernel32.dll", "hwnd", "CreateFile", _
                                    "str", $sFilename, _ ;lpFileName
                                    "dword", $iDA, _ ;dwDesiredAccess
                                    "dword", 0x00000000, _ ;dwShareMode = DO NOT SHARE
                                    "dword", 0x00000000, _ ;lpSecurityAttributes = NULL
                                    "dword", $OPEN_EXISTING, _ ;dwCreationDisposition = OPEN_EXISTING
                                    "dword", $FILE_ATTRIBUTE_NORMAL, _ ;dwFlagsAndAttributes = FILE_ATTRIBUTE_NORMAL
                                    "hwnd", 0) ;hTemplateFile = NULL
    $iError = @error
    If @error Or IsArray($aRet) = 0 Then Return SetError($iError, 0, -1)
    $hFile = $aRet[0]
    If $hFile = -1 Then ;INVALID_HANDLE_VALUE = -1
        $aRet = DllCall("Kernel32.dll", "int", "GetLastError")
        ;ERROR_SHARING_VIOLATION = 32 0x20
        ;The process cannot access the file because it is being used by another process.
        If @error Or IsArray($aRet) = 0 Then Return SetError($iError, 0, 1)
        Return SetError($aRet[0], 0, 1)
    Else
        ;close file handle
        DllCall("Kernel32.dll", "int", "CloseHandle", "hwnd", $hFile)
        Return SetError(@error, 0, 0)
    EndIf
EndFunc

Below an example of the vba code for the second part (FileLocked function is from Bill Jelen ('Mr. Excel'))

With Application
  .EnableEvents = False
  .Calculation = xlCalculationManual
  .ScreenUpdating = False
End With

Dim ws As Worksheet
Set ws = Sheets("Sheet1")
Dim iFileNum As Integer, sFilePath As String, sFileName As String, sCmd As String, i As Integer
sFilePath = "_somepath_\bridge\line.txt"
'sCmd = "AU3_Example.a3x|parameter1 'parameter 2' ""parameter 1A"""
sCmd = "SomeScriptName.a3x|" & sWhere
iFileNum = FreeFile
Open sFilePath For Output As iFileNum
Print #iFileNum, sCmd
Close iFileNum

sFilePath = "_somepath\out\Comp2.xml"
On Error Resume Next
Kill sFilePath

Dim Pausa As Long, Inicio As Long
Pausa = 0.1
'Check if file exists
For i = 0 To 30
  'Debug.Print i
  sFileName = Dir(sFilePath)
  If sFileName <> "" Then
    Do
      If FileLocked(sFilePath) Then
        Inicio = Timer
        Do While Timer < Inicio + Pausa
          DoEvents
        Loop
       Else: Exit Do
       End If
    Loop
    Exit For
  Else: Application.Wait (Now + TimeValue("00:00:01"))
  End If
Next

On Error GoTo ExceptionHandling

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset


rst.Open sPathAceites & "_somepath_\out\Comp.xml", "Provider=MSPersist;", , , adCmdFile

With ws.ListObjects("TB_SomeTable1")
  .DataBodyRange.ClearContents
  .DataBodyRange(1, 1).CopyFromRecordset rst
End With

rst.Close

rst.Open sPathAceites & "_somepath_\out\Comp2.xml", "Provider=MSPersist;", , , adCmdFile
With ws.ListObjects("TB_SomeTable2")
  .DataBodyRange.ClearContents
  .DataBodyRange(1, 1).CopyFromRecordset rst
End With
rst.Close

CleanUp:
 With Application
   .EnableEvents = True
   .Calculation = xlCalculationAutomatic
   .ScreenUpdating = True
 End With
 On Error Resume Next
 cn.Close
 Set rst = Nothing
 Set cn = Nothing
 Exit Sub
ExceptionHandling:
 msgbox "Error: " & Err.Description
 Resume CleanUp
End Sub

 

Edited by robertocm

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

This is a more elaborated version of 'Main.au3':

  • the path to the bridge folder can be edited and saved in .ini file
  • save mail credentials, because i'm trying to send mail reports
#pragma compile(AutoItExecuteAllowed, True)
;#pragma compile(Icon, myicon.ico)

#include <Misc.au3>
#include <WinAPIEx.au3>
#include <GUIConstantsEx.au3>
#include <EditConstants.au3>
;https://www.autoitscript.com/forum/topic/128201-ini-crypt/
;SmOke_N ( Ron Nielsen )
#include "IniCrypt.au3"

If _Singleton("test", 1) = 0 Then
   Msgbox(48,"Warning","already running." & @LF & "This instance will close.", 5)
   Exit
EndIf

OnAutoItExitRegister("OnAutoItExit")
Opt("TrayMenuMode", 3) ; The default tray menu items will not be shown and items are not checked when selected. These are options 1 and 2 for TrayMenuMode.

#NoTrayIcon
TraySetIcon ("shell32.dll", -177)  ;-131, -42
;TraySetIcon("myicon.ico")

Global $g_sPath, $sFileRead, $aArray, $iID

;Read the INI file
If FileExists("settings.ini") Then
   $g_sPath = IniRead("settings.ini", "General", "BridgePath", @ScriptDir & "\bridge")
Else
   $g_sPath = @ScriptDir & "\bridge"
EndIf

Global $hDir = _WinAPI_FindFirstChangeNotification($g_sPath, $FILE_NOTIFY_CHANGE_LAST_WRITE)

Global $IniCryptPwd = "mypassword" ;change this to a value only known to yourself prior to compile! DO NOT LEAVE IT AS "mypassword" FOR PROPER SECURITY!

Local $idBridgePath = TrayCreateItem("Settings")
Local $idCredentials = TrayCreateItem("Credentials")
Local $idTestCredentials = TrayCreateItem("test using credentials")
TrayCreateItem("") ; Create a separator line.
Local $idExit = TrayCreateItem("Exit")
TraySetState()

Local $nMsg
While 1
   $nMsg = TrayGetMsg()
   Switch $nMsg
      Case 0
         CheckFile()
         ContinueLoop
      Case $idBridgePath
          BridgePath()
      Case $idCredentials
          Credentials()
      Case $idTestCredentials
          TestUsingCredentials()
      Case $idExit
          Exit
   EndSwitch
WEnd

Func CheckFile()
   Sleep(1000)
   $iID = _WinAPI_WaitForSingleObject($hDir, 0)
   If $iID = 0 Then ; WAIT_OBJECT_0
      ;ConsoleWrite('A file was changed in the directory.' & @CRLF)
      Local $sFilePath = $g_sPath & "\line.txt"
      ;Open the file for reading and store the handle to a variable.
      Local $hFileOpen = FileOpen($sFilePath, $FO_READ)
      If $hFileOpen = -1 Then
        MsgBox(0, "", "An error occurred when reading the file.")
        Return False
      EndIf
      ;Read the fist line of the file using the handle returned by FileOpen.
      $sFileRead = FileReadLine($hFileOpen, 1)
      ;Close the handle returned by FileOpen.
      FileClose($hFileOpen)

      ;_RunAU3(".\scripts\AU3_Example.au3", $sFileRead, "", @SW_SHOW, 8)
      ;in $sFileRead there is a string with this format: AU3_Example.au3|parameter1 'parameter 2' ""parameter 3""
      $aArray = StringSplit($sFileRead, "|")
      _RunAU3(".\scripts\" & $aArray[1], $aArray[2], "", @SW_SHOW, 8)
   EndIf
   If Not _WinAPI_FindNextChangeNotification($hDir) Then
      MsgBox(48, 'Error', 'Bridge folder not found: revise settings.ini')
      Exit
   EndIf
EndFunc

;https://www.autoitscript.com/forum/topic/135203-call-another-script/?do=findComment&comment=943199
;guinness
Func _RunAU3($sFilePath, $sParamet="", $sWorkingDir = "", $iShowFlag = @SW_SHOW, $iOptFlag = 0)
   Return Run('"' & @AutoItExe & '" /AutoIt3ExecuteScript "' & $sFilePath & '" "' & $sParamet & '"', $sWorkingDir, $iShowFlag, $iOptFlag)
   ;Return Run('"' & @AutoItExe & '" /AutoIt3ExecuteScript "' & $sFilePath & '"' & ' "Bos Días"', $sWorkingDir, $iShowFlag, $iOptFlag)
EndFunc   ;==>_RunAU3

Func BridgePath()
   Local $MyStr1
   Local $hGUI = GUICreate('Settings', 800, 125)
   GUISetBkColor(0xA9A9A9)
   GUISetIcon("shell32.dll", -58, $hGUI)
   GUISetFont(10, 0, 0, 'Segoe UI')

   Local $idBridgePath = GUICtrlCreateInput('', 10, 10, 700, 30)
   Local $idBridgeBrowse = GUICtrlCreateButton("...", 710, 10, 40, 30)
   GUICtrlCreateLabel("Path to Bridge Folder", 8, 40, 400, 30)
   Local $idSave = GUICtrlCreateButton("SAVE", 400, 60, 100, 40)
   GUICtrlSetBkColor($idSave, 0xF0F0F0)
   ;GUICtrlSetState($idSave, 512) ; allows enter inside input boxes

   GUISetState(@SW_SHOW, $hGUI)

   Local $msg2, $sBridgePath
   While 1
   $msg2 = GuiGetMsg()
      Switch $msg2
         Case $GUI_EVENT_CLOSE
            ExitLoop
         Case $idBridgeBrowse
            $sBridgePath = FileSelectFolder("Browse to the 'bridge' folder", "")
            If @error Then
               ContinueLoop
            EndIf
            GUICtrlSetData($idBridgePath, $sBridgePath)
         Case $idSave, $idBridgePath
            $MyStr1 = GUICtrlRead($idBridgePath)
            If $MyStr1 <> "" Then
               IniWrite("settings.ini", "General", "BridgePath", $MyStr1)
               _WinAPI_FindCloseChangeNotification($hDir)
               $g_sPath = $MyStr1
               $hDir = _WinAPI_FindFirstChangeNotification($g_sPath, $FILE_NOTIFY_CHANGE_LAST_WRITE)
            EndIf
            ExitLoop
      EndSwitch
   WEnd
   GUIDelete($hGUI)
EndFunc

Func Credentials()
Local $hGUI = GUICreate('Mail credentials', 300, 200)
GUISetBkColor(0xA9A9A9)
GUISetIcon("shell32.dll", -49, $hGUI)
GUISetFont(10, 0, 0, 'Segoe UI')

Local $idUser = GUICtrlCreateInput('', 10, 10, 280, 30)
GUICtrlCreateLabel("User", 8, 40, 400, 30)
Local $idPwd = GUICtrlCreateInput('', 10, 70, 280, 30, $ES_PASSWORD)
GUICtrlCreateLabel("Password", 8, 100, 400, 30)

Local $idSave = GUICtrlCreateButton("SAVE", 100, 150, 100, 40)

GUICtrlSetBkColor($idSave, 0xF0F0F0)

GUISetState(@SW_SHOW, $hGUI)

While 1
$msg2 = GuiGetMsg()
   Switch $msg2
      Case $GUI_EVENT_CLOSE
         GUIDelete($hGUI)
         ExitLoop
      Case $idUser
         ;GUICtrlSetState($idPwd, 256)
         ;GUICtrlSetState($idPwd, $GUI_FOCUS)
         ControlFocus($hGUI, "", $idPwd)
         ContinueLoop
      Case $idSave, $idPwd
         ;SmOke_N ( Ron Nielsen )
         ;https://www.autoitscript.com/forum/topic/128201-ini-crypt/
         ;read notes: 'The sections and keys are lower cased when written to and read from the ini-file'
         FileDelete("credentials.ini")
         _IniCrypt_Initiate()
         _IniCrypt_SetPassword($IniCryptPwd)  ;change this to a value only known to yourself prior to first use!  DO NOT LEAVE IT AS "mypassword" FOR PROPER SECURITY!
         Local $ga_data[2][2] = [ [ "user", GUICtrlRead($idUser) ], [ "password", GUICtrlRead($idPwd) ] ]
         _IniCrypt_WriteSection("credentials.ini", "section1", $ga_data, 0)
         _IniCrypt_Shutdown()
         GUIDelete($hGUI)
         ExitLoop
   EndSwitch
WEnd
EndFunc

Func TestUsingCredentials()
   _IniCrypt_Initiate()
   _IniCrypt_SetPassword($IniCryptPwd)
   Local $ga_data = _IniCrypt_ReadSection("credentials.ini", "section1")
   Msgbox(0,"", $ga_data[1][1])
   Msgbox(0,"", $ga_data[2][1])
   _IniCrypt_Shutdown()
EndFunc

Func OnAutoItExit()
    _WinAPI_FindCloseChangeNotification($hDir)
EndFunc

 

Edited by robertocm

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