Visual Basic for Applications (VBA) is the superset language from which all other flavors of Visual Basic are derived. For the most part, the Visual Basic that you're accustomed to is VBA. For instance, VBA holds the math functions, such as Sqr(), Sin(), and Tan(). It has the common string functions, including (but not limited to) Format(), Mid(), Left(), Right(), and Instr(). VBA is also where you find the conversion functions, such as CStr(), CInt(), and CDbl(). The ability to declare and manipulate different types of variables (standard and object) is part of VBA. Again, apart from the graphical elements of the programming environment and the ability to create standalone programs, most of the functionality that you've come to know as VB is really VBA.
As of the Office 97 version, all applications in the Microsoft Office suite have VBA built in to them. Microsoft Project also contains VBA. Other software vendors have licensed VBA to be the underlying scripting language for their applications. One such prominent non-Microsoft application with built-in VBA is Visio, a popular program for making technical drawings and schematics.
You might wonder what having VBA "built in" to an application means in a hands-on sense. When VBA is built in to an application, the application is enabling itself to be scripted by VBA. The application is also exposing its functionality in a way that allows other applications access to it via VBA.
An application exposes its functionality through objects. Applications such as Word and Excel have hundreds of objects that they expose and that can be manipulated via VBA. For example, if you want to work with a Word table--either within Word or through another application--you use the Table object within Word. If you want to work with the grammar engine in Word, you use the CheckGrammar() method of the Word Document object. Getting a grasp on all these objects can be a chore, but you can use some tricks and tools to make working with objects a bit easier.
All Microsoft Office applications have VBA built in. You work with VBA just as you would with VB, through an Integrated Development Environment (IDE). In Office, the IDE is called the Visual Basic Editor. You can access the Visual Basic Editor for Word 97, Excel, and PowerPoint by choosing Macro from the Tools menu or by pressing Alt+F11 (see Figure 25.1). To get to the Visual Basic Editor in Access 97, you create a new module by selecting Module from the Insert menu.
FIGURE 25.1 In MS Office, you access the Visual Basic Editor as a macro tool.
Inside the Visual Basic Editor, you can code objects within the application or in other applications just as you would in a standard Visual Basic IDE. All the editing tools that you're accustomed to in the Visual Basic IDE--such as automatic word completion, parameter help, and Quick Info--are readily available to you in the Microsoft Office Visual Basic Editor.
As mentioned earlier, VBA allows you to do some very sophisticated programming to Microsoft Office after you have a grasp of the different objects residing within the various Microsoft Office applications. However, learning to manipulate all the properties, methods, and events of the various objects is difficult for the inexperienced. Luckily, Microsoft Office comes with a tool that not only gives you a quick introduction to the mysteries of the various objects and object models within Microsoft Office, but also writes code for you as you go along. This tool is known as the Macro Recorder.
Writing macros in Microsoft Office is the same as writing a procedure in VBA. When you use the Macro Recorder, you're required to declare a name for the macro that you want to record. This process is the same as naming a procedure in VBA. When you look at the resulting code generated by the Macro Recorder, you'll see that the name you assigned to the resulting VBA procedure is the same as the one that you assigned to the macro that you recorded.
Write a simple macro that automatically inserts a blank line
FIGURE 25.2 Naming a macro is operationally the same as naming a procedure.
FIGURE 25.3 Use the Record Macro toolbar to pause or stop recording a macro.
View the code that you've generated
Although this use of the Macro Recorder is somewhat simplistic, it's a practical way to show how to use it as a tool to generate and understand VBA code as it pertains to the Word object model. The Microsoft Office object model is tricky and takes some getting use to. If you don't know that within the Word object model, you use the TypeParagraph method of the Selection object to insert a blank line, you will be hacking VBA and Office for a long time, trying to figure out how to do a simple line insert. Clearly, the Macro Recorder is a good tool for learning how to manipulate the Word object model with VBA.
FIGURE 25.4 You access the Visual Basic code for a macro through the Macros dialog.
Microsoft Office is composed of hundreds of objects. The relation between these objects can be complex. Microsoft understood that having a detailed understanding of and operational facility with the properties, methods, and events of each object in the applications suite are nearly impossible tasks for the average programmer. To simplify working with objects, Microsoft made a tool that allows you to look at the different objects that reside within the various applications, ActiveX controls, and ActiveX components that you use when you program with VB and VBA. This tool is the Object Browser.
Access the Object BrowserYou access the Object Browser by pressing F2 from within the Visual Basic Editor in Microsoft Office or the Visual Basic IDE.
The Object Browser shows you all the properties, methods, and events for all objects contained in the ActiveX controls included in the project on which you're working. If your project references VB class libraries or ActiveX components, these also appear in the Object Browser. In addition to showing you various objects, the Object Browser allows you to access a description of an object's various properties, methods, and events. If you need more detailed information, the Object Browser lets you access the help file for a particular item. The Object Browser also allows you to search the various components for a specified object, property, method, or event.
The Object Browser displays only the ActiveX controls, ActiveX components, and class libraries added to a given project. If your project contains a class module in the Project Explorer, the methods, properties, and events of that class module will also appear in the Object Browser. If an ActiveX control or ActiveX component is referenced in your project, it too will appear in the Object Browser (see Figure 25.5).
FIGURE 25.5 You can see all the libraries accessible to the Object Browser from the Libraries drop-down list.
If you want to work within an ActiveX control that doesn't appear in the Object Browser, you must add it to your project by choosing Components from the Project menu (within the Visual Basic IDE) or by pressing Ctrl+T. If you're in the Visual Basic Editor of Microsoft Office, choose Additional Controls from the Tools menu. In the Component dialog, you can select the ActiveX control that you want to add.
If you want to add an ActiveX component to your project from within the VB IDE, choose References from the Project menu. If you are in the Microsoft Office Visual Basic Editor, choose References from the Tools menu. The References dialog shows all the ActiveX components registered on your computer. You select a component from this dialog.
Granted, the terminology can be confusing--going to the Components dialog to choose an ActiveX control and going to the References dialog to choose an ActiveX component--but you'll grow accustomed to it after a while. In the next section, where you interact with Word, you learn how to add ActiveX components to your project.
Download this project's codeYou can download the code for this program from http://www. mcp.com/info. At that site, you'll be asked to enter an ISBN; enter 078971633x and then click the Search button to go to the Book Info page for Using Visual Basic 6. The project name is SmpleVBA.prj.
Now that you have a fundamental understanding of the concepts and tools that you need in order to work with VBA and Microsoft Office applications, it's time to build a simple VB/Microsoft Office application. The application that you'll build allows you to take some textual data from a standalone Visual Basic program and insert that data into a Word document.
The tasks of this program are straightforward. When users click a button, the program will open a Word document, write a simple sentence (the contents of which come from a text box on a form in the VB application), save the document to a file with a serialized naming convention, close the document, and quit Word.
Create the sample application
FIGURE 25.6 The References dialog shows all the ActiveX components registered on your computer.
FIGURE 25.7 The text that you enter in the TextBox will be transferred to a Word document.
01 Static i As Integer
02 Dim strMsg As String
03 Dim w As New Word.Application
04
05 cmdWordApp.Enabled = False
06
07 If txtMain.Text = "" Then
08 MsgBox "You cannot use a blank TextBox", vbCritical, _
"Entry Error"
09 txtMain.SetFocus
10 cmdWordApp.Enabled = True
11 Exit Sub
12 Else
13
14 strMsg = txtMain.Text
15 End If
16
17 w.Documents.Add
18
19 w.Selection.TypeText (strMsg)
20
21 w.ChangeFileOpenDirectory (App.Path)
22
23 w.ActiveDocument.SaveAs filename:="VBAsmpl_" & _
CStr(i) & ".doc", _
FileFormat:=wdFormatDocument, _
LockComments:=False, Password:="", _
AddToRecentFiles:=True, WritePassword:="", _
ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
SaveNativePictureFormat:=False, SaveFormsData:=False, _
SaveAsAOCELetter:=False
24
25 w.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
26 w.Application.Quit
27 Set w = Nothing
28
29 strMsg = "The document, " & "VBAsmpl_" & CStr(i) & _
".doc" & vbCrLf
30 strMsg = strMsg & "has be saved in the directory, " & _
App.Path & "."
31 MsgBox strMsg
32
33 cmdWordApp.Enabled = True
34
35 i = i + 1
FIGURE 25.8 The simple VBA program connects Visual Basic to Word.
The key to understanding the code is to understand the Word Application object. Look at line 3 of the code:
Dim w As New Word.Application
Confirm that MS Office is on the computerDon't be misled into thinking that because you reference the Word Object Library at design time, Word magically goes with your program to the end user's system when he or she runs your program. It doesn't. You should provide some functionality in your code that confirms that Word is indeed installed on the user's computer.
Line 3 declares an object variable, w, that references a Word.Application object with the New keyword. The Word.Application object is, for all intents and purposes, a fully functional instance of Microsoft Word. After you create this object variable, you can access nearly all the functionality of Word through that object, provided that the user has Word installed on his or her computer.
Lines 7-15 are error-checking code to make sure that the user has typed in some text to enter into Word.
Line 17 is important; it adds a new document to the Word session. Just because you've opened an instance of a Word application, it doesn't automatically follow that you have a document on which to work. You don't. This line addresses this issue. (If you go to Word, you can close all the documents in Word and still have the application running without a document in it. You can't do much, but the application is running.)
Line 19 inserts the text from the text box into the Word document by using the Word.Application's Selection child object's TypeText method. Line 21 changes the Word's save directory to be the same as the VB application's.
Line 23 saves the document by using the ActiveDocument object's SaveAs method. ActiveDocument is a child object of the Word.Application object. (If you save a document in Word while running the Macro Recorder, you'll see this code generated.)
Line 25 closes the document. Line 26 closes the Word application. Line 27 clears the object variable's value. Lines 29-31 create and report a message to users, informing them where the saved Word document is located.
Line 35 increments the counter variable. This variable is concatenated to new filenames to accommodate the creation and addition of new Word document files should the user want to keep making documents, as shown in line 23.
Let's develop the techniques for having VB "talk to" Word a little further. Now, no edition of Visual Basic comes with an ActiveX control that enables you to check the spelling of a word. You can buy an ActiveX control from a third party to do spell checking, or you can use Word to accomplish this task.
One of Word's most commonly used features is the ability to check spelling. Word encapsulates this functionality into two methods: CheckSpelling and GetSpellingSuggestions. The GetSpellingSuggestions method is a member function of the Application and Range objects in Word. CheckSpelling is a member function of the Application, Range, and Document objects. You use CheckSpelling to determine whether a word is spelled correctly. If a word is misspelled, you use the GetSpellingSuggestions method to return a collection of SpellingSuggestion objects for the misspelled word. The SpellingSuggestion object has a number of properties that describe the object (see Figure 25.9).
FIGURE 25.9 You use the search area of the Object Browser to locate an object such as SpellingSuggestion, in addition to a property, method, or event for any object.
The Name property is a string that represents a suggested word to replace the misspelled word. Thus, if you want to find out whether a word is correctly spelled and obtain a list of suggested corrections in case of error, you pass the word in question to the CheckSpelling method as a parameter. If the method returns False, you pass the misspelled word to GetSpellingSuggestions. As described earlier, you'll receive a SpellingSuggestions collection. When you have the SpellingSuggestions collection in hand, you cycle through the collection, examining the Name property of each SpellingSuggestion object in the collection to determine which string is the suggestion.
Create a SpellCheck program
View the sample codeThe sample code for this exercise is in the project prjSplCk.vbp, which you can find on the Web at http:// www.mcp.com/info. This code is fully commented.
FIGURE 25.10 The word you enter to spell check in the text box is passed on to Word.
Private Const NUM_OF_SUGS = _[sr] "Number of Spelling Suggestions: "
lblSugs.Caption = NUM_OF_SUGS & CStr(0)
End
01 Dim wd As New Word.Application
02 Dim wdsp As Word.SpellingSuggestions
03 Dim i%
04 Dim strBuffer As String
05
06 On Error GoTo cmdCheckErr
07
08 lblSugs.Caption = NUM_OF_SUGS & CStr(0)
09 lblMsg.Caption = ""
10 lstSuggestions.Clear
11
12 strBuffer = txtMain.Text
13
14 cmdCheck.Enabled = False
15 cmdQuit.Enabled = False
16
17 frmMain.MousePointer = vbHourglass
18
19 If Not wd.CheckSpelling(strBuffer) Then
20 txtMain.ForeColor = vbRed
21 lblMsg.Caption = "Spelling Incorrect!"
22 wd.Documents.Add
23 Set wdsp = wd.GetSpellingSuggestions(strBuffer)
24 lblSugs.Caption = NUM_OF_SUGS & CStr(wdsp.Count)
25
26 For i% = 1 To wdsp.Count
27 lstSuggestions.AddItem wdsp(i%).Name
28 Next i%
29
30 wd.Documents.Close
31 Else
32 lblMsg.Caption = "Spelling OK"
33 End If
34
35 wd.Quit
36 set wd = nothing
37 txtMain.ForeColor = vbBlack
38 frmMain.MousePointer = vbDefault
39 cmdCheck.Enabled = True
40 cmdQuit.Enabled = True
41 Exit Sub
42 cmdCheckErr:
43 MsgBox Err.Description
44 wd.Q'uit
FIGURE _25.11 The Spell Check utility uses Word's SpellingSuggestions collection to report back corrections to the misspelled word.
With regard to declaring variables, line 1 declares an object variable, wd, that creates an instance of the Word.Application object. Line 2 declares an instance of the Word.SpellingSuggestions collection. Line 3 is a counter integer, and line 4 is a string buffer that holds message-box message strings. Line 6 puts some fundamental error checking in place through the On Error keywords. Lines 8-17 initialize the GUI for the Click() event procedure and disable the CommandButtons (Lines 14 and 15) so that users can't reinvoke the process until the spelling check is complete. (Hooking up to Word can take a bit of time--hence, the need to set the mouse pointer to an hourglass and disable the CommandButtons.)
Setting an object variable to NothingAn object variable doesn't hold a value the way data variables (such as Integer and Double) do. A variable of type Integer or Double actually contains the value you assign to it. When you assign a value to an object variable by using the Set keyword, you're assigning the location of the object in computer memory to be the value of the object variable. This is somewhat similar to the use of pointers in C++. When you're through with an object variable, it's good practice to reset it so that it doesn't contain any object's memory location as its value (this is called garbage collection). You do this with the keyword Nothing. The following code creates an object variable, sets it to a Word application object, and then releases the object variable from Word:
Dim wd As New Word.Application Set wd = Nothing
Line 19 begins the spelling check. Embedded in the If...Then...Else statement in lines 29-33 is a call to the Word.Application's CheckSpelling method (line 19), by way of the object variable wd. The string strBuffer assigned from the Text property of txtMain is passed as a parameter to CheckSpelling. If the return is True, flow of the program passes to line 32; if CheckSpelling returns False, the program offers some suggested spellings in the lstSuggestions ListBox (lines 26-28).
A document is added to the Word application as shown in line 23. This is important because the GetSpellingSuggestions method won't work if the instance of the Word.Application object doesn't have a document in its Documents collection. The collection variable wdsp is set to reference the SpellingSuggestions collection returned by the method GetSpellingSuggestions, as shown in line 24. After you set the collections variable, you cycle through each SpellingSuggestion in the collection by using a For...Next statement and add the suggestion to the ListBox, lstSuggestions. You find out how many SpellingSuggestion objects are in the SpellingSuggestions collection by examining the Count property of the collection. This is done on lines 23-29.
After this process, it's pretty much cleaned up. You close the document on line 30, quit the Word application on line 35, release the object variable its reference in line 36, reset the user interface on lines 37 and 38, and enable the CommandButtons in lines 39 and 40. You then Exit the click event handler in line 41 to avoid flow-through to the Error label in the line below.
You can use VBA with objects in Microsoft Word to find a word within one or more Word documents. Finding a word in a document is similar to checking the spelling of a word, except that you use some previously unused objects and a few different method calls.
To find a word within a Word document, you use the Range object and its child object, the Find object. The Range object can be thought of as a continuous area of a Word document, very similar to dragging your mouse pointer across a portion of a page or pages within Word. A Range object can contain a few words, a few sentences or paragraphs, or the entire contents of a document. In this case, the Range object will be the entire contents of an active document.
The Find object is almost identical to the Find dialog in Word. The Find object has many different properties--Font, ParagraphFormat, and Style, to name a few. With the proper amount of forethought, you can program the Find object to do anything that you would do within the Find (and Replace) dialog, were you working directly in Word.
For this WordSearch application, you'll program the Find object's Execute method and Found property to search multiple documents for a word. Then, if the sought-after word is found, the WordSearch program adds the document's filename to a ListBox control.
Create the WordSearch program
FIGURE _25.12 Numbering the steps necessary to utilize the Word Find program makes it easier for end users to use.
Dir1.Path = Drive1.Drive
File1.Path = Dir1.Path
End
01 Dim wd As New Word.Application
02 Dim myRange As Word.Range
03 Dim i%, j%, k%
04 Dim strPath As String
05 Dim NumOfFiles%
06 Dim strFileBuff() As String
07 Dim strMsg As String
08
09 If txtWord.Text = "" Then
10 strMsg = "No word has been entered for a seach." & _
bCrLf & vbCrLf
11 strMsg = strMsg & "Please enter a word."
12 MsgBox strMsg, vbCritical, "Missing Search Word"
13 txtWord.SetFocus
14 Exit Sub
15 End If
16
17 i% = File1.ListCount
18
19 For j% = 0 To i% - 1
20 If File1.Selected(j%) = True Then
21 NumOfFiles% = NumOfFiles% + 1
22 End If
23 Next j%
24
25 If NumOfFiles = 0 Then
26 strMsg = "A document file has not been selected." & _
vbCrLf & vbCrLf
27 strMsg = strMsg & "Please select one or more document "
28 strMsg = strMsg & "files."
29 MsgBox strMsg, vbCritical, "File Selection Error"
30 File1.SetFocus
31 Exit Sub
32 End If
33
34 ReDim strFileBuff(NumOfFiles% - 1)
35
36 For j% = 0 To i% - 1
37 If File1.Selected(j%) = True Then
38 strFileBuff(k%) = File1.Path & "\" & File1.List(j%)
39 k% = k% + 1
40 End If
41 Next j%
42
43 i% = 0
44 j% = 0
45 k% = 0
46
47 For j% = 0 To UBound(strFileBuff)
48 strPath = strFileBuff(j%)
49 cmdFind.Enabled = False
50 frmMain.MousePointer = vbHourGlass
51 frmMain.Caption = "Searching " & strPath
52 wd.Documents.Open (strPath)
53 wd.Documents(1).Activate
54 Set myRange = wd.ActiveDocument.Content
55 myRange.Find.Execute FindText:=txtWord.Text, _
Forward:=True
56
57 While myRange.Find.Found = True
58 i% = i% + 1
59 myRange.Find.Execute FindText:=txtWord.Text
60 Wend
61
62 If i% > 0 Then
63 List1.AddItem strPath
64 i% = 0
65 End If
66 Next j%
67
68 wd.Quit
69 set wd = nothing
70 frmMain.Caption = "Search Complete"
71 frmMain.MousePointer = vbDefault
72 cmdFind.Enabled = True
01 Dim wd As New Word.Application
02
03 On Error GoTo cmdOpenDocErr
04
05 If List1.Selected(List1.ListIndex) = True Then
06 wd.Documents.Open (List1.List(List1.ListIndex))
07 wd.Visible = True
08 End If
09 Exit Sub
10
11 cmdOpenDocErr:
12 Select Case Err.Number
13 Case 381
14 MsgBox "Please select a document.", vbCritical, _
"Document Selection Error"
15 List1.SetFocus
16
17 End Select
01 Dim wd As New Word.Application
02
03 If List1.Selected(List1.ListIndex) = True Then
04 wd.Documents.Open (List1.List(List1.ListIndex))
05 wd.Visible = True
06 End If
14. Save the project and run the code (see Figure 25.13).
FIGURE 25.13 The Word Search program allows you to open the Word documents in which the sought-after word is located.
Let's examine how the program works. The code behind the CommandButton cmdQuit, the DriveListBox, and the DirListBox was explained as you built the code. The areas of code that need detailed review are Listings 25.3 and 25.4, the cmdFind_Click() and cmdOpenDoc_Click() event handlers.
The cmdFind_Click() event handler in Listing 25.3 first ensures that there is indeed a search word entered in the TextBox (line 9). If there isn't, some error messages and behaviors are generated, and the event handler is terminated (lines 10-14). If a word is in the TextBox, the program figures out how many document files are in the FileListBox by using the FileListBox's ListCount property (line 17). Then, it cycles through the FileListBox to determine which files have been selected (lines 19-23). Another error trap is inserted to make sure that filenames have been selected (lines 25-32). The selected files are added to a string array for further manipulation (lines 34-41). The counting variables i%, j%, and k% reinitialize on lines 43-45.
The purpose of the code at this point is to set up for the subsequent work of opening an instance of a Word Document object. All the filenames for the files to be searched are stored in the string array strFileBuff(). Lines 47-66 are a For...Next loop in which one file at a time is opened as a Word.Document object (line 52) and set to be the document with the focus (line 53). The contents of the open document are set to the object variable for the Range object myRange (line 54). The Range object variable then implements the Execute method of its child object, the Find object. The Execute method takes two named parameters, FindText (the word to search for) and Forward (a Boolean value that directs Word to search forward). A While loop is set up around the condition of the Find object's Found property (line 57). If the word being sought is found, the Found property is automatically set to True. While the Found property is True (line 57), a counter variable is incremented (line 58) and the Execute method is reinvoked (line 59), keeping the While loop in force. After the counter variable i% is incremented, this means that the sought-after word has been identified within the document and that the document's filename should be added to the ListBox (line 63).
LBound() and Ubound()You use the LBound() and UBound() functions to determine the lower and upper elements of an array, respectively. For example, if you have the array MyArray(25), LBound(MyArray) evaluates to zero and UBound(MyArray) evaluates to 25.
The For...Next loop continues for all the filenames in the string array strFileBuff (line 66). When the upper bound of the string array strFileBuff is reached, the For...Next loop is exhausted. Then the Quit method of the Word.Application object wd is invoked, terminating the Word session (line 68). The user interface is reset. The user is notified that the search is complete (line 70). The mouse pointer resets to default (line 71), and the cmdFind CommandButton is enabled to allow the user to search other documents (line 72).
Listing 25.4 is the code for the CommandButton cmdOpenDoc_Click() event handler. This event handler opens the document selected in the ListBox that denotes a list of files in which a sought-after word was found.
Word can run unseenRemember, one tricky thing about accessing Word through VBA by using Word.Application object variables is that although Word is working hard in memory, it doesn't appear on the taskbar or onscreen unless the Visible property of the object is set to True. The only evidence that Word is active is the Winword entry in the task list that shows itself when you press Ctrl+Alt+Delete.
The code in Listing 25.4 creates an object variable, wd, which is an instance of the Word.Application object (line 1). An error handler is inserted at line 3 in case the ListBox doesn't have any document filenames or one hasn't been selected. After the document is selected, the Open method of the Window.Application's Documents collection is invoked on the file selected in the ListBox (line 6). When the document is opened, the Word application running in memory is brought forth to the user. This is done by setting the Word.Application's Visible property to True (line 7).
Listing 25.5 is similar to Listing 25.4, except that the error- handling code has been omitted. Logic dictates that the Dbl_Click() event handler of the ListBox wouldn't be invoked unless there were files listed in it.
Data accessWe won't cover the basics of data access using DAO here. If your DAO is rusty, you might want to review Chapter 30, "Database Basics and the Data Control," and Chapter 32, "Enhancing Your Programs Using the Advanced Data Controls."
Until now, all the work you've done to make VB work with Office has centered on using Microsoft Word. Let's move on to two other applications in the Microsoft Office suite: Excel and Access. In this section, you'll build a utility named DBTransporter, which opens an Access database file, reads the tables and associated fields within the file, and then transports records from selected fields into an Excel spreadsheet.
You locate the data in the Access database .MDB file by using Data Access Objects (DAO). After you obtain the data from the Access database, use the Excel Application, Workbook, Worksheet, and Range objects to insert the retrieved data into an Excel spreadsheet.
Create the DBTransporter Utility
Download the project's codeAgain, the sample code for this exercise (in the project DBtoXLS.vbp) is available on the Web at http://www. mcp.com/info.
FIGURE 25.14 You use a CommonDialog control to locate the database from which to access data to send to Excel. The cdlgMain CommonDialog control is a windowless control.
End
Private Const ACCESS_SYSTEM_PREFIX = "MSYS" Private Const MAXIMUM_RECS = 20 Private Const TRANS_MSG = "DBTransporter is _ transporting..." Private Const DEFAULT_XLS_FILENAME = "DBTrans" Private gf_strDbPath As String
Getting commented codeThe code in the sample application, although completely identical in syntax and operation to the code listed here, is more highly commented. Much of what you'll read in this section is built in to the comments in that code.
01 Dim ws As Workspace
02 Dim db As Database
03 Dim strTblName As String
04 Dim strFilter As String
05 Dim strMsg As String
06 Dim iReturn As Integer
07 Dim i%
08
09 strFilter = "Access DB (*.mdb)|*.mdb"
10 cdlgMain.Filter = strFilter
11
12 cdlgMain.ShowOpen
13
14 While gf_strDbPath = ""
15 gf_strDbPath = cdlgMain.filename
16 If gf_strDbPath = "" Then
17 strMsg = "You have not selected a file."
18 strMsg = strMsg & vbCrLf & vbCrLf
19 strMsg = strMsg & "Do you want to select one now?"
20 iReturn = MsgBox(strMsg, vbCritical + vbYesNo, _
"Selection Error")
21 If iReturn = vbNo Then
22 Exit Sub
23 Else
24 cdlgMain.ShowOpen
25 End If
26 End If
27 Wend
28
29 Set ws = DBEngine.Workspaces(0)
30 Set db = ws.OpenDatabase(gf_strDbPath)
31
32 cboTables.Clear
33
34 For i% = 0 To db.TableDefs.Count - 1
35 strTblName = db.TableDefs(i%).Name
36 If Left(UCase(strTblName), 4) <> _
ACCESS_SYSTEM_PREFIX Then
37 cboTables.AddItem strTblName
38 End If
39 Next i%
40
41 db.Close
42 ws.Close
LCase() and UCase()Use the LCase() and UCase() functions to change all characters in a given string to lowercase or uppercase, respectively. Suppose that you have the string strFirstName, containing the characters "Dorothy". LCase(strFirstName) evaluates to "dorothy" and UCase(strFirstName) evaluates to "DOROTHY". LCase() and UCase() are useful functions to use to avoid user typing errors. If you want to ensure that users enter the correct characters into your program, regardless of case, you can convert all the characters to either lowercase or uppercase and then check them. If, however, you require the data to be case-sensitive correct (a password, for example), you should check the characters as is.
01 Dim ws As Workspace
02 Dim db As Database
03 Dim tdef As TableDef
04 Dim i%
05 Dim strFieldName As String
06
07 lstFields.Clear
08 Set ws = DBEngine.Workspaces(0)
09 Set db = ws.OpenDatabase(gf_strDbPath)
10 Set tdef = _
db.TableDefs(cboTables.List(cboTables.ListIndex))
11 For i% = 0 To tdef.Fields.Count - 1
12 lstFields.AddItem tdef.Fields(i%).Name
13 Next i%
14
15 db.Close
16 ws.Close
01 Dim xla As New Excel.Application
02 Dim xlb As New Excel.Workbook
03 Dim xls As New Excel.Worksheet
04 Dim xlr As Excel.Range
05 Dim ws As Workspace
06 Dim db As Database
07 Dim rs As Recordset
08 Dim strSQL As String
09 Dim i%, j%, k%
10 Dim rc%
11 Dim r%, c%
12 Dim strFields() As String
13 Dim strTable As String
14 Dim strMsg As String
15 Dim strBuffer As String
16 Dim strFilter As String
17
18 For i% = 0 To lstFields.ListCount - 1
19 If lstFields.Selected(i%) = True Then
20 j% = j% + 1
21 End If
22 Next i%
23
24 If j% - 1 = -1 Then
25 strMsg = "You must select at least one field."
26 strMsg = strMsg & vbCrLf & vbCrLf
27 strMsg = strMsg & _
"Please select a field in the Field Listbox"
28 MsgBox strMsg, vbCritical, "Field Selection Error"
29 lstFields.SetFocus
30 Exit Sub
31 End If
32
33 ReDim strFields(j% - 1)
34
35 cmdQuit.Enabled = False
36 cmdOpenDB.Enabled = False
37 cboTables.Enabled = False
38 lstFields.Enabled = False
39
40 frmMain.MousePointer = vbHourGlass
41 strBuffer = frmMain.Caption
42 frmMain.Caption = TRANS_MSG
43
44 j% = 0
45
46 For i% = 0 To lstFields.ListCount - 1
47 If lstFields.Selected(i%) = True Then
48 strFields(j%) = lstFields.List(i%)
49 j% = j% + 1
50 End If
51 Next i%
52
53 strTable = cboTables.Text
54
55 Set ws = DBEngine.Workspaces(0)
56 Set db = ws.OpenDatabase(gf_strDbPath)
57
58 Set xlb = xla.Workbooks.Add
59 Set xls = xlb.Worksheets.Add
60
61 xls.Activate
62
63 For i% = 0 To UBound(strFields)
64 strSQL = "SELECT " & strTable & ".[" _
& strFields(i%) & "]"
65 strSQL = strSQL & " FROM " & strTable
66 Set rs = db.OpenRecordset(strSQL)
67
68 rs.MoveLast
69 rs.MoveFirst
70
71 c% = i% + 1
72 j% = 0
73 `Set the column name
74 xls.Cells(1, c%) = strFields(i%)
75 Set xlr = xls.Cells(1, c%)
76 `Make the top row bold
77 xlr.Select
78 xlr.Font.Bold = True
79
80 If rs.RecordCount > MAXIMUM_RECS Then
81 rc% = MAXIMUM_RECS
82 Else
83 rc% = rs.RecordCount
84 End If
85
86 For r% = 2 To rc% + 1
87 xls.Cells(r%, c%) = rs(strFields(i%))
88 rs.MoveNext
89 Next r%
90 Next i%
91
92 frmMain.MousePointer = vbDefault
93 frmMain.Caption = strBuffer
94
95 strMsg = "Access file: " & vbCrLf & vbCrLf
96 strMsg = strMsg & gf_strDbPath & vbCrLf & vbCrLf
97 strMsg = strMsg & "has been successfully transported."
98 MsgBox strMsg, vbExclamation, "Transport successful"
99
100 cmdQuit.Enabled = True
102 cmdSave.Enabled = True
103 cmdOpenDB.Enabled = True
104 cboTables.Enabled = True
105 lstFields.Enabled = True
106
107 strMsg = "DBTransporter has transported " _
& "the Access data "
108 strMsg = strMsg & "to an Excel spreadsheet."
109 strMsg = strMsg & vbCrLf & vbCrLf
110 strMsg = strMsg & "Do you want to save " _
& "the Excel spreadsheet?"
111
112 If MsgBox(strMsg, vbQuestion & vbYesNo) = vbYes Then
113 strFilter = "Excel Spreadsheet (*.xls)|*.xls"
114 cdlgMain.Filter = strFilter
115 cdlgMain.filename = DEFAULT_XLS_FILENAME
116 cdlgMain.ShowSave
117
118 If cdlgMain.filename <> "" Then
119 xls.SaveAs (cdlgMain.filename)
120 strMsg = "Access data has been saved to file:"
121 strMsg = strMsg & vbCrLf & vbCrLf
122 strMsg = strMsg & cdlgMain.filename
123 MsgBox strMsg, vbExclamation, "Spreadsheet saved"
124 End If
125 End If
126
127 xlb.Saved = True
128
129 xla.Quit
The code for DBTransporter is divided into three areas. The first area is the code that allows users to select a database and then automatically populates the ComboBox cboTables with the tables from the database. The second area is the code that populates the ListBox lstFields with the fields of a selected table. The last part is the code that does the actual transporting of data between the Access and Excel.
FIGURE 25.15 DBTransporter boldfaces the first row of the Excel sheet to distinguish the field names from the data.
Listing 25.6 is the code that presents a CommonDialog to users for selecting a database from which to transport data. Lines 1-7 set up the variables that you'll use throughout the event handler. For users to see only Access database files (.MDB), the code sets a file filter string to the string variable strFilter (line 9). That string is assigned to the Filter property of the CommonDialog control (line 10). The CommonDialog is shown in line 12, using the ShowOpen method.
An error trap loop is set up on lines 14-27 with a While loop. When users select a valid .MDB file, the program flow will move out of the loop. However, should users not choose a valid file, they will be prompted with a message box asking them whether they want to try again or discontinue the process (line 20). If they choose not to go on, the event handler is exited (line 22).
After an .MDB file is selected, the Workspace and Database object variables are set (lines 29 and 30), and the contents of the ComboBox are cleared (line 32). The database is examined for all the tables within it. The examination is done by running the counter variable i% from the For...Next loop as the index parameter of the database's TableDefs collection (line 35). As each TableDef is determined, it's checked to make sure that it isn't an Access system table (line 36). Access system tables begin with the letters Msys. These letters have been assigned to the constant ACCESS_SYSTEM_PREFIX in the Declarations section of the form. If a table isn't a system table, it's added to the ComboBox (line 37). Having exhausted the For...Next loop, the code disconnects from the database (lines 41 and 42).
The next order of business is getting all the field names from a chosen table listed in the lstFields ListBox (Listing 25.7). Lines 1-5 declare variables, some of which are DAO object variables, as you've seen before. Line 7 uses the Clear method to eliminate any field names that might exist in the ListBox because of a previous inspection.
Collections within databases are hierarchicalTo get a handle on the hierarchy of collections of a database, remember this: All databases contain a collection of tables, and each table contains a collection of fields.
Lines 8 and 9 open the database that the user has previously chosen. Line 10 determines the string that the user has clicked, using the ListBox's ListIndex property as the index of the ListBox's List property, and uses that string to determine which TableDef in the TableDefs collection should be set to the TableDef object variable tdef. After a table is determined, a For...Next loop is used to cycle through the Fields collection of the TableDef object and to add the field names to the ListBox lstFields by using the AddItem method (line 12). Having cycled through all the fields of a given table, the event handler disconnects the database from the program (lines 15 and 16).
The work of moving the data from Access to Excel begins after a database is chosen, a table is determined, and a set of fields is chosen to transport. This activity takes place in Listing 25.7.
Lines 1-16 declare many variables; most you've seen before, but a few are new. The new ones are Excel objects. To make this whole procedure more comprehensible, let's take a moment to review the hierarchy of objects in Excel. The base object is Excel.Application, which can hold multiple Workbooks during a session. Each Workbook can hold one or more Worksheets. Each Worksheet contains cells. A collection of geometrically contiguous cells is a Range. Lines 1-4 declare object variables for an Application, Workbook, Worksheet, and Range.
Resizing arraysYou can resize an array by using the ReDim keyword. If you have MyArray(25), you can increase the size of the array to 50 by using Redim MyArray(50). When you resize an array, however, all its elements lose their values. To resize an array and allow its elements to keep their values, use the Preserve keyword: Redim Preserve MyArray(50).
After the variables are declared, the program cycles through all the field names listed in the lstField ListBox to determine how many have been selected. If none have been selected (line 24), a message is sent to users, informing them of such (lines 25-28). The focus is returned to the ListBox (line 29), and the event handler is then terminated (line 30). If a field name or set of field names is selected, the string array that holds the field names for further manipulation is redimensioned (line 33). The CommandButtons are disabled on lines 35-38. The mouse pointer is set to an hourglass icon (line 40). Also, users are told that data is about to be transported by using the string constant TRANS_MSG, defined in the Declarations section of the form, as the value of the form's Caption property (lines 35-42).
The program cycles through the strings in the lstFields ListBox a second time, adding the selected strings to the string array strFields (lines 46-51). The database object variables are set, as are object variables for an Excel workbook and worksheet (lines 55-59). This effectively has Access and Excel open so that data can be passed between them. The added worksheet is brought to the foreground on line 61.
A For...Next loop is set up to accommodate each field listed in the strFields string array (line 63). The logic is that for each field in the For...Next loop, a SQL statement will be created, asking the table for all the records for that field (lines 64 and 65). The request is made on line 66, and a quick move is made through the returned recordset to figure out how many records were retrieved (lines 68 and 69). Data in hand, the appropriate column of the Excel worksheet in which to list the retrieved records of the field is determined (line 71). The program moves to the first row of the Excel worksheet (line 74) where the field name in the strFields() array is inserted as a column heading. A cell is assigned to the Excel Range object variable xlr (line 75) to take advantage of the Range object's Font.Bold property. The font in the first row of the column is set to boldface (lines 77 and 78).
For the program to execute more quickly, a limit has been placed on the number of records that the program will write to a given column. This limit is determined by the constant MAXIMUM_RECS, as defined in the Declarations section of the startup form. The Count of the retrieved recordset is compared with the maximum constant (lines 80-84).
When the column header has been written and set to boldface, the program proceeds through another For...Next loop, which retrieves the pertinent data from the recordset, writes the data to the cell, and then moves to the next recordset and next cell (lines 86-89). When the original For...Next loop cycle for the field is completed (line 90), the program repeats the cycle again, moving to the next column and using the next field name in the strFields() string array.
After the Excel sheet is filled with all data retrieved from the fields of a given table, the user interface is reset (lines 92 and 93), and users see a message box that tells them that the process has been successful (lines 95-98). The CommandButtons are enabled on lines 100-105. Then users are asked whether they want to save the newly created spreadsheet (lines 107-110). If they answer yes, the CommonDialog is presented again by using the ShowSave method to get a filename (line 116). The spreadsheet is saved by using the Worksheet object's SaveAs method. A message is sent to users, informing them that the file is saved. The Workbook object's SaveAs property is set to True (line 127) so that the Excel application object doesn't prompt users when DBTransporter quits Excel (line 129).
© Copyright, Macmillan Computer Publishing. All rights reserved.
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}