Hi,
I was learning about web queries for a small project. I wanted a value
taken from a web page to be showed in a textbox in a form.

I recorded the steps to create the web query to dump the data in a sheet
and it worked. It's kind of a table, but I need just one data (always in
the same cell). So first question: Is it possible to get data and no
needing to put in a sheet first? (remember I need it in a form textbox)

As I didn't find another way to do this, I tried using a very hidden
sheet (which was already in use by other procedures). The problem is
that now the query doesn't let me put the data in another sheet that is
not the active sheet. Second question: Any workaround to this problem?

Finally the only solution I see is, by code, unhide the very hidden
sheet, switch to it, do the web query, then go back to the previous
active sheet, re hide the very hidden sheet and continue working. All
this "hidden" behind "Application.ScreenUpdating=False".

Is there any better solutions to this?

Regards,
--
Beto
Reply: Erase between the dot (inclusive) and the @.
Responder: Borra la frase obvia y el punto previo.

Beto,

If you use WebQuery, then as far as I can see you have to return it to a
worksheet. You could use APIs to circumvent this if you are so inclined.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Beto" wrote in message
...
Hi,
I was learning about web queries for a small project. I wanted a value
taken from a web page to be showed in a textbox in a form.

I recorded the steps to create the web query to dump the data in a sheet
and it worked. It's kind of a table, but I need just one data (always in
the same cell). So first question: Is it possible to get data and no
needing to put in a sheet first? (remember I need it in a form textbox)

As I didn't find another way to do this, I tried using a very hidden
sheet (which was already in use by other procedures). The problem is
that now the query doesn't let me put the data in another sheet that is
not the active sheet. Second question: Any workaround to this problem?

Finally the only solution I see is, by code, unhide the very hidden
sheet, switch to it, do the web query, then go back to the previous
active sheet, re hide the very hidden sheet and continue working. All
this "hidden" behind "Application.ScreenUpdating=False".

Is there any better solutions to this?

Regards,
--
Beto
Reply: Erase between the dot (inclusive) and the @.
Responder: Borra la frase obvia y el punto previo.

Bob Phillips wrote:

Beto,

If you use WebQuery, then as far as I can see you have to return it to a
worksheet. You could use APIs to circumvent this if you are so inclined.


Ok, then the Hidden sheet it is... APIs are way out of my league.

Regards,
--
Beto
Reply: Erase between the dot (inclusive) and the @.
Responder: Borra la frase obvia y el punto previo.

Beto,

There are a few ways to do this programmatically without using API
functions. One is to use the XMLHTTP object to "screen scrape" the web page
and get the value. Here's a thread with a good example of how to do it:

http://groups.google.com/groups?hl=e...GP09.phx.gb l

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Beto wrote:
Hi,
I was learning about web queries for a small project. I wanted a value
taken from a web page to be showed in a textbox in a form.

I recorded the steps to create the web query to dump the data in a
sheet and it worked. It's kind of a table, but I need just one data
(always in the same cell). So first question: Is it possible to get
data and no needing to put in a sheet first? (remember I need it in a
form textbox)

As I didn't find another way to do this, I tried using a very hidden
sheet (which was already in use by other procedures). The problem is
that now the query doesn't let me put the data in another sheet that
is not the active sheet. Second question: Any workaround to this
problem?

Finally the only solution I see is, by code, unhide the very hidden
sheet, switch to it, do the web query, then go back to the previous
active sheet, re hide the very hidden sheet and continue working. All
this "hidden" behind "Application.ScreenUpdating=False".

Is there any better solutions to this?

Regards,

Jake Marx wrote:

Beto,

There are a few ways to do this programmatically without using API
functions. One is to use the XMLHTTP object to "screen scrape" the web page
and get the value. Here's a thread with a good example of how to do it:

http://groups.google.com/groups?hl=e...GP09.phx.gb l

Hi Jake, I tried the example, but it failed in a compilation error right
he

