none
QueryTables Works on one computer but not another 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:

    On Error GoTo Done
          
       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.

    Any thoughts?

    Wednesday, May 3, 2017 1:05 AM

All replies

  • Hi,

    I notice the issue is related to the Macro code. To get more suggestion, I would move the thread to Excel for Developers forum for more help.

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding :)


    Regards,

    Winnie Liang


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Wednesday, May 3, 2017 6:36 AM
  • Hello,

    I suggest query the website manually in your home computer to check if you could retrieve any data.

    To debug your code, I suggest you comment out line On Error GoTo Done to see if there is any error.

    Do you check the difference between the two computers? For example, Office version, Office platform and Office settings.

    Besides, you could share a CompanyCode or share the file here, we could test if the code above could work for us.


    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, May 3, 2017 7:36 AM
    Moderator
  • Hi,

    Thank you or your response.

    I have done all that you suggest. What I have been able to work out is that the issue is caused by the Java Script running on the website. But I don't know how to fix the code or settings on my computer to fix this. I have checked all the computer and excel settings etc and think they are ok.

    The work excel is from Microsoft Office Professional whereas at home (where it doesn't work) excel is the Microsoft Home and student version for 2013

    • Marked as answer by Godders01 Thursday, May 4, 2017 12:00 AM
    • Unmarked as answer by Godders01 Thursday, May 4, 2017 12:00 AM
    Wednesday, May 3, 2017 11:53 PM
  • Hello,

    If you query manually, what error would you get?

    I think the issue causes from your IE settings. Please enable JavaScript in your IE. Please visit How to enable JavaScript in Windows

    Regards,

    Celeste


    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:26 AM
    Moderator
  • Thanks Celeste. I think all the JavaScript settings are correct
    Thursday, May 4, 2017 3:19 AM

  • In your code, would you get any error? What error would you get if you do the web query from UI?

    If you could not retrieve any data when querying the website manually, of cause we could not do it via macro.

    If the issue does not cause from code, please visit the similar thread:

    https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_winother/microsoft-excel-web-query-issue-reading-script/83357777-7604-47b4-afca-1a422fe6fac0


    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 7:54 AM
    Moderator