Issues with example in VBA Macros Book

[ctan]

Hi,

I just purchased the book and was testing out the code in Chapter 14 on web queries. Specifically, I was testing the code in pg 341.

It doesn't seem to work at all. None of the queries on the stock prices worked instead returning a N/A entry

I also downloaded the code associated with the book.
Found issues with missing variable declarations in the code that related to the example in the book.

Any assistance would be greatly appreciated.

My goal is to build spreadsheets that would ultimately do valuation of companies but this requires the importation of financial statements from a variety of web sites.

Thanks,

 

[Tom Urtis]

That exercise deals with a running record of the national debt. When I tested this the first time a few years ago everything worked fine.

Just now I tested everything again and it worked great again, no problem.

There could be a few reasons why this is not working for you. Maybe (just thinking out loud) your internet connections is not established or you did not establish the web query in cell A2 of a sheet named MyQuery, or any other tasks that are required in advance of running the code in that book.

At any rate, if you want, PM me with your email address and I can send you a copy of the workbook I just created that does indeed work based on that example in the book.

 

[ctan]

No. Its not that one. Its the chapter on reading from the web and the excercise dealing with a flexible query to pull stock quotes.

The code in question is:

Sub CreateNewQuery()
    ' Page 335
    Dim WSD As Worksheet
    Dim WSW As Worksheet
    Dim QT As QueryTable
    
    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;http://finance.Yahoo.com/q/cq?d=v1&s=" & 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
    
    WSW.Select
    WSW.Cells.Clear
    
    ' 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 = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "20"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
    End With
    
    ' Refresh the Query
    QT.Refresh BackgroundQuery:=True
    
   ' Application.StatusBar = "Waiting"
   ' Application.Wait Now + TimeSerial(0, 0, 10)
   ' Application.StatusBar = False
    
    ' Define a Named Range for the Results
    WSW.Cells(1, 1).Resize(FinalRow, 6).Name = "WebInfo"
    
    ' 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).Value = Time
    WSD.Cells(2, 6).Resize(RowCount, 1).NumberFormat = "h:m:s"
        
    WSD.Select
    MsgBox "Data Will be Updated Momentarily"
    
End Sub

EDIT: Added Code tags - Moderator

 

[Tom Urtis]

I based my first post on this you wrote:

Specifically, I was testing the code in pg 341.

...which led me to think you were referring to the national debt code on page 341.



Since it appears you are instead asking about page 335 because you later wrote this...

The code in question is:

Sub CreateNewQuery()
' Page 335

..then I modified the macro by reducing the WebTables property from 20 to 10 and the following code worked fine for me, provided that the stock symbols are placed per the exercise into range A2:Ax of the Portfolio worksheet. This creates a rolling set of updated tables for those symbols on the Workspace sheet after each macro run.






Sub CreateNewQuery()
' Page 335
Dim WSD As Worksheet
Dim WSW As Worksheet
Dim QT As QueryTable
Dim FinalRow As Long
Dim i%
Dim ConnectString$
Dim RowCount$
Dim FinalResultsRow 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;http://finance.Yahoo.com/q/cq?d=v1&s=" & 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

''''WSW.Select
''''WSW.Cells.Clear

' 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 = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "10"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
End With

' Refresh the Query
QT.Refresh BackgroundQuery:=False


' Define a Named Range for the Results
FinalResultsRow = WSW.Cells(65536, 1).End(xlUp).Row
WSW.Cells(1, 1).Resize(FinalRow, 7).Name = "WebInfo"

' 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).Value = Time

'WSD.Select
MsgBox "Data Updated"

End Sub

 

[ctan]

Tom,

Sorry for the confusion. In my book, pg 341 is the stocks query topic. In the code, it lists it as 335. I just got the book (additional pages since it may be a revision?), so this may explain the difference in the paging.

I'll check it out and PM you if I run into any issues.

Colin