Import Gsheet Page into Excel

Question

I am trying to import a Gsheet page into an Excel by asking the URL of the Gsheet into a message box.

I achieve to import it when I pout the URL directly into the VBA code but not when I copy the URL into the MsgBox, I get an Error 1004 on the ".Refresh" saying that the address isn't good, when I delete this line the code doesn't work.

Here is the code:

Sub I_G()

    Sheets("Feuil1").Select
    resultats = InputBox("Copy the URL of the data collection", "URL")

    If ActiveSheet.FilterMode = True Then ActiveSheet.ShowAllData

    With ActiveSheet.QueryTables.Add(Connection:= _
      "URL; resultats", Destination:=Range("$A$1"))
         .WebFormatting = xlWebFormattingNone
         .BackgroundQuery = True
         .Refresh

    End With

End Sub

Answer 1

You're not passing the entered URL to the Querytables.Add-commmand, you are passing the string "URL; resultats". VBA doesn'tlook inside a string to check if it contains a variable name.

var myVar as string
myVar = "ABC"
Debug.print "URL; myVar"      ' <-- Will print "URL; myVar" 
Debug.print "URL; " & myVar   ' <-- Will print "URL; ABC"

So, you have to modify the line to

With ActiveSheet.QueryTables.Add(Connection:= _
  "URL; " & resultats, Destination:=Range("$A$1"))
    ...
End With

This way, the content of your variable is concatenated with the Prefix URL;