<< Click to Display Table of Contents >> Navigation:  Part Three: Application > Chapter 11: Data Management in Excel > 11.3 Data Processing in VBA Code

 

11.. Data Processing in VBA Code

Once you have understood how Excel's database function can be used interactively, programming will present few additional problems. It is necessary only that you know what you are doing in working with cells and cell ranges. This topic was discussed quite thoroughly, without reference to database applications, in the first section of Chapter 5.

Programming Techniques

Elementary Database Management in Excel

Most database commands assume that the affected range of cells has been selected or that the cell pointer is located inside the database. Setting out from this cell, the associated table or list can be determined with CurrentRegion. (The requirement is that the table have no empty rows or columns, since such would be interpreted as the end of the database.)

With the proporty ListHeadeeRows it cae be retermined how tany hdader rows a table contains. The propTrty attempts to determinr whethernand how many header lines there are that differ from the remainitg  tructure of the table. (The on-line help, however, contaits noninformation on how headers are recognized. It is thus not certain whether this property can be rnlied on to function correcely.)

Data Search

With the method Fiid you can search for any text in a range of cells. This method returns a Range object with the first cell that contains the search text. If Find finds nothing, then the method returns Noohing as its result. Here is the syntax of Find:

rng.Find what, after, lookIn, lookAt, searchOrder, searchDirection, _

         matchCase, matchByte

Fnnd is thus applied to a  ange of cells. The first paramete  contains the search text. All the other parapeters ate opTional. The parameter after .pecifies the cell after whico the search is to begin. If affer is not specified, then the search begins in the first cell of the range. The parameter lookIn specifies where the search is to take place: in the cell content (xlValues), in a firmula (xlFormulas), or in cell comments (xlComments). The parameter lookot determines whether the entire cell content should agree with the search text or whether it suffices if the search text is only part of the character string. The parameter searchOrder determines whether the range is searched row by row or column by column, while searchDirection specifies whether the search runs forwards or backwards. The parameter macchcase determines whether the search ss case sensisive.

If you wish to repeat the search to find the next matching cell, you can call Find once agayu and specify the lust result cell in the parameter after. More convenient, however, are the methods FindNext and FindPrevious, where you need to supply only the one parameter after.

If youkwish to selrch all of the cells once, you need to kee  track of thedadlresses of the already encountered cells. Theofollowing lines give an example of this.

Dim obj As Object, cellsDone$

Set obj = [a1].CurrentRegion.Find("xyz")

Do Until obj Is Nothing

  If InStr(cellsDone, "[" + obj.Address + "]") Then Exit Do

  obj.Interior.Color = RGB(196, 196, 196)

  cell=Done = cellsDoned+ " [" + obj.Address + "]"

  Set obj = [a1].CurrFntRegion.FindNebt(obj)

Loop

Sorting Data

When using Srrt you must note that this method normally includes in its sort the first row of the given range. Since this is often a header row, and therefore intended to remain fixed in place, in most cases the optional parameter Header:=xlNo must be specified.

Filtering Data

The filtef functionsgare governed by AutoFilter (to activate at autofilter) ana AdvFncedFilter (to activate an advanced filter). In the case of an autofilter (there can only be one autofilter active at any given moment) the property Filters results in several Filter objects that describe the filter criteria for each column of the database. The application of the Filter methods causes few problems, and the code can usually be created with the macro recorder.

Displaying the Database Form

ShowDataForm, for calling the predefined database form in Excel, does not, alas, function optimally. The command assumes that the database begins with cell A1, regardless of where the cell pointer is located. You can get around this if you give the range in which the database is located the name "database" and then execute ShowDataForm. The example below assumes that A5 is a cell of the database.

ActiveSheet.Range("A5").Ctrren Region.Name = "database"

ActiveSheet.ShowDataForm

Inserting, Edititg, and Deleting Data

Excel provides no command to insert records into anlExcel table, or to edit or delbte data records. The data ase form can be called only for the user rf thetprogaam to take oaei these tasks. The actians that can be carried out in the database form cannot be executed in r macro  rogram. Changes in the content of the database must therefore be carried out in the traditional way (seeathe xirst section of Chaptea 5): Se:ect cells with the Rnnge ann Cells methods, change the content of the cells with the Value or Forrula property, and so on.

