How to Write a Macro in Word That Reads Data From Excel

1. Open Excel, then type any value in the first cell of the first spreadsheet. Click the Office button's 'Save' command, then type 'c:\ReadFromExcel.xlsx' for the filename. Click 'Save' to save the workbook, then close Excel.
2. Click the Office button in Word, then click the 'Options' button. Click the 'Developer' checkbox to display the 'Developer' tab, which holds Word's macro commands.
3. Click the 'Developer' tab's 'Visual Basic' button to enter the Visual Basic development environment.
4. Click the 'Tools' menu, then click the 'References' command. Click the 'Microsoft Excel objects' item, which lets your macro access the virtual objects of Excel.
5. Type the following program in the code window. This program creates a link to the Excel application, then close the link. The program doesn't yet do anything with the established link. You'll now use the link to open the Excel workbook you created in step 1.Public Sub ReadExcelData()Dim pgmExcel As Excel.ApplicationSet pgmExcel = CreateObject('Excel.Application')pgmExcel.QuitEnd Sub
6. Type the following new statements after the 'Set' statement. These statements open the Excel workbook you created in step 1, then close the workbook. The revised program doesn't yet read any information from the workbook. The next statement you'll write will read information from the workbook.PgmExcel.Workbooks.Open 'c:\ReadFromExcel.xlsx'
7. Type the following statements after the 'Open' statement. This statement uses the pgmExcel object to read the value of the first cell in the workbook from step 1. The 'MsgBox' statement displays that value.MsgBox pgmExcel.ActiveWorkbook.Sheets(1).Cells(1, 1)
8. Click any of the program's statements, then click the 'Run' command of the 'Run' menu. Your program will run and display the value of the cell into which you typed in step 1.