Amit_Kumar Posted April 7, 2023 Posted April 7, 2023 Hi People, I am facing trouble at line 256 in Func ReadStockFile($filePath). The idea is to open a spreadsheet and the complete program works when this spreadsheet is being accessed via local drives (C: or D:) however, I need to use this program on another PC where it opens the same spreadsheet from the same location via a mapped network drive (in this case Z:) and in this scenario the code doesn't work. If you look at lines 254 and 255 I have commented out the original variables and tried to open the spreadsheet directly using the path with drive letter (line 255) and path with network location (line 256) in both cases the func _Excel_BookOpen gives error 2, extended 0 (Specified $sFilePath does not exist). If I copy-paste either the drive letter path or network location path in explorer address bar and hit enter, in both cases the file opens. Attached is the original code and screenshot of the error message. Any advise / insight is appreciated. Stock 22.au3
Solution Amit_Kumar Posted April 7, 2023 Author Solution Posted April 7, 2023 Copy-pasting complete code for easy read: expandcollapse popup#RequireAdmin #Region ;**** Directives created by AutoIt3Wrapper_GUI **** #AutoIt3Wrapper_Icon=Old Files\icon.ico #AutoIt3Wrapper_Outfile_x64=Finder.exe #AutoIt3Wrapper_Res_Fileversion=0.0.0.12 #AutoIt3Wrapper_Res_Fileversion_AutoIncrement=y #AutoIt3Wrapper_Res_CompanyName=Happy Hosiery #AutoIt3Wrapper_Res_LegalCopyright=Amit Kumar #AutoIt3Wrapper_Res_requestedExecutionLevel=requireAdministrator #AutoIt3Wrapper_Run_Tidy=y #EndRegion ;**** Directives created by AutoIt3Wrapper_GUI **** #include <AutoItConstants.au3> #include <Array.au3> #include <Excel.au3> #include <MsgBoxConstants.au3> #include <EditConstants.au3> #include <GUIConstantsEx.au3> #include <GUIListBox.au3> #include <GuiListView.au3> #include <StaticConstants.au3> #include <WindowsConstants.au3> #include <FontConstants.au3> #include <GUIConstantsEx.au3> #include <ListBoxConstants.au3> #include <ListViewConstants.au3> #include <ColorConstants.au3> #include <MsgBoxConstants.au3> Global $AppHwnd = "[CLASS:AutoIt v3 GUI; TITLE:Product Finder]" If WinExists($AppHwnd) Then ;~ WinSetState($AppHwnd, "", @SW_SHOW) MsgBox($MB_OK, "Product Finder", "Product Finder already running." & @CRLF & "Please open MARG Window and check again.") Exit EndIf #Region ###Global Variables $testString = "" $bVisible1 = 0 ; 0 is Not visible, 1 is visible $bVisible2 = 0 ; 0 is Not visible, 1 is visible $fileDel = 1 ; 0 is Not delete, 1 is delete Global $nSheet = "RAW DUMP" ;~ $sFile = @ScriptDir & "\stock_1.xls" Global $gPath = "" Global $configFile = FileOpen(@ScriptDir & "\" & "config.txt") If $configFile <> -1 Then $gPath = FileRead($configFile) If $gPath <> 1 And $gPath <> -1 Then ;MsgBox($MB_OK, "Product Finder", "$gPath content is." & @CRLF & $gPath) Else MsgBox($MB_OK, "Product Finder", "Unable to read the Config.txt file in root directory." & @CRLF & "Please check the file contents and try again.") Exit EndIf Else MsgBox($MB_OK, "Product Finder", "Config.txt file not found in root directory." & @CRLF & "Please create the file and try again.") Exit EndIf ;Global $sFile = "D:\MARGERP\users\44651\report\STOCK_1.XLS" ;Global $nFile = "D:\MARGERP\users\44651\report" & "\" & $nSheet & ".xlsx" ;Global $tFile = "D:\MARGERP\users\44651\report\STOCK_2.XLS" Global $sFile = $gPath & "STOCK_1.XLS" Global $nFile = $gPath & $nSheet & ".xlsx" Global $tFile = $gPath & "STOCK_2.XLS" Global $dbArray, $uArray, $pArray, $sArray, $cArray Global $pCol = "", $sCol = "", $cCol = "" Global $cStock = "", $cCost = "", $cMRP = "", $cDate = "", $cParty = "", $cBCode = "" Global $LVColumn = "PCs" & "|" & "MRP" & "|" & "RATE" & "|" & "BARCODE" & "|" & "PARTY " & @TAB & @TAB & @TAB & "|" & "DATE" & @TAB & @TAB & @TAB Global $MARGHwnd = "[TITLE: MARG ERP 9+|Silver-1 User|; CLASS:margwin9c000000]" Global $xPOS = 1520, $yPOS = 78 Global $xPOS2 = $xPOS + 335, $yPOS2 = $yPOS + 900 Global $xOld = $xPOS, $yOld = $yPOS Global $vFLAG = 0, $hFLAG = 0 Global $gSTYLE = $WS_POPUP Global $gSTYLE_ext = $WS_EX_WINDOWEDGE Global $COLOR_CREAM = 0Xfffeeb #EndRegion ###Global Variables #Region ### START Koda GUI section ### Form= Global $Form1 = GUICreate("Product Finder", 396, 946, $xPOS, $yPOS, $gSTYLE, $gSTYLE_ext) WinSetOnTop($Form1, "", 1) GUISetFont(Default, $FW_SEMIBOLD) Global $Label1 = GUICtrlCreateLabel("ARTICLE NAME / NO: ", 20, 50, 80, 25) GUISetFont(14, $FW_BOLD) Global $Label2 = GUICtrlCreateLabel("PRODUCT NAME", 100, 80, 300, 25) Global $Label3 = GUICtrlCreateLabel("SIZE", 80, 310, 80, 25) Global $Label4 = GUICtrlCreateLabel("COLOUR", 250, 310, 80, 25) Global $SumLabel = GUICtrlCreateLabel("", 120, 620, 150, 25) GUISetFont(10, $FW_SEMIBOLD) Global $Input1 = GUICtrlCreateInput($testString, 110, 50, 250, 25) GUICtrlSetBkColor($Input1, $COLOR_CREAM) GUISetFont(10, $FW_SEMIBOLD) Global $sButton = GUICtrlCreateButton("SEARCH", 5, 15, 70, 25) GUICtrlSetBkColor($sButton, 0xC0DCC0) Global $cButton = GUICtrlCreateButton("CLEAR", 85, 15, 70, 25) GUICtrlSetBkColor($cButton, 0xA6CAF0) Global $hButton = GUICtrlCreateButton("HIDE", 165, 15, 70, 25) GUICtrlSetBkColor($hButton, 0xC0C0C0) Global $rButton = GUICtrlCreateButton("RELOAD", 245, 15, 75, 25) GUICtrlSetBkColor($rButton, 0Xf9ffc9) Global $eButton = GUICtrlCreateButton("EXIT", 325, 15, 70, 25) GUICtrlSetBkColor($eButton, 0Xfcc2bb) GUISetFont(12, $FW_SEMIBOLD) Global $List1 = GUICtrlCreateList("", 10, 100, 360, 215, BitOR($WS_BORDER, $WS_VSCROLL)) GUICtrlSetBkColor($List1, $COLOR_CREAM) GUISetFont(12, $FW_BOLD) Global $List2 = GUICtrlCreateList("", 10, 330, 175, 280, BitOR($WS_BORDER, $WS_VSCROLL)) GUICtrlSetBkColor($List2, $COLOR_CREAM) Global $List3 = GUICtrlCreateList("", 195, 330, 175, 280, BitOR($WS_BORDER, $WS_VSCROLL)) GUICtrlSetBkColor($List3, $COLOR_CREAM) GUISetFont(9) Global $Listview = GUICtrlCreateListView($LVColumn, 5, 650, 385, 200) GUICtrlSetBkColor($Listview, $COLOR_CREAM) _GUICtrlListView_SetColumnWidth($Listview, 0, 35) _GUICtrlListView_SetColumnWidth($Listview, 2, 45) GUISetFont(9) Global $ticker = GUICtrlCreateLabel("", 10, 860, 380, 80, $WS_BORDER) Global $Form2 = GUICreate("Hidden", 52, 44, $xPOS2, $yPOS2, $WS_POPUP) WinSetOnTop($Form2, "", 1) Global $Icon1 = GUICtrlCreateIcon("C:\WINDOWS\system32\imageres.dll", 68, 0, 0, 50, 50) #EndRegion ### END Koda GUI section ### Func ErrorHandle($error, $extended, $level, $Msg1, $Msg2) If $error = 0 Then Switch $level Case 0 ; Log: Console Only GUICtrlSetData($ticker, "Func: " & $Msg1 & " " & $Msg2 & @CRLF) ConsoleWrite("Func: " & $Msg1 & " " & $Msg2 & @CRLF) Case 1 ; Alert: Console & MsgBox GUICtrlSetData($ticker, "Func: " & $Msg1 & " " & $Msg2 & @CRLF) ConsoleWrite("Func: " & $Msg1 & " " & $Msg2 & @CRLF) MsgBox($MB_SYSTEMMODAL, "Func: " & $Msg1, $Msg2) Case 2 ; Fatal: Console, MsgBox & Exit GUICtrlSetData($ticker, "Func: " & $Msg1 & " " & $Msg2 & " Unable to continue. Program will now exit" & @CRLF) ConsoleWrite("Func: " & $Msg1 & " " & $Msg2 & " Unable to continue. Program will now exit" & @CRLF) MsgBox($MB_SYSTEMMODAL, "Func: " & $Msg1, $Msg2 & @CRLF & "Unable to continue. Program will now exit.") ConsoleWrite("Func: " & $Msg1 & " END." & @CRLF) Case 3 GUICtrlSetData($ticker, "Func: " & $Msg1 & " " & $Msg2 & " Unable to continue. Program will now exit" & @CRLF) ConsoleWrite("Func: " & $Msg1 & " " & $Msg2 & " Unable to continue. Program will now exit" & @CRLF) MsgBox($MB_SYSTEMMODAL, "Func: " & $Msg1, $Msg2 & @CRLF & "Unable to continue. Program will now exit.") ConsoleWrite("Func: " & $Msg1 & " END." & @CRLF) Exit EndSwitch Else Switch $level Case 0 ; Log: Console Only GUICtrlSetColor($ticker, $COLOR_RED) GUICtrlSetData($ticker, "Func: " & $Msg1 & " " & $Msg2 & " " & "@error = " & $error & ", @extended = " & $extended & @CRLF) GUICtrlSetColor($ticker, $COLOR_BLACK) ConsoleWrite("Func: " & $Msg1 & " " & $Msg2 & " " & "@error = " & $error & ", @extended = " & $extended & @CRLF) Case 1 ; Alert: Console & MsgBox GUICtrlSetColor($ticker, $COLOR_RED) GUICtrlSetData($ticker, "Func: " & $Msg1 & " " & $Msg2 & " " & "@error = " & $error & ", @extended = " & $extended & @CRLF) GUICtrlSetColor($ticker, $COLOR_BLACK) ConsoleWrite("Func: " & $Msg1 & " " & $Msg2 & " " & "@error = " & $error & ", @extended = " & $extended & @CRLF) MsgBox($MB_SYSTEMMODAL, "Func: " & $Msg1, $Msg2 & @CRLF & "@error = " & $error & ", @extended = " & $extended & ".") Case 2 ; Fatal: Console, MsgBox & Exit GUICtrlSetColor($ticker, $COLOR_RED) GUICtrlSetData($ticker, "Func: " & $Msg1 & " " & $Msg2 & @CRLF & "@error = " & $error & ", @extended = " & $extended & "." & @CRLF & "Unable to continue. Program will now exit." & @CRLF) GUICtrlSetColor($ticker, $COLOR_BLACK) ConsoleWrite("Func: " & $Msg1 & " " & $Msg2 & @CRLF & "@error = " & $error & ", @extended = " & $extended & "." & @CRLF & "Unable to continue. Program will now exit." & @CRLF) MsgBox($MB_SYSTEMMODAL, "Func: " & $Msg1, $Msg2 & @CRLF & "@error = " & $error & ", @extended = " & $extended & "." & @CRLF & "Unable to continue. Program will now exit.") ConsoleWrite("Func: " & $Msg1 & " END." & @CRLF) Case 3 GUICtrlSetColor($ticker, $COLOR_RED) GUICtrlSetData($ticker, "Func: " & $Msg1 & " " & $Msg2 & @CRLF & "@error = " & $error & ", @extended = " & $extended & "." & @CRLF & "Unable to continue. Program will now exit." & @CRLF) GUICtrlSetColor($ticker, $COLOR_BLACK) ConsoleWrite("Func: " & $Msg1 & " " & $Msg2 & @CRLF & "@error = " & $error & ", @extended = " & $extended & "." & @CRLF & "Unable to continue. Program will now exit." & @CRLF) MsgBox($MB_SYSTEMMODAL, "Func: " & $Msg1, $Msg2 & @CRLF & "@error = " & $error & ", @extended = " & $extended & "." & @CRLF & "Unable to continue. Program will now exit.") ConsoleWrite("Func: " & $Msg1 & " END." & @CRLF) Exit EndSwitch EndIf EndFunc ;==>ErrorHandle Func StdSize($SizeText) Switch $SizeText Case "s" Return "SMALL" Case "S" Return "SMALL" Case "SMAL" Return "SMALL" Case "m" Return "MEDIUM" Case "M" Return "MEDIUM" Case "l" Return "LARGE" Case "L" Return "LARGE" Case "xl" Return "XTRA LARGE" Case "Xl" Return "XTRA LARGE" Case "XL" Return "XTRA LARGE" Case "xxl" Return "2XTRA LARGE" Case "Xxl" Return "2XTRA LARGE" Case "XXL" Return "2XTRA LARGE" Case "2XL" Return "2XTRA LARGE" Case "3XL" Return "3XTRA LARGE" Case "4XL" Return "4XTRA LARGE" Case "5XL" Return "5XTRA LARGE" Case "6XL" Return "6XTRA LARGE" Case "7XL" Return "7XTRA LARGE" Case Else Return $SizeText EndSwitch EndFunc ;==>StdSize Func ReadStockFile($filePath) If FileExists($sFile) = 0 Then ErrorHandle(0, 0, 1, "Product Finder", "The Stock File Does Not Exist" & @CRLF & "Please run BCODE Report from MARG & Try Again.") Else ConsoleWrite(@CRLF) ErrorHandle(0, 0, 0, "ReadStockFile", "START.") Local $oExcel = _Excel_Open($bVisible1, Default, Default, Default, True) If @error Then ErrorHandle(@error, @extended, 3, "ReadStockFile", "_Excel_Open: Error creating the Excel application object.") ; add check to see if excel app is running & visible Else ErrorHandle(0, 0, 0, "ReadStockFile", "Excel object created successfully") EndIf Sleep(250) ;Local $oWorkbook = _Excel_BookOpen($oExcel, $filePath, False, $bVisible1) ;Local $oWorkbook = _Excel_BookOpen($oExcel, "Z:\users\44651\report\RAW DUMP.xlsx", False, $bVisible1) Local $oWorkbook = _Excel_BookOpen($oExcel, "\\happy-hosiery\HH-MARGERP\users\44651\report\RAW DUMP.xlsx", False, $bVisible1) If @error Then ErrorHandle(@error, @extended, 2, "ReadStockFile", "_Excel_BookOpen: Error opening workbook: [" & $filePath & "].") _Excel_Close($oExcel) Exit Else ErrorHandle(0, 0, 0, "ReadStockFile", "Workbook: [" & $filePath & "] opened successfully.") EndIf ; add check to see if this particular file is open Sleep(250) Local $arrayExcel = _Excel_RangeRead($oWorkbook, 1, Default) If @error Then ErrorHandle(@error, @extended, 2, "ReadStockFile", "_Excel_RangeRead: Error reading stock data from workbook.") _Excel_BookClose($oWorkbook, False) _Excel_Close($oExcel, False) Exit Else ErrorHandle(0, 0, 0, "ReadStockFile", "Worksheet data successfully exported to $arrayExcel.") _Excel_BookClose($oWorkbook, False) _Excel_Close($oExcel, False) EndIf If IsArray($arrayExcel) Then ;~ _ArrayDisplay($arrayExcel, "Func: ReadStockFile RAW Data as $arrayExcel.") ;~ If @error Then ErrorHandle(@error, @extended, 1, "Func: ReadStockFile", "_ArrayDisplay: Error displaying $arrayExcel.") _ArrayDelete($arrayExcel, "3") If @error Then ErrorHandle(@error, @extended, 1, "ReadStockFile", "_ArrayDelete: Error deleting Row 3 from array.") Else ErrorHandle(0, 0, 0, "ReadStockFile", "Row 3 deleted successfully from $arrayExcel") EndIf _ArrayDelete($arrayExcel, "1") If @error Then ErrorHandle(@error, @extended, 1, "ReadStockFile", "_ArrayDelete: Error deleting Row 1 from array.") Else ErrorHandle(0, 0, 0, "ReadStockFile", "Row 1 deleted successfully from $arrayExcel") EndIf _ArrayDelete($arrayExcel, "0") If @error Then ErrorHandle(@error, @extended, 1, "ReadStockFile", "_ArrayDelete: Error deleting Row 0 from array.") Else ErrorHandle(0, 0, 0, "ReadStockFile", "Row 0 deleted successfully from $arrayExcel") EndIf $arrayExcel[0][4] = "Sales Scheme Deal" $arrayExcel[0][5] = "Sales Scheme Free" $arrayExcel[0][6] = "Purc.Scheme Deal" $arrayExcel[0][7] = "Purc.Scheme Free" $arrayExcel[0][13] = "SIZE" Sleep(250) ;~ _ArrayDisplay($arrayExcel, "Func ReadStockFile Valid Cells only.") ;~ If @error Then ErrorHandle(@error, @extended, 1, "ReadStockFile", "_ArrayDisplay: Error displaying $arrayExcel.") Local $aLen = UBound($arrayExcel, 1) - 1 If $arrayExcel[$aLen][0] = "" Then _ArrayDelete($arrayExcel, $aLen) If @error Then ErrorHandle(@error, @extended, 1, "ReadStockFile", "_ArrayDelete: Error deleting blank Row: " & $aLen & " from array.") Else ErrorHandle(0, 0, 0, "ReadStockFile", "Blank Row: " & $aLen & " deleted successfully from $arrayExcel") EndIf EndIf If $arrayExcel[$aLen - 1][0] = "" Then _ArrayDelete($arrayExcel, $aLen - 1) If @error Then ErrorHandle(@error, @extended, 1, "ReadStockFile", "_ArrayDelete: Error deleting blank Row: " & $aLen - 1 & " from array.") Else ErrorHandle(0, 0, 0, "ReadStockFile", "Blank Row: " & $aLen - 1 & " deleted successfully from $arrayExcel") EndIf EndIf ErrorHandle(0, 0, 0, "ReadStockFile", "END.") $aLen = UBound($arrayExcel, 1) - 1 For $x = 1 To $aLen Step 1 $Text1 = $arrayExcel[$x][13] ;~ ConsoleWrite("STD Size: " & $Text1 & @CRLF) $Text1 = StdSize($Text1) ; code breaking line added $arrayExcel[$x][13] = $Text1 $Text2 = $arrayExcel[$x][18] $Text2 = StringStripWS($Text2, 4) $arrayExcel[$x][18] = $Text2 Next ;~ _ArrayDisplay($arrayExcel, "Func ReadStockFile Valid Cells only.") If $fileDel = 1 Then FileDelete($sFile) Return $arrayExcel Else ErrorHandle(0, 0, 3, "ReadStockFile", "Could not create Array from file: " & $filePath & ".") EndIf Return 0 EndIf EndFunc ;==>ReadStockFile Func CleanData($array1) ConsoleWrite(@CRLF) ErrorHandle(0, 0, 0, "CleanData", "START.") If IsArray($array1) Then ;~ _ArrayDisplay($array1, "Func: CleanData $array1.") ;~ If @error Then ErrorHandle(@error, @extended, 1, "CleanData", "_ArrayDisplay: Error displaying $array1.") Local $LocArray1 = _ArrayExtract($array1, 0, UBound($array1, 1) - 1, 1, 1) If @error Then ErrorHandle(@error, @extended, 3, "CleanData", "_ArrayExtract: Error extracting $LocArray1 from $array1.") Else ErrorHandle(0, 0, 0, "CleanData", "$LocArray1 extracted from $array1 successfully.") EndIf Sleep(250) If IsArray($LocArray1) Then ;~ _ArrayDisplay($LocArray1, "Func: CleanData $LocArray1 Product Names.") ;~ If @error Then ErrorHandle(@error, @extended, 1, "CleanData", "_ArrayDisplay: Error displaying $LocArray1") Local $aLen = UBound($LocArray1, 1) - 1 ;~ ConsoleWrite("Length of Array[" & $aLen & "]" & @CRLF) For $i = 1 To $aLen Step 1 Local $sText1 = $LocArray1[$i] $sText1 = StringStripWS($sText1, 1) ; leading $sText1 = StringStripWS($sText1, 2) ; trailing $sText1 = StringStripWS($sText1, 4) ; between ;~ ConsoleWrite("Row [" & $i & "]" & " ") ;~ ConsoleWrite("Current String[" & $sText1 & "]" & " ") Local $sLast1 = StringRight($sText1, 1) Local $sLast2 = StringMid($sText1, StringLen($sText1) - 1, 1) ;~ ConsoleWrite("Last1 String:[" & $sLast1 & "]" & " ") ;~ ConsoleWrite("Last2 String:[" & $sLast2 & "]" & @CRLF) If ($sLast1 = 1 Or $sLast1 = 2) And $sLast2 = " " Then $sText1 = StringTrimRight($sText1, 2) $sText1 = StringStripWS($sText1, 2) ; trailing EndIf $LocArray1[$i] = $sText1 $array1[$i][1] = $sText1 ;~ ConsoleWrite("Cleaned String:[" & $sText1 & "]" & " ") ;~ ConsoleWrite(@CRLF) Next ; LOOP ENDS ErrorHandle(0, 0, 0, "CleanData", "Garbage data cleaned from $LocArray1 successfully.") ErrorHandle(0, 0, 0, "CleanData", "END.") $LocArray1 = _ArrayExtract($array1, Default, Default, 0, 1) ;~ _ArrayDisplay($array1, "Func: CleanData $array1.") ;~ _ArrayDisplay($LocArray1, "Func: CleanData $LocArray1 Product Names.") Return $LocArray1 Else ErrorHandle(0, 0, 3, "CleanData", "Error reading column containing Product Names.") EndIf Else ErrorHandle(0, 0, 3, "CleanData", "Error converting excel sheet: [" & $sFile & "] to array.") EndIf EndFunc ;==>CleanData Func StdColour($ClrText) Switch $ClrText Case "ASSTD" Return "ASSORTED" Case "Asstd" Return "ASSORTED" Case "asstd" Return "ASSORTED" Case "asst" Return "ASSORTED" Case "ASST" Return "ASSORTED" Case "" Return "-BLANK-" Case " " Return "-BLANK-" Case " " Return "-BLANK-" Case " " Return "-BLANK-" Case Else $ClrText = StringReplace($ClrText, "ASSTD", "ASSORTED") $ClrText = StringReplace($ClrText, "Asstd", "ASSORTED") $ClrText = StringReplace($ClrText, "asstd", "ASSORTED") $ClrText = StringReplace($ClrText, "asst", "ASSORTED") $ClrText = StringReplace($ClrText, "ASST", "ASSORTED") Return $ClrText EndSwitch EndFunc ;==>StdColour Func SplitColor($array2) ;~ _ArrayDisplay($array2, "SplitColor3: $array2") Local $LocArray2[0][2] ConsoleWrite(@CRLF) ErrorHandle(0, 0, 0, "SplitColor", "START.") Local $oExcel = _Excel_Open($bVisible1, Default, Default, Default, True) If @error Then ErrorHandle(@error, @extended, 3, "WriteSheet", "_Excel_Open: Error creating the Excel application object.") Else ErrorHandle(0, 0, 0, "SplitColor", "Excel object created successfully.") EndIf Local $oWorkbook = _Excel_BookOpen($oExcel, $tFile, False, $bVisible1) If @error Then ErrorHandle(@error, @extended, 2, "SplitColor", "_Excel_BookOpen: Error opening workbook:[" & $tFile & "].") _Excel_Close($oExcel) Exit Else ErrorHandle(0, 0, 0, "SplitColor", "New workbook created successfully.") EndIf Local $pList = _Excel_RangeRead($oWorkbook, 1) If @error Then ErrorHandle(@error, @extended, 2, "SplitColor", "_Excel_RangeRead: Error reading from excel file:[" & $tFile & "].") _Excel_BookClose($oWorkbook, False) _Excel_Close($oExcel) Exit Else ;~ _ArrayDisplay($pList, "$pList default") _Excel_BookClose($oWorkbook, False) _Excel_Close($oExcel) ErrorHandle(0, 0, 0, "SplitColor", "Data read successfully.") EndIf ;~ _ArrayDelete($pList, "4") ;~ _ArrayDisplay($pList, "$pList del 4") _ArrayDelete($pList, "3") ;~ _ArrayDisplay($pList, "$pList del 3") _ArrayDelete($pList, "1") ;~ _ArrayDisplay($pList, "$pList del 1") _ArrayDelete($pList, "0") ;~ _ArrayDisplay($pList, "$pList del 0") $aLen = UBound($array2, 1) - 1 For $i = 0 To $aLen Step 1 ;~ For $i = 0 To 10 Step 1 Local $index = _ArraySearch($pList, $array2[$i][0]) If $index <> -1 Then Local $FullName = $array2[$i][1] Local $Name = $pList[$index][1] Local $Colour = StringReplace($FullName, $Name, "") $Colour = StringStripWS($Colour, 1) ;~ ErrorHandle(0, 0, 0, "", "FullName:[" & $FullName & "]" & " Name:[" & $Name & "]" & " Colour:[" & $Colour & "]") ElseIf $index = -1 And @error = 6 Then Local $FullName = $array2[$i][1] Local $Name = $FullName Local $Colour = "-blank-" ErrorHandle(0, 0, 0, "No match found for code:[" & $array2[$i][0] & "]", "Item Name:[" & $Name & "] " & "at index:[" & $i & "].") EndIf $Colour = StdColour($Colour) _ArrayAdd($LocArray2, $Name & "|" & $Colour, 0, "|") Next $LocArray2[0][1] = "Colour" ;~ _ArrayDisplay($LocArray2, "$LocArray2") Return $LocArray2 ErrorHandle(0, 0, 0, "SplitColor", "END.") EndFunc ;==>SplitColor Func WriteSheet($wArray, $sArray) ConsoleWrite(@CRLF) ErrorHandle(0, 0, 0, "WriteSheet", "START.") Local $ProdArray = _ArrayExtract($sArray, Default, Default, 0, 0) ;~ _ArrayDisplay($ProdArray , "WriteSheet: $ProdArray") Local $ColArray = _ArrayExtract($sArray, Default, Default, 1, 1) ;~ _ArrayDisplay($ColArray , "WriteSheet: $ColArray") Local $oExcel = _Excel_Open($bVisible1, Default, Default, Default, True) If @error Then ErrorHandle(@error, @extended, 3, "WriteSheet", "_Excel_Open: Error creating the Excel application object.") Else ErrorHandle(0, 0, 0, "WriteSheet", "Excel object created successfully.") EndIf ;~ add check to see if excel app is running & visible Sleep(250) Local $oWorkbook = _Excel_BookNew($oExcel, 1) If @error Then ErrorHandle(@error, @extended, 2, "WriteSheet", "_Excel_BookOpen: Error creating new workbook.") _Excel_Close($oExcel) Exit Else ErrorHandle(0, 0, 0, "WriteSheet", "New workbook created successfully.") EndIf ; add check to see if this particular file is open Sleep(250) _Excel_SheetDelete($oWorkbook, $nSheet) ; If sheet already existing from last time, delete it first If @error = 2 Then ErrorHandle(0, 0, 0, "WriteSheet", "Worksheet: " & $nSheet & " did not exist already.") ElseIf @error <> 0 Then ErrorHandle(@error, @extended, 3, "WriteSheet", "_Excel_SheetDelete: Error Deleting already existing worksheet: " & $nSheet & ".") EndIf Sleep(500) _Excel_SheetAdd($oWorkbook, Default, False, 1, $nSheet) ; add new sheet If @error Then ErrorHandle(@error, @extended, 3, "WriteSheet", "_Excel_SheetAdd: Error adding new worksheet: " & $nSheet & ".") Else ErrorHandle(0, 0, 0, "WriteSheet", "New worksheet: " & $nSheet & " added successfully.") EndIf Sleep(500) _Excel_SheetDelete($oWorkbook, "Sheet1") ; Delete "Sheet1" as it is not required If @error Then ErrorHandle(@error, @extended, 1, "WriteSheet", "_Excel_SheetDelete: Error Deleting worksheet: Sheet1.") Else ErrorHandle(0, 0, 0, "WriteSheet", "Worksheet: Sheet1 deleted successfully.") EndIf Sleep(500) ;~ _ArrayDisplay($wArray, "WriteSheet $wArray") _Excel_RangeWrite($oWorkbook, $nSheet, $wArray) ; copy same data as array from sheet 1 ; Global Variable $aStock If @error Then ErrorHandle(@error, @extended, 3, "WriteSheet", "_Excel_RangeWrite: Error adding data from array $aStock to worksheet: " & $nSheet & ".") Else ErrorHandle(0, 0, 0, "WriteSheet", "Data from array $aStock added to worksheet: " & $nSheet & " successfully.") EndIf Sleep(500) $oWorkbook.Activesheet.Columns("A:Z").AutoFit ; apply autofit to columns Sleep(250) ;~ _Excel_RangeInsert($oWorkbook.ActiveSheet, "C:C", $xlShiftToRight) ; insert blank column after B ;~ If @error Then ;~ ErrorHandle(@error, @extended, 3, "WriteSheet", "_Excel_RangeInsert: Error inserting blank column C.") ;~ Else ;~ ErrorHandle(0, 0, 0, "WriteSheet", "Blank column C inserted successfully.") ;~ EndIf ;~ Sleep(500) _Excel_RangeWrite($oWorkbook, $nSheet, $ProdArray, "B1") ; insert cleaned product names in column C & Colours in Column D ; Global Variable $aProduct1 If @error Then ErrorHandle(@error, @extended, 3, "WriteSheet", "_Excel_RangeWrite: Error writing cleaned Product Names & Colour to columns B & C.") Else ErrorHandle(0, 0, 0, "WriteSheet", "Cleaned Product Names & Colour added to columns B & C successfully.") EndIf Sleep(500) _Excel_RangeWrite($oWorkbook, $nSheet, $ColArray, "X1") ; insert cleaned product names in column C & Colours in Column D ; Global Variable $aProduct1 If @error Then ErrorHandle(@error, @extended, 3, "WriteSheet", "_Excel_RangeWrite: Error writing cleaned Product Names & Colour to columns B & C.") Else ErrorHandle(0, 0, 0, "WriteSheet", "Cleaned Product Names & Colour added to columns B & C successfully.") EndIf Sleep(500) $oWorkbook.Activesheet.Columns("A:AZ").AutoFit ; apply FINAL autofit to columns Sleep(250) _Excel_BookSaveAs($oWorkbook, $nFile, Default, True) _Excel_BookClose($oWorkbook, True) _Excel_Close($oExcel, True) ErrorHandle(0, 0, 0, "WriteSheet", "New Spreadsheet created successfully.") ErrorHandle(0, 0, 0, "WriteSheet", "END.") EndFunc ;==>WriteSheet Func CreateNewFile() HideButtonActions() ErrorHandle(0, 0, 0, "CreateNewFile", "START.") Local $aStock = 0 Local $aResult = 0 Local $aProduct1 = 0 Local $aStock = ReadStockFile($sFile) ; Size is in Column 13 of aStock Array If IsArray($aStock) And $aStock <> 0 Then Local $aResult = CleanData($aStock) If IsArray($aResult) And $aResult <> 0 Then Local $aProduct1 = SplitColor($aResult) If IsArray($aProduct1) And IsArray($aProduct1) <> 0 Then WriteSheet($aStock, $aProduct1) Main() ClearButtonActions() EndIf EndIf EndIf ErrorHandle(0, 0, 0, "CreateNewFile", "END.") ShowButtonActions() EndFunc ;==>CreateNewFile Func CreateMainDB($filePath) ConsoleWrite(@CRLF) ErrorHandle(0, 0, 0, "CreateMainDB", "START.") Local $oExcel = _Excel_Open($bVisible2, Default, Default, Default, True) If @error Then ErrorHandle(@error, @extended, 3, "CreateMainDB", "_Excel_Open: Error creating the Excel application object.") ;~ add check to see if excel app is running & visible Else ErrorHandle(0, 0, 0, "CreateMainDB", "Excel object created successfully") EndIf Sleep(250) Local $oWorkbook = _Excel_BookOpen($oExcel, $filePath, False, $bVisible2) If @error Then ErrorHandle(@error, @extended, 2, "CreateMainDB", "_Excel_BookOpen: Error opening workbook: [" & $filePath & "].") _Excel_Close($oExcel) Exit Else ErrorHandle(0, 0, 0, "CreateMainDB", "Workbook: [" & $filePath & "] opened successfully.") EndIf ; add check to see if this particular file is open Sleep(250) Local $arrayExcel = _Excel_RangeRead($oWorkbook, 1, Default) If @error Then ErrorHandle(@error, @extended, 2, "CreateMainDB", "_Excel_RangeRead: Error reading stock data from workbook.") _Excel_BookClose($oWorkbook, False) _Excel_Close($oExcel, False) Exit Else ErrorHandle(0, 0, 0, "CreateMainDB", "Worksheet data successfully exported to $arrayExcel.") _Excel_BookClose($oWorkbook, False) _Excel_Close($oExcel, False) EndIf If IsArray($arrayExcel) Then ErrorHandle(0, 0, 0, "CreateMainDB", "END.") _ArrayColDelete($arrayExcel, 0) ;~ _ArrayDisplay($arrayExcel, "CreateMainDB: $arrayExcel") Return $arrayExcel Else ErrorHandle(0, 0, 3, "CreateMainDB", "Could not create Array from file: " & $filePath & ".") EndIf EndFunc ;==>CreateMainDB Func Assign_Col_No($dbarray_1) $pCol = _ArraySearch($dbarray_1, "Product Name", 0, 0, 0, 0, 1, 0, True) ErrorHandle(0, 0, 0, "Assign_Col_No", "$pCol:[" & $pCol & "]") $sCol = _ArraySearch($dbarray_1, "SIZE", 0, 0, 0, 0, 1, 0, True) ErrorHandle(0, 0, 0, "Assign_Col_No", "$sCol:[" & $sCol & "]") $cCol = _ArraySearch($dbarray_1, "COLOUR", 0, 0, 0, 0, 1, 0, True) ErrorHandle(0, 0, 0, "Assign_Col_No", "$cCol:[" & $cCol & "]") $cStock = _ArraySearch($dbarray_1, "Current Stock", 0, 0, 0, 0, 1, 0, True) ErrorHandle(0, 0, 0, "Assign_Col_No", "$cStock:[" & $cStock & "]") ;~ $cCost = _ArraySearch($dbarray_1, "Cost Price", 0, 0, 0, 0, 1, 0, True) ;~ ErrorHandle(0, 0, 0, "Assign_Col_No", "$cCost:[" & $cCost & "]") $cMRP = _ArraySearch($dbarray_1, "M.R.P.", 0, 0, 0, 0, 1, 0, True) ErrorHandle(0, 0, 0, "Assign_Col_No", "$cMRP:[" & $cMRP & "]") $cCost = _ArraySearch($dbarray_1, "Purchase Price", 0, 0, 0, 0, 1, 0, True) ErrorHandle(0, 0, 0, "Assign_Col_No", "$cCost:[" & $cCost & "]") $cDate = _ArraySearch($dbarray_1, "Inv.Date", 0, 0, 0, 0, 1, 0, True) ErrorHandle(0, 0, 0, "Assign_Col_No", "$cDate:[" & $cDate & "]") $cParty = _ArraySearch($dbarray_1, "Supplier", 0, 0, 0, 0, 1, 0, True) ErrorHandle(0, 0, 0, "Assign_Col_No", "$cParty:[" & $cParty & "]") $cBCode = _ArraySearch($dbarray_1, "Batch", 0, 0, 0, 0, 1, 0, True) ErrorHandle(0, 0, 0, "Assign_Col_No", "$cBCode:[" & $cBCode & "]") EndFunc ;==>Assign_Col_No Func CreateUArray($array3) ConsoleWrite(@CRLF) ErrorHandle(0, 0, 0, "CreateUArray", "START.") If IsArray($array3) Then ;~ _ArrayDisplay($array3, "Func: CreateUArray $array3.") $LocArray3 = _ArrayUnique($array3, $pCol, 0, 0, 0) If @error Then ErrorHandle(@error, @extended, 3, "CreateUArray", "_ArrayUnique: Unable to create unique array: $LocArray3 from $array3.") Else _ArrayDelete($LocArray3, 0) ;~ _ArrayDisplay($LocArray3, "Func: CreateUArray $LocArray3.") ErrorHandle(0, 0, 0, "CreateUArray", "END.") Return $LocArray3 EndIf Else ErrorHandle(0, 0, 3, "CreateUArray", "$array3 is not an array.") EndIf EndFunc ;==>CreateUArray Func CreatePArray($array4, $String4) ;~ _ArrayDisplay($array4, "Func CreatePArray") Local $LocArray4[0][23] Local $temp_Array1[0][23] If IsArray($array4) Then ;~ Local $index = _ArrayFindAll($array4, $String4, Default, Default, 0, 1) Local $index = _ArrayFindAll($array4, $String4, 1, UBound($array4, 1) - 1, 0, 1, $pCol) If @error = 6 Then GUICtrlSetColor($ticker, $COLOR_RED) ErrorHandle(@error, @extended, 1, "CreatePArray", "Product name [" & $String4 & "] not found in." & @CRLF & "Try again with a different name.") GUICtrlSetColor($ticker, $COLOR_BLACK) Return 0 ElseIf @error Then ErrorHandle(@error, @extended, 3, "CreatePArray", "Error searching for:" & $String4 & " in $array4.") Else ;~ _ArrayDisplay($index, "CreatePArray $index.") $len = UBound($index, 1) - 1 $wid = UBound($array4, 2) - 1 For $i = 0 To $len Step 1 $temp_Array1 = _ArrayExtract($array4, $index[$i], $index[$i], 0, $wid) If @error Then ErrorHandle(@error, @extended, 3, "CreatePArray", "Error extracting row: " & $i & " from $array4.") Else _ArrayAdd($LocArray4, $temp_Array1) If @error Then ErrorHandle(@error, @extended, 3, "CreatePArray", "Error occured while adding value: " & $i & " to $LocArray4.") ;~ _ArrayDisplay($temp_Array, "$temp_Array") EndIf Next ;~ _ArrayDisplay($LocArray4, "Func: CreatePArray $LocArray4.") Return $LocArray4 EndIf Else ErrorHandle(0, 0, 3, "CreatePArray", "$array4 is not an array.") EndIf EndFunc ;==>CreatePArray Func CreateSArray($array5, $String5) Local $LocArray5[0][23] Local $temp_Array2[0][23] If IsArray($array5) Then Local $index = _ArrayFindAll($array5, $String5, 0, UBound($array5, 1) - 1, 0, 2, $pCol) If @error Then ErrorHandle(@error, @extended, 3, "CreateSArray", "_ArrayFindAll: Error searching for: " & $String5 & " in $array5.") Else ;~ _ArrayDisplay($index, "CreateSArray $index.") $len = UBound($index, 1) - 1 $wid = UBound($array5, 2) - 1 For $i = 0 To $len Step 1 $temp_Array2 = _ArrayExtract($array5, $index[$i], $index[$i], 0, $wid) If @error Then ErrorHandle(@error, @extended, 3, "CreateSArray", "_ArrayExtract: Error extracting row: " & $i & " from $array5.") Else ;~ _ArrayDisplay($LocArray5, "$LocArray5") ;~ _ArrayDisplay($temp_Array2, "$temp_Array2") _ArrayAdd($LocArray5, $temp_Array2) If @error Then ErrorHandle(@error, @extended, 3, "CreateSArray", "_ArrayAdd: Error occured while adding value: " & $i & " to $LocArray5.") ;~ _ArrayDisplay($temp_Array, "$temp_Array2") EndIf Next ;~ _ArrayDisplay($LocArray5, "Func: CreateSArray $LocArray5") Return $LocArray5 EndIf Else ErrorHandle(0, 0, 3, "CreateSArray", "$array5 is not an array.") EndIf EndFunc ;==>CreateSArray Func CreateCArray($array6, $String6) ;~ _ArrayDisplay($array6, "CreateCArray: $array6") Local $LocArray6[0][23] Local $temp_Array3[0][23] If IsArray($array6) Then Local $index = _ArrayFindAll($array6, $String6, 0, UBound($array6, 1) - 1, 0, 0, $sCol) If @error Then ErrorHandle(0, 0, 3, "CreateCArray", "Error searching for:[" & $String6 & "] in $array6") Else ;~ _ArrayDisplay($index, "CreateSArray $index.") $len = UBound($index, 1) - 1 $wid = UBound($array6, 2) - 1 For $i = 0 To $len Step 1 $temp_Array3 = _ArrayExtract($array6, $index[$i], $index[$i], 0, $wid) If @error Then ErrorHandle(@error, @extended, 3, "CreateCArray", "Error extracting row: " & $i & " from $array6") Else _ArrayAdd($LocArray6, $temp_Array3) If @error Then ErrorHandle(@error, @extended, 3, "CreateCArray", "Error occured while adding value: " & $i & " to $LocArray6") ;~ _ArrayDisplay($temp_Array, "$temp_Array2") EndIf Next ;~ _ArrayDisplay($LocArray6, "Func: CreateCArray $LocArray6") Return $LocArray6 EndIf Else ErrorHandle(0, 0, 3, "CreateCArray", "$array5 is not an array.") EndIf EndFunc ;==>CreateCArray Func CreateFArray($array7, $String7) ;~ _ArrayDisplay($array6, "CreateCArray: $array6") Local $LocArray7[0][23] Local $temp_Array4[0][23] If IsArray($array7) Then Local $index = _ArrayFindAll($array7, $String7, 0, UBound($array7, 1) - 1, 0, 0, $cCol) If @error Then ErrorHandle(0, 0, 3, "CreateFArray", "Error searching for:[" & $String7 & "] in $array6") Else ;~ _ArrayDisplay($index, "CreateSArray $index.") $len = UBound($index, 1) - 1 $wid = UBound($array7, 2) - 1 For $i = 0 To $len Step 1 $temp_Array4 = _ArrayExtract($array7, $index[$i], $index[$i], 0, $wid) If @error Then ErrorHandle(@error, @extended, 3, "CreateFArray", "Error extracting row: " & $i & " from $array6") Else _ArrayAdd($LocArray7, $temp_Array4) If @error Then ErrorHandle(@error, @extended, 3, "CreateFArray", "Error occured while adding value: " & $i & " to $LocArray6") ;~ _ArrayDisplay($temp_Array, "$temp_Array2") EndIf Next ;~ _ArrayDisplay($LocArray7, "Func: CreateFArray $LocArray7") Return $LocArray7 EndIf Else ErrorHandle(0, 0, 3, "CreateFArray", "$array5 is not an array.") EndIf EndFunc ;==>CreateFArray Func CreateSum($array8) ;~ _ArrayDisplay($array8, "CreateSum: $array8") Local $sum = "" If IsArray($array8) Then $len = UBound($array8, 1) - 1 For $i = 0 To $len Step 1 $sum = $sum + $array8[$i][$cStock] ;~ ErrorHandle(0, 0, 0, "CreateSum", "$sum:[" & $sum & "]") Next If $sum <> 0 Then ErrorHandle(0, 0, 0, "CreateSum", "Final sum:[" & $sum & "]") Return $sum Else ErrorHandle(0, 0, 0, "CreateSum", "Final sum:[0]") Return 0 EndIf Else ErrorHandle(0, 0, 0, "CreateSum", "$array8 is not an array.") EndIf EndFunc ;==>CreateSum Func CreateListView($array9) Local $temp_Array5 ;~ _ArrayDisplay($temp_Array5, "CreateListView $temp_Array5") If IsArray($array9) Then $temp_Array5 = $array9 ;~ _ArrayDisplay($temp_Array5, "CreateListView $temp_Array5") $lenC = UBound($temp_Array5, 2) - 1 ErrorHandle(0, 0, 0, "CreateListView", "Column len:[" & $lenC & "]") ;~ _ArrayDisplay($temp_Array5, "CreateListView $temp_Array5 before loop ") For $x = $lenC To 0 Step -1 ;~ If $x <> $cBCode And $x <> $cParty And $x <> $cDate And $x <> $cMRP And $x <> $cCost And $x <> $cStock Then _ArrayColDelete($temp_Array5, $x) If $x <> $cDate And $x <> $cParty And $x <> $cBCode And $x <> $cMRP And $x <> $cCost And $x <> $cStock Then _ArrayColDelete($temp_Array5, $x) Next ;~ _ArrayDisplay($temp_Array5, "CreateListView $temp_Array5 after loop") $lenR = UBound($temp_Array5, 1) - 1 ErrorHandle(0, 0, 0, "CreateListView", "Row len:[" & $lenC & "]") For $x = 0 To $lenR Step 1 Local $dString = $temp_Array5[$x][5] ;~ ErrorHandle(0, 0, 0, "CreateListView", "Date String:[" & $dString & "]") Local $year = StringMid($dString, 1, 4) Local $month = StringMid($dString, 5, 2) Local $date = StringMid($dString, 7, 2) Local $formattedDate = $date & "/" & $month & "/" & $year ;~ ErrorHandle(0, 0, 0, "CreateListView", "Formatted Date:[" & $formattedDate & "]") $temp_Array5[$x][5] = $formattedDate Next Return $temp_Array5 Else ErrorHandle(0, 0, 3, "CreateListView", "$array9 is not an array.") EndIf EndFunc ;==>CreateListView Func PushList1($array_1) If IsArray($array_1) Then Local $temp_Array_1_1 = _ArrayUnique($array_1, $pCol, 0, 0, 0) Local $pText = _ArrayToString($temp_Array_1_1, "|", 0, UBound($temp_Array_1_1, 1) - 1, "", 0, 0) GUICtrlSetData($List1, $pText) ;~ _GUICtrlListBox_ClickItem($List1, 0, "left", False, 1) Else ErrorHandle(0, 0, 3, "PushList1", "$array4 is not an array.") EndIf EndFunc ;==>PushList1 Func PushList2($array_2) If IsArray($array_2) Then Local $temp_Array_1_2 = _ArrayUnique($array_2, $sCol, 0, 0, 0) ;~ _ArrayDisplay($temp_Array_1_2, "PushList2: $temp_Array_1_2") $len = UBound($temp_Array_1_2, 1) - 1 For $i = 0 To $len Step 1 $tSize = $temp_Array_1_2[$i] If IsInt($tSize) Then $temp_Array_1_2[$i] = Number($tSize) Next _ArraySort($temp_Array_1_2) Local $sText = _ArrayToString($temp_Array_1_2, "|", 0, UBound($temp_Array_1_2, 1) - 1, "", 0, 0) GUICtrlSetData($List2, $sText) _GUICtrlListBox_ClickItem($List2, 0, "left", False, 1) Else ErrorHandle(0, 0, 3, "PushList2", "$array4 is not an array.") EndIf EndFunc ;==>PushList2 Func PushList3($array_3) If IsArray($array_3) Then Local $temp_Array_1_3 = _ArrayUnique($array_3, $cCol, 0, 0, 0) Local $cText = _ArrayToString($temp_Array_1_3, "|", 0, UBound($temp_Array_1_3, 1) - 1, "", 0, 0) GUICtrlSetData($List3, $cText) _GUICtrlListBox_ClickItem($List3, 0, "left", False, 1) Else ErrorHandle(0, 0, 3, "PushList3", "$array_3 is not an array.") EndIf WinActivate($MARGHwnd) EndFunc ;==>PushList3 Func PushListView($array_4) ;~ _ArrayDisplay($array_4, "PushListView") If IsArray($array_4) Then $aLen = UBound($array_4, 1) - 1 For $x = 0 To $aLen Step 1 Local $fList = _ArrayToString($array_4, "|", $x, $x) $LVitem = GUICtrlCreateListViewItem($fList, $Listview) ErrorHandle(0, 0, 0, "PushListView $fList:", $fList) GUICtrlSetColor($LVitem, $COLOR_BLUE) Next _GUICtrlListView_SetColumnWidth($Listview, 0, 35) _GUICtrlListView_SetColumnWidth($Listview, 2, 45) Else ErrorHandle(0, 0, 3, "PushListView", "$array_4 is not an array.") EndIf EndFunc ;==>PushListView Func ClearButtonActions() GUICtrlSetData($Input1, "") GUICtrlSetData($List1, "") GUICtrlSetData($List2, "") GUICtrlSetData($List3, "") GUICtrlSetData($SumLabel, "") GUICtrlSetData($ticker, "") RefreshLV() WinActivate($MARGHwnd) EndFunc ;==>ClearButtonActions Func SearchButtonActions() GUICtrlSetData($List1, "") GUICtrlSetData($List2, "") GUICtrlSetData($List3, "") GUICtrlSetData($SumLabel, "") RefreshLV() Local $iTextS = GUICtrlRead($Input1) If $iTextS = "" Then $pArray = $dbArray PushList1($uArray) Else ErrorHandle(0, 0, 0, "SearchButtonActions", "Search Box:[" & $iTextS & "]" & @CRLF) $pArray = CreatePArray($dbArray, $iTextS) ;~ _ArrayDisplay($pArray, "$pArray") If IsArray($pArray) Then PushList1($pArray) EndIf WinActivate($MARGHwnd) EndFunc ;==>SearchButtonActions Func HideButtonActions() $hFLAG = 1 GUISetState(@SW_SHOWNOACTIVATE, $Form2) GUISetState(@SW_HIDE, $Form1) WinActivate($MARGHwnd) EndFunc ;==>HideButtonActions Func ShowButtonActions() $hFLAG = 0 GUISetState(@SW_HIDE, $Form2) GUISetState(@SW_SHOWNOACTIVATE, $Form1) WinActivate($MARGHwnd) EndFunc ;==>ShowButtonActions Func ReloadButtonActions() CreateNewFile() WinActivate($MARGHwnd) EndFunc ;==>ReloadButtonActions Func ListBox1Actions() GUICtrlSetData($List2, "") GUICtrlSetData($List3, "") GUICtrlSetData($SumLabel, "") RefreshLV() ;~ _ArrayDisplay($pArray, "$pArray") Local $iText1 = GUICtrlRead($List1) If $iText1 <> "" Then ErrorHandle(0, 0, 0, "ListBox1Actions", "ListBox1:[" & $iText1 & "]") If IsArray($pArray) Then $sArray = CreateSArray($pArray, $iText1) ;~ _ArrayDisplay($sArray, "$sArray") PushList2($sArray) EndIf WinActivate($MARGHwnd) EndFunc ;==>ListBox1Actions Func ListBox2Actions() GUICtrlSetData($List3, "") GUICtrlSetData($SumLabel, "") RefreshLV() Local $iText2 = GUICtrlRead($List2) If $iText2 <> "" Then ErrorHandle(0, 0, 0, "ListBox2Actions", "ListBox2:[" & $iText2 & "]") If IsArray($sArray) Then $cArray = CreateCArray($sArray, $iText2) ;~ _ArrayDisplay($cArray, "$cArray") PushList3($cArray) EndIf WinActivate($MARGHwnd) EndFunc ;==>ListBox2Actions Func ListBox3Actions() GUICtrlSetData($SumLabel, "") RefreshLV() Local $iText3 = GUICtrlRead($List3) If $iText3 <> "" Then ErrorHandle(0, 0, 0, "ListBox3Actions", "ListBox3:[" & $iText3 & "]") If IsArray($cArray) Then $fArray = CreateFArray($cArray, $iText3) If IsArray($fArray) Then ;~ _ArrayDisplay($fArray, "$fArray") Local $sum = CreateSum($fArray) GUICtrlSetData($SumLabel, "TOTAL: " & $sum) Local $aSum = CreateListView($fArray) EndIf If IsArray($aSum) Then PushListView($aSum) EndIf WinActivate($MARGHwnd) EndFunc ;==>ListBox3Actions Func RefreshLV() GUICtrlDelete($Listview) $Listview = GUICtrlCreateListView($LVColumn, 5, 650, 385, 200) GUICtrlSetBkColor($Listview, $COLOR_CREAM) ;_GUICtrlListView_SetColumnWidth($Listview, 0, 35) ;_GUICtrlListView_SetColumnWidth($Listview, 2, 40) EndFunc ;==>RefreshLV Func SetVisible() Local $mState = WinGetState($MARGHwnd) Local $g1State = WinGetState($Form1) Local $g2State = WinGetState($Form2) ;~ ErrorHandle(0,0,0, "WinGetState", $state) If $hFLAG = 0 Then If BitAND($mState, $WIN_STATE_MINIMIZED) Then If $vFLAG <> 0 Then GUISetState(@SW_HIDE, $Form1) EndIf $vFLAG = 0 ElseIf Not BitAND($mState, $WIN_STATE_ACTIVE) And Not BitAND($g1State, $WIN_STATE_ACTIVE) Then If $vFLAG <> 0 Then GUISetState(@SW_HIDE, $Form1) EndIf $vFLAG = 0 ElseIf BitAND($mState, $WIN_STATE_ACTIVE) Then If $vFLAG <> 1 Then GUISetState(@SW_SHOW, $Form1) Sleep(150) WinActivate($MARGHwnd) EndIf $vFLAG = 1 ElseIf BitAND($mState, $WIN_STATE_MAXIMIZED) Then SetPos() If $vFLAG <> 1 Then GUISetState(@SW_SHOW, $Form1) Sleep(150) WinActivate($MARGHwnd) EndIf $vFLAG = 1 EndIf ElseIf $hFLAG = 1 Then If BitAND($mState, $WIN_STATE_MINIMIZED) Then If $vFLAG <> 0 Then GUISetState(@SW_HIDE, $Form2) EndIf $vFLAG = 0 ElseIf Not BitAND($mState, $WIN_STATE_ACTIVE) And Not BitAND($g2State, $WIN_STATE_ACTIVE) Then If $vFLAG <> 0 Then GUISetState(@SW_HIDE, $Form2) EndIf $vFLAG = 0 ElseIf BitAND($mState, $WIN_STATE_ACTIVE) Then If $vFLAG <> 1 Then GUISetState(@SW_SHOW, $Form2) Sleep(150) WinActivate($MARGHwnd) EndIf $vFLAG = 1 ElseIf BitAND($mState, $WIN_STATE_MAXIMIZED) Then SetPos() If $vFLAG <> 1 Then GUISetState(@SW_SHOW, $Form2) Sleep(150) WinActivate($MARGHwnd) EndIf $vFLAG = 1 EndIf EndIf EndFunc ;==>SetVisible Func SetPos() Local $aPOS = WinGetPos($MARGHwnd) If Not @error Then Local $xNew = $aPOS[0] Local $yNew = $aPOS[1] If $xNew <> $xOld Or $yNew <> $yOld Then ;~ ConsoleWrite("X:" & $xNew & " " & "Y:" & $yNew & @CRLF) If $xNew = -3 And $yNew = -3 Then ;~ GUISetStyle($gSTYLE, "", $Form1) WinMove($Form1, "", $xPOS, $yPOS) WinMove($Form2, "", $xPOS2, $yPOS2) If @error Then MsgBox($MB_SYSTEMMODAL, "", "Window not found") ElseIf $xNew <> -32000 And $yNew <> -32000 Then ;~ GUISetStyle($gSTYLE, "", $Form1) WinMove($Form1, "", $xPOS + $xNew + 2, $yPOS + $yNew) WinMove($Form2, "", $xPOS2 + $xNew + 2, $yPOS2 + $yNew) If @error Then MsgBox($MB_SYSTEMMODAL, "", "Window not found") EndIf $xOld = $xNew $yOld = $yNew EndIf EndIf EndFunc ;==>SetPos Func Main() $dbArray = CreateMainDB($nFile) ;~ _ArrayDisplay($dbArray, "Main DataBase") Assign_Col_No($dbArray) $uArray = CreateUArray($dbArray) ;~ _ArrayDisplay($uArray, "Unique Product Names") ;~ GUISetState(@SW_SHOW, $Form1) ;~ GUISetState(@SW_HIDE, $Form1) ;~ GUISetState(@SW_SHOW, $Form2) EndFunc ;==>Main ;~ CreateNewFile() Main() While 1 SetVisible() SetPos() $nMsg = GUIGetMsg() Switch $nMsg Case $eButton Exit Case $hButton HideButtonActions() Case $sButton SearchButtonActions() Case $cButton ClearButtonActions() Case $rButton ReloadButtonActions() Case $List1 ListBox1Actions() Case $List2 ListBox2Actions() Case $List3 ListBox3Actions() Case $Icon1 ShowButtonActions() EndSwitch WEnd
ioa747 Posted April 7, 2023 Posted April 7, 2023 (edited) assuming it exists 'Z:\users\44651\report\RAW DUMP.xlsx' with a quick overview, 2 dark spots, take a look in line 50 line 256 if that help Edited April 7, 2023 by ioa747 I know that I know nothing
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now