Download Project & Source Codes - Thanks for your support!

 This page has been migrated to: https://pamaitech.com/downloads/

Add Control and Position Controls in UserForm Design Mode

Add Control and Position Controls in UserForm Design Mode

https://www.youtube.com/watch?v=csUhqCN0CBI

Option Explicit

Sub addctrl_design_mode_v2()
't1-54;114;108;78
'T54, 60/42, T54

'Microsoft Visual Basic Applications Extensibility 5.3
Dim UFvbc As VBComponent
Dim r As Long

Set UFvbc = ThisWorkbook.VBProject.VBComponents("UserForm1")
Dim cb As Control
Dim t As Long, h&, w&, rowCnt&, ColCnt&, objLeft&, vGap&, objLeft_last&, totalColumns&
Dim objRow_last As Long
Dim TotalRows&

t = 15 'top
h = 10 'hieght
w = 84 'width

rowCnt = 1
ColCnt = 1
objLeft = 40
objLeft_last = 10

vGap = 1
objRow_last = 20

TotalRows = 10
totalColumns = 11

For r = 1 To (TotalRows * totalColumns) '(6 * 7)

Set cb = UFvbc.Designer.Controls.Add("Forms.Label.1", "Label" & r, True)
'Set cb = UFvbc.Designer.Controls.Add("Forms.Textbox.1", "Textbox" & r, True)
    
    't1_r1_c1
    cb.Name = "t1_r" & rowCnt & "_c" & ColCnt
    cb.BackColor = &HC0C0C0   'vbGreen
    cb.Height = h
    cb.Width = w
    cb.Top = objRow_last

    '----------------------
    If ColCnt = 1 Then
      cb.Left = objLeft
      objLeft_last = objLeft
      'vGap = 1
    Else
      cb.Left = objLeft_last + w + vGap
      objLeft_last = objLeft_last + w + vGap
    End If
    
    ColCnt = ColCnt + 1
    '----------------------
    
    If ColCnt = totalColumns + 1 Then
      ColCnt = 1
      rowCnt = rowCnt + 1
      objLeft_last = 10
      objRow_last = objRow_last + h + vGap
        
    End If
    
  Set cb = Nothing
  
Next r

End Sub

VBA to Upload Files to Access Database as Attachment

VBA to Upload Files to Access Database as Attachment - New Video

https://www.youtube.com/watch?v=NXttvEe4DSo

Option Compare Database
Option Explicit

Sub upload_file_from_excel_to_access_db()

Dim db As DAO.Database
Dim ws As DAO.Workspace

Dim rst As DAO.Recordset
Dim attachFld As DAO.Recordset

Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase("D:\VBAA2Z Demo\Access VBA\Attachment Load\org_db.accdb", False, False, "MS Access;PWD=")

Set rst = db.OpenRecordset("SELECT * FROM AttachmentDemotb;", dbOpenDynaset)

rst.AddNew
  
  rst!Title = "Test from Access-4"
  
  Set attachFld = rst.Fields("Attachements").Value
  
  attachFld.AddNew
    attachFld.Fields("FileData").LoadFromFile "D:\VBAA2Z Demo\Access VBA\Attachment Load\flows_report.pdf"
  attachFld.Update
  
  attachFld.AddNew
    attachFld.Fields("FileData").LoadFromFile "D:\VBAA2Z Demo\Access VBA\Attachment Load\plugandplay.JPG"
  attachFld.Update
  
  
rst.Update

rst.Close
db.Close
ws.Close

Set rst = Nothing
Set attachFld = Nothing
Set db = Nothing
Set ws = Nothing

End Sub

Kill function sample codes

Deletes files from a disk. The example below uses .txt file but you may replace with any file types.

Kill MacID("C:\Users\LP\AppData\Local\Temp\TestFile.txt")

If you use the MacID function with Kill in Microsoft Windows, an error occurs. An error also occurs if you try to use Kill to delete an open file.

' Assume TESTFILE is a file containing some data.
Kill "C:\Users\LP\AppData\Local\Temp\TestFile.txt" ' Delete file. 

 

' Delete all *.TXT files in the current directory.
Kill "C:\Users\LP\AppData\Local\Temp\*.TXT"