Code to get hyperlinks from open web page.

  • Thread starter Thread starter Peter Jamieson
  • Start date Start date
P

Peter Jamieson

Using Win xp and Office xp.
I go to a web-based database, enter my username and password which brings up
several hyperlinks for me to then follow and obtain data.
When I don't have time to follow the hyperlinks straight away I would like
to be able to run some code to get these hyperlink addresses into my
spreadsheet for later use .
Is this possible using VBA code?
I have tried linking a web query to my sheet but it does not give me any
joy.
Any assistance appreciated!
 
Can you verify the QueryTables.
Follows an example:

Dim qt As QueryTable
sqlstring = "select 96Sales.totals from 96Sales where profit < 5"
connstring = _
"ODBC;DSN=96SalesData;UID=Rep21;PWD=NUyHwYQI;Database=96Sales"
With ActiveSheet.QueryTables.Add(Connection:=connstring, _
Destination:=Range("B1"), Sql:=sqlstring)
.Refresh
End With

Or other example:
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://noticias.uol.com.br/economia", Destination:=Range("A1"))
.Name = "economia"
.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 = xlWebFormattingNone
.WebTables = "9"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

Cleber
 
Thanks for your input Cleber!
However as I mentioned in my post I had tried the query approach you suggest
but it failed.
I think the reason is maybe related to the online db getting its data via
Javascript after a request is made.
Cheers, Peter J.
 
Back
Top