none
Excel 2010 - QueryTable code not able open to URL - getting error "Unable to open URL. Cannot download the information you requested" RRS feed

  • Question

  • Hello,

    I have inherited a VBA excel program.  This code is pre excel 2010.   I am getting the err code: "Unable to open URL. Cannot download the information you requested"

    I am not sure why this is occuring because I can open the url when accessing it online.  The URL is correct.

    I woud appreciate help!  I have found some info on it, one suggestion was to go to

    TRUST CENTER ----> EXTERNAL CONTENT ----> uncheck "Enable automatic update for all Workbook Links (not recommended)"

    That did not work either.  Dont know where to go from here.  All assistence and direction is truly is appreciated. 

    My code is below:

    'loop through each cell in the column
        For r = begseq To endseq
           
                   
            Worksheets("QryHold").Activate
            '
            Worksheets("QryHold").UsedRange.ClearContents
            '
            Worksheets("QryHold").Range("A1").Select
            
            On Error Resume Next
            With Selection.QueryTable
                    .Connection = _
                    "URL;https://pts-2.xxxexample.gov/pts2-web/tcIntranetTrackingNumResponse?label=" & label & "&status=Live&mailingDate=&userSearchTypeChoice=Extensive"
                    .Name = _
                    "tcIntranetTrackingNumResponse?label=" & label & "&status=Live&mailingDate=&userSearchTypeChoice=Extensive"
                    .WebSelectionType = xlSpecifiedTables
                    .WebFormatting = xlWebFormattingNone
                    .WebTables = "4,5,6,7,8,9,""extraservicedetails"",12,""events"""
                    '
                    '.Refresh
                    '
                    .WebPreFormattedTextToColumns = False
                    .WebConsecutiveDelimitersAsOne = True
                    .WebSingleBlockTextImport = False
                    .WebDisableDateRecognition = False
                    .WebDisableRedirections = False
                    .Refresh BackgroundQuery:=False
                    
            End With
            
        
            If Application.WorksheetFunction.CountA(Worksheets("QryHold").Range("A:A")) < 15 Then
                 With Selection.QueryTable
                    .Connection = _
                    "URL;https://pts-2.xxxexample.gov/pts2-web/tcIntranetTrackingNumResponse?label=" & label & "&status=Archived&mailingDate=&userSearchTypeChoice=Extensive"
                    .Name = _
                    "tcIntranetTrackingNumResponse?label=" & label & "&status=Archived&mailingDate=&userSearchTypeChoice=Extensive"
                    .WebSelectionType = xlSpecifiedTables
                    .WebFormatting = xlWebFormattingNone
                    .WebTables = "4,5,6,7,8,9,""extraservicedetails"",12,""events"""
                    '
                    .Refresh
                    '
                    .WebPreFormattedTextToColumns = False
                    .WebConsecutiveDelimitersAsOne = True
                    .WebSingleBlockTextImport = False
                    .WebDisableDateRecognition = False
                    .WebDisableRedirections = False
                    .Refresh BackgroundQuery:=False
                 End With
                 
                 Worksheets("Barcodes").Hyperlinks.Add Anchor:=Worksheets("Barcodes").Cells(r, c), Address:= _
                 "https://pts-2.usps.gov/pts2-web/tcIntranetTrackingNumResponse?label=" & label & "&status=archived&mailingDate=&userSearchTypeChoice=Extensive" _
                    , TextToDisplay:="'" & label
            End If
                           
            ''' DATA EXTRACTED FROM THE URL REFRESHES THE WORKSHEET "QryHold"
        Next r

    smsemail

    Monday, October 20, 2014 1:39 PM

Answers

All replies

  • Hi,

    >>I am getting the err code: "Unable to open URL. Cannot download the information you requested"<<

    When do you get the error message above? Running the code or click the hyperlink in the worksheet?

    If the exception happens when running the code, have you debug in the code to find which lines cause the error?

    Because we are not sure about the parameters of your web site, it's hard for us to reproduce your issue with the code above. Since you said you can open the URL when accessing it online, an easier way to troubleshoot is recording a macro about the process of creating query table from your web site manually and comparing the macro with your VBA code.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, October 21, 2014 9:01 AM
    Moderator
  • Thank you Luna for your help!

    I figured out why the URL is not opening up.  It is not opening up because  Excel cannot initiate a connection because of the settings on the secure Web server.  So to test my code, I was able to temporarily workaround the problem by modifying the registry using the information I found on the attached link;

    http://blogs.technet.com/b/the_microsoft_excel_support_team_blog/archive/2011/11/15/quot-cannot-download-the-information-you-requested-quot-executing-web-query-from-excel.aspx

    I need a permanent solution, because the application will be used b other users.  Have you any suggestions?

    smsemail

     

    Tuesday, October 21, 2014 4:24 PM
  • Thank you Luna for your help!

    I figured out why the URL is not opening up.  It is not opening up because  Excel cannot initiate a connection because of the settings on the secure Web server.  So to test my code, I was able to temporarily workaround the problem by modifying the registry using the information I found on the attached link;

    http://blogs.technet.com/b/the_microsoft_excel_support_team_blog/archive/2011/11/15/quot-cannot-download-the-information-you-requested-quot-executing-web-query-from-excel.aspx

    I need a permanent solution, because the application will be used b other users.  Have you any suggestions?

    smsemail

     


    Hi smsemail,

    For this new question, please feel free to open a new thread for better communication.

    Thank you for your understanding.

    Best Regards,
    George


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Sunday, November 9, 2014 7:22 AM
    Moderator