Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Excel Web Query Macro

0

I have been working with this marco for about 3 months trying to make it work for me to download some specific Vanguard Group quotations.  My issue is getting only the information I need from this comprehensive generalized code.  I have searched endlessly and unsuccessfully for some decent field definitions for the QueryTables keyword.  I only need to pull down the Symbol name definition,  last close price value and previous close price value and date.  I could probably us some of the other .Name stuff too if I know what it was, i.e., .WebTables = "1,2" definition(s).  And I would like to pull about 8 or 10 symbols down to a single/multiple sheet(s) in a workbook.  I am better than the average bear with macros but like most its the syntax that kills me.  Not looking for copy/cut and paste code, just good keyword definitions and direction.

*********************************************************************

Sub Basic_Web_Query()

With ActiveSheet.QueryTables.Add(Connection:= _
 "URL;http://finance.yahoo.com/q?s=VBIAX", Destination:=Range("$A$1"))
 .Name = "q?s=VBIAX"
 .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 = "1,2"
 .WebPreFormattedTextToColumns = True
 .WebConsecutiveDelimitersAsOne = True
 .WebSingleBlockTextImport = False
 .WebDisableDateRecognition = False
 .WebDisableRedirections = False
 .Refresh BackgroundQuery:=False
 End With

excel
MS Office Version: 2013
posted by: demckaig on Tue Dec 19, 2017 at 3:22 pm
Answer
Discuss

Answers

0

Please try the code below.

Sub Web_Query()
    With ActiveSheet.QueryTables.Add( _
                     Connection:="URL;http://finance.yahoo.com/q?s=VBIAX", _
                     Destination:=Range("$A$1") _
                     )
        .BackgroundQuery = True
        .Name = "VBIAX"
        .Refresh
     End With
 End Sub

This code will create a 'QueryTable' object and display that object in a worksheet table rooted in cell A1 of the 'ActiveSheet'. This process will fail if the URL is wrong.

The QueryTable object has many properties. I picked only two. The 'BackgroundQuery' property determines how the QueryTable is created, the 'Name' property gives a name to the table displayed in your worksheet. Please observe the underlying thoughts here.

  1. Distinguish clearly between the creation of the object and its display in table form.
  2. Properties are set for a purpose. You should know the purpose before you set a property.
  3. Since the above code already downloads the data you want any improvement you might seek relates either to the way the object is created or to the way it is displayed. If you don't have any complaint, don't set any more properties. In fact, you might well remove the two there are.

Here is a complete list of all available properties and methods. The list applies to Excel 365. You haven't disclosed your Excel version in your profile. I found that the 'Range' property isn't available in 2010. Anyway, I suggest that you allow yourself to be driven by your wishes for improvement, not the list of available tools.

posted by: Variatus (rep: 4889) on Tue Dec 19, 2017 at 9:27 pm
Discuss

Discussion

MS Office Version: 2013 has been saved to my profile.  Have not found the complete list of all available properties and methods yet that apply to Excel 2013.  I do see, however, that 2013 is on the cusp of being unsupported.  And, the names associated with the properties and methods are not really intuitive to the datas they return.  For example, with no properties specified, the query  returns "Prior Close".  I assume this value is prior day closing price.  So what property will return "Closing Price".  
demckaig Dec 20, '17 at 4:51 pm
I haven't been able to access the table you are interested in because of a weak Internet connection from where I am. I don't know if there is a field for "Last Close" but "Prior Close" may have different meanings at different times of the day.
However, the code returns a QueryTable which should contain many cells. It isn't possible limit the table's download to certain cells only. The idea is that you should download the entire table to a dedicated sheet and then pick the data you want from that sheet to paste into another one which puts them into the context you require.
Incidentally, the 'Destination' which you specify as Range("$A$1") - I copied that into my answer above - is very dangerous to use. I strongly urge you to specify a workbook and worksheet, like, ThisWorkbook.Sheet1.Range("$A$1"). As it is, the QueryTable is created in the ActiveSheet which could be any of the sheets in any of the workbooks you may have open at the time. You could lose a lot of valuable data.
Variatus (rep: 4889) Dec 20, '17 at 11:02 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login