XLAM add-in as database

From some time I’ve tried to find solution for fast and simply database use in Excel. Some time ago I’ve made test with XLAM add-ins as VBA code collection and database too. My project has been saved on network folder with authorization to all users.

In order to know how to create XLAM add-in please read this post: “Excel XLAM add-ins creation”.

Following code confirms that .xlam file might be use as “database” to storage needed data. After adding add-ins to Excel, your VBA code might be run in every .xlsx worksheet. Excel sees that two files are using such as ThisWorkbook and ActiveWorkbook. One is opened worksheet and the second is .xlam add-in.

Short explanation:

' Source: n/d

Public Sub XLAMDB()
Dim i As Long

With ThisWorkbook.Sheets("*******")
    i = 1
    Do Until .Cells(i, 1) = ""
        ActiveWorkbook.ActiveSheet.Cells(i, 1) = .Cells(i, 1)
        ActiveWorkbook.ActiveSheet.Cells(i, 2) = .Cells(i, 2)
        ActiveWorkbook.ActiveSheet.Cells(i, 3) = .Cells(i, 3)
    i = i + 1
    Loop
End With
End Sub

Next code is my project which shows how to use .xlam add-in as database. Somebody might ask “why?”. I will try explain it. Application has been created in order to check some warehouse storage bins – details are’t needed. What is important, user has to processing data exported from SAP. Normally to process data, content of exported file should be copied to .xlsm file where VBA code is located. In .xlsm file in some sheet there are storage bins added to be considered by app. It means that this sheet is some kind of database. Thanks to .xlam file there is no need to search .xlsm file, data hasn’t to copied. Code is ready to use when .xlsx file is just opened. Also it is difficult for normal user to delete some data from .xlam file.

Below entire project VBA code, but only a small part is a exaple how to use .xlam as database.
XLAM add-in as database example:

Option Explicit

' ----------- MODULE INFO v. 0.0.21 --------------------------------------------------------
' 1. Replenishment order generated autimaticlly to move some goods within warehouse.
'    Automatic data procesing receiving from SAP, final doc is given to warehouse employee 
'    as Goods Move Order.
' 2. Code from this module is hidden in .xlam file - this file has to be added to Excel 
'    as .xlam from user/local computer
' 3. XLAM add-in is using as database (Replenishment Bin sheet), where all needed 
'    information are kept to make VLOOKUP function.
' ------------------------------------------------------------------------------------------

Public Sub FinalizeReplenishment()
' Create Replenishmentu Goods Move Order

Dim XlamFileName, ReplenishmentFile, CodeVersion As String
Dim ThisWorkbookCounter, XlamWorkbookCounter, LastRow, i  As Long
Dim rng As Range
Dim ErrorJump, StartTime, EndTime, FinalTime

On Error GoTo ErrorJump

' Variables assignment
StartTime = Format(Time(), "Long Time")
CodeVersion = "App. v.0.0.21" 'to be change when upadate
XlamFileName = ThisWorkbook.Name
ReplenishmentFile = ActiveWorkbook.Name
ThisWorkbookCounter = 2
XlamWorkbookCounter = 2

