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.

Jump to

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
  40.     ChDir myNewPath
  41.     Workbooks.Open Filename:=myNewPath & myNewFile
  42.     Sheets.Add After:=ActiveSheet
  43.     ActiveSheet.Name = NewSheet
  44.     ' here you can rename the column headers to be more meaningful to you
  45.    Cells(1, 1).Value = "Title"
  46.     Cells(1, 2).Value = "Description"
  47.     Cells(1, 3).Value = "Front_picture"
  48.     Cells(1, 4).Value = "Back_picture"
  49.     Cells(1, 5).Value = "Barcode_picture"
  50.     Cells(1, 6).Value = "Label"
  51.     Cells(1, 7).Value = "Favorite"
  52.     Cells(1, 8).Value = "Barcode_value"
  54.     Sheets(MainSheet).Select
  55.     LasRow = Range("A" & Rows.Count).End(xlUp).Row
  56.    
  57.     For i = 1 To LasRow
  58.         For j = 1 To 16 Step 2
  59.             tempValue = Cells(i, j + 1).Value
  60.             tempLabel = LCase(Cells(i, j).Value)
  61.             Sheets(NewSheet).Select
  62.             Select Case tempLabel
  63.                 Case "title"
  64.                     Cells(i + 1, 1).Value = tempValue
  65.                 Case "description"
  66.                     Cells(i + 1, 2).Value = tempValue
  67.                 Case "front_picture"
  68.                     Cells(i + 1, 3).Select
  69.                     ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
  70.                         Address:=myNewPath & tempValue & ".png", TextToDisplay:=tempValue & ".png"
  71.                 Case "back_picture"
  72.                     Cells(i + 1, 4).Select
  73.                     ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
  74.                         Address:=myNewPath & tempValue & ".png", TextToDisplay:=tempValue & ".png"
  75.                 Case "barcode_picture"
  76.                     Cells(i + 1, 5).Select
  77.                     ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
  78.                         Address:=myNewPath & tempValue & ".png", TextToDisplay:=tempValue & ".png"
  79.                 Case "label"
  80.                     Cells(i + 1, 6).Value = tempValue
  81.                 Case "favorite"
  82.                     Cells(i + 1, 7).Value = tempValue
  83.                 Case "barcode_value"
  84.                     Cells(i + 1, 8).Value = "'" & tempValue
  85.             End Select
  86.             Sheets(MainSheet).Select
  87.         Next j
  88.     Next i
  90.     Sheets(NewSheet).Select
  91.     Cells.Select
  92.     Selection.ColumnWidth = 19
  93.    
  94.     Sheets(MainSheet).Delete
  95.     MsgBox "Done!"
  96.     Application.DisplayAlerts = True
  97.     Application.ScreenUpdating = True
  98. End Sub
  99. '
  100. Function Unzip() As String
  101.     ' needs reference to Microsoft Scripting Runtime
  102.    Dim fso As Object
  103.     Dim oApp As Object
  104.     Dim Fname As Variant
  105.     Dim FileNameFolder As Variant
  106.     Dim DefPath As String
  107.     Dim strDate As String
  108.     Dim test As String
  110.     Fname = Application.GetOpenFilename(filefilter:="Zip Files (*.zip), *.zip", _
  111.                                         MultiSelect:=False)
  112.     If Fname = False Then
  113.         'Do nothing
  114.    Else
  115.         'Root folder for the new folder.
  116.        DefPath = Left(Fname, InStrRev(Fname, "\"))
  118.         'Create the folder name
  119.        FileNameFolder = Mid(Fname, Len(DefPath) + 1, Len(Fname) - Len(DefPath) - 4)
  121.         'Make the normal folder in DefPath
  122.        test = Dir(Left(Fname, Len(Fname) - 4) & "\*.*")
  123.         If test <> "" Then ' if folder exists, delete files inside
  124.            Kill Left(Fname, Len(Fname) - 4) & "\*.*"
  125.         Else
  126.             MkDir FileNameFolder
  127.         End If
  128.        
  129.         'Extract the files into the newly created folder
  130.        Set oApp = CreateObject("Shell.Application")
  131.         oApp.Namespace(DefPath & FileNameFolder).CopyHere oApp.Namespace(Fname).items
  132.         Unzip = DefPath & FileNameFolder & "\"
  133.     End If
  135. End Function
  136. '
  137. Sub AddPNGExtension(MyFolder As String)
  138.     Dim fso As Scripting.FileSystemObject
  139.     Dim SourceFolder As Scripting.Folder
  140.     Dim S As Integer, fl As Scripting.file, i As Integer
  141.     Dim nameArray() As String
  142.     On Error GoTo err_treat
  144.     Set fso = New Scripting.FileSystemObject
  145.     Set SourceFolder = fso.GetFolder(MyFolder)
  147.     ' get file names
  148.    S = 0
  149.     For Each fl In SourceFolder.Files
  150.         If InStr(1, fl.Name, ".") = 0 Then
  151.             ReDim Preserve nameArray(S)
  152.             nameArray(UBound(nameArray)) = fl.Name
  153.             S = S + 1
  154.         End If
  155.     Next fl
  156.    
  157.     ChDir MyFolder
  158.     For i = 0 To UBound(nameArray)
  159.         Name nameArray(i) As nameArray(i) & ".png"
  160.     Next i
  161.    
  162. exit_sub:
  163.     Exit Sub
  164. err_treat:
  165.     Select Case Err.Number
  166.     Case 76
  167.         Exit Sub
  168.     Case Else
  169.         MsgBox Err.Number & vbCr & Err.Description
  170.     End Select
  171. 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.