again web query

[Bukol]

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!!!

 

[Legacy 98055]

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

 

[Legacy 98055]

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

 

[Bukol]

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

 

[Legacy 98055]

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

 

[Bukol]

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