VBA or Visual Basic for Applications is a fantastic tool for automating a number of tasks across the Microsoft suite. Some of the greatest uses come from integrating Excel with Internet Explorer allowing a user to be able to scrape data off various websites and dump the data into Excel. This post will show how to write code allowing Excel to launch Internet Explorer and navigate to a webpage.
Next, under the Developer tab in Excel click “Macro Security” and enable macros. I usually select “Enable all macros” because I don’t ever run VBA code that I haven’t inspected personally, but consider selecting the security options that will be applicable to you.
Finally, click the Visual Basic button in the developer tab, and once in the editor, from the tools menu, select “References”. While for different VBA projects you might enable different libraries, the basics are going to be: Visual Basic for Application, Microsoft Excel Object Library, OLE Automation, Microsoft Office Object Library.
At this point, you’ve completed the configuration needed to run a basic Visual Basic project. The following block of code will show you a simple way to control Internet Explorer from Excel. Create a new macro by selecting “Marco” from the developer tab. Name it something like “Basic_Navigation”. When you click “create” the editor window will pop open. Copy the following code into the editor, then close the Visual Basic window.
Sub Basic_Navigation() Set IE = CreateObject("InternetExplorer.Application") IE.Navigate "http://tipsformarketers.com" MsgBox ("Check Internet Explorer, click ok when done.") IE.Quit Set IE = Nothing ' Clean up Application.StatusBar = "" End Sub
To run this script, click the macro button in the developer tab, select the macro you just created, and click “Run”. Excel will now open up Internet Explorer and navigate to TipsForMarketers.com. When you go back into Excel, there will be a message box which, upon confirmation, will close down Internet Explorer and return you to your spreadsheet.
While this simple script by itself isn’t that that amazing, you now have the basics of how to create a simple marco that controls Excel. Stay tuned for more in-depth walk-throughs that should help you out as a marketer!