Code to get hyperlinks from open web page.
 

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!

Code to get hyperlinks from open web page.
 

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;Datab ase=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


"Peter Jamieson" escreveu na mensagem
...
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!

Code to get hyperlinks from open web page.
 

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.

"Cleber P. de Souza" wrote in message
...
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;Datab ase=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


"Peter Jamieson" escreveu na mensagem
...
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!