Dim xml As XMLHTTP40

I already set "Microsoft XML, v3.0" (version 3 in my case) in
Tools/References of the VBE Editor.

Any ideas of what's wrong?

Regards,
PS: I copied the post you were referring to so you won't need to look
for it again.
--
Beto
Reply: Erase between the dot (inclusive) and the @.
Responder: Borra la frase obvia y el punto previo.

------------------------------------------------------------------
[COPIED POST STARTS]

You can post data to a website and grab the response using XMLHTTP. Here's
some sample code that will use the babelfish site to translate a phrase,
then return the translated phrase. In order to get it to work, you need to
set a reference to "Microsoft XML, vX.0" (where in my case, X=4).

'/-------------BEGIN CODE---------------
Public Enum TranslateLanguages
Eng_Fren = 1
Eng_Ger = 2
Eng_Ita = 3
Eng_Port = 4
Eng_Span = 5
Fren_Eng = 6
Fren_Ger = 7
Ger_Eng = 8
Ger_Fren = 9
Ita_Eng = 10
Port_Eng = 11
Span_Eng = 12
End Enum

Public Function gsTranslateText(rsTextToTranslate, _
rMode As TranslateLanguages) As String
Dim xml As XMLHTTP40
Dim abytPostData() As Byte
Dim sMode As String
Dim sResponse As String
Dim nStartPos As Integer
Dim nEndPos As Integer

Select Case rMode
Case Eng_Fren
sMode = "en_fr"
Case Eng_Ger
sMode = "en_de"
Case Eng_Ita
sMode = "en_it"
Case Eng_Port
sMode = "en_pt"
Case Eng_Span
sMode = "en_es"
Case Fren_Eng
sMode = "fr_en"
Case Fren_Ger
sMode = "fr_de"
Case Ger_Eng
sMode = "de_en"
Case Ger_Fren
sMode = "de_fr"
Case Ita_Eng
sMode = "it_en"
Case Port_Eng
sMode = "pt_en"
Case Span_Eng
sMode = "es_en"
End Select

abytPostData = StrConv("doit=done&intl=1" _
& "&tt=urltext&lp=" & sMode & "&urltext=" _
& rsTextToTranslate, vbFromUnicode)

Set xml = New XMLHTTP40
With xml
.Open "POST", _
"http://babelfish.altavista.com/babelfish/tr"
.setRequestHeader "Content-Type", _
"application/x-www-form-urlencoded"
.send abytPostData
sResponse = .responseText
End With

'/ find translation
nStartPos = InStr(1, sResponse, "lang=" & _
Right$(sMode, 2), vbTextCompare)
If nStartPos Then
nStartPos = nStartPos + 8
nEndPos = InStr(nStartPos, sResponse, _
"</div", vbTextCompare) - 1
If nEndPos = nStartPos Then gsTranslateText = _
Mid$(sResponse, nStartPos, nEndPos - _
nStartPos + 1)
End If

Set xml = Nothing
End Function
'/--------------END CODE----------------

in version 3 there is an XMLHTTP30, but no XMLHTTP40

try using the 30.

--
Regards,
Tom Ogilvy

"Beto" wrote in message
...
Jake Marx wrote:

Beto,

There are a few ways to do this programmatically without using API
functions. One is to use the XMLHTTP object to "screen scrape" the web
page
and get the value. Here's a thread with a good example of how to do it:


http://groups.google.com/groups?hl=e...GP09.phx.gb l

Hi Jake, I tried the example, but it failed in a compilation error right
he

Dim xml As XMLHTTP40

I already set "Microsoft XML, v3.0" (version 3 in my case) in
Tools/References of the VBE Editor.

Any ideas of what's wrong?

Regards,
PS: I copied the post you were referring to so you won't need to look
for it again.
--
Beto
Reply: Erase between the dot (inclusive) and the @.
Responder: Borra la frase obvia y el punto previo.

------------------------------------------------------------------
[COPIED POST STARTS]

