从其他Excel工作簿提取长数字时格式保留问题求助

你遇到的这个坑我也碰过——Power Query默认会自动识别数据类型，源文件里存为文本的长数字，很容易被它当成数值加载，事后再改单元格格式只是改了显示方式，底层数据还是浮点数，所以才会出现科学计数的情况。

要彻底解决这个问题，得在Power Query加载数据的环节就强制把目标列设为文本类型，而不是事后补救。这里给你修改后的宏代码，关键是完善了Power Query的公式部分：

Sub Macro1()
    Dim queryFormula As String
    ' 构建包含类型转换的Power Query公式
    queryFormula = "let" & Chr(10) & _
        "    Source = Excel.Workbook(File.Contents(""C:\Book1.xlsx""), null, true)," & Chr(10) & _
        "    Sheet1_Sheet = Source{[Item=""Sheet1"",Kind=""Sheet""]}[Data]," & Chr(10) & _
        "    PromotedHeaders = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true])," & Chr(10) & _
        "    ChangedTypeToText = Table.TransformColumnTypes(PromotedHeaders, {{""你的长数字列名"", type text}})" & Chr(10) & _
        "in" & Chr(10) & _
        "    ChangedTypeToText"
    
    ' 添加查询
    ActiveWorkbook.Queries.Add Name:="Sheet1", Formula:=queryFormula
    
    ' 导入数据到新工作表
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Sheet1;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Sheet1]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

关键修改说明： #

1. 补全Power Query步骤：原来的公式只定义了Source，现在我们添加了三个必要步骤：

   • 定位到源文件里的具体工作表（Sheet1_Sheet）
   • 把第一行提升为表头（PromotedHeaders）
   • 核心操作：用Table.TransformColumnTypes强制将目标列的类型设置为type text，你需要把代码里的"你的长数字列名"替换成实际的列名（比如如果列名叫"用户ID"，就改成{"用户ID", type text}）。

2. 批量处理多列：如果你有多个长数字列需要处理，可以在类型转换里添加多个条目，比如：

   "ChangedTypeToText = Table.TransformColumnTypes(PromotedHeaders, {{""ID"", type text}, {""银行卡号"", type text}})"
   

3. 全部列设为文本：如果想把所有列都强制设为文本（避免其他列也出现类似问题），可以用下面的代码替换类型转换步骤：

   "ChangedTypeToText = Table.TransformColumnTypes(PromotedHeaders, List.Transform(Table.ColumnNames(PromotedHeaders), each {_, type text}))"
   

这样修改后，Power Query在加载数据时就会直接把指定列识别为文本类型，导入到工作表后自然就是纯文本格式，不会再出现科学计数的情况，也不需要事后再调整单元格格式。

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