Web query with dynamic parameter picked up from cell values

  • Hi friends,


    I am trying to pull data from the following base url:


    http://www.powerexindia.com/PX…/pages/MCPReport_New.aspx


    However, the underlying url in the script is like this:


    http://www.powerexindia.com/PX…553204060865be107f2705193
    &ControlID=e2859e2be9fd&Culture=1033&UICulture=1033&ReportStack=1&OpType=ReportArea&Controller=ClientControllerctl00_ContentPlaceHolder4_ReportViewerData&PageNumber=1&ZoomMode=Percent&ZoomPct=100&ReloadDocMap=true&EnableFindNext=False&LinkTarget=_top


    Here, the variable parameters are ReportID and ControlID


    What I want is that I should be able to enter these two parameters in two different cells and then they should be automatically pulled in the above url as I change them and the query should pull the data into my worksheet.


    How can this be made possible pls guide me I am new to VBA and macros with only basic understanding.

  • Re: Web query with dynamic parameter picked up from cell values


    Hi rohit.razdan,
    Nice to meet you.
    First turn on macro recoder followed by web query with underlying url.
    Then adjust macro like below.

    Code
    ....&ControlID=" [B]& range("controlid").value &[/B] "e2859e2be9fd&Culture=1033&UICulture=1033&ReportStack=1&OpType=ReportArea&Controller=" [B]& range("ClientControllerctl00_ContentPlaceHolder4_ReportViewerData").value &[/B] "&PageNumber=1&ZoomMode=Percent&ZoomPct=100&ReloadDocMap=true&EnableFindNext=False&LinkTarget=_top"
    '......
    .RefreshStyle = [B]xlOverwriteCells
    [/B]


    Regards, junho

  • Re: Web query with dynamic parameter picked up from cell values


    Thanks Junho for you help. However, I am not able to understand what you meant by "first turn on the recoder followed by the web query"


    I mean in which cell do I enter the value for controlid? And what about the ReportId parameter.


    Please guide me more elaborately, I am total dodo at coding and VBA stuff. Would be highly grateful to you, if you could tell me how to achieve in a step-wise manner.


    Thanks. God bless you.


    Quote from junho lee;541611

    Hi rohit.razdan,
    Nice to meet you.
    First turn on macro recoder followed by web query with underlying url.
    Then adjust macro like below.

    Code
    ....&ControlID=" [B]& range("controlid").value &[/B] "e2859e2be9fd&Culture=1033&UICulture=1033&ReportStack=1&OpType=ReportArea&Controller=" [B]& range("ClientControllerctl00_ContentPlaceHolder4_ReportViewerData").value &[/B] "&PageNumber=1&ZoomMode=Percent&ZoomPct=100&ReloadDocMap=true&EnableFindNext=False&LinkTarget=_top"
    '......
    .RefreshStyle = [B]xlOverwriteCells
    [/B]


    Regards, junho

  • Re: Web query with dynamic parameter picked up from cell values


    Actually, this is the complete URL that excel uses to query the webpage:


    Code
    http://www.powerexindia.com/PXILReport/Reserved.ReportViewerWebControl.axd?Mode=true&ReportID=38d6d7139d834a7fab2f8a58c7478ffe&ControlID=02386901-6e32-4886-8146-e2859e2be9fd&Culture=1033&UICulture=1033&ReportStack=1&OpType=ReportArea&Controller=ClientControllerctl00_ContentPlaceHolder4_ReportViewerData&PageNumber=1&ZoomMode=Percent&ZoomPct=100&ReloadDocMap=true&EnableFindNext=False&LinkTarget=_top


    Here, ReportID and ControlID are parameters that change with time. While I am able to grab these parameters from the script with the help of a code developed by my friend, I am not able to feed them directly into the web query.

  • Re: Web query with dynamic parameter picked up from cell values


    My friend,


    I couldn't find any xls file using this link in google. The only results I get are the forums where I have discussed this problem.


    Please help.

  • Re: Web query with dynamic parameter picked up from cell values


    Hi Junho,


    But this file has been submitted in response to my query only. It is for Indian energy exchange (IEX). While I have been able to pull data for IEX using Randy Harmelink's SMF add-in, the power exchange's website has a little complex coding, so I was not able to apply the coding there.


    So I thought of going the other around, with certain manipulations to web query.


    Please help me develop the code. I would request you to please test the code once, so that when I make a mistake you are sure of what is going wrong with me.


    Thanks for all your help buddy.

  • Re: Web query with dynamic parameter picked up from cell values


    Hi rohit.razdan,
    For me this works.


    Regards, junho

  • Re: Web query with dynamic parameter picked up from cell values


    Hi Junho,


    Could you please post the file, I just don't seem to get it.


    Moreover, I also thought of feeding the entire URL to the query from a cell. So someone helped me develop this code:


    Code
    Sub ChangeQueryURL() 
        With Activesheet.Querytables(1) 
            .Connection="URL;" & Range("A1").Value 
            .Refresh False 
        End With 
    End Sub


    The steps I follow are:


    1. Set up the web query manually the first time only.
    2. Designate a cell to contain the full URL (say cell A1)
    3. Put a forms button on the sheet and assign the above macro to it.


    But this does not work either. The code is executed and a msg box appears with 400 written in it.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!