trying to substitute a string variable for web address

medic5678

Board Regular
Joined
Nov 13, 2018
Messages
60
Here's the code that works fine. However, I want to change the internet address and use a string variable. I cannot find a way to substitute.


ActiveWorkbook.Queries.Add Name:="Table 0", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Web.Page(Web.Contents(""Inmate Population Information Detail""))," & 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"""
Sheets.Add After:=ActiveSheet
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 0"";Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Table 0]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = False
.ListObject.DisplayName = "Table_0"
.Refresh BackgroundQuery:=False
End With
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
ActiveSheet.Name = "querysheet"
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

medic5678

Board Regular
Joined
Nov 13, 2018
Messages
60
The problem seems to stem from the double quotes. What I'd like to do is build a string based on a list to change this web address to pull the results from different people. No matter what I do, I can't seem to make it work where I can substitute a new web address for the ""Inmate Population Detail"".
 

Watch MrExcel Video

Forum statistics

Threads
1,118,764
Messages
5,574,103
Members
412,569
Latest member
kiteifitswindy
Top