Magazine article Computers in Libraries

Interlibrary Loan Records with Excel

Magazine article Computers in Libraries

Interlibrary Loan Records with Excel

Article excerpt

Recently an interlibrary loan librarian asked me if I knew of a way to keep track of the number of times individual titles were requested, either by or from his library. Excel, in conjunction with the OCLC ILL subsystem and SaveScreen feature, can be used for this purpose.

Using SaveScreen

This procedure can save entire screens to a floppy or hard disk (a floppy is probably easier because you will not have to specify levels of subdirectories to retrieve the file into Excel).

Using SaveScreen Files in Excel OCLC's SaveScreen function saves the file as a text (ASCII) file that can be imported into Excel. To do this, start Excel as you usually do, and open the SaveScreen file. Excel will place the textual information in the first column of the worksheet. Although the column is not wide enough, since there is no information in the adjoining columns, widening column A is unnecessary. If you want to widen the columns, select it and depress ALT, format, column width.

You must place a name for the A column in the top row of the OCLC records. Insert a row ALT, edit, insert shift down) and place the name "OCLC" in cell A1.

We'll place the criteria to be used to perform the data extract on a separate portion of this worksheet. Looking at the OCLC records, you will notice that in the ILL subsystem all the titles are on lines that begin with a few blank spaces and a "3" followed by the title. To set the criteria, place the heading "OCLC" in the top cell, and in the cell below it press the spacebar a few times, type a "3" and then an asterisk (*). This tells Excel to look for a 3" followed by any text. Since the "3" designates the title field, this will cause Excel to find titles.

You must also set up the extract range by typing the heading "OCLC" in another portion of the worksheet. Since the extract range will be defined as only one line, Excel will erase anything in any cells in that column below it and use all the necessary cells. It is important not to have any information in the cells under the heading "OCLC" used as an extract range.

This worksheet can be named "OCLC." Excel will supply the extension to make the file name "OCLC.xlm."

Sheet1 Worksheet

Open a new worksheet or make sheet1 the active worksheet. This worksheet will be used to compile the extracted titles from the OCLC worksheet. In cell Al you can place a title such as Titles Requested." In cell A2, place a series of asterisks *) to underline the worksheet title. You must have something in cell A2 in order for the macro to work properly.

In cell B1 place the heading "Additional"; this column will be used to count the number of times a title has been requested after the first time.

Macro Sheet

Open a macro sheet. The macro that you write must do several things:

* make OCLC.XLS the active worksheet

* define the database range

* define the criteria

* define the extract range

* extract the titles from the defined database

* select the extracted titles

* cut and paste them to the sheet1 worksheet

* make sheet1.xls the active worksheet sort the extracted and existing titles on the list

* alphabetically determine how many additional times each title has been requested

The macro to do all these steps see Figure 1) is long and has to be written. Unfortunately the macro recorder will not accurately record all these steps.)

Macro to Set Up Database The macro begins by activating the OCLC.XLS worksheet:


The name of the worksheet is text and must be enclosed in quotation marks.

Then the macro defines the database, criteria range, and extract range: =DEFINE.NAME("database",$A:$A) =DEFINE.NAME("criteria",11:12) =DEFINE.NAME("extract",Kl)

These lines define the database as the entire A column, set the criteria range as cells I1:I2 (which in the example is where the criteria was typed), and set the extract range as K1. …

Search by... Author
Show... All Results Primary Sources Peer-reviewed


An unknown error has occurred. Please click the button below to reload the page. If the problem persists, please try again in a little while.