时间戳

首页 > 解决方案 > Excel ActiveSheet.QueryTables.Add 和非 ASCII 字符

excel - Excel ActiveSheet.QueryTables.Add 和非 ASCII 字符

问题描述

我使用以下代码从 www.merriam-webstercom 获取“尖顶”页面。它工作正常，只是音标没有正确显示。我得到了这样的东西： \ˈkÉ™-ËŒspÄt, -spÉ™t 。尝试将音标粘贴到此页面时，我得到了完全相同的涂鸦内容。

我搜索了网络，但没有得到任何有用的线索。

有什么想法吗？谢谢。

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

标签： excelvbanon-ascii-characters

解决方案

我最终使用了一种完全不同的方法。我没有创建链接，而是打开页面并将页面复制并粘贴到 Excel。

感谢这个线程：

Excel2010：从 IE 复制时 PasteSpecial 失败

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

推荐阅读