When the button is clicked, the macro builds a connect string by concatenating the first part of the URL with the first stock symbol from cell A2:

ConnectString = "URL;" & _ WSD.Cells(i, 1).Value

As the program loops through additional stock symbols, it takes the existing connect string and adds a comma and a plus sign, and then the next stock symbol:

ConnectString = ConnectString & ",+" & WSD.Cells(i, 1).Value

After the connect string has been built, then the macro uses a worksheet called "Workspace" to build the Web query. This hides the unformatted Web query results from view. Because it is possible that the list of stock symbols has changed since the last Web query, the program deletes any old query and then builds the new query.

It is important to set the query up with BackgroundRefresh set to False. This ensures that the macro pauses to give the query time to refresh before continuing.

After the results have been received, the program assigns a range name to the results and uses VLOOKUP formulas to retrieve the desired columns from the Web query:

Sub CreateNewQuery()

Dim WSD As Worksheet

Dim WSW As Worksheet

Dim QT As QueryTable

Dim FinalRow As Long

Dim i As Integer

Dim ConnectString As String

Dim FinalResultRow As Long

Dim RowCount As Long

Set WSD = Worksheets("Portfolio") Set WSW = Worksheets("Workspace")

' Read column A of Portfolio to find all stock symbols FinalRow = WSD.Cells(65536, 1).End(xlUp).Row For i = 2 To FinalRow Select Case i Case 2

ConnectString = "URL;" & WSD.Cells(i, 1).Value Case Else

ConnectString = ConnectString & ",+" & WSD.Cells(i, 1).Value End Select Next i

' On the Workspace worksheet, clear all existing query tables For Each QT In WSW.QueryTables

QT.Delete Next QT

' Define a new Web Query

Set QT = WSW.QueryTables.Add(Connection:=ConnectString, _

Destination:=WSW.Range("A1")) With QT

.Name = "portfolio" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = False .RefreshStyle = xllnsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0

.WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingNone .WebTables = "20"

.WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextlmport = False .WebDisableDateRecognition = False .WebDisableRedirections = False End With

' Refresh the query

QT.Refresh BackgroundQuery:=False

1 Define a named range for the results FinalResultRow = WSW.Cells(65536, 1).End(xlUp).Row WSW.Cells(1, 1).Resize(FinalResultRow, 7).Name = "Weblnfo"

' Build a VLOOKUP to get quotes from WSW to WSD RowCount = FinalRow - 1

WSD.Cells(2, 2).Resize(RowCount, 1).FormulaR1C1 = _

"=VLOOKUP(RC1,WebInfo,3,False)" WSD.Cells(2, 3).Resize(RowCount, 1).FormulaR1C1 = _

"=VLOOKUP(RC1,WebInfo,4,False)" WSD.Cells(2, 4).Resize(RowCount, 1).FormulaR1C1 = _

"=VLOOKUP(RC1,WebInfo,5,False)" WSD.Cells(2, 5).Resize(RowCount, 1).FormulaR1C1 = _

"=VLOOKUP(RC1,WebInfo,6,False)" WSD.Cells(2, 6).Resize(RowCount, 1).FormulaR1C1 = _ "=VLOOKUP(RC1,WebInfo,2,False)"

MsgBox "Data Updated" End Sub

When the program runs, you will get nicely formatted results, as shown in Figure 14.5.

Figure 14.5

After running the macro, only the relevant columns are shown on the report.The back worksheet contains the unformatted Web query from the Web site.

0 0

Post a comment