Using the PubMed Title Only Macro

(Revised 1/8/13)

General Description:

How to use a Microsoft (MS) Excel macro to convert an emailed list of PubMed publication summary results into aconcatenated list of titles that can be used in QVR to facilitate assignment of reviewers based on matching publication title and application terms.

This document describes the following processes (click on the relevant link to go directly to that section):

Emailing PubMed search results and running the MS Excel macro

Running the MS Excel macro

Searching QVR Searching QVR for Matching Title and Application Terms – Resulting ‘Match Score’

Installing the macro for use in MS Excel

Emailing PubMed Search Results

  1. Open the PubMed website’s main page:
  2. Enter criteria into the search box and click Search
  3. Either leave all unselected to send all or select specific publication summaries that you would like emailed
  4. Click on Send to: and select E-mail
  5. Enter the appropriate E-mail address and click on the E-mail button (shown below)

Running the macro within MS Excel

These instructions assume that the macro is installed and the command is placed on the Quick Access Toolbar (any icon can be chosen-doesn’t have to match what is shown below). If the macro is not installed, review the installation instructions below. If the macro is installed, but there is no button established, the following instructions can be used to create one: R:\SRP-DEAS-SHARED\Tools and Technical Guides\Office2010-CustomizeQuickAccessToolbar.doc

  1. It takes a few seconds for the pubmed email to be received. Once is it, select and copy all of the text from the body of the email into the first cell of a blank/new excel worksheet (sample shown below).

  1. Click the preset Quick Access Toolbar Button
  1. After the macro runs, the concatenated titles will appear on the third tab (shown below)

Searching QVR for Matching Title and Application Terms – Resulting ‘Match Score’

  1. Log into QVR:
  2. Click on Project Search
  3. Enter/select appropriate Study Section and Council for the related review meeting
  4. Click on the Search tab and then click “Like on the Fly”/QUICK LIKE

  1. Copy the concatenated title text from excel into the ‘Text (required): terms weighted by frequency’ text box

  1. Clickthe Find Matches button to view the resulting match scores per application based on the publication title terms
  2. To view details for a specific application click on the match score number (sample details shown below)

Installing the PubMed macro within MS Excel

The easiest way to install both the OldPrintWindow and PubMedTitlesOnly Macros is to replace or create a new MS Excel template file using the one that has both of these macros already saved. Instructions for both installation methods are listed below.

These steps have to be re-done if MS Excel is re-installed on the computer or the computer is replaced.

Installing the macro(s) by replacing the MS Excel Personal.XLSB template file

  1. Close the MS Excel program
  2. Copy the PERSONAL.XLSB file from the following location: R:\SRP-DEAS-SHARED\Tools and Technical Guides\Excel-Print Window+PubMedTitle Macros
  3. Paste it into the following folder (substitute the applicable NIH login name where specified):
  4. Windows XP: C:\Documents and Settings\NIHLogInName\Application Data\Microsoft\Excel\XLSTART
  5. Windows 7: C:\Users\NIHLogInName\AppData\Roaming\Microsoft\Excel\XLSTART

Note: If the ‘Application Data’ or ’App Data’ folder is not visible, the following Tools Menu\Folder Options default needs to be set.

  1. Open Excel
  2. The macro will now be available in the MS Excel program and can be added to the Quick Access Toolbar from the ‘Choose commands from: Macros’ list

If there is already a Personal Workbook template file established, add the macro from within MS Excel

  1. Open MS Excel
  2. Click on the Developer tab > Visual Basic button to open the Visual Basic window shown below
  1. Highlight and copy the text in Appendix A, then paste it into the window as shown above
  2. Select File\Save Personal.XLSB and then Select File\‘Close and Return to Microsoft Excel’
  1. The macro will now be available in the MS Excel program and can be added to the Quick Access Toolbar from the ‘Choose commands from: Macros’ list

Appendix A: PubMedTitlesOnly Macro Code

Sub PubMedTitlesOnly()

Worksheets("Sheet1").Columns("A").Delete Shift:=xlToLeft

Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Dim lngLastRow As Long

lngLastRow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row

Worksheets("Sheet2").Range("A1:A" & lngLastRow).Formula = "=OFFSET(Sheet1!$A$1,(ROW()-1)*5,0)"

Worksheets("Sheet2").Activate

Dim x As Long, lastrow As Long

lastrow = Cells(Rows.Count, 1).End(xlUp).Row

For x = lastrow To 1 Step -1

If Cells(x, 1).Value = "0" Then

Rows(x).Delete

End If

Next x

'concatenate code

Dim lRow As Long

Dim i As Long

Dim myString As String

Dim myCell As Excel.Range

myString = ""

lRow = Worksheets("Sheet2").Cells(Worksheets("Sheet2").Rows.Count, 1).End(xlUp).Row

For i = 1 To lRow

Set myCell = Worksheets("Sheet2").Cells(i, 1)

If Not IsEmpty(myCell) Then

If myString = "" Then

myString = myCell.Value

Else

myString = myString & ", " & myCell.Value

End If

End If

Next i

If myString > "" Then

Worksheets("Sheet3").Cells(1, 1).Value = myString

End If

Worksheets("Sheet3").Activate

Worksheets("Sheet3").Cells(1, 1).WrapText = True

Worksheets("Sheet3").Cells(1, 1).RowHeight = 100

Worksheets("Sheet3").Cells(1, 1).ColumnWidth = 90

Worksheets("Sheet3").Cells(1, 1).VerticalAlignment = xlTop

Worksheets("Sheet3").Name = "Titles-Concatenated"

Worksheets("Sheet2").Name = "Titles-In Rows"

End Sub

1