Use Card Storage app to inventory items

When you need a quick tool to scan the barcodes of the items you install on field you can use Card Storage app from Google Play.

Card storage app

The app allows you to store (at this time, Feb 2018) the following data:

  • Two pictures (front and back view fields)
  • Barcode picture
  • Barcode info
  • Two fields of text (card title and description)
  • A label field, visually a color but saved as a number
  • A flag to mark as Favorite, stored as boolean

All info can be backed up to Google Drive as a zip archive called card_storage.zip. From there, you can download it to your computer and use the following code to extract the data.

Usage example

  • A picture of item and a picture of the surroundings (front and back view fields)
  • Barcode picture – as is
  • Barcode info – as is
  • Customer & Location as Title; item description as description
  • Unit of items as Label
  • Urgent as Favorite

 

VBA code to get the info

Click to expand the code snippet
 
  1. Public Sub List_Card_Storage()
  2.     Const myZipDATFile = "card_storage.dat"
  3.     Const myNewFile = "card_storage.xls"
  4.     Const MainSheet = "card_storage"
  5.     Const NewSheet = "List"
  6.     Const LastCol = 8
  7.    
  8.     Dim f, i, j
  9.     Dim StrFile, myNewDATFile
  10.     Dim myNewPath As String, tempValue As String, tempLabel As String
  11.     Dim LasRow As Double
  12.    
  13.     myNewPath = Unzip()
  14.    
  15.     'myNewDATFile = entUnZip1File(myZipPath, myNewPath, myZipDATFile)
  16.    Call AddPNGExtension(myNewPath)
  17.    
  18.     f = FreeFile
  19.     Open myNewPath & myZipDATFile For Input As #f
  20.     ' read whole file into variable
  21.    StrFile = Input(LOF(f), #f)
  22.     Close f
  23.     ' replace some characters to make the file readable
  24.    StrFile = Replace(StrFile, "},{", vbCr)
  25.     StrFile = Replace(StrFile, ",", vbTab)
  26.     StrFile = Replace(StrFile, ":", vbTab)
  27.     StrFile = Replace(StrFile, "[{", "")
  28.     StrFile = Replace(StrFile, "}]", "")
  29.    
  30.     f = FreeFile
  31.     Open myNewPath & myNewFile For Output As #f
  32.     ' save newly formated file
  33.    Print #f, StrFile
  34.     Close f
  35.    
  36.    
  37.     Application.DisplayAlerts = False
  38.     Application.ScreenUpdating = False
  39.     ChDir myNewPath
  40.     Workbooks.Open Filename:=myNewPath & myNewFile
  41.     Sheets.Add After:=ActiveSheet
  42.     ActiveSheet.Name = NewSheet
  43.     ' here you can rename the column headers to be more meaningful to you
  44.    Cells(1, 1).Value = "Title"
  45.     Cells(1, 2).Value = "Description"
  46.     Cells(1, 3).Value = "Front_picture"
  47.     Cells(1, 4).Value = "Back_picture"
  48.     Cells(1, 5).Value = "Barcode_picture"
  49.     Cells(1, 6).Value = "Label"
  50.     Cells(1, 7).Value = "Favorite"
  51.     Cells(1, 8).Value = "Barcode_value"
  52.     Sheets(MainSheet).Select
  53.     LasRow = Range("A" & Rows.Count).End(xlUp).Row
  54.    
  55.     For i = 1 To LasRow
  56.         For j = 1 To 16 Step 2
  57.             tempValue = Cells(i, j + 1).Value
  58.             tempLabel = LCase(Cells(i, j).Value)
  59.             Sheets(NewSheet).Select
  60.             Select Case tempLabel
  61.                 Case "title"
  62.                     Cells(i + 1, 1).Value = tempValue
  63.                 Case "description"
  64.                     Cells(i + 1, 2).Value = tempValue
  65.                 Case "front_picture"
  66.                     Cells(i + 1, 3).Select
  67.                     ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
  68.                         Address:=myNewPath & tempValue & ".png", TextToDisplay:=tempValue & ".png"
  69.                 Case "back_picture"
  70.                     Cells(i + 1, 4).Select
  71.                     ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
  72.                         Address:=myNewPath & tempValue & ".png", TextToDisplay:=tempValue & ".png"
  73.                 Case "barcode_picture"
  74.                     Cells(i + 1, 5).Select
  75.                     ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
  76.                         Address:=myNewPath & tempValue & ".png", TextToDisplay:=tempValue & ".png"
  77.                 Case "label"
  78.                     Cells(i + 1, 6).Value = tempValue
  79.                 Case "favorite"
  80.                     Cells(i + 1, 7).Value = tempValue
  81.                 Case "barcode_value"
  82.                     Cells(i + 1, 8).Value = "'" & tempValue
  83.             End Select
  84.             Sheets(MainSheet).Select
  85.         Next j
  86.     Next i
  87.     Sheets(NewSheet).Select
  88.     Cells.Select
  89.     Selection.ColumnWidth = 19
  90.    
  91.     Sheets(MainSheet).Delete
  92.     MsgBox "Done!"
  93.     Application.DisplayAlerts = True
  94.     Application.ScreenUpdating = True
  95. End Sub
  96. '
  97. Function Unzip() As String
  98.     ' needs reference to Microsoft Scripting Runtime
  99.    Dim fso As Object
  100.     Dim oApp As Object
  101.     Dim Fname As Variant
  102.     Dim FileNameFolder As Variant
  103.     Dim DefPath As String
  104.     Dim strDate As String
  105.     Dim test As String
  106.     Fname = Application.GetOpenFilename(filefilter:="Zip Files (*.zip), *.zip", _
  107.                                         MultiSelect:=False)
  108.     If Fname = False Then
  109.         'Do nothing
  110.    Else
  111.         'Root folder for the new folder.
  112.        DefPath = Left(Fname, InStrRev(Fname, "\"))
  113.         'Create the folder name
  114.        FileNameFolder = Mid(Fname, Len(DefPath) + 1, Len(Fname) - Len(DefPath) - 4)
  115.         'Make the normal folder in DefPath
  116.        test = Dir(Left(Fname, Len(Fname) - 4) & "\*.*")
  117.         If test <> "" Then ' if folder exists, delete files inside
  118.            Kill Left(Fname, Len(Fname) - 4) & "\*.*"
  119.         Else
  120.             MkDir FileNameFolder
  121.         End If
  122.        
  123.         'Extract the files into the newly created folder
  124.        Set oApp = CreateObject("Shell.Application")
  125.         oApp.Namespace(DefPath & FileNameFolder).CopyHere oApp.Namespace(Fname).items
  126.         Unzip = DefPath & FileNameFolder & "\"
  127.     End If
  128. End Function
  129. '
  130. Sub AddPNGExtension(MyFolder As String)
  131.     Dim fso As Scripting.FileSystemObject
  132.     Dim SourceFolder As Scripting.Folder
  133.     Dim S As Integer, fl As Scripting.file, i As Integer
  134.     Dim nameArray() As String
  135.     On Error GoTo err_treat
  136.     Set fso = New Scripting.FileSystemObject
  137.     Set SourceFolder = fso.GetFolder(MyFolder)
  138.     ' get file names
  139.    S = 0
  140.     For Each fl In SourceFolder.Files
  141.         If InStr(1, fl.Name, ".") = 0 Then
  142.             ReDim Preserve nameArray(S)
  143.             nameArray(UBound(nameArray)) = fl.Name
  144.             S = S + 1
  145.         End If
  146.     Next fl
  147.    
  148.     ChDir MyFolder
  149.     For i = 0 To UBound(nameArray)
  150.         Name nameArray(i) As nameArray(i) & ".png"
  151.     Next i
  152.    
  153. exit_sub:
  154.     Exit Sub
  155. err_treat:
  156.     Select Case Err.Number
  157.     Case 76
  158.         Exit Sub
  159.     Case Else
  160.         MsgBox Err.Number & vbCr & Err.Description
  161.     End Select
  162. End Sub

 

How to use the code

  • Double-click the snippet above and press Ctrl+c to copy the code.
  • Open an Excel file, right-click on a tab and select View Code
  • Use Insert => Module to add a new module
  • Immediately paste the code with Ctrl+v
  • Use Tools => References and check Microsoft Scripting Runtime
  • Return to Excel (Alt+q)
  • Use Developer => Macros and run List_Card_Storage. If the menu doesn’t exist, go to File => Options => Customize Ribbon and check Developer on the right side.
  • When the macro starts, it will prompt you to select the Card Storage archive. It may have any name, doesn’t matter if you renamed it.
  • When ready you’ll see a message box.

 

What you’ll get

On the same folder with the zip file there will be created a new subfolder with the same name as the zip, containing:

  • The archive’s content. Compared to the zip, all pictures will have the .png extension
  • The original .dat file storing the info you input on Card Storage app
  • The excel file called card_storage.xls, containing the result, as in the picture below

 

Important

  • There’s no need to fill in all fields when you create the cards in app. Use only what you need plus the mandatory fields like barcode itself
  • As long as you don’t move the files the pictures can be accessed from Excel by clicking on their hyperlinks
  • If you have trouble using the code, you can download a ready to use file from my Google Drive.

Add a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.