Dynamic Web Query

[sinison]

Dear community,

I could really use some help on this one, i've been trying to get this functioning for weeks now but i can't really get it to work properly.


The Situation
I need to keep track of 100+ different counters, each, located on their own (so different) IP address. I want to automate the process so users only have to enter their own username, and then the data is retrieved accordingly.

I'm trying to make a query, which changes upon changing the Cell value
For example: 1.111.11.1 is entered in cell "A1", due to this value, the query automatically loads in the data of that IP address. However, when "A1" changes to, let's say; 1.111.11.2, the query needs to automatically erase the previous data, and return the numbers of the given IP address.

I've attached a image to this post; there it shows what i want to accomplish.

I think this should not be to hard, i've tried making the query in VBA, but, when i need to specify the URL, i don't know how to refer the the cell value.

Sub GetCourseList()

Dim URL As String
Dim qt As QueryTable
Dim ws As Worksheet

Set ws = Worksheets.Add

URL = "CELL VALUE?!?!"

Set qt = ws.QueryTables.Add( _
Connection:="URL;" & URL, _
Destination:=Range("A10"))

With qt
.RefreshOnFileOpen = True
.Name = "test"
.FieldNames = True
.WebSelectionType = xlAllTables
.Refresh BackgroundQuery:= TRUE
End With

End Sub

I've tried this code, did not work, i know entered ?!?! at the cell value part since this is the part i'm confused about.

Hope you guys can help!

 

[jmacleary]

Replace URL = "CELL VALUE?!?!" with URL=range("A1").Text

 

[sinison]

Thanks! that helped me out.

I get another error message now, saying ERROR 1004: on this line of code: .Refresh BackgroundQuery:=True

Then i tried recording a macro and run the query via excel so i can edit the code in VBA.

It gave me this code:

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://185.30.29.7/cgi-bin/counts.cgi", Destination:=Range("$F$10"))
.Name = "counts"
.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

But when i change the URL to your suggested code, it gives me an error.


Any suggestions on this? i feel im really close but not there at all...

 

[jmacleary]

You will need to build up the full url based on the macro as follows:

URL="URL;http://"&range("A1").Text&"/cgi-bin/counts.cgi"

Then in your recorded code replace the URL text string with the URL variable so it will read:
With ActiveSheet.QueryTables.Add(Connection:= _
URL, Destination:=Range("$F$10"))

 

[sinison]

I think i missed something; im getting a syntaxerror

Current code:
Sub Macro1()
'
' Macro1 Macro
'


'
With ActiveSheet.QueryTables.Add(Connection:= _
URL, Destination:=Range("$F@10"))
URL = "URL;http://"&Range("A1").text&"/cgi-bin/counts.cgi"
.Name = "counts"
.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

[/URL][/IMG]

 

[sinison]

i've tried most variations on this, all resulting in syntax error or Compile error: Expected: List Separator or ).


What am I doing wrong?


gr

 

[sinison]

I think I got a bit further, however, I keep getting an error I can't figure out


current Code
Sub testnet()


Dim sh As Worksheet
Dim Rng As Range
Dim Cell As Range
Dim URL As String
URL = Range("A1").Text


With ActiveSheet.QueryTables.Add(Connection:=URL, Destination:=Range("$F$10"))
.Name = "counts"
.FieldNames = True
.RowNumbers = False
.FilladjaccmtFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.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
Range("A2").Select
End Sub

The red colored text is highlighted yellow in VBA.

What needs to be changed?

[/URL][/IMG]

 

[jmacleary]

The yellow highlighting means you have set a breakpoint on that line - press Ctrl-shift-F9 together to remove all breakpoints.

It looks like you left in some of the code that needed to be removed, so try this varsion:

Sub testnet()


Dim sh As Worksheet
Dim Rng As Range
Dim Cell As Range
Dim URL As String
URL="[B]URL;http://"&[COLOR=#666666][FONT='inherit']range("A1").Text&"[/FONT][/COLOR]/cgi-bin/counts.cgi"[/B]

[COLOR=#ff0000]With ActiveSheet.QueryTables.Add(Connection:=URL, Destination:=Range("$F$10"))[/COLOR]
        .Name = "counts"
        .FieldNames = True
        .RowNumbers = False
        .FilladjaccmtFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .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
    Range("A2").Select
End Sub

 

[sinison]

[/URL][/IMG]

First first first; thnx for helping me, this is much appreciated.

Secondly; I used the code as you gave me, this results in a syntax error. What's wrong now? I tried the keyboard shortcut with CTRL+SHIFT+F9, did not work. I've tried renaming and rewriting the code in new files, didn't work unfortunately

gr