In Part I of
this article, we discussed how to use VB and Excel to pull data in from
a remote website and have the data automatically parsed for us by Excel. This
portion of the article will focus on getting the data from Excel and into an XML
file that can used for display on a web page. If you didn't get a chance
to read Part I don't worry. We'll give you a very brief review, so as to get you
up to speed. If you've read Part I already, then please feel free to jump to the
Section "Adding to the Application Structure" further below.
The Application Structure Revisited
Before we jump into the specifics, let's go over the basic application
structure. First of all, by using Excel we can avoid the headache of having to
manually parse an HTML page to obtain certain data elements. The model in
Figure 1 shows how the user interface, the class module
(clsExcelObj), and the Excel file work together to accomplish this
task. The combination of these various elements working together allows
information to be pulled in from the web extremely easily.
Figure 1
The user interface's main job is to pass parameters to
clsExcelObj by letting it know when to begin and when to end the
pull cycle. It also controls how often individual data pulls
are performed. As various pulls are attempted and eventually completed,
the user interface captures the events and displays them in the status area.
This allows administrators of the application to easily view when the last
pull may have occurred. Figure 2 shows a representation of the
user interface.
Figure 2
All of the information captured in the user interface is sent to the
clsExcelObj class module. This class does all the work of starting
and stopping Excel and contains the timer functionality. As pulls are
performed, the status of these pulls is sent back to the user interface for
display through the functionality provided by the "WithEvents"
keyword in VB.
clsExcelObj manipulates Excel through its automation interface
and through calling macro routines within Excel. So, as a timer event is
triggered within the class, VBA code is called within Excel, which tells it to
retrieve a certain website's data and then place it into an Excel spreadsheet.
The sample code included with this article contains all of the Visual Basic
files, if you want to take a closer look or customize the application for your
own purposes. Visual Basic 6 was used to write and compile the application and
Excel 97 or higher is required for the "Web Query" functionality to work
properly.
Adding To The Application Structure
Figure 3 builds upon the model shown in Figure 1 by showing the
movement of data from the Excel file to an ASP page. An Excel macro routine
calls the ASP page which then updates an XML file located on the web server. The
data within the XML file is then used for display on a web page using an XSL
Style sheet and ASP. If your current server doesn't have XML support you can
easily modify the code to write to a database instead. I chose to use XML in
combination with XSL and ASP, simply because of the speed and because doing it
this way requires absolutely no calls to a database. So, access to the XML file
is always available unless the web server is down. And, if the web server is
down, then there's bigger problems to worry about!
Figure 3
When developing this application, I wanted it to leverage Internet
capabilities so that it could run from any PC that had Excel and an Internet
connection. Otherwise the application would need to run on the web server. Doing
it all on the server would lead to the unnecessary consumption of CPU cycles as
well as memory. To enable it to work on any PC, I decided to use the Internet
Explorer automation object by calling it from within Excel (Dim browser As
InternetExplorer). This object's "navigate" method is used
to call an ASP page (updateXML.asp) located on a remote server with
the appropriate data embedded in the query string. The details of this process
are outlined below.
)
)
)