none
Pass String into Web.Contents RRS feed

  • Question

  • I'm setting up some web queries that will be automated over a list for similar URLs, i.e. http://www.webpage.com/ch=1/pg=1 I currently have

        For i = 1 To 17
            For Each j In MyArray
                QueryString = "http://www.webpage.com/ch=" & Str(i) & "/pg=" & Str(j)
                ActiveWorkbook.Queries.Add Name:=QueryName, Formula:= _
                    "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(QueryString)),...                

    This ultimately creates a bunch of empty queries because it tries to query the webpage QueryName rather the URL contained in that variable. How do you pass a string variable into this function?

    Monday, December 12, 2016 4:08 AM

Answers

  • Hi,

    You need to make changes in Source:

    ActiveWorkbook.Queries.Add Name:=QueryName, Formula:= _
                    "let" & Chr(13) & "" & Chr(10) & " Source = Web.Page(Web.Contents("& QueryString & ")),...  


    Vish Mishra

    Monday, December 12, 2016 6:00 AM

All replies

  • Hi,

    You need to make changes in Source:

    ActiveWorkbook.Queries.Add Name:=QueryName, Formula:= _
                    "let" & Chr(13) & "" & Chr(10) & " Source = Web.Page(Web.Contents("& QueryString & ")),...  


    Vish Mishra

    Monday, December 12, 2016 6:00 AM
  • Thank you. That brings the string in. Now I am getting an expected comma error. My other error was not including Chr(34) in my string so it was getting Web.Page(Web.Contents(www.webpage.com)) rather than Web.Page(Web.Contents("www.webpage.com")). Appreciate the prompt reply.
    Tuesday, December 13, 2016 2:45 AM
  • Hi,

    Looking at your partial code available, I tested the below code and it works without any error.

    If you still get an error related to the query, then please check

    1. the data which you are getting in Str(i) and  Str(j) to form the Source URL.

    2. I see that you are using QueryName as a variable but I do not see what value you are assigned to it. Please make sure that for every query, it has a unique name otherwise it will give you an error stating that Query name already exists.

        quryString = "http://www.webpage.com/ch=1/pg=1"
        ActiveWorkbook.Queries.Add Name:="QueryName", Formula:= _
    "let" & Chr(13) & "" & Chr(10) & " Source = Web.Page(Web.Contents(" & """ & quryString &""" & "))"
    Let me know if this code and info helps you. If not.. please provide some more code and data. Then i will be able to help you further.


    Vish Mishra

    Tuesday, December 13, 2016 11:30 AM
  • I'm working now. Thanks. QueryName was also generated with i and j.
    Wednesday, December 14, 2016 3:12 AM