Macro Script that Extract URL from Cell and Run

[ybh]

Hello everyone ,
I have written a macro script to extract a table from a specific URL, as attached here.
Both are running fine, what I would like to improve:

• Is there a way I can replace the URL in the script, to point to a specific cell for the macro to execute?
• This way I do not have to keep on editing the macro script, instead I can just change the URL from Cell B1 and B2 (as in my example)

Thanks.

 

[p45cal]

Something like:

Sub blah()
For Each cll In Sheets("Sheet1").Range("B1:B2").Cells
  myUrl = cll.Value
  ActiveWorkbook.Queries.Add Name:="Table 0", Formula:= _
                             "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(""" & myUrl & """))," & Chr(13) & "" & Chr(10) & "    Data0 = Source{0}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Data0,{{""Column1"", type text}, {""Column2"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""

'the rest of your code here'

    ActiveWorkbook.Queries("Table 0").Delete
Next cll
End Sub

You don't really need the myUrl variable, you can put the cll.value in directly:

Sub blah()
For Each cll In Sheets("Sheet1").Range("B1:B2").Cells
  ActiveWorkbook.Queries.Add Name:="Table 0", Formula:= _
                             "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(""" & cll.Value & """))," & Chr(13) & "" & Chr(10) & "    Data0 = Source{0}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Data0,{{""Column1"", type text}, {""Column2"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""

'the rest of your code here.

ActiveWorkbook.Queries("Table 0").Delete
Next cll
End Sub

 

[ybh]

Hello p45cal,
You've made it! The script solved my problem and makes my macro so much tidy and efficient now.
Awesome, great appreciation!
Have a great day and hope to learn more from you again!