How to Use Visual Basic to Control Access, Excel and Word

Visual Basic has now launched Visual Basic for Applications or VBA. This program allows you to control virtually all aspects of Access, Excel and Word. It also allows you to manipulate one of the programs while in another. Follow these steps to learn how to use Visual Basic to control Access, Excel and Word.

Instructions

1. Use Visual Basic to control Access, Excel and Word by clicking on "Tools" on the program toolbar. Select "Macro" then "Record New Macro." This is where you can enter the commands in Steps 2 through 5.

2. Program the present date in Word by entering the following code: "Sub EnterCurrentDate()" "'EnterCurrentDate Macro" "'Macro recorded 15/03/2005 by Username" "'Selection.InsertDateTime DateTimeFormat:=dd-MM-yy", InsertAsField:=False,_DateLanguage:=wdEnglishAUS, CalendarType:=wdCalendarWestern, _InsertAsFullWidth:=False" "End Sub"

3. Automate your database tasks such as traversing a table by entering the following code: "Sub LoopTableExample" "Dim db As DAO.Database" "Dim rs As DAO.Recordset" "Set db = CurrentDb" "Set rs = db.OpenRecordset ("SELECT * FROM tblMain")" "Do Until rs.EOF" "MsgBox rs!FieldName" "rs.MoveNext" "loop" "rs.Close" "Set db = Nothing" "End Sub"

4. Create a UDF to use in a Excel workbook by entering this code: "Public Function BusinessDayPrior (dt As Date) As Date" "Select Case Weekday(dt, vbMonday)" "Case 1" "BusinessDayPrior = dt - 3 'Monday becomes Friday" "Case 7" "BusinessDayPrior = dt - 2 'Sunday becomes Friday" " Case Else" " BusinessDayPrior = dt - 1 'All other days become previous day" "End Select" "End Function"

5. Add an external application object to your program by entering this code: "Public Sub Example()" "Dim XLApp As Excel.Application" "Dim WDApp As Word.Application" "Set XLApp = CreateObject ("Excel.Applcation")" "set WDAPP = createObject("Word.Application") ' ...your code here... "XLApp.Quit" "WDApp.Quit" "Set XLApp = Nothing" "Set WDApp = Nothing" "End Sub"