Setting Webquery url as a string...

[bapcki1]

Hi everyone!

I want to have a webquery that pulls figures from a url in a cell. I was thinking it might be possible to do this using a string but I cant seem to get it to work.

Can anyone help me please?

   With Selection.QueryTable
        .Connection = "[COLOR="Red"]INSERT STRING HERE[/COLOR]"
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingAll
        .WebPreFormattedTextToColumns = False
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = True
        .Refresh BackgroundQuery:=False
    End With

Many thanks,


Paddy

 

[bensonsearch]

Hi,
the connection should be a string so if pulling from a cell something like

dim urls as string
urls = sheets("Sheet1").range("A1").value
With Selection.QueryTable
        .Connection = urls
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingAll
        .WebPreFormattedTextToColumns = False
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = True
        .Refresh BackgroundQuery:=False
    End With

 

[bapcki1]

Thank you for your reply Bensonsearch,

Im afraid the code you suggested didn't quite work.

I've re-tried the webquery with the URL as a string called newurl.

Sub Macro2()

Dim newurl As String
newurl = ThisWorkbook.Sheets("Sheet1").Range("B2").Value

    Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:=[COLOR="red"][I][B]"URL;http://bit.ly/zqw44F+"[/B][/I][/COLOR] _
        , Destination:=Range("A1"))
        .Name = [COLOR="Red"][B]newurl[/B][/COLOR]
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

The string you suggested works for the .Name but it seems that no matter what variation i try with the (Connection:= and the newurl string it wont work.

When I debug it, the last line ".Refresh BackgroundQuery:=False" is highlighted in yellow.

Do you have a way to solve this please?

Thank you for your time and help.


Paddy

 

[bensonsearch]

try commenting out the background wuery part but leaving the .refresh in there.

the code you have is somewhat different now. is the url for a name or connection?

if its to go to the connection part then

With ActiveSheet.QueryTables.Add(Connection:=[COLOR=red][I][B]"URL;" & newurl[/B][/I][/COLOR] _
        , Destination:=Range("A1"))

 

[bapcki1]

Hi bensonsearch,

THANK YOU for solving my problem. I have been looking for a more effecient, reliable way of managing web queries for about 4 months now and you have just solved it!

Thank you so much!


Paddy

 

[bapcki1]

How would I mark this post as solved?

Paddy

 

[bensonsearch]

Hi Paddy,

Your more than welcome, always happy to help

I dont beleive their is a "solved" mark. never seen one