Listing Moving Notes from Word to Excel

Public Sheet As Integer Public Row As String Public Column As String

Public Sub SendNote()

Dim NoteText As String

With ActiveWindow.Selection

' Go to the beginning of the document. .GoTo What:=wdGoToLine, Which:=wdGoToFirst

' Select the document text.

.EndOf Unit:=wdSection, Extend:=wdExtend

' Get the text. NoteText = .Text End With

' Create a file dialog. Dim GetFile As FileDialog

Set GetFile = Application.FileDialog(msoFileDialogOpen) GetFile.AllowMultiSelect = False GetFile.Filters.Clear

GetFile.Filters.Add "Excel Files", "*.XL*"

' Get the Excel file. GetFile.Show

' Get the selected file.

Dim Filename As String

Filename = GetFile.Selectedltems(l)

' Open the Excel workbook.

Dim TheBook As Excel.Workbook

Set TheBook = Excel.Workbooks.Open(Filename)

' Request the sheet and cell number for the note. NoteSelect.Show

' Get the worksheet.

Dim TheSheet As Excel.Worksheet

Set TheSheet = TheBook.Sheets(l)

' Add the comment.

TheSheet.Range(Column + Row).AddComment NoteText

' Close the workbook. TheBook.Close True End Sub

The code begins by getting the note text from the Word document. This means placing the cursor at the beginning of the document, selecting a range of text, and copying that text to a local variable.

The next step is to figure out which Excel file to modify. The GetFile object is a file dialog box. Unfortunately, the file dialog box filters for Word point to Word documents (not to the Excel documents you need), so the code has to change the filter by using the Filters.Clear and Filters.Add methods. It's also important to set GetFile so that it doesn't allow multiple selections — this program works with only one file at a time.

After the code displays the dialog box and you select a file, GetFile contains the name of the selection. This is the full path to the file, so you can select a file anywhere, and the code still works.

It's time to open the Excel file. You don't actually see Excel open — everything takes place in the background. The code uses the value in Filename as input to the Excel.Workbooks.Open method.

Notice that everything to do with Excel begins with the word Excel. Word and Excel often use objects with the same name. Adding the word Excel avoids confusion.

This program also requires a custom form. Figure 16-2 shows what this form looks like. The NoteSelect form appears onscreen long enough for you to select a sheet, column, and row for the note.

Figure 16-2:

Design forms as needed to get program input.

9 Smvd Note, - WoleSefed ((JSLthi) | i= i Éj

Note Arec Selectio" \wë2m

o< I

1 '

iow

wOlunn

1 A

After the code knows what you want to write and where to place the information, it can open the Excel worksheet by using the Sheets collection. Notice how the code combines the Column and Row values to create a range for the comment. The AddComment method accepts the note text. Always make sure that you close the Workbook object when you're finished, or else you'll end up with multiple background copies of Excel.

The NoteSelect form has a few interesting features. The code shown in Listing 16-8 makes the OK button shown in Figure 16-2 functional.

_Chapter 16: Applications that Work Together 377

0 0

Post a comment