Excel宏网页数据采集故障：Url变量无法正常传递问题

你的代码核心问题是把VBA变量直接写在了Power Query的字符串常量里，导致程序把Url、Mese & Anno当成固定字符串，而非读取变量的实际值。以下是具体修复步骤和完整代码：

关键错误点及修复 #

1. Power Query公式中的Url引用错误
   原代码里的Web.Contents(""Url"")是把字符串"Url"传给Web.Contents，而非你定义的Url变量。需要把变量拼接到公式字符串中，改成Web.Contents(""" & Url & """)。

2. 查询名称的动态引用错误
   创建QueryTable时，Location=Mese & Anno和SELECT * FROM [Mese & Anno]同样是把Mese & Anno当成字符串，需替换为实际的查询名称（即Mese & Anno的计算结果）。

3. 未声明Mese变量
   原代码里的Mese未用Dim声明，属于隐式变量，建议显式声明避免潜在问题。

修复后的完整代码 #

Sub Recupera_dati()
    Dim dtToday As Date
    Dim Anno As String
    Dim Mese As String ' 显式声明变量
    Dim Url As String
    Dim queryName As String ' 定义查询名称变量，方便复用

    Anno = "2023"
    Mese = "Novembre"
    queryName = Mese & Anno ' 提前计算查询名称

    Url = "https://www.ilmeteo.it/portale/archivio-meteo/Momo/" & Anno & "/" & Mese

    ' 创建Power Query查询，正确拼接Url变量
    ActiveWorkbook.Queries.Add Name:=queryName, Formula:= _
            "let" & Chr(13) & "" & Chr(10) & _
            "    Origine = Web.Page(Web.Contents(""" & Url & """))," & Chr(13) & "" & Chr(10) & _
            "    Data2 = Origine{2}[Data]," & Chr(13) & "" & Chr(10) & _
            "    #""Modificato tipo"" = Table.TransformColumnTypes(Data2,{{""Giorno"", Int64.Type}, {""T Media"", type text}, {""T min"", type text}, {""T max"", type text}, {""Precip.", type text}, {""Umidità"", type text}, {""Vento Max"", type text}, {""Raffica"", type text}, {""Fenomeni"", type text}, {""Info"", type text}})" & Chr(13) & "" & Chr(10) & _
            "in" & Chr(13) & "" & Chr(10) & _
            "    #""Modificato tipo"""

    ' 添加工作表并加载查询数据，正确引用查询名称
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
            "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & queryName & ";Extended Properties="""""" _
            , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [" & queryName & "]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = Mese
        .Refresh BackgroundQuery:=False
    End With
End Sub

额外说明 #

• 用queryName变量统一存储查询名称，避免重复拼接出错；
• VBA中字符串拼接用&，若要在字符串里加双引号，需用两个双引号""表示一个实际的双引号；
• 修复后程序会正确读取Url变量生成的链接，动态创建对应年份月份的天气数据查询。

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