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

看板Office作者ahlfsri (40＋大叔)時間6年前 (2020/01/06 12:52)推噓0(0推 0噓 16→)

留言16則, 2人參與討論串1/1

軟體: 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 -- ※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 220.135.13.55 (臺灣) ※ 文章網址: https://www.ptt.cc/bbs/Office/M.1578286337.A.AF3.html