Excel中VBA加载Power Query时日期格式异常显示为数值序列号问题咨询

问题根源 #

手动执行「关闭并加载」操作时，Excel会自动继承Power Query返回的列类型、格式元数据；而VBA通过OLEDB接口创建查询表时，默认关闭了列元数据保留开关，导致日期列直接读取底层存储的数值序列号。

第一步：调整查询表核心参数 #

原代码中PreserveColumnInfo = False是核心问题点，该参数控制是否保留Power Query返回的列类型信息，需改为启用状态，同时统一异步刷新配置避免加载不同步：
修改后的完整代码参考：

With Data.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & qry.Name _
        , Destination:=Data.Range("$B$1")).QueryTable
        .CommandType = xlCmdDefault
        .CommandText = Array("SELECT * FROM [" & qry.Name & "]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        ' 统一关闭异步刷新，避免加载不同步
        .BackgroundQuery = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        ' 核心修改：启用*列元数据保留*，自动继承Power Query的列类型
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
        
        ' 兜底兼容：刷新后强制指定日期列格式，需替换为实际日期列的范围
        ' 示例：日期列在C列，从第2行开始为数据行
        Data.Range("C2:C" & Data.Cells(Data.Rows.Count, "C").End(xlUp).Row).NumberFormatLocal = "mm/dd/yyyy"
End With

第二步：Power Query端类型加固（兼容老旧Excel版本） #

如果上述参数修改后仍存在版本兼容问题，可在Power Query编辑器内强化日期类型约束：

• 选中对应日期列
• 点击「转换」选项卡→「数据类型」→ 选择「日期」，确认后保存查询再执行VBA加载即可。

注意事项 #

• 不要将.RefreshStyle设置为xlOverwriteCells，否则会直接覆盖Power Query返回的格式元数据
• 若需要显示时分信息，可将格式字符串调整为"mm/dd/yyyy hh:mm"匹配需求

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