none
Excel 2016/2007 VBA Web Queries both having problems on this website RRS feed

  • Question

  • Good day all,

    Please take a look at this URL: http://racing.hkjc.com/racing/Info/Meeting/RaceCard/English/Local/20180311/ST/1

    I found out that when running my existing 2007/2003 Excel VBA web query program, the data under the column "Priority" wasn't correct as the "+" signs were always missing.  And I'd tried all the options that I know of. Here is my existing VBA codes:

         

    With ActiveSheet.QueryTables.Add(Connection:= _
     "URL;http://racing.hkjc.com/racing/Info/Meeting/RaceCard/English/Local/20180311/ST/1", Destination:=Range("A1"))
            .Name = M8_getRaceCard_thisNameString
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
    '        .PreserveFormatting = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlEntirePage
            .WebFormatting = xlWebFormattingNone
    '        .WebFormatting = xlWebFormattingAll
    '        .WebPreFormattedTextToColumns = False
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
    '        .WebSingleBlockTextImport = False
            .WebSingleBlockTextImport = True
            .WebDisableDateRecognition = True
    '        .WebDisableRedirections = False
            .WebDisableRedirections = True
            .Refresh BackgroundQuery:=False
            .Delete
        End With

    Then I tried with the Excel 2016 Web Query.  The data under the column "Priority" was correct ! However, this newer version of the Web Query had truncated the data.  This website has an option selecting all or part of the entire data set.  There is a button next to " SETUP MY STARTER LIST - Customise your own starter information " where you can select the data.  In my existing Excel 2007/2003 VBA web query, it will return the whole set of the data.  The following is the Excel 2016 VBA web query code: 

        ActiveWorkbook.Queries.Add Name:="Table 0", Formula:= _

            "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(""http://racing.hkjc.com/racing/Info/Meeting/RaceCard/English/Local/20180311/ST/1""))," & Chr(13) & "" & Chr(10) & "    Data0 = Source{0}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Data0,{{""Horse No."", Int64.Type}, {""Last 6 Runs"", type text}, {""Colour"", type text}, {""Horse"", type text}, {""Wt."", Int64.Type}, {""Jockey"", type tex" & _
            "t}, {""Draw"", Int64.Type}, {""Trainer"", type text}, {""Rtg."", Int64.Type}, {""Rtg.+/-"", Int64.Type}, {""Horse Wt. (Declaration)"", Int64.Type}, {""Priority"", type text}, {""Gear"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
        ActiveWorkbook.Worksheets.Add
        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
            "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 0"";Extended Properties=""""" _
            , Destination:=Range("$A$1")).QueryTable
            .CommandType = xlCmdSql
            .CommandText = Array("SELECT * FROM [Table 0]")
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .ListObject.DisplayName = "Table_0"
            .Refresh BackgroundQuery:=False
        End With
    End Sub

    The above Excel 2016 web query will ONLY return the default data set but not the whole set of data like the old one.  Please help !

    Thanks.

    LHLK

    Friday, March 9, 2018 12:36 PM

All replies

  • Hello LHLK,

    >>I found out that when running my existing 2007/2003 Excel VBA web query program, the data under the column "Priority" wasn't correct as the "+" signs were always missing

    What's result if you add the web query manually? 

    >>The following is the Excel 2016 VBA web query code: 

    Why do you use different code in 2007/2016. If you adjust the code in 2007 to run in 2016 or adjust the code in 2016 to run in 2007, what results will you get?

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, March 12, 2018 2:27 AM
  • Hello Terry,

    Thanks for your reply. To answer your questions (1) Q: What's result if you add the web query manually?  A: The result is the same as I posted.  I'd been running the 2003/2007 to retrieve data and until recently I need to work on the data located on the "Priority" column where I found out the "+" character under that column is always missing when running the either in a standalone (manual) or web query resided in an Excel VBA macro. (2) Q: Why do you use different code in 2007/2016. If you adjust the code in 2007 to run in 2016 or adjust the code in 2016 to run in 2007, what results will you get?  A: I wasn't so sure what do you mean.  I didn't change anything.  What I tried to do is to check whether or not the Excel 2016 web query with the same URL: http://racing.hkjc.com/racing/Info/Meeting/RaceCard/English/Local/20180311/ST/1 will work.  As I stated above, the newer version of the Excel 2016 web query will return the correct data under the column "Priority"; however, the newer version of the Excel 2016 web query did NOT return or retrieve the entire data set as you can test it by simply manually do the web query with the Excel 2016 and you will know what I meant.  The older or 2003/2007 version of the web query will return the entire set of the web data.  And you can copy and paste my code (above) and you will see the different.  Again thanks for your help.

    LHLK

    p.s. FYI I did try to move and run the Excel 2003/2007 web query VBA code using Excel 2016 VBA web query but with error returned stating that all those keywords with prefix ".Web" (worked with Excel 2003/2007 VBA web query) did not work anymore while running inside Excel 2016 VBA web query.  I didn't try to run the Excel 2016 VBA web query code with Excel 2003/2007 as I don't think it will work and I do NOT plan to go backward in terms of using the Excel versions (agree?)

    • Edited by LHLK Tuesday, March 13, 2018 10:22 AM
    Tuesday, March 13, 2018 10:10 AM
  • Hello LHLK,

    >>The result is the same as I posted

    What VBA could do is limited by Excel. If you could not implement this by Excel itself, VBA would also fail to do this.

    It's result should depend on the power query and Excel product itself. I would suggest you post your thread on Power Query forum for help how to adjust the power query to show result you want in Excel.

    >> I didn't change anything. 

    You used different code to add the query so I though that you are using different setting to add the query. I would suggest you keep variables identical while you are comparing with the difference between 2007 and 2016. 

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, March 14, 2018 7:35 AM
  • Hi Terry,

    I will post to suggested Power Query forum.  Just to make it clear of my problem: 

    (1) It's a BUG within the Excel 2003/2007 web query either running it within Excel itself OR inside a VBA macro of Excel 2003/2007 where under the "Priority" column of this URL;http://racing.hkjc.com/racing/Info/Meeting/RaceCard/English/Local/20180311/ST/1"  where it SHOULD return a "STRING" but it's returned a "INTEGER" i.e, the "+" sign was ignored by the web query.

    (2) With 2016 Excel, please note that I didn't add those whatever headers or codes by myself, the code was RECORDED using the macro recorder utility within Excel 2016 itself while accessing the same URL;http://racing.hkjc.com/racing/Info/Meeting/RaceCard/English/Local/20180311/ST/1".  The code was actually generated by Excel recorder by itself (not by me).  So there is a "BUG" also existing on Excel 2016 web query even though under the "Priority" column it's returned a correct data with the "+" sign.  As I mentioned, the result data set wasn't completed or it's being truncated.  In this case, both Excel web queries 2003/2007 and 2016 have errors while returning web data. 

    Regards,

    LHLK




    • Edited by LHLK Wednesday, March 14, 2018 9:34 AM
    Wednesday, March 14, 2018 9:29 AM
  • Hello LHLK,

    If it is limited by Excel to show data as you want, please go to File->Feedback to submit a feedback for this.

    Before that, I would suggest you go to Power Query forum to check if it is related to the query itself or if there is any workaround for it.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, March 19, 2018 6:02 AM