You can post data to a website and grab the response using XMLHTTP.
Here's
some sample code that will use the babelfish site to translate a phrase,
then return the translated phrase. In order to get it to work, you need
to
set a reference to "Microsoft XML, vX.0" (where in my case, X=4).

'/-------------BEGIN CODE---------------
Public Enum TranslateLanguages
Eng_Fren = 1
Eng_Ger = 2
Eng_Ita = 3
Eng_Port = 4
Eng_Span = 5
Fren_Eng = 6
Fren_Ger = 7
Ger_Eng = 8
Ger_Fren = 9
Ita_Eng = 10
Port_Eng = 11
Span_Eng = 12
End Enum

Public Function gsTranslateText(rsTextToTranslate, _
rMode As TranslateLanguages) As String
Dim xml As XMLHTTP40
Dim abytPostData() As Byte
Dim sMode As String
Dim sResponse As String
Dim nStartPos As Integer
Dim nEndPos As Integer

Select Case rMode
Case Eng_Fren
sMode = "en_fr"
Case Eng_Ger
sMode = "en_de"
Case Eng_Ita
sMode = "en_it"
Case Eng_Port
sMode = "en_pt"
Case Eng_Span
sMode = "en_es"
Case Fren_Eng
sMode = "fr_en"
Case Fren_Ger
sMode = "fr_de"
Case Ger_Eng
sMode = "de_en"
Case Ger_Fren
sMode = "de_fr"
Case Ita_Eng
sMode = "it_en"
Case Port_Eng
sMode = "pt_en"
Case Span_Eng
sMode = "es_en"
End Select

abytPostData = StrConv("doit=done&intl=1" _
& "&tt=urltext&lp=" & sMode & "&urltext=" _
& rsTextToTranslate, vbFromUnicode)

Set xml = New XMLHTTP40
With xml
.Open "POST", _
"http://babelfish.altavista.com/babelfish/tr"
.setRequestHeader "Content-Type", _
"application/x-www-form-urlencoded"
.send abytPostData
sResponse = .responseText
End With

'/ find translation
nStartPos = InStr(1, sResponse, "lang=" & _
Right$(sMode, 2), vbTextCompare)
If nStartPos Then
nStartPos = nStartPos + 8
nEndPos = InStr(nStartPos, sResponse, _
"</div", vbTextCompare) - 1
If nEndPos = nStartPos Then gsTranslateText = _
Mid$(sResponse, nStartPos, nEndPos - _
nStartPos + 1)
End If

Set xml = Nothing
End Function
'/--------------END CODE----------------

Beto,
The folling code works with the sheet hidden. At it does on my system.

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Worksheets("managed funds").Activate
Range("j2").Select
Selection.QueryTable.Refresh BackgroundQuery:=True
' Range("a1").Select
With Client
..TextBox1.Text = Range("D14").Text
End With
End Sub

HTH
Charles


---
Message posted from http://www.ExcelForum.com/

Charles < wrote:
Beto,
The folling code works with the sheet hidden. At it does on my system.

Hi,

I hadn't even tried it without unhiding and rehiding the very hidden
sheet, because I thought I needed to Select it. Now I tried using
Activate as in your code and it works fine, no need of unhiding and
rehiding. I don't even need to turn off ScreenUpdating. Thanks!

Regards,
--
Beto
Reply: Erase between the dot (inclusive) and the @.
Responder: Borra la frase obvia y el punto previo.

Tom Ogilvy wrote:

in version 3 there is an XMLHTTP30, but no XMLHTTP40

try using the 30.

Now it's working! Thanks Jake and Tom. I had to do the "Do While
..readyState < 4 : DoEvents : Loop" trick, because it was giving me the
#VALUE error.

Now I'll try to figure out its innerworkings to modify it for suiting my
needs.

Regards,
--
Beto
Reply: Erase between the dot (inclusive) and the @.
Responder: Borra la frase obvia y el punto previo.