批量合并EXCEL数据时出现“外部表不是预期格式”或“文件包含损坏数据”错误的解决方法

本文参考资料如下：

1、批量合并Excel数据时“外部表不是预期格式”或“文件包含损坏数据”的两种情况-腾讯云开发者社区-腾讯云

2、1.2-在哪里写VBA — Excel-VBA

       用EXCEL的Power Query合并多个Excel表格的数据时，出现“DataFormat.Error：外部表不是预期格式”或“DataFormat.Error：文件包含损坏的数据”的错误提示。这两种错误，主要是由以下两种情况导致的：

1、要合并汇总的数据是从某些专业平台或系统导出的xls（2003版以前）格式Excel文件；

2、文件夹中包含了一些临时的缓存文件。

解决方案：

1、针对xls格式的excel文件，将其转换成xlsx格式，可使用VBA代码，具体操作如下：

（1）在所需合并文件夹上一层新建一个excel表格，打开表格，点击“文件”-“选项”-“自定义功能区”，勾选“开发工具”，确定，退出。

（2）在菜单栏中选择“开发工具”-“Visual Basic”，打开VBA代码编辑窗口，点击菜单栏的“视图”-“代码窗口”，在代码窗口输入以下代码（不需要修改），然后运行，会弹出窗口，选择需要转换格式的excel文件所在的文件夹路径，一段时间后就会转换完成。

Sub save_xls_to_xlsx()
    '作者：大海
    '日期：2019-11-9
    '公众号：Excel到PowerBI
    
    Dim folder As FileDialog
    Dim f, fdi As FileDialogSelectedItems
    Dim i As Integer
    Dim file_count As Integer
    Dim xls_file As String
    Dim xlsx_file As String
    Dim wb As Workbook
    
    Set folder = Application.FileDialog(msoFileDialogFolderPicker)
    folder.Show
    
    Set fdi = folder.SelectedItems
    
    If fdi.Count = 0 Then
        MsgBox "未选择任何文件夹。"
        Exit Sub
    End If
   
    For Each f In fdi
        xls_file = Dir(f & "\*.xls")
        file_count = 0
        Do
            If Right(xls_file, 4) = ".xls" Then
                Set wb = Workbooks.Open(f & "\" & xls_file)
                Application.ScreenUpdating = False
                xlsx_file = f & "\" & xls_file & "x"
                ActiveWorkbook.SaveAs Filename:=xlsx_file, FileFormat:=xlWorkbookDefault, CreateBackup:=False
                wb.Close savechanges:=False
                Kill f & "\" & xls_file '若不想删除原文件，可注释掉本行
                file_count = file_count + 1
                Application.ScreenUpdating = True
            End If
            xls_file = Dir
        Loop Until xls_file = ""
    Next
    MsgBox "该文件夹下的xls文件（共" & CStr(file_count) & "个）已全部转换为xlsx文件。谢谢您对大海的支持！"
    
End Sub

第二种情况参考第一个链接：批量合并Excel数据时“外部表不是预期格式”或“文件包含损坏数据”的两种情况-腾讯云开发者社区-腾讯云