Importing Information in a Script using Excel 2007

VB scripts provide various methods of importing data in Excel. Excel's macro recorder is the best them.

Launch Excel and Press “Alt + F," to enable macros and select "Options>Trust Center>Macro settings>Enable all macros...>Trust access..." tabs, Press "OK" on all windows.

Right click Excel's status bar and click "Macro Recording." Click on macro recording tab.

Enter "loadword" under Record Macro box, then press "OK" button to close. Select "Insert," then "Object," on toolbar.

Choose "Create from file" from "Object" box, then browse to a existing Microsoft Word file. Select "Insert," and press "OK". Excel will load selected Word file on current worksheet. Stop macro recording by clicking square button.

Click recently loaded Word document on worksheet, then select "Delete" to remove. Press "Alt-F8" to bring up the list of macros, then double-click the "loadword" macro you just recorded. Excel will load Word document.

Type VB IDE by hitting "Alt + F8," clicking "loadword" from list of macros, then hit "Edit."

Adjust "loadword" subroutine to load a file given a file name to enter on a worksheet: Replace program code between "Sub" and "End Sub" keywords with following program code:

Dim s
s = Range("a1").Value
ActiveSheet.OLEObjects.Add(Filename:=s, _
Link:=False, DisplayAsIcon:= _
False).Select

Go back to Excel by hitting "alt + F11," then enter full path name of any Word file in cell "A1" of current worksheet.

Re run loadword macro as step 5, and Excel loads the specified file.
Click macro recording, enter "loadweb" for macro's name, then close macro recording box.

Click "Data," then "From Web" from toolbar. Move to "techfules.com," and click "Import". Hit "OK" on "Import Data" window. Excel will specify load web page, then finish macro recording as did in step 4.

Remove data loaded from Web. Replay "loadweb" macro by step 5's, with "loadweb" substituted for "loadword." Notice that Web document is reloaded on worksheet.