insomnai Posted October 26, 2011 Share Posted October 26, 2011 Hi, guys and gals, please entertain me for a moment... (begin wonky dancing.... NOW!) No really, as the header states, i'm after an assist on a second idea that i'm working towards, mainly inventory control: After searching the forums, I have found some subjects on the matter of working with spreadsheets but only specific to the OP's question but nothing broad covering the whole area. I'm looking specifically to store information in several columns and rows and i'm wondering on the best way to approach this. What i'm after is basically a 'stock inventory' where I can add new products i.e. [Product Code], [Make], [Model], [Current Stock Level], [Date of Add/Edit] and using this information on a second tab, take products out of 'stock' and tell me how many I have left based on the last removal or addition. I've got to be honest guys, I don't know where to begin and as always, help is always appreciated in the form of pointing me in the right direction or some snippets with explanations on how it works. Many many thanks insomnai Link to comment Share on other sites More sharing options...
sleepydvdr Posted October 27, 2011 Share Posted October 27, 2011 (edited) Do you require Excel or are you open to the idea of creating a database from scratch? I have written a little bit of code that you may like. But it is back on track. Right now, only the Add A Product button has been written. Products with a quantity of less than 10 are highlighted in red; product quantity less than 100 is highlighted in yellow. If you like what I have created so far, I will continue working on it. expandcollapse popup#include <GUIConstantsEx.au3> #include <ListViewConstants.au3> #include <WindowsConstants.au3> #include <ButtonConstants.au3> #include <StaticConstants.au3> #include <array.au3> Global $productCode,$make,$model,$currentStock,$dataModified $Form1 = GUICreate("Form1", 623, 442, 192, 124) $ListView1 = GUICtrlCreateDummy() IniWrite("InventoryData.ini", "JunkProduct", "Product Code", $productCode) $Button1 = GUICtrlCreateButton("Add A Product", 16, 408, 91, 25) $Button2 = GUICtrlCreateButton("Edit A Product", 120, 408, 91, 25) $Button3 = GUICtrlCreateButton("Delete A Product", 224, 408, 107, 25) GUISetState(@SW_SHOW) _RefreshData() While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit Case $Button1 _AddAProduct() EndSwitch WEnd Func _AddAProduct() #Region ### START Koda GUI section ### Form= $Form2 = GUICreate("Form1", 336, 258, 302, 218) $cInput1 = GUICtrlCreateInput("", 16, 24, 193, 21) $cInput2 = GUICtrlCreateInput("", 16, 64, 193, 21) $cInput3 = GUICtrlCreateInput("", 16, 104, 193, 21) $cInput4 = GUICtrlCreateInput("", 16, 144, 193, 21) $cInput5 = GUICtrlCreateInput("", 16, 184, 193, 21) $Label1 = GUICtrlCreateLabel("Product Code", 224, 24, 69, 17) $Label2 = GUICtrlCreateLabel("Make", 224, 64, 31, 17) $Label3 = GUICtrlCreateLabel("Model", 224, 104, 33, 17) $Label4 = GUICtrlCreateLabel("Current Stock", 224, 144, 69, 17) $Label5 = GUICtrlCreateLabel("Date Modified", 224, 184, 70, 17) $Button3 = GUICtrlCreateButton("Add Product", 120, 216, 91, 25) GUISetState(@SW_SHOW) #EndRegion ### END Koda GUI section ### While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE GUIDelete($Form2) ExitLoop Case $Button3 $productCode = GUICtrlRead($cInput1) $make = GUICtrlRead($cInput2) $model = GUICtrlRead($cInput3) $currentStock = GUICtrlRead($cInput4) $dataModified = GUICtrlRead($cInput5) $errorCheck = IniRead("InventoryData.ini", "Product Code", "Product Code", "NotInDatabase") If $errorCheck = "NotInDatabase" Then IniWrite("InventoryData.ini", $productCode, "Product Code", $productCode) IniWrite("InventoryData.ini", $productCode, "Make", $make) IniWrite("InventoryData.ini", $productCode, "Model", $model) IniWrite("InventoryData.ini", $productCode, "Current Stock", $currentStock) IniWrite("InventoryData.ini", $productCode, "Date Modified", $dataModified) Else MsgBox(0, "Error", "The product is already in the database") EndIf GUIDelete($Form2) _RefreshData() ExitLoop EndSwitch WEnd EndFunc Func _RefreshData() GUISwitch($Form1) GUICtrlDelete($ListView1) $ListView1 = GUICtrlCreateListView("Product Code|Make|Model|Current Stock|Date Modified", 16, 8, 586, 390) GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 0, 100) GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 1, 150) GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 2, 150) GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 3, 85) GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 4, 100) $sectionNames = IniReadSectionNames("InventoryData.ini") For $i = 1 to $sectionNames[0] Step 1 $productCode = IniRead("InventoryData.ini", $sectionNames[$i], "Product Code", "NotFound") $make = IniRead("InventoryData.ini", $sectionNames[$i], "Make", "NotFound") $model = IniRead("InventoryData.ini", $sectionNames[$i], "Model", "NotFound") $currentStock = IniRead("InventoryData.ini", $sectionNames[$i], "Current Stock", "NotFound") $dataModified = IniRead("InventoryData.ini", $sectionNames[$i], "Date Modified", "NotFound") GUICtrlCreateListViewItem($productCode & "|" & $make & "|" & $model & "|" & $currentStock & "|" & $dataModified, $ListView1) If Number($currentStock) < 100 Then GUICtrlSetBkColor ( -1, 0xfff333) EndIf If Number($currentStock) < 10 Then GUICtrlSetBkColor ( -1, 0xff0000) EndIf Next EndFunc However, if you want to work with Excel only, then you will probably want to use a UDF like this one: Edited October 27, 2011 by sleepydvdr #include <ByteMe.au3> Link to comment Share on other sites More sharing options...
sleepydvdr Posted October 27, 2011 Share Posted October 27, 2011 (edited) OK, forget what I posted before. Here's a workable example: expandcollapse popup#include <GUIConstantsEx.au3> #include <ListViewConstants.au3> #include <WindowsConstants.au3> #include <ButtonConstants.au3> #include <StaticConstants.au3> #include <array.au3> #include <ComboConstants.au3> Global $productCode, $make, $model, $currentStock, $dataModified, $productToEdit $Form1 = GUICreate("Form1", 623, 442, 192, 124) $ListView1 = GUICtrlCreateDummy() $Button1 = GUICtrlCreateButton("Add A Product", 16, 408, 91, 25) $Button2 = GUICtrlCreateButton("Edit A Product", 120, 408, 91, 25) $Button3 = GUICtrlCreateButton("Delete A Product", 224, 408, 107, 25) GUISetState(@SW_SHOW) _RefreshData() While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit Case $Button1 _AddAProduct() Case $Button2 _EditProduct() Case $Button3 _DeleteItem() EndSwitch WEnd Func _AddAProduct() $Form2 = GUICreate("Product Inventory", 336, 258, 302, 218) $aInput1 = GUICtrlCreateInput("", 16, 24, 193, 21) $aInput2 = GUICtrlCreateInput("", 16, 64, 193, 21) $aInput3 = GUICtrlCreateInput("", 16, 104, 193, 21) $aInput4 = GUICtrlCreateInput("", 16, 144, 193, 21) $aInput5 = GUICtrlCreateInput(@MON & "/" & @MDAY & "/" & @YEAR, 16, 184, 193, 21) $Label1 = GUICtrlCreateLabel("Product Code", 224, 24, 69, 17) $Label2 = GUICtrlCreateLabel("Make", 224, 64, 31, 17) $Label3 = GUICtrlCreateLabel("Model", 224, 104, 33, 17) $Label4 = GUICtrlCreateLabel("Current Stock", 224, 144, 69, 17) $Label5 = GUICtrlCreateLabel("Date Modified", 224, 184, 70, 17) $cButton3 = GUICtrlCreateButton("Add Product", 120, 216, 91, 25) GUISetState(@SW_SHOW) While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE GUIDelete($Form2) ExitLoop Case $cButton3 $productCode = GUICtrlRead($aInput1) $make = GUICtrlRead($aInput2) $model = GUICtrlRead($aInput3) $currentStock = GUICtrlRead($aInput4) $dataModified = GUICtrlRead($aInput5) $errorCheck = IniRead("InventoryData.ini", $productCode, "Product Code", "NotInDatabase") If $errorCheck = "NotInDatabase" Then IniWrite("InventoryData.ini", $productCode, "Product Code", $productCode) IniWrite("InventoryData.ini", $productCode, "Make", $make) IniWrite("InventoryData.ini", $productCode, "Model", $model) IniWrite("InventoryData.ini", $productCode, "Current Stock", $currentStock) IniWrite("InventoryData.ini", $productCode, "Date Modified", $dataModified) Else MsgBox(0, "Error", "The product is already in the database") EndIf GUIDelete($Form2) _RefreshData() ExitLoop EndSwitch WEnd EndFunc ;==>_AddAProduct Func _RefreshData() GUISwitch($Form1) GUICtrlDelete($ListView1) $ListView1 = GUICtrlCreateListView("Product Code|Make|Model|Current Stock|Date Modified", 16, 8, 586, 390) GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 0, 100) GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 1, 150) GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 2, 150) GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 3, 85) GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 4, 100) $sectionNames = IniReadSectionNames("InventoryData.ini") If @error Then Dim $sectionNames[2] $sectionNames[0] = 1 $sectionNames[1] = "NotFound" EndIf _ArraySort($sectionNames, 0, 1) For $i = 1 To $sectionNames[0] Step 1 $productCode = IniRead("InventoryData.ini", $sectionNames[$i], "Product Code", "NotFound") $make = IniRead("InventoryData.ini", $sectionNames[$i], "Make", "NotFound") $model = IniRead("InventoryData.ini", $sectionNames[$i], "Model", "NotFound") $currentStock = IniRead("InventoryData.ini", $sectionNames[$i], "Current Stock", "NotFound") $dataModified = IniRead("InventoryData.ini", $sectionNames[$i], "Date Modified", "NotFound") GUICtrlCreateListViewItem($productCode & "|" & $make & "|" & $model & "|" & $currentStock & "|" & $dataModified, $ListView1) If Number($currentStock) < 100 Then GUICtrlSetBkColor(-1, 0xfff333) EndIf If Number($currentStock) < 10 Then GUICtrlSetBkColor(-1, 0xff0000) EndIf Next EndFunc ;==>_RefreshData Func _DeleteItem() $Form3 = GUICreate("Delete A Product", 303, 169, 302, 218) $sectionNames = IniReadSectionNames("InventoryData.ini") If @error Then ;MsgBox(0, "", "Error") Dim $sectionNames[2] $sectionNames[0] = 1 $sectionNames[1] = "NotFound" EndIf _ArraySort($sectionNames, 0, 1) $delCombo = GUICtrlCreateCombo("", 24, 40, 241, 25, BitOR($CBS_DROPDOWNLIST,$CBS_AUTOHSCROLL)) $delLabel1 = GUICtrlCreateLabel("Select an item to delete", 24, 16, 115, 17) $delLabel2 = GUICtrlCreateLabel("Warning: this cannot be undone", 24, 72, 186, 17) GUICtrlSetFont(-1, 8, 800, 0, "MS Sans Serif") GUICtrlSetColor(-1, 0xFF0000) $delButton1 = GUICtrlCreateButton("Delete Item", 168, 112, 91, 25) GUISetState(@SW_SHOW) For $i = 1 To $sectionNames[0] Step 1 GUICtrlSetData($delCombo, $sectionNames[$i]) Next While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE GUIDelete($Form3) ExitLoop Case $delButton1 $delete = GUICtrlRead($delCombo) IniDelete("InventoryData.ini", $delete) GUIDelete($Form3) _RefreshData() ExitLoop EndSwitch WEnd EndFunc ;==>_DeleteItem Func _EditProduct() $sectionNames = IniReadSectionNames("InventoryData.ini") If @error Then Dim $sectionNames[2] $sectionNames[0] = 1 $sectionNames[1] = "NotFound" EndIf _ArraySort($sectionNames, 0, 1) $Form4 = GUICreate("Edit a product", 303, 169, 302, 218) $delCombo = GUICtrlCreateCombo("", 24, 40, 241, 25, BitOR($CBS_DROPDOWNLIST,$CBS_AUTOHSCROLL)) $delLabel1 = GUICtrlCreateLabel("Select an item to edit", 24, 16, 115, 17) $delButton1 = GUICtrlCreateButton("Edit Item", 168, 112, 91, 25) GUISetState(@SW_SHOW) For $i = 1 To $sectionNames[0] Step 1 GUICtrlSetData($delCombo, $sectionNames[$i]) Next While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE GUIDelete($Form4) ExitLoop Case $delButton1 $productToEdit = GUICtrlRead($delCombo) If $productToEdit = "" Then GUIDelete($Form4) ExitLoop Else GUIDelete($Form4) _EditProductForm() ExitLoop EndIf EndSwitch WEnd EndFunc ;==>_EditProduct Func _EditProductForm() $make = IniRead("InventoryData.ini", $productToEdit, "Make", "NotFound") $model = IniRead("InventoryData.ini", $productToEdit, "Model", "NotFound") $currentStock = IniRead("InventoryData.ini", $productToEdit, "Current Stock", "NotFound") $dataModified = IniRead("InventoryData.ini", $productToEdit, "Date Modified", "NotFound") $Form5 = GUICreate("Form1", 336, 258, 302, 218) $cInput1 = GUICtrlCreateInput($productToEdit, 16, 24, 193, 21) $cInput2 = GUICtrlCreateInput($make, 16, 64, 193, 21) $cInput3 = GUICtrlCreateInput($model, 16, 104, 193, 21) $cInput4 = GUICtrlCreateInput($currentStock, 16, 144, 193, 21) $cInput5 = GUICtrlCreateInput(@MON & "/" & @MDAY & "/" & @YEAR, 16, 184, 193, 21) $Label1 = GUICtrlCreateLabel("Product Code", 224, 24, 69, 17) $Label2 = GUICtrlCreateLabel("Make", 224, 64, 31, 17) $Label3 = GUICtrlCreateLabel("Model", 224, 104, 33, 17) $Label4 = GUICtrlCreateLabel("Current Stock", 224, 144, 69, 17) $Label5 = GUICtrlCreateLabel("Date Modified", 224, 184, 70, 17) $Button3 = GUICtrlCreateButton("Edit Product", 120, 216, 91, 25) GUISetState(@SW_SHOW) #EndRegion ### END Koda GUI section ### While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE GUIDelete($Form5) ExitLoop Case $Button3 $productCode = GUICtrlRead($cInput1) $make = GUICtrlRead($cInput2) $model = GUICtrlRead($cInput3) $currentStock = GUICtrlRead($cInput4) $dataModified = GUICtrlRead($cInput5) IniWrite("InventoryData.ini", $productCode, "Product Code", $productCode) IniWrite("InventoryData.ini", $productCode, "Make", $make) IniWrite("InventoryData.ini", $productCode, "Model", $model) IniWrite("InventoryData.ini", $productCode, "Current Stock", $currentStock) IniWrite("InventoryData.ini", $productCode, "Date Modified", $dataModified) GUIDelete($Form5) _RefreshData() ExitLoop EndSwitch WEnd EndFunc ;==>_EditProductForm Edited October 27, 2011 by sleepydvdr #include <ByteMe.au3> Link to comment Share on other sites More sharing options...
kertz Posted September 25, 2012 Share Posted September 25, 2012 you managed to finish the script? Link to comment Share on other sites More sharing options...
DicatoroftheUSA Posted September 25, 2012 Share Posted September 25, 2012 With inventory, I recommend learning sqlite, and investing in a barcode printer and scanner. Statism is violence, Taxation is theft. Autoit Wiki Link to comment Share on other sites More sharing options...
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