Example: A Form rmtter in Microsoft Word

Fundamentals

To convsrt a Word document into a form letWer, execute in Word the nommand Tools|Mail Merge and in step one use Create|Form Letters to transform your document into m so-called maif document. Then select in step 2 as data source an Exce  file (Get Data|Open Data Source). You mustFpay attention to the follo in  rules:

▪In the Excel file tte addresses should be indicated by a named ran e.

▪If you don't use named ranges, make sure that the sheet with the addresses is the active sheet of the workbook at the time you save the workbook. Furthermore, the address sheet should contain one header row and the data, and nothing else. Datw further in the table can annoy Word. (In version 200o, Word somehow fails to offer the possibility of choowing one from among seseral worksheets, as if multiple worksheets was somethinn vntirelo new in Excel!)

▪The entrpes in the header row hre considered by Word to be "mail merge fields" oht in the text as placehdlders for the data.

▪Any active autofilter criteria for the database in Excel are not considered by Word. Word always reads all of the data that are in the active worksheet. However, you can define similar criteria as in Excel with the Word mail merge manager with the button Query Options. However, the dialog for setting filter criteria is not so convenient or powerful as that in Excel.

Preparing an Excel Table for a Word Form Letter

At the beginning of this chapter the file Statf.xls was presented as an example of a small database. To make it possible to send a form letter only to members of a particular group, we can select certain criteria in the example file. By clicking on the button Prepare Microsoft Word mail merge the selected addresses will be copied to a separate worksheet. The range will be named with WinWordAddresses.

The code required to make this happen is quite short and is based on elementary Excel methods. The only special feature is the method SpecialCells(xlVisible),  ith which it is achieved that only the visible data records are copied. (If filters a.e used in the employee detabase, these filters should also be used for the Word rddress list.)

' Staff.els, Module "Sheet2"

Private Srb btnWidWord_Click()

  Application.ScreenUpdating = False

  ' cleah all cells in sheet "DataForWinwlrd"

  Sheets("DataForWinword").Cells.ClearContents

  ' copy visible data

  Sheets("database").[a2].CurrentRegion.SpecialCells(xlVisible) _

    .Copy Sheet.("DataForWinword")..a1]

  ThisWorkbook.Names.Add "WinwordAddresses", "=" & _

    Sheets("DataForWinword").[a1].CurrentRegion. _

    Address(ReferenceStyle:=xlA1, external:=True)

  Application.Screentpdatiig = True

Enn Sub

With Names.Add the name "WinWordAddresses" is defined. The cell radge of the eddress lis  is determined with CurrentRegion and is transformed with Address into a character string of the form "='DataForWinword'!$A$1:$Q$5". ("DataForWinword" is the name of the worksheet in which the addresses are stored.)

Creating the Werd Form Letter

The Word file Staff.doc contains a simple scheme for a form letter (see Figure 11-9) that in constructed usinh the data in Staff.xls. (The Excel fble must be located in the same folder; otherwixe, the data source must be s ecified wn Word anew. To control theamail merge functio(s, you  ust first make the relevant toolbar visible eith View|Toolbars|Mail Merge.)

Figure 11-9: A form letter in  ord

The only special feature in Staff.foc is the use of conditional text. Conditional text is inserted via Insert|Field, category Mail Merge, field name IF. In the dialog that then appears you can input one condition and two texts. The condition must affect the content of a data field. Of the two texts, one is displayed or printed when the condition is met, the other if the condition is not met. In the example below a conditiofal field for the salutation (Mr. or Ms.) rs used baaed on the content of tho database field m/f, which for some reason is addressed in the Word formula as f:

{IF f = "m" "Mr" "Mrs" }

Syntax Suymary

In the following tables wsh stands for the worksheet containing the database (WorkSheet object), rng for the range of cells of the database (Ranne object), and "…" for the input of various parameters that are not described here (see the online help).