Microsoft Office Tutorials and References
In Depth Information
Lesson 23: Managing External Data
Does your Web query take too long to refresh? You can cancel the Refresh
method if it’s running longer than you want to wait with this block of code:
If Application.Wait(Now + TimeValue(“0:00:10”)) Then
If .Refreshing Then .CancelRefresh
While on the subject of corporate performance, the following macro opens a .csv file for you,
depending on which stock symbol you are searching for, and copies several years of historical stock
price activity to Sheet3 of your workbook:
Dim strStockSymbol As String
Dim strURL1 As String, strURL2 As String
‘Download the past years’ stock price activity.
strURL1 = “http://ichart.finance.yahoo.com/table.csv?s=”
strURL2 = “&d=2&e=18&f=2010&g=d&a=2&b=13&c=1986&ignore=.csv”
strStockSymbol = “EBAY”
Workbooks.Open Filename:=strURL1 & strStockSymbol & strURL2
‘Copy data from the csv file to your worksheet.
‘Close the csv file without saving it.
‘Autofit the columns.
There may be times when you are composing Web queries for other people to use. Before they try
running your macros, seeing as an Internet connection is required for the code to perform, it might
be a good idea to programmatically verify that the user has an Internet connection. You can place
the following procedures into a new standard module, and if no Internet connection exists, it would
be wise to use this code to halt any web-related activity your project may involve.
The strange-looking Public Declaration Function is not VBA code. It is
Applications Programming Interface, or API, which is the programming language
of the Microsoft Windows operating system. If you are wondering why API is being
used in this example, the reason is that the task at hand is a query about Internet
connectivity, which is a computer-wide issue that is not related specifically to any
one application, including Excel. In your programming travels, you will see API
being used with VBA for controlling objects that are related to Windows, such as
manipulating the task bar or returning the name of the computer.