Excel VBA QueryTables Preserve Hyperlinks

Question

I use QueryTables quite a lot in VBA to scrape data from webpages. I wondered if there was a way to preserve the hyperlinks that the webpage the query is pulling data from has.

Here is an example Query:

With ActiveSheet.QueryTables.Add(Connection:= _
 "URL;http://dom.com/table, Destination:=Range("$A$1"))
 .Name = "table"
 .FieldNames = True
 .RowNumbers = False
 .FillAdjacentFormulas = False
 .PreserveFormatting = True
 .RefreshOnFileOpen = False
 .BackgroundQuery = True
 .RefreshStyle = xlInsertDeleteCells
 .SavePassword = False
 .SaveData = True
 .AdjustColumnWidth = True
 .RefreshPeriod = 0
 .WebSelectionType = xlSpecifiedTables
 .WebFormatting = xlWebFormattingRTF
 .WebTables = """datatable"""
 .WebPreFormattedTextToColumns = True
 .WebConsecutiveDelimitersAsOne = True
 .WebSingleBlockTextImport = False
 .WebDisableDateRecognition = False
 .WebDisableRedirections = False
 .Refresh BackgroundQuery:=False
End With

Yes that did it @VBasic2008, feel free to write up an answer. — FreeSoftwareServers, Nov 05 '20 at 19:25
if webpage is static /largely static you could use less expensive xmlhttp with clipboard to copy table and refresh with button or schedule. — QHarr, Nov 05 '20 at 21:41
I found @VBasic2008 way to work fine for my needs, I'm ok with a bit of "cost" during scraping (it's fairly fast in my case anyway). But Thanks! I'll wait till tomorrow and if VBasic doesn't write an answer, I'll write one up. — FreeSoftwareServers, Nov 05 '20 at 22:06
I'm interested to learn new ways/things tho, perhaps you want to write an answer and I'll test and vote accordingly if it resolves question! @QHarr — FreeSoftwareServers, Nov 05 '20 at 22:07
I used to use it a lot, but not lately. I even asked a [question](https://stackoverflow.com/questions/60902093/last-zeros-truncated-when-getting-external-data) when I had an issue with `International` where QHarr responded. I accepted the other, Ron's answer since it solved my problem. But QHarr's solution was superior, more efficient. Check it out to see how little code is needed. Feel free to post an answer since I won't be writing one. I'm pretty unfamiliar with the subject. It was just by chance that I noted your question. But thanks anyway. I will check out your answer though. — VBasic2008, Nov 05 '20 at 22:42

score 0 · Accepted Answer · answered Nov 05 '20 at 22:42

The trick was changing .WebFormatting = xlWebFormattingRTF to .WebFormatting = xlWebFormattingAll

Additional Resources:

https://learn.microsoft.com/en-us/office/vba/api/excel.querytable.webformatting https://learn.microsoft.com/en-us/office/vba/api/excel.xlwebformatting

Name              Value Description
xlWebFormattingAll  1   All formatting is imported.
xlWebFormattingNone 3   No formatting is imported.
xlWebFormattingRTF  2   Rich Text Format - compatible formatting is imported.

Excel VBA QueryTables Preserve Hyperlinks

1 Answers1