Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Excel VBA QueryTable Loop

Posted on 2010-08-18
4
1,511 Views
Last Modified: 2012-05-10
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
Comment
Question by:canesbr
4 Comments
 
LVL 11

Expert Comment

by:thydzik
ID: 33472406
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
 
LVL 2

Expert Comment

by:insaneCFC
ID: 33474470
0
 
LVL 33

Expert Comment

by:Norie
ID: 33475560
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
 

Accepted Solution

by:
canesbr earned 0 total points
ID: 33497742
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
randomize data from excell cell 15 45
Limit the # times a macro code will run 14 50
Excel if formula 2 19
VBA to Delete the Filtered Rows in an Excel Table 3 32
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question