Daily Importing of External Data using Web Query

[Treefeller]

Every month I create a spreadsheet from a template by creating one ta
for each day of the month named after day of the month/year (010104).
then maually enter a Web Query on each tab which downloads dail
weather information for an insect life cycle model.

This is a very time consuming and tedious task because the onl
difference in the links is the expression "20040101" (for January 01
2004) to "20040102" (for January 02,2004) INCLUDED IN THE LINK.

Is it possible to use a macro (VBA code) to enter the Web Query addres
in cell A1 on each tab when the workbook is created and jus
update/refresh the link when the date has passed and the new data se
is available for download? Previous data sets remain available at th
site so multiple downloads can be accomplished at any time.

My attempts to copy information into the Web Query using a mcro hav
failed

 

[jeff]

Hi, Treefeller.

Although you don't say exactly what's wrong with your
macro, I assume you've recorded your steps into a
macro and taken a look at it to see where a variable
which picks up your date from A1 could go? I think
your wishes should be do-able. Perhaps your date needs
to go into part of the
"With ActiveSheet.QueryTables.Add" string?

A quick query to a local link to load employees gave me
this code:

Sub Macro1()
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://myplace.com/people/people.asp",
Destination:=Range("A2"))
.Name = "people"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "6"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

 

[alan]

Here's a formula -- implementated as a function, but you could make it
a macro -- that takes two arguments: parameters to tack on the end of
the base URL, and the destination.

Right now, it's designed to take ticker symbols as input.
ManageQuery(A1,A5) will take the tickers in A1 ("aicx+stel+msft+sdix",
for example) and output the yahoo finace quote table starting in cell
A5.

To make this work for you, you'll need to replace the rootURL and
tableNumber with your values. Then put your date in cell A1, call the
function with A1 as input and output as wherever you want to put the
output.

Enhancements: I wanted to make this do refreshing, but right now it
doesn't. (If you delete the output, it will refresh.) Also, there
should be logic and prompts for handling overwriting. Right now, it
fails if anything is in the output range.

***
Function ManageQuery(Params As String, Plop As Range)
Dim rootURL As String
Dim tableNumber As Integer

rootURL = "http://finance.yahoo.com/q/cq?d=v1&s="
tableNumber = 22

URL = rootURL & Params

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & URL, Destination:=Plop)
.Name = "?d=v1&s=aicx+msft"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = tableNumber
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
ManageQuery = "done"
End Function
***