Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1693
  • Last Modified:

Excel VBA QueryTable Loop

In Excel VBA, how would I use QueryTables to loop through, for example, on http://translate.reference.com/ to supply a word, say English "two" and translate into every language on its drop down to>> list. Then store results in Sheet1 like
Afrikaans twee
Albanian dy
Arabic ¿¿¿¿¿
...
French deux
...
Yiddish ¿¿¿¿¿


Regards
Brian
0
canesbr
Asked:
canesbr
1 Solution
 
thydzikCommented:
what do you mean querytables? is this a static table you have, or are you referring to going online?
what do you mean by 'on its dropdown list'?
this is possible but need there clarifications.
is it possible to attach an example spreadsheet?
0
 
insaneCFCCommented:
0
 
NorieData ProcessorCommented:
Brian

If you wanted to use QueryTables then the first thing you would probably need to do is find out if there is a URL that does the translation using the text entered and the language(s) picked.

For example this translates 'house' from English to Icelandic.

http://translate.reference.com/translate?query=house&src=en&dst=is&v=1.0

If you need to do this for multiple words and languages you would need to construct this URL using string concatenation.

Then you would need to use this URL for the QueryTable, which I assume is a web query.

You might have problems with that though, I'm afraid the URL doesn't return any data according to Excel.
0
 
canesbrAuthor Commented:
I have an intermediate solution
Please see code attached.
I will ask a follow up in another question.
Regards
Brian
Option Explicit
Sub GetTranslationAllLanguagesSAS()

    Dim myword As String
    Dim L As Range
    Dim ss As Worksheet
    Dim mfr As Range
    
    Set ss = Sheets(1)
    Range("b2").Resize(Cells(Rows.Count, 1).End(xlUp).Row, 2).ClearContents
    Application.ScreenUpdating = False
        myword = Range("b1")
        For Each L In Range("Languages")
           With ss.QueryTables(1)
                .Connection = _
                 "URL;http://translate.reference.com/translate?query=" & _
                  myword & "&src=en&dst=" & L & "&v=1.0"
                .Refresh BackgroundQuery:=False
            End With
         Set mfr = ss.Columns(1).Find(What:="Translation:", LookIn:=xlValues, _
         LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)
         If Not mfr Is Nothing Then
            L.Offset(, 1).Value = Mid(ss.Cells(mfr.Row, 1), 14, 256)
            L.Offset(, 2).Value = ss.Cells(mfr.Row + 1, 1)
         End If
        Next L
        Columns.AutoFit
    Application.ScreenUpdating = True
End Sub
Sub Macro1() 'macro above modified from this recorded while editing querytable.  by d guillett
    Range("A5").Select
    With Selection.QueryTable
        .Connection = _
        "URL;http://translate.reference.com/translate?query=two&src=en&dst=tl&v=1.0"
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

Open in new window

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now