Using a URL from a Cell in a Web query macro

[bracken752]

Hey all,

I hope you're all well and are able to help me out (yet again) for my new project (I will soon be able to help people instead of being a pain in your ***** lol).

I am using =CONCATENATE to create a URL based on the outcome of a drop down menu (For example, select "Barry Smith" from the drop down, using vlookup this finds his "ID number" and using =CONCATENATE I put this into the URL.

The issue is now that I want to use a web query and take advantage of the cell where I have used =CONCATENATE (For example Cell A1)

The webquery needs to create a new worksheet and then query the URL that is in Cell A1, I currently do this manually via macro record which is as follows:

    ActiveWorkbook.Worksheets.Add    With ActiveSheet.QueryTables.Add(Connection:="URL;http://www.REMOVED.net/RestrictedPages/UserDetail.aspx?userID=36", _
        Destination:=Range("$B$2"))
        .Name = "UserDetail.aspx?userID=36"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False

Any ideas? I would love you long time if you could help me out here, I think my head will explode if I look at anymore VBA lol.

Kindest Regards,
JB

 

[bracken752]

Still can't get this working any of you guys have any ideas?

JB

 

[John_w]

The issue is now that I want to use a web query and take advantage of the cell where I have used =CONCATENATE (For example Cell A1)

If this A1 cell is on Sheet1, then:

Sub WQ()
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.QueryTables.Add(Connection:="URL;" & Worksheets("Sheet1").Range("A1").Value, _
        Destination:=Range("$B$2"))
        .Name = "UserDetail.aspx?userID=36"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[bracken752]

Hey John_w,

Thank you for your help on this matter, but sadly doesn't seem to work, it opens a new sheet (Which is what I want) starts the web query and looks like it does something but just leaves the new sheet blank.

Any other ideas?

Sorry to be a pain.

Regards
JB

 

[bracken752]

Got it working yay . Using a copy and CutCopyMode functions.

ActiveWorkbook.Worksheets.AddSelection.Copy
Application.CutCopyMode = False
Range("S12").Select
With ActiveSheet.QueryTables.Add(Connection:="URL;" & Sheets("Sheet1").Range("S12").Value, Destination:=Range("A1"))
.Name = "names"
.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

I have now encountered a new problem but I will create a new post for this and mark this as solved.