VBA Archives - BI Practice

Access VBA to Customize Report

In Microsoft Access, VBA code can be used to customize Access reports. Various subroutines can be embedded in the components of the report using “Private Sub”.
The following example applies to the format function/procedure of the Categoryfooter section of the report, so the notes(label) can be customized, showing or hiding the note contents fore specific group.
View of the report design:

The components in the report are objects that be used for VBA coding. The name of the components can be checked through the property window. The name of the components needs to match the object names in the VBA code window.

View of the project explorer:

View of the object and procedure selector in the code window:

In order for the VBA code to be effective, the code section are uniquely named as object_procedure. When you move the cursor from section to section in the code window, the Object and Procedure selector at the top of the code window will always reflect the object and procedure of the section where your cursor is.

Private Sub Categroyfooter_Format(Cancel As Integer, FormatCount As Integer)
If Me.level1.Value = "A" Then
    Me.CategoryFooter.Visible = True
    Me.catAfootnote.Visible = True
    Me.catAfootnote.Caption = "*Note:For 18/19, the admission information are as of the reporting date. "
ElseIf Me.level1.Value = "C" Then
    Me.CategoryFooter.Visible = True
    Me.catAfootnote.Visible = True
    Me.catAfootnote.Caption = "*Note:For 18/19, the enrolment FTEs are as of the reporting date."
Else
    Me.catAfootnote.Visible = False
    Me.CategoryFooter.Visible = False
    End If
End Sub

VBScript: Open and Process Excel Files in a Folder and Save in another Folder

In cmd, run “cscript test.vbs” under the folder where test.vbs is saved.

Contents of test.vbs

Set objFSO = CreateObject("Scripting.FileSystemObject")
sFolder = "X:\Work\test1\"
oFolder = "X:\Work\test1\result\"
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.DisplayAlerts = False
For Each objFile In objFSO.GetFolder(sFolder).Files
Set objWorkbook = objExcel.Workbooks.Open(sFolder & objFile.Name)
objWorkbook.sheets("delete").Select
objWorkbook.sheets("delete").Delete
objWorkbook.SaveAs oFolder & objFile.Name
objWorkbook.Close
objExcel.Quit
Set objWorkbook = Nothing
Next
Set objFSO = Nothing
Set objExcel = Nothing;

VBA: Tricks in Access VBA and VBscripting(1)

Naming of the procedures: Don’t name the procedure name the same as the module names.

module name

modulename

procedure name

procedurename

The following VBscripting code is trying to run a sub within Access. The sub is in a module called “RunQueries”. The sub was named “RunQueries” initially because it is the only procedure in the module. The Run method in the VBcripting couldn’t find the sub in the Access because it has the same name as the module. I changed the sub name to “OpenQueries” and the VPscript works fine now.

Dim objaccess
Set objaccess =CreateObject("Access.Application")
objAccess.OpenCurrentDatabase "D:\accessdb.mdb", False
objAccess.Run ("OpenQueries")
ObjAccess.Quit
set objAccess = Nothing

2. Name of the Object: If the object has event procedure attached to it, the name of the object needs to be consistent with the object name reference in the procedure name.

Object Name

objectname

Procedure Name

When you change object name in the Property window, the corresponding procedure name in the code window doesn’t update automatically and the event procedure following the old procedure name will not run until you manually change the procedure name prefix to match the new object name.

3. Compact Access database in VPscript

objAccess.Application.SetOption "Auto compact", True

VBA: VBScripting Reference

Microsoft WSH and VBScript Programming for the Absolute Beginner, Fourth Edition by Jerry Lee Ford

Terminology
VBScript: Visaual Basic Scripting

VBA: Visual Basic for Application

HTAs: HTML Applications

GUI: Graphical User Interface

WSH: Windows Script Host

Script Execution Host: CScript.exe and WScript.exe

object/property/method

Windows command prompt: C:\>

TRICK:

Run commands with access privilege.

cmd

File Extension:

VBScript: .vbs
JScript: .js

DLL: C:\WINDOWS\SYSTEM32\VBSCRIPT.DLL