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.

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


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=490455

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


"Alarmbloke" wrote:

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.

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!!!!

"Ron Coderre" wrote:

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


"Alarmbloke" wrote:

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.

Hi,

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

connstring = Range("Sheet2!B2").Value
With ActiveSheet.QueryTables.Add(Connection:=connstring , _

"pinmaster" wrote:


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


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=490455