How to do automatic Web Queries??

[Guest]

Hi,

I would like to be able to import data automatically from a web page without
having to type in the web address.

I can create the correct url path in a cell using a CONCATENATE function but
cannot create a macro which references to that cell for the url look up.

This is the macro as it currently stands


With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.************************", _
Destination:=Range("a1"))
.Name = "main.php?cid=5516"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "2"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False

I would like this part ie With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.************************", _
Destination:=Range("a1"))
to use an url from a cell rather than a fixed link

Sorry if I have descibed this problem poorly.

 

[pinmaster]

Hi, I wanted to do excatly that a few months ago, so I was able to come
up with this:
connstring = Range("Sheet2!B2").Value
With ActiveSheet.QueryTables.Add(Connection:=connstring,

you will have to add "URL;" to your cell.

HTH
JG

 

[Guest]

Try something like this:

Dim URL2Use as String
URL2Use = "URL;" & Sheet1.Range("B11").Value

With ActiveSheet.QueryTables.Add(Connection:=URL2Use,
Destination:=Range("A1"))


Does that help?

***********
Regards,
Ron

 

[Guest]

Hi, yes that worked great, just needed a _ adding
ie
Dim URL2Use as String

URL2Use = "URL;" & Sheet1.Range("B11").Value

With ActiveSheet.QueryTables.Add(Connection:=URL2Use, _
Destination:=Range("A1"))

Thanks very much, am in your debt!!!!

 

[Guest]

Hi,

This worked a treat thanks, just needed a _ adding as below