With Workbooks(ReplenishmentFile).ActiveSheet

    ' Find last row in activated sheet
    LastRow = .Cells(.Rows.Count, "H").End(xlUp).Row	

    ' Adding VLOOKUP formula (much faster then loop) to show Replenishment
    ' bins found in .xlam database
    .Cells(1, 20) = "Formula"
    .Cells(2, 20).Formula = "=VLOOKUP(J2,'[" & XlamFileName & "]Replenishment Bin'!$A$2:$E$71,1,0)"
    .Range("T2").AutoFill Destination:=Range("T2:T" & LastRow)
    
    ' Data filtering - showing only Replenishment not all Transfer Order Numbers
    .Range("A1:T1").Select
    Selection.AutoFilter 
    .Range("T1").Activate
    .Range("$A$1:$T" & LastRow).AutoFilter Field:=20, Criteria1:="<>#N/A", Operator:=xlFilterValues
    
    ' Copying filtered data to other place in active sheet in order to keep them
    .Range("$A$1:$T" & LastRow).Select
    Selection.Copy
    .Cells(1, 22).Select
    .Paste
    
    ' Removing source data - preparing pleace fro needed data
    .Columns("A:U").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    
    ' Removing unneeded columns from final data
    .Cells(1, 2) = "Barcode TO"
    .Columns("G:I").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    .Columns("H:Q").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    
    ' Find last row in final data
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    
    ' Creating bar codes from TO numbers - set up font and font size
    .Cells.Select
    With Selection
        .Font.Name = "Calibri"
        .Font.Size = 11
        .VerticalAlignment = xlCenter
        .HorizontalAlignment = xlCenter
    End With
    For i = 2 To LastRow
        .Cells(i, 2) = "*" & .Cells(i, 1) & "*"
        .Cells(i, 2).Font.Name = "Free 3 of 9 Extended"
        .Cells(i, 2).Font.Size = 26
    Next i
    
    ' Adding inforamtion under Goods Move Order
    .Cells(LastRow + 2, 1) = "                            " ' easy way for wider column / better adjust for printed out
    .Cells(LastRow + 2, 2) = "Picking bins filling" 
    .Cells(LastRow + 2, 2).HorizontalAlignment = xlLeft
    .Cells(LastRow + 2, 3) = "                                  " ' easy way for wider column / better adjust for printed out
    .Cells(LastRow + 4, 2) = "Realizaed by - ........................... / date - ..................."
    .Cells(LastRow + 4, 2).HorizontalAlignment = xlLeft
    
    ' Borders adding
    Set rng = Range("A1:G" & LastRow)
    With rng.Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    
    ' Columns and rows autofit
    .Cells.Select
    Columns.EntireColumn.AutoFit
    Rows.EntireRow.AutoFit
    
    ' Adujst content to be printed
    .PageSetup.PrintArea = "$A:$G"
    Application.PrintCommunication = False
    With .PageSetup
        .PrintTitleRows = "$1:$1"
        .Orientation = xlLandscape
        .FitToPagesWide = 1
        .FitToPagesTall = False
        .CenterHorizontally = True
    End With
    Application.PrintCommunication = True
    
    ' Code execution confirmation
    .Cells(1, 1).Select
    EndTime = Format(Time(), "Long Time")
    FinalTime = Format(CDate(EndTime) - CDate(StartTime), "Long Time")
    MsgBox "Done in " & FinalTime & ".", vbInformation, CodeVersion & & " CONFIRMATION..."
    Exit Sub '
    
End With

Exit Sub
    
' Error handling
ErrorJump:

MsgBox "Code has been stopped. Problems should be solved - code has to be run once again." & Chr(10) & Chr(10) & _
        "Error no.: " & Err.Number & Chr(10) & _
        "Error desc.: " & Err.Description & Chr(10) & _
        "Code line: " & Erl, vbCritical, CodeVersion & " BLAD KODU..."
End Sub

50 thoughts on “XLAM add-in as database

  1. Pingback: ตู้แปลภาษา

  2. Pingback: mơ thấy quả ổi đánh con gì

  3. Pingback: chim bồ câu bay vào nhà

  4. Pingback: mơ thấy mất của

  5. Pingback: mơ cháy

  6. Pingback: mơ thấy ổ khóa

  7. Pingback: mơ thấy sâu đánh con gì

  8. Pingback: security services

  9. Pingback: mơ thấy con đỉa

  10. Pingback: mơ thấy sếp

  11. Pingback: mơ thấy voi

  12. Pingback: mơ bắt cá trê

  13. Pingback: mơ thấy con ngựa

  14. Pingback: những cách tỏ tình lãng mạn

  15. Pingback: người yêu cũ muốn quay lại

  16. Pingback: những cách khiến chàng yêu mình hơn

  17. Pingback: mơ thấy nhà vệ sinh bẩn

  18. Pingback: mơ thấy xem bói

  19. Pingback: nằm mơ thấy hoa sen

  20. Pingback: kimber 1911

  21. Pingback: sự tôn trọng trong tình yêu

  22. Pingback: bí kíp giữ chồng

  23. Pingback: mơ thấy chiến tranh

  24. Pingback: nằm mơ thấy phụ nữ

  25. Pingback: mơ thấy có con gái

  26. Pingback: mơ bắt được ếch đánh con gì

  27. Pingback: mơ thấy bố mẹ cãi nhau

  28. Pingback: mơ thấy gái đánh con gì

  29. Pingback: mo thay to ong

  30. Pingback: nằm mơ thấy tắm sông

  31. Pingback: Oxycontin For Sale Online

  32. Pingback: https://proxy-seller.com/

  33. Pingback: this post

  34. Pingback: Skrota bilen Trollhättan pris

  35. Pingback: qqslot

  36. Pingback: Parkridge

  37. Pingback: http://95.111.232.180/

  38. Pingback: Power to Choose Alternative

  39. Pingback: https://jacksontnpainters.com/

  40. Pingback: dumps101.us

  41. Pingback: how to sell your house on your own

  42. Pingback: dewaqq

  43. Pingback: https://www.pornachi.com/porn/family-strokes-full/

  44. Pingback: https://www.pornachi.com/porn/chanel-west-coast-upskirt/

  45. Pingback: https://www.pornachi.com/porn/clara-rugaard-nude/

  46. Pingback: Isha Dorsey

  47. Pingback: dumps shop 2019

  48. Pingback: expense reports

  49. Pingback: here

  50. Pingback: free uk sex dating

Leave a Reply

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