[算表] EXCEL VBA 將網址修改成有變數

作者: ahlfsri (40+大叔)   2020-01-06 12:52:14
軟體: EXCEL VBA
版本: office 365
請問我要將https://goodinfo.tw/StockInfo/EquityDistributionClassHis.asp?STOCK_ID=3049中的3049改為sheet(Company)中的儲存格B2,
試了""https://goodinfo.tw/StockInfo/EquityDistributionClassHis.asp?STOCK_ID="" &
Sheets("Company").Range("B2").Value,VBA說語法錯誤,請問版上高手是否能幫忙解惑?
Application.CutCopyMode = False
ActiveWorkbook.Queries.Add Name:="Table 3", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " 來源 =
Web.Page(Web.Contents(""https://goodinfo.tw/StockInfo/EquityDistributionClassHis.asp?STOCK_ID=3049"")),"
& Chr(13) & "" & Chr(10) & " Data3 = 來源{3}[Data]," & Chr(13) & "" &
Chr(10) & " 已變更類型 = Table.TransformColumnTypes(Data3,{{""週別"", type
text}, {""統計 日期"", type date}, {""當月股價 收盤"", type number}, {""當月股
價 漲跌 (元)"", type number}, {""當月股價 漲跌 (%)"", type number}, {""各持股
等級股東之持股比例(%) 10張 以下"", type number" & _
"}, {""各持股等級股東之持股比例(%) 10至 50張"", type number}, {""各持
股等級股東之持股比例(%) 50至 100張"", type number}, {""各持股等級股東之持股比
例(%) 100至 200張"", type number}, {""各持股等級股東之持股比例(%) 200至 400張
"", type number}, {""各持股等級股東之持股比例(%) 400至 800張"", type number},
{""各持股等級股東之持股比例(%) 800至 1千張"", type number}, {""各持股等級股東
之持股比例(%) 超過 1千張"", type number}})" & Chr(13) & "" & Chr(10) & "in" &
Chr(13) & "" & Chr(10) & " 已變更類型" & _
""
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data
Source=$Workbook$;Location=""Table 3"";Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Table 3]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_3"
.Refresh BackgroundQuery:=False
End With
End Sub
作者: soyoso (我是耀宗)   2020-01-06 12:53:00
"https//....STOCK_ID" & sheets(..).range("b2").value字串連接上,以"字串" & 儲存格值 的方式,改左右各一個雙引號如用於內文巨集formula的字串的話https://i.imgur.com/ocM9egW.jpg 類似這樣
作者: ahlfsri (40+大叔)   2020-01-06 13:46:00
感謝soyoso,終於可用了
作者: soyoso (我是耀宗)   2020-01-07 07:35:00
更新方面,activeworkbook.queries(...).formula如出現...範圍重疊的錯誤訊息的話先range.clear再listobjects.add
作者: ahlfsri (40+大叔)   2020-01-07 08:36:00
更新方面,activeworkbook.queries(...).formula, 對不起,我太菜了,有點試不出來,這一句要怎麼用呢?
作者: soyoso (我是耀宗)   2020-01-07 08:39:00
...formula = 要調整的字串字串為let起至最後的已變更類型

Links booklink

Contact Us: admin [ a t ] ucptt.com