Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: again web query

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Philippines
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am using Excel 2000.
    I try to extract data from an external database which stores the data on several thousand pages with different URL addresses. The addresses only differ by one word, i.e. the username :"Klaus" in the following example.
    "URL;http://1on1.xxxxxxxx.com/admin/viewaccount.cgi?username=klaus",
    How can I put a placeholder in the script instead of "Klaus", like a cell on the same worksheet, so everytime I run the query (by using a macro) it would replace the name "Klaus" with the name which is then in that particular cell?
    Please somebody help, I am working on this already for 3 days without sleep!!!

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    I guess the reply I sent you from a different post did not help?
    "URL;http://1on1.xxxxxxxx.com/admin/viewaccount.cgi?username=" & "Sheet1.range("A1")&"""",
    I'll see if I can make it work
    Tom

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Bukol

    I did a simple web query with the following URL.

    http://www.time.gov/timezone.cgi?Eastern/d/-5

    In the code for the query itself, the URL looks like this:

    "URL;http://www.time.gov/timezone.cgi?Eastern/d/-5",

    I removed Eastern/d/-5 from the end and replaced it with a cell reference:
    Namely sheet2.range("A1")

    Sheet2.range("A1") = "Eastern/d/-5"

    The new code, which did work, looks like this:

    "URL;http://www.time.gov/timezone.cgi?" & Sheet2.Range("A1"),


    In your example:
    "URL;http://1on1.xxxxxxxx.com/admin/viewaccount.cgi?username=klaus",

    If we were to store the string "klaus" in sheet2.range("A1")
    Your URL call would appear as such:

    "URL;http://1on1.xxxxxxxx.com/admin/viewaccount.cgi?username=" & sheet2.range("A1"),

    I hope this helps you.
    If you need any help with the script, post your code and where the values will be stored, and I'm sure we can help you out further. I know what it is like to be stuck.

    Tom


    [ This Message was edited by: TsTom on 2002-04-02 02:32 ]

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Philippines
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Tom.

    Tried the new address, which differs a lttle from the last one you suggested.
    Now I got problems in line 3 (.Name = "viewaccount.cgi?username= & sheet2.range("A1")
    It say's "expected end of statement"
    What character is missing there?
    Here is the whole code:

    Sub dieses()
    '
    ' dieses Macro
    ' Macro recorded 4/2/2002 by Hans
    '
    ' Keyboard Shortcut: Ctrl+p
    '
    With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;http://1on1.adultclub.com/admin/viewaccount.cgi?username=" & Sheet2.Range("A1"), _
    Destination:=Range("A1"))
    .Name = "viewaccount.cgi?username= & sheet2.range("A1")
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlAllTables
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .Refresh BackgroundQuery:=False
    End With
    End Sub
    Hans

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    Looks like you are missing the quotes after username=

    Yours:
    Name = "viewaccount.cgi?username= & sheet2.range("A1")

    Change to:
    Name = "viewaccount.cgi?username=" & sheet2.range("A1")

    Remember this as a rule.
    Whenever you are combining string with variable:

    The string is always enclosed in quotes
    The variable is combined with an ampersand
    &

    "String1" & Variable1 & "String2" & Variable2

    Hope this helps you!
    Tom

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Philippines
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    TOM
    IT WORKS!
    I LOVE YOU (don't tell my wife I said that)
    Seriously, thanks an awful lot. Let's keep in touch anyway. My e-mail: hansbraun@digitelone.com
    Maybe I can help you out sometimes. I am here in the Philippines in the rental business (girls)
    Hans

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •