Passing input box answer to a url

[LGMan]

Hi, i've been running a web query from excel which dumps down data to excel, worked fine while the session_id was a constant, however it now changes each day.
I wanted to build an input box to allow me to paste in the new session_id into the html string.
however I've not done much with variables and dont know what the correct format is and how to add the session-id value into the code.
An example of the session_id is shown below in red.

Thanks

Dim Session_Id As String


Session_Id = InputBox("what is the Session Id (from Matt)")


With ActiveSheet.QueryTables.Add(Connection:="URL;[URL unfurl="true"]http://myreporting.me.com/data/Summary.asp?WDT=LR&[/URL][COLOR=red]1y1y2782xqkUgatdIPmZ74V9[/color red]&zip=exe11&CB5=&CB4=&CB3=exe1&CB2=exe1&e=.csv", Destination:=Range("c10"))
      End With

 

[SkipVought]

Hi,

Pick a cell. Lets say Sheet1 A1. Paste your URL there...

With ActiveSheet.QueryTables.Add(Connection:="URL;[b]" & Sheets("Sheet1").[A1][/b], Destination:=Range("c10"))
      End With

CAVEAT: Each time you executr this code, you ADD a querytable object to your sheet.

Better to do it ONCE, manually and then in code...

With ActiveSheet.QueryTables(1)
  .Connection:="URL;[b]" & Sheets("Sheet1").[A1][/b]
  .Refresh False
End With

along with whatever properties you may need.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LGMan]

sorry skip, thanks for replying and I like your plan, however I'm getting a compile error around the .connection:=
The error box says "Expected: expression"

 

[SkipVought]

sorry for the typo...

  .Connection = "URL;" & Sheets("Sheet1").[A1]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LGMan]

thanks Skip, that's great