PhilBall

Hi there I have created a script that will show the file open dialog with a filter on the first 3 digits of the filename. I can get it to work to open one file, but when it comes to opening multiple files, I have created a further script as per below, but cannot seem to get it to work. I'd appreciate a fresh pair of knowledgable eyes on this. thanks, very much, Phil #Region ;**** Directives created by AutoIt3Wrapper_GUI **** #AutoIt3Wrapper_icon=C:\Users\phil.PAYCHECK\Desktop\PDF.ICO #EndRegion ;**** Directives created by AutoIt3Wrapper_GUI **** #Include <Array.au3> $SC = InputBox("Open a PDF","Type the SC of the PDFs you want to see") $filter = "PDFs (" & $SC & "*.pdf)" $message = "Hold down Ctrl or Shift to choose multiple files." $var = FileOpenDialog($message, "S:\Shared\EXCEL\PDF\", $filter, 1 + 4 ) If @error Then MsgBox(4096,"","No File(s) chosen") Else $var = StringReplace($var, "S:\Shared\EXCEL\PDF", "") $var = StringReplace($var, "|", ",S:\Shared\EXCEL\PDF\") $files = StringSplit($var,",") EndIf ;_ArrayDisplay($files) $end = _ArrayMaxIndex($files) $n = 1 while $n < $end $n = $n + 1 ;msgbox(4096,"",$files[$n]) ShellExecute($files[$n]) WEnd

This was very usefull for me. I have managed to plagarise it for a process I needed and include it now for anyone else use or further comments/amendments. (BTW this searchs voer 2000 sub folders) Thanks, Phil #include<array.au3> #Include <Excel.au3> local $folder_list = r_search('P:TspiritUser') ;local $folder_list2 = r_search('P:TspiritUser') ;_arraydisplay($folder_list) FileDelete("P:TspiritUserPAYEBACSLOG.CSV") Func r_search ($s_dir) local $folders[1] local $files = FileFindFirstFile($s_dir & "*.*") If @error Then msgbox(0,'Filefindfirst Error',@error) While 1 $next = FileFindNextFile($files) If @error Then return($folders) If @extended then $folders[ubound($folders)-1] = $s_dir & $next redim $folders[ubound($folders) + 1] EndIf WEnd FileClose($files) EndFunc $end = _ArrayMaxIndex($folder_list) ;MsgBox(4096,"End",$end) $n = 0 $count = 0 ProgressOn("Progress Meter", "Increments every second", "0 percent") While $n < $end $n = $n + 1 ;$folder_list[$n] = $folder_list[$n] & "PAYEBYBACS.EXT" if FileExists($folder_list[$n] & "PAYEBYBACS.EXT") Then $count = $count + 1 ;MsgBox(4096,"File",$folder_list2[$n] & "PAYPBACS.EXT") ;MsgBox(4096,"File",$folder_list[$n] & "PAYEBYBACS.EXT") $ShortCode = StringRight($folder_list[$n],3) FileWrite("P:TspiritUserPAYEBACSLOG.CSV", $ShortCode & @CRLF) FileClose("P:TspiritUserPAYEBACSLOG.CSV") FileCopy($folder_list[$n] & "PAYEBYBACS.EXT",$folder_list[$n] & "PAYPBACS.EXT",1) FileDelete($folder_list[$n] & "PAYEBYBACS.EXT") EndIf ProgressSet(100/$end * $n ,"Done", "Complete") WEnd ProgressOff() MsgBox(4096,"The End","This is now complete") _ExcelBookOpen("P:TspiritUserPAYEBACSLOG.CSV")

Hi shornw thanks for that, school boy error coming from not having someone else to proof the code! We do need the sub directories as we are filing payroll reports by client and then by payroll month/week. It worked really well Thanks very much Phil

Hello I have written a script that is designe to read the filename and move it a to a directory and sub directory based on it's filename. The beginning of a file has a 3 digit shortcode and the end of a file has another code starting with either an M, W, F or T and then two digits. When I run the script, it creates the folder and subfolder, but then does not move the file. I have tried using the #RequireAdmin keyword but that then will not find the file in the first place. Here is my script: $SC = InputBox("Move PDF Files", "Enter Shortcode") iF @error = 1 Then MsgBox(0,"Exiting Move PDF Files", "This program is closing, none of your PDFs have been moved") Exit EndIf If $SC = "" Then MsgBox(0,"ERROR", "You did not enter a shortcode, try again") $SC = InputBox("Move PDF Files", "Enter Shortcode") If @error = 1 Then MsgBox(0,"Exiting Move PDF Files", "This program is closing, none of your PDFs have been moved") Exit EndIf EndIf $File_Name = "S:\SHARED\EXCEL\PDF\" & $SC & "*.PDF" $search = FileFindFirstFile($File_Name) ; Check if the search was successful If $search = -1 Then MsgBox(0, "Error", "No files/directories matched the search pattern") Exit EndIf While 1 $file = FileFindNextFile($search) If @error Then ExitLoop ;MsgBox(4096, "File:", $file) $tail = StringRight($file,7) $freq = Stringleft($tail,3) $NumTest = StringLEFT($tail,1) if $NumTEST = "M" OR $NumTEST = "W" OR $NumTEST = "T" OR $NumTEST = "F" Then ;MsgBox(4096,"Freq",$freq) MsgBox(4096,"FILENAME","S:\SHARED\EXCEL\PDF\PBTEST\" & $SC & "\" & $freq & "\" & $file) ;$FileAtt = FileGetAttrib ("S:\SHARED\EXCEL\PDF\PBTEST\" & $SC & "\" & $freq & "\" & $file ) ;If @error Then ;MsgBox(4096,"Error", "Could not obtain attributes.") ;Else ;MsgBox(4096,"Attributes",$FileAtt) ;EndIf FileCopy ($file, "S:\SHARED\EXCEL\PDF\PBTEST\" & $SC & "\" & $freq & "\" & $file,8) If @error = 0 Then MsgBox(0,"Error", "file did not move") EndIf EndIf WEnd ; Close the search handle FileClose($search) Thanks, Phil

