Excel 365执行VBA导入XML时出现内存不足错误的技术问询

我们通过工作簿导入机器人输出的XML文件，该操作在Excel 2016中运行正常，但在Excel 365里会触发内存不足错误。

临时解决方法 #

• 手动在Excel中打开目标XML文件，将其另存为XLSX格式
• 修改VBA代码中的Workbooks.OpenXML方法为Workbooks.Open

原VBA代码 #

Sub Get_Samples()
Dim MyPath, MyName, MyFile
MyPath = Application.ActiveWorkbook.Path
ChDir MyPath
Dim LastRow As Long
Dim LastCol As Integer

Application.DisplayAlerts = False
Application.ScreenUpdating = False

' Unhide Sheets
Sheets("Qiagen").Visible = True
Sheets("Barcodes").Visible = True

MsgBox "Select location of Qiagen Export File", vbOKOnly, "Qiagen"
  
' Open Qiagen Export List with 1D barcodes
MyName = ActiveWorkbook.Name
Application.Dialogs(xlDialogOpen).Show
MyFile = ActiveWorkbook.Name
Workbooks.OpenXML Filename:=MyFile, LoadOption:= _
    xlXmlLoadImportToList

修改后的适配代码 #

Sub Get_Samples()
Dim MyPath, MyName, MyFile
MyPath = Application.ActiveWorkbook.Path
ChDir MyPath
Dim LastRow As Long
Dim LastCol As Integer

Application.DisplayAlerts = False
Application.ScreenUpdating = False

' Unhide Sheets
Sheets("Qiagen").Visible = True
Sheets("Barcodes").Visible = True

MsgBox "Select location of Qiagen Export File", vbOKOnly, "Qiagen"
  
' Open Qiagen Export List with 1D barcodes
MyName = ActiveWorkbook.Name
Application.Dialogs(xlDialogOpen).Show
MyFile = ActiveWorkbook.Name
' 替换OpenXML为Open，避免Excel 365内存不足错误
Workbooks.Open Filename:=MyFile

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