Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel-Web Queries
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel-Web Queries
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel-Web Queries
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel-Web Queries
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] M wrote: 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel-Web Queries
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] M wrote: 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 . . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel-Web Queries
No problem - glad to help!
-- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] M wrote: 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] M wrote: 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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel queries | Excel Discussion (Misc queries) | |||
excel queries dsn | Excel Discussion (Misc queries) | |||
queries in excel | Excel Discussion (Misc queries) | |||
Web Queries Excel 97 | Excel Discussion (Misc queries) | |||
Excel Queries. | Excel Programming |