通过循环变量从SharePoint导入XML的VBA代码问题排查

需求：通过循环变量修改URL，从SharePoint获取用户组XML内容，不同用户组的URL仅ID数字存在3或4位差异。

现象：手动指定URL的XmlImport语句可正常运行，但使用变量构建URL时执行失败。调试打印的变量URL和手动URL看起来一致，但无法正常执行。

原代码：

Dim ws As Worksheet
Dim new_name As String
Dim Sheet_number As Long
Dim URL_String As String

'loop through the array when I have one!

WS_name = ActiveSheet.Name
new_name = "Prog Leadership - Secure"
Sheet_number = 6587

URL_String = """URL.com/sites/FSCC/_api/Web/SiteGroups/getbyid(" & Sheet_number & ")/Users"""
Debug.Print URL_String


ActiveWorkbook.XmlImport URL:=URL_String _
    , ImportMap:=Nothing, Overwrite:=True, Destination:=Range("$A$1")

'    ActiveWorkbook.XmlImport URL:= _
        "https://URL.com/sites/FSCC/_api/Web/SiteGroups/getbyid(6587)/Users" _
        , ImportMap:=Nothing, Overwrite:=True, Destination:=Range("$A$1")

问题分析 #

导致失败的两个关键错误：

1. 多余的双引号：构建URL_String时，外层的"""会让最终字符串包含额外的双引号。实际传递给XmlImport的是带双引号的字符串（如"https://URL.com..."），但XmlImport的URL参数需要的是不带引号的纯URL字符串。
2. 缺失协议前缀：手动URL包含https://，但变量构建的URL只写了URL.com，缺少必要的https://前缀，导致地址无效。

修正后的代码 #

Dim ws As Worksheet
Dim new_name As String
Dim Sheet_number As Long
Dim URL_String As String

'loop through the array when I have one!

WS_name = ActiveSheet.Name
new_name = "Prog Leadership - Secure"
Sheet_number = 6587

' 修正：去掉多余双引号，补充https://前缀
URL_String = "https://URL.com/sites/FSCC/_api/Web/SiteGroups/getbyid(" & Sheet_number & ")/Users"
Debug.Print URL_String


ActiveWorkbook.XmlImport URL:=URL_String _
    , ImportMap:=Nothing, Overwrite:=True, Destination:=Range("$A$1")

说明：

• 现在构建的URL_String和手动写的URL完全一致，没有额外引号且包含正确的协议前缀
• 后续扩展循环数组时，只需要将Sheet_number替换为数组元素即可

内容的提问来源于stack exchange，提问作者Miles