如何根据 excel 2013 VBA 中的单元格值更改 url 值

Question

我对编程和编码世界相当陌生，在我的 VBA 编码中遇到了一个障碍。我需要更改 URL 字符串中的值，该值是 Web 查询的一部分，并且无法更改该值。这是代码：

Dim FC_Name As String
FC_Name = Worksheets("Home").Range("H4")

Sheets("PickWorkforce").Visible = True
Sheets("PickWorkforce").Select
Cells.Select
Selection.ClearContents
    urlStr = "https://picking-nexus.iad.amazon.com/FC_Name/Workforce/data"
With Sheets("PickWorkforce").QueryTables.Add(Connection:= _
    "URL;" & urlStr, Destination:=Sheets("PickWorkforce").Range("$A$1"))
    .Name = "employeeRoster?&warehouseId=FC_Name"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlSpecifiedTables
    .WebFormatting = xlWebFormattingNone
    .WebTables = "1,2,3,4,5,6,7,8"
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
End With
Range("L1") = "Employee Id"
Range("L2").Select
ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-11], Roster!R2C2:R10000C13, 12,FALSE),"""") "
Range("L2").Select
Selection.AutoFill Destination:=Range("L2:L1000"), Type:=xlFillDefault
Sheets("PickWorkforce").Visible = True
End Sub

"FC_Name"是我尝试在 URL 中更改的部分，因此它会从我在工作簿主页的单元格中键入的任何仓库 ID 读取。

谁能帮我解决这个问题？

Answer 1

我建议从代码中删除任何Select方法，并简单地引用范围。

当引用变量时，它需要在引号之外，并使用与号 （ & 连接）：

Dim FC_Name As String
FC_Name = Worksheets("Home").Range("H4")
Sheets("PickWorkforce").Visible = True
Sheets("PickWorkforce").Cells.ClearContents
    urlStr = "https://picking-nexus.iad.amazon.com/" & FC_Name & "/Workforce/data"
With Sheets("PickWorkforce").QueryTables.Add(Connection:= _
    "URL;" & urlStr, Destination:=Sheets("PickWorkforce").Range("$A$1"))
    .Name = "employeeRoster?&warehouseId=" & FC_Name
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlSpecifiedTables
    .WebFormatting = xlWebFormattingNone
    .WebTables = "1,2,3,4,5,6,7,8"
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
End With
Range("L1") = "Employee Id"
Range("L2").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-11], Roster!R2C2:R10000C13, 12,FALSE),"""") "
Range("L2").AutoFill Destination:=Range("L2:L1000"), Type:=xlFillDefault
Sheets("PickWorkforce").Visible = True
End Sub