Hi I think you miss understood me. The 100 files a day is the files being re named, not new PAYE references. They will only change if we have a new client. Only I will be carrying out this process and editing the CSV file as and when needed. The code is assigned manually, it is the reference in our payroll software to designate each different company's payroll. Phil

Worked it out for myself: #Include <Excel.au3> $sFilePath1 = "Y:\IN\PAYE1.CSV" $oExcel = _ExcelBookOpen($sFilePath1) #Include <Array.au3> $aArray = _ExcelReadSheetToArray($oExcel) ;Using Default Parameters _ExcelBookClose($oExcel) ; And finally we close out _ArrayDisplay($aArray, "Array using Default Parameters") $sSearch = "PAYE" If @error Then Exit $iIndex = _ArraySearch($aArray, $sSearch) If @error Then MsgBox(0, "Not Found", '"' & $sSearch & '" was not found in the array.') Else MsgBox(0, "Found", '"' & $sSearch & '" was found in the array at position ' & $iIndex & ".") $sEnd = "2" $payeref = $aArray[$iIndex][$sEnd] msgbox(0, "PAYE Ref", $payeref) EndIf

Sorry, yes, the 3 digit codes is the unique code we use to identify each company in our payroll software and therefore is used to identify the company throughout our administration and file structure here.

Hi Thanks You pretty much have it. This is something that will get quite busy once it goes live and I estimate us receiving 100 of these files each day. It's to do with individual employee's taxes rates, we process about 40,000 peoples salaries each month. The PAYE ref and 3 digit shortcode are actually stored in an access database if that can be used better than a CSV? The HMRC files will come in with a generic name and the only way to differentiate is to read the PAYE ref in the file. Each one designates a different business/client. The PAYE reference and 3 digit shortcode are semi permanent in that they do change but not very often. Plus new ones are added all the time. Cheers, Phil

I am hoping to search incoming files from HMRC and find the PAYE ref as per $payeref in the RENAME INCOMING FILE WITH PAYE REF.au3 but instead of renaming the file like that, using the PAYE ref to lookup in the PAYE1.csv and returning the 3 digit code that represents that ref. i.e. if the PAYE ref is PAYD the the filename will become ZZ1-HMRC-EDI-DATA.gff and if it is PAYE then the filename will become ZZ2-HMRC-EDI-DATA.gff. The HMRC.zip contains the 2 test files received from HMRC. When we actually use this the csv file will contain over 1000 PAYE ref (all unique) and the corresponding 3 digit shortcode. I hope that is as clear as mud!! Thanks, Phil PAYE1.zip RENAME INCOMING FILE WITH PAYE REF.au3 OPEN EXCEL.au3 HMRC.zip

I would be completely clueless with what to do and where to start or even anything to do with that!!

Thanks very much for your help. As you may tell, I am a little out of my depth. I have now got the Position of the text I was looking up from the _ArraySearch() Function, but am stumped as to how I can use that to return the text in the same row, the next cell over. I understand the math of it, but cannot find the right function to use to return the text. Can you help? Thanks, Phil

I uninstalled and re installed with the latest version and it worked. Thanks, Phil

Very strange, I ran your code and got the following result: >"C:\Program Files\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.exe" /run /prod /ErrorStdOut /in "Y:\IN\testfrom web.au3" /autoit3dir "C:\Program Files\AutoIt3" /UserParams +>09:37:46 Starting AutoIt3Wrapper v.2.0.0.3 Environment(Language:0409 Keyboard:00000809 OS:WIN_XP/Service Pack 3 CPU:X64 OS:X86) >Running AU3Check (1.54.14.0) from:C:\Program Files\AutoIt3 +>09:37:46 AU3Check ended.rc:0 >Running:(3.3.0.0):C:\Program Files\AutoIt3\autoit3.exe "Y:\IN\testfrom web.au3" Not Found: "PAYE" was not found in the array. +>09:37:48 AutoIT3.exe ended.rc:0 +>09:37:49 AutoIt3Wrapper Finished >Exit code: 0 Time: 3.566 Do you think there is something wrong with my AutoIT?

Well I managed to get the _ExcelReadSheetToArray() to work, but not the _ArraySearch(). #Include <Excel.au3> $sFilePath1 = "Y:\IN\PAYE1.CSV" $oExcel = _ExcelBookOpen($sFilePath1) #Include <Array.au3> $aArray = _ExcelReadSheetToArray($oExcel) ;Using Default Parameters _ExcelBookClose($oExcel) ; And finally we close out _ArrayDisplay($aArray, "Array using Default Parameters") #include <Array.au3> $sSearch = "PAYE" If @error Then Exit $iIndex = _ArraySearch($aArray, $sSearch, 0, 0, 0, 1) If @error Then MsgBox(0, "Not Found", '"' & $sSearch & '" was not found in the array.') Else MsgBox(0, "Found", '"' & $sSearch & '" was found in the array at position ' & $iIndex & ".") EndIf The csv file has: T_Reference,Shortcode_ID,Company Name PAYD,ZZ1, PAYE,ZZ2, Any thoughts? Thanks, Phil

All sounds wonderful. How on earth do I do that? Lookups in memeory after reading the excel sheet to an array, that is? Thanks