Excel ActiveSheet.QueryTables.Add and non-ASCII characters

Question

I use the following code to fetch the page for "cusp" from www.merriam-webstercom. It worked fine except that phonetic symbols didn't show up properly. I got things like these: \ËˆkÉ™-ËŒspÄt, -spÉ™t . When trying to paste the phonetic symbols to this page, I got exactly the same scribbling things.

I searched the web but didn't get any helpful leads.

Any thoughts? Thanks.

Sub import_from_web(ByVal lookup_word As String)

With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;https://www.merriam-webster.com/dictionary/" & lookup_word,   Destination:= _
    Range("$A$1"))
    .Name = "d"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .TextFilePlatform = xlMSDOS
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlEntirePage
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
End With
End Sub

[This](https://stackoverflow.com/questions/23626622/vba-importing-utf-8-csv-file-from-a-web-server) may be of use — cybernetic.nomad, Oct 26 '18 at 21:21
Thanks. I had actually tried "TEXT" instead of "URL" but it fetched the HTML document (full of HTML tags). I just want the simple text file. — joehua, Oct 26 '18 at 21:28
I was thinking of the encoding issue. What happens if you try setting `TextFilePlatform` to 65000 or 65001? — cybernetic.nomad, Oct 26 '18 at 21:32
@cybemetic.nomad Thanks for the comment. Actually, that line should be commented out because it, I believe, only works with TEXT, not URL. It must be a remnant from earlier trial. With that line, the code cannot compile. — joehua, Oct 26 '18 at 23:12
65001 actually preserves the phonetic symbols. Anyway to remove HTML tags and leave only the simple text? This maybe my only option. — joehua, Oct 27 '18 at 06:53

score 0 · Accepted Answer · answered Oct 28 '18 at 12:22

I ended up using a completely different approach. Instead of creating a link, I just opened the page and copied & pasted the page to Excel.

Thank to this thread:

Excel2010: PasteSpecial failing when copying from IE

Sub search_paste(ByRef IE As Object, ByVal lookup_word As String)

' this sub can handle non-ASCII characters
' it accepts a word from the calling sub and searches the word at Merian-Webster
' it then copies the web page and pastes to the ActiveSheet for further processing

With IE
    .Visible = True
'        .Navigate 
    .Navigate "https://www.merriam-webster.com/dictionary/" & lookup_word ' open the page containing the search word

    Do Until .ReadyState = 4: DoEvents: Loop
End With
DoEvents
IE.ExecWB 17, 0 '// SelectAll
IE.ExecWB 12, 2 '// Copy selection
ActiveSheet.PasteSpecial link:=False, DisplayAsIcon:=False, NoHTMLFormatting:=True

End Sub

Excel ActiveSheet.QueryTables.Add and non-ASCII characters

1 Answers1