vba 使用单元格中的值创建动态 url
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18577966/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create dynamic url using value from cell
提问by user2740589
I am running a web query for an Excel sheet and have been able to pull data correctly however I want to reference a cell that can be changed in the worksheet.
我正在运行 Excel 工作表的 Web 查询，并且能够正确提取数据，但是我想引用可以在工作表中更改的单元格。
Ex. I want to make the F reference a cell so I can easily change the request without changing the code. I've been trying to use a range function.
前任。我想让 F 引用成为一个单元格，这样我就可以在不更改代码的情况下轻松更改请求。我一直在尝试使用范围函数。
Sub URL_Get_Query()
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/is?s=F+Income+Statement&annual", _
Destination:=Range("a1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub
回答by Jon Crowell
This will take whatever symbol you have in cell A1 of Sheet1 and use it in the query. The results are written to cell A1 on Sheet2. You can modify this to use different ranges and write to different sheets if you need to.
这将采用您在 Sheet1 的单元格 A1 中的任何符号并在查询中使用它。结果将写入 Sheet2 上的单元格 A1。如果需要，您可以修改它以使用不同的范围并写入不同的工作表。
Sub UseDynamicURL()
Dim wb As Workbook
Dim src As Worksheet
Dim tgt As Worksheet
Set wb = ThisWorkbook
Set src = wb.Sheets("Sheet1")
Set tgt = wb.Sheets("Sheet2")
Dim url As String
Dim symbol As String
symbol = src.Range("A1")
url = "URL;http://finance.yahoo.com/q/is?s="
url = url & symbol & "+Income+Statement&annual"
With tgt.QueryTables.Add(Connection:= _
url, _
Destination:=tgt.Range("A1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub
