Thanks for the recent post. I hope you enjoyed your holiday time off! I have been using a modified version of your previous version to save a copy of the .csv file every 3 min. of the current log file. I have been simply stopping the scripts in the morning, manually renaming the file to the current date, then restarting the scripts (I have one I wrote which clicks the save icon every second on the scanner program since that is currently the only way to get it to save, different issue entirely, and the one you created with some minor tweaks). It seems to work in the background and hasn't given any errors or stopped running in the week since I started using it (except when they cut the power for the New Year shutdown, of course). I think that means that we can work with the log file while it is updating, so that test is done. Here is what I am using for this:
Code: Select all
#NoEnv ; Recommended for performance and compatibility with future AutoHotkey releases.
#Persistent
SetWorkingDir %A_ScriptDir% ; Ensures a consistent starting directory.
#SingleInstance force
LogFile := "C:\Users\chickman\Documents\terminal-01-02-2020.log" ; points to the log file
OutputFile := A_ScriptDir "\Scan Log 01-02-2020.csv" ; points to the output file
StdPltQty := A_ScriptDir "\Std pallet qty-loc.xlsx" ; points to the standard quantity file
CopyToFile := "R:\Tolleson\DuroShare\SW Scan Logs\Scan Log 01-02-2020.csv"
CSVHead := "Pallet ID,Quantity,Product ID,Loc. Code"
; Checks to make sure there's either the log file
; or Standard Quantity files(s) exist
s := FileExist( StdPltQty ), l := FileExist( LogFile )
if (!s||!l)
{
MsgBox, 0x10, Whoops!, % "Cannot Locate The " ( (b:=!s&&!l) ? "Log & The Standard Quantity"
: !s ? "Standard Quantity" : !l ? "Log" : "" ) " File" (b?"s":"") "!`nPoint To Or Install "
. (b?"Them":"It") " & Try Again!"
return
}
Loop
{
if FileExist( OutputFile ) ; if output file exists...
FileDelete % OutputFile ; ...delete it
if ( !IsObject( xlObj ) )
{
xlObj := ComObjCreate( "Excel.Application" ), xlObj.Visible := False ; Reads the Standard Pallet Quanity Location worksheet
xlObj.Workbooks.Open( StdPltQty )
}
Loop, Read, % LogFile, % ( OutputFile, PidArray := [] ) ; Reads & Loops through lines of logfile
{
FileAppend % ( A_Index = 1 ? CSVHead "`n" : "" ) ; add the column headings
Match := RegExMatch( A_LoopReadLine, "^.*?(?<pid>RO\d{11})", pallet_ ) ; looks for 13 char pallet ID in log line
if ( pallet_pid ) ; if 13 char pallet ID detected...
PidArray.push( pallet_pid ) ; ... push to pallet id to array
if ( Match && ( !InStr( A_LoopReadLine, PidArray[ PidArray.length()-1 ] ) || PidArray.length() = 1 ) ) ; checks for 13 char RO# & ignores duplicates
{
pallet_loc := "" ; standard location code reset
; Regular Expression Array to search for each value
NeedleArray := [ "[-`,](?<quantity>\d{1,3})\D", "\D(?<prid>\d{5}(-\d{1,3}|))\D", "\D(?<spid>RO\d{8})\D" ]
For Each, Needle in NeedleArray ; matches each value in current line
RegExMatch( A_LoopReadLine, Needle, pallet_ )
QtyRowObj := xlObj.Range( "A1" ).End( -4162 ).Find( pallet_prid, xlObj.Cells( 1 ), -4163, 2, 1, 1, 0, 0 ) ; Finds the pallet quantity based on product ID in Standard Pallet Quantity-Location worksheet
Try ( pallet_loc := xlObj.Range( "D" QtyRowObj.Row ).Value ) ; Finds the associated location code for each product ID
Catch
TrayTip, Warning!, % "Product ID: " pallet_prid " Skipped!`nLocation Code Not Found!",, 0x2
if ( pallet_spid && !pallet_quantity ) ; short pallet ID & no quantity, fills in the standard pallet quantity if missing from scan
pallet_quantity := Floor( xlObj.Range( "B" QtyRowObj.Row ).Value )
FileAppend % pallet_pid "," pallet_quantity "," pallet_prid "," pallet_loc "`n"
}
}
DetectHiddenWindows, On
xlHwnd := xlObj.Hwnd ; used the grab the Excel client process handle
; The below methods should close the workbook,
; quit ( kill ) the client, and free the object
xlObj.Workbooks.Close
( xlObj.Quit ), xlObj := ""
WinGet, xlPID, PID, % "ahk_id " xlHwnd ; used the get the Excel client process ID
Process, Close, % xlPID ; used the close / kill the Excel client process
if FileExist( OutputFile ) ; if output file exists...
FileCopy, %OutputFile%, %CopytoFile%,1
Sleep, 180000
}
return
Thanks again for all your help!
Chris