none
VBA to access website information RRS feed

  • Question

  • Hi all,

    I hope you can help me. I have a spreadsheet that runs a macro on opening to retrieve share prices from the ASX website. This file and macro work perfectly on one computer (my work computer at work) but when run on my home computer does not. Until recently it did work on the home computer.

    The problem appears to be at the point in the code where it gets to and is due to a Java Script running on the target website:

         
       With ActiveSheet.QueryTables.Add(Connection:= _
          "URL;http://www.asx.com.au/asx/markets/priceLookup.do?by=asxCodes&asxCodes=" & CompanyCode, _
             Destination:=Range("a1"))
       
          .BackgroundQuery = True
          .TablesOnlyFromHTML = True
          .Refresh BackgroundQuery:=False
          .SaveData = True
       End With

    At work it pastes the appropriate information into the excel file worksheet. At home, that worksheet remains blank.

    I am running Microsoft Excel Professional at work and Excel Home and Student 2013 at home.

    Any thoughts?

    Wednesday, May 3, 2017 11:59 PM

All replies

  • Hi Godders01,

    I can see that you had posted the few lines of your code with that I am not able to reproduce the issue and also not able to find any issue with that lines.

    you had mentioned that ,"The problem appears to be at the point in the code where it gets to and is due to a Java Script running on the target website:"

    how you know that the issue is related with javascript running on that website.

    if possible then show the error message that you got.

    if the issue is related with the website running on that site then we can't solve it.

    we don't have any control on that website.

    if issue is related with your code then we can try to look in to that and try to test it.

    here below is one example to fetch the data from website in to Excel.

    Sub Macro2()
    
        With ActiveSheet.QueryTables.Add(Connection:= _
            "URL;http://www.bundesbank.de/Navigation/EN/Statistics/Time_series_databases/Macro_economic_time_series/its_details_value_node.html?nsc=true&listId=www_s201_b9233&tsId=BBK01.ED0439" _
            , Destination:=Range("$A$1"))
            .Name = _
            "its_details_value_node.html?nsc=true&listId=www_s201_b9233&tsId=BBK01.ED0439"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlEntirePage
            .WebFormatting = xlWebFormattingNone
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
            .TablesOnlyFromHTML = True
            .SaveData = True
        End With
        
    End Sub

    try to refer the code and if you find that issue is with code then try to correct it.

    also let us know about that so that we can try to test it on our side.

    try to provide further details regarding your issue to get further suggestions.

    Regards

    Deepak 


    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.

    Thursday, May 4, 2017 2:10 AM
    Moderator
  • Hi Godders01,

    I try to test your code on my side.

    I find that I don't have the data like yours and lots of variables , objects, are not available which you are referring in code above.

    so I can't run the code.

    can you show me the screen shot of the error. it will help us to understand the issue better.

    as I informed you before if it is the script that running on that website then we can't do anything.

    as per the above statement it looks like it is preventing to fetch the data.

    is it need to require any login or authentication or permission to get data from that website? that you don't have.

    what about the code I suggested you last time, is it generating the same issue?

    Regards

    Deepak


    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.

    Friday, May 5, 2017 7:52 AM
    Moderator