SHARE
TWEET

LoadByFlag

reac Sep 3rd, 2019 (edited) 109 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Sub LoadByFlag()
  2.  
  3. Const ROW_FIRST = 2
  4. Const COL_ID = 1
  5. Const COL_LOADED = 2
  6. Const COL_BARCODE = 3
  7.  
  8. Dim pictname As String
  9. Dim pictpath As String
  10. Dim pictext As String
  11.  
  12. Dim pastehere As Range
  13. Dim pasterow As Long
  14. Dim i As Long
  15. Dim lastrow As Long
  16.  
  17. Dim pictloaded As Boolean
  18.  
  19. Dim objFSO As Object
  20. Dim barcodeexists As Boolean
  21.    
  22.     On Error Resume Next
  23.  
  24.     pictpath = "D:\Dev\AGC\barcodes\barcode_"
  25.     pictext = ".png"
  26.     Set objFSO = CreateObject("Scripting.FileSystemObject")
  27.    
  28.     lastrow = Worksheets("Data").Range("A1").CurrentRegion.Rows.Count
  29.     For i = ROW_FIRST To lastrow
  30.    
  31.         Set pastehere = Cells(i, COL_BARCODE)
  32.         pasterow = pastehere.Row
  33.         Cells(pasterow, COL_BARCODE).Select
  34.         pictname = Cells(i, COL_ID)
  35.        
  36.         '/// Check for the loaded flag and only insert a picture if not present.
  37.        '/// Also check to make sure the barcode exists.
  38.        pictloaded = Cells(i, COL_LOADED) = "1"
  39.         barcodeexists = objFSO.FileExists(pictpath & pictname & pictext)
  40.         If (Not pictloaded) And barcodeexists Then
  41.        
  42.            ActiveSheet.Pictures.Insert(pictpath & pictname & pictext).Select
  43.            With Selection
  44.                .Left = Cells(pasterow, COL_BARCODE).Left
  45.                .Top = Cells(pasterow, COL_BARCODE).Top
  46.                .ShapeRange.LockAspectRatio = msoTrue
  47.                .ShapeRange.Height = 80#
  48.                .ShapeRange.Width = 80#
  49.                .ShapeRange.Rotation = 0#
  50.            End With
  51.            
  52.            '/// Make sure that we update the loaded flag for this row
  53.           Cells(i, COL_LOADED).Value = "1"
  54.            
  55.         End If
  56.        
  57.     Next
  58.  
  59. End Sub
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top