Excel-Web Queries

  • Thread starter Thread starter M
  • Start date Start date
M

M

I know about web queries being used to get data such as
stock quotes to populate cells. And I want to set up a
similiar web query in Excel 2000, but in this case it's a
little different. I have data that I export from Access
2000 to Excel 2000 with no formatting. This data includes
basic information that is in English. What I want to do is
have a web query set up in Excel (ie. using
http://babelfish.altavista.com/babelfish/tr or the like)
that takes the English word and provides a translation in
an adjacent column. Possible in this day and age?

Thanks

M
 
Hi M,

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

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
Thanks for the quick response. The code works great but I
am getting a run-time error---

'-2147483638 (8000000a) The data necessary to complete
this operation is not yet available.'

Could this be due to our wonderful firewall that prohibits
Excel Web Queries? Aside from those pesky network folk,
thanks again for the great help.

M


-----Original Message-----
Hi M,

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

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

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

I know about web queries being used to get data such as
stock quotes to populate cells. And I want to set up a
similiar web query in Excel 2000, but in this case it's a
little different. I have data that I export from Access
2000 to Excel 2000 with no formatting. This data includes
basic information that is in English. What I want to do is
have a web query set up in Excel (ie. using
http://babelfish.altavista.com/babelfish/tr or the like)
that takes the English word and provides a translation in
an adjacent column. Possible in this day and age?

Thanks

M
Click to expand...

.
Click to expand...
 
Hi M,

I don't think it's due to your firewall - I'd be very surprised if this type
of traffic was being blocked or otherwise restricted. It's using standard
HTTP POST requests, which all takes place on TCP port 80. If the firewall
were blocking this port, you'd have no access to the web.

It works fine on my machine, but maybe you need to check the state of the
request before trying to get the results. Add these 3 lines of code after
invoking the send method and before you request the responseText property:

Do While .readyState <> 4
DoEvents
Loop

That should help - let us know if it doesn't.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

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

Thanks for the quick response. The code works great but I
am getting a run-time error---

'-2147483638 (8000000a) The data necessary to complete
this operation is not yet available.'

Could this be due to our wonderful firewall that prohibits
Excel Web Queries? Aside from those pesky network folk,
thanks again for the great help.

M


-----Original Message-----
Hi M,

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

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

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

I know about web queries being used to get data such as
stock quotes to populate cells. And I want to set up a
similiar web query in Excel 2000, but in this case it's a
little different. I have data that I export from Access
2000 to Excel 2000 with no formatting. This data includes
basic information that is in English. What I want to do is
have a web query set up in Excel (ie. using
http://babelfish.altavista.com/babelfish/tr or the like)
that takes the English word and provides a translation in
an adjacent column. Possible in this day and age?

Thanks

M
Click to expand...

.
Click to expand...
Click to expand...
 
Jackpot! That did the trick. If I hadn't stated earlier,
this will enable me to start offering translations on our
Access reports to our associates. I am definately sold on
XML now. You have really been a great help. Thanks again
for everything.


-----Original Message-----
Hi M,

I don't think it's due to your firewall - I'd be very surprised if this type
of traffic was being blocked or otherwise restricted. It's using standard
HTTP POST requests, which all takes place on TCP port 80. If the firewall
were blocking this port, you'd have no access to the web.

It works fine on my machine, but maybe you need to check the state of the
request before trying to get the results. Add these 3 lines of code after
invoking the send method and before you request the responseText property:

Do While .readyState <> 4
DoEvents
Loop

That should help - let us know if it doesn't.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

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

Thanks for the quick response. The code works great but I
am getting a run-time error---

'-2147483638 (8000000a) The data necessary to complete
this operation is not yet available.'

Could this be due to our wonderful firewall that prohibits
Excel Web Queries? Aside from those pesky network folk,
thanks again for the great help.

M


-----Original Message-----
Hi M,

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

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

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


M wrote:
I know about web queries being used to get data such as
stock quotes to populate cells. And I want to set up a
similiar web query in Excel 2000, but in this case it's a
little different. I have data that I export from Access
2000 to Excel 2000 with no formatting. This data includes
basic information that is in English. What I want to do is
have a web query set up in Excel (ie. using
http://babelfish.altavista.com/babelfish/tr or the like)
that takes the English word and provides a translation in
an adjacent column. Possible in this day and age?

Thanks

M

.
Click to expand...
Click to expand...

.
Click to expand...
 
No problem - glad to help!

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

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

Jackpot! That did the trick. If I hadn't stated earlier,
this will enable me to start offering translations on our
Access reports to our associates. I am definately sold on
XML now. You have really been a great help. Thanks again
for everything.


-----Original Message-----
Hi M,

I don't think it's due to your firewall - I'd be very surprised if
this type of traffic was being blocked or otherwise restricted. It's
using standard HTTP POST requests, which all takes place on TCP port 80. If the firewall
were blocking this port, you'd have no access to the web.

It works fine on my machine, but maybe you need to check the state
of the request before trying to get the results. Add these 3 lines
of code after invoking the send method and before you request the
responseText property:

Do While .readyState <> 4
DoEvents
Loop

That should help - let us know if it doesn't.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

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

Thanks for the quick response. The code works great but I
am getting a run-time error---

'-2147483638 (8000000a) The data necessary to complete
this operation is not yet available.'

Could this be due to our wonderful firewall that prohibits
Excel Web Queries? Aside from those pesky network folk,
thanks again for the great help.

M



-----Original Message-----
Hi M,

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

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

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


M wrote:
I know about web queries being used to get data such as
stock quotes to populate cells. And I want to set up a
similiar web query in Excel 2000, but in this case it's a
little different. I have data that I export from Access
2000 to Excel 2000 with no formatting. This data includes
basic information that is in English. What I want to do is
have a web query set up in Excel (ie. using
http://babelfish.altavista.com/babelfish/tr or the like)
that takes the English word and provides a translation in
an adjacent column. Possible in this day and age?

Thanks

M

.
Click to expand...

.
Click to expand...
Click to expand...
 
Hi ,
I am facing the same problem
I am trying to post xml to an MQ using excel VB
This error is thrown
2147483638 (8000000A) The data necessary to complete this operation

This is the piece of code
gstrUrl = "http://172.24.175.151:61010/eai_enu...EExtCmd=Execute&Username=test1&Password=test1" ' &SWEExtData= abcd"

Set oHttp = New MSXML2.ServerXMLHTTP40

oHttp.Open "POST", gstrUrl, True
oHttp.send (strDataHTTP_IN)

The problem out here is
The parameter &SWEExtData should contain the xml data
and if we append it in the url when .send is renedered useless
and .send should contain the xml which should be preceeded by &SWEExtData
as this is the siebel mandatry format

please help
 
Back
Top