gpt4 book ai didi

vba - 第二次打开文件时 Excel 崩溃

转载 作者:行者123 更新时间:2023-12-03 02:32:21 26 4
gpt4 key购买 nike

更新 2 (3/21/17)

我发现,只有当从模块中导入的一张(或全部)工作表被删除时,才会在单击“提交”按钮后尝试打开 Excel 图书一次。 (该过程会在重新提交之前删除旧工作表,以清除工作簿以重新开始)。对于手动测试,我点击提交按钮,删除任何导入的工作表,然后尝试打开任何 excel 文件,但它崩溃了。我还确保删除工作表时删除所有已失效的命名范围。我还在一个仅导入空白纸的文件上对此进行了测试。然后我删除它并能够正常打开工作簿。我想避免创建我的模块(因为它是一种拖累)。

原始问题

我有一个 Excel 工作簿工具,可以打开其他 Excel 工作簿,并在处理一些信息后从这些工作簿导入工作表。

总共有 5 个模块工作簿。在较高层次上,这些工作簿是相同的 - 工作表结构、通用代码结构等。存在不同的公式,并且某些命名范围不同等。

在主工具中,可以重新运行从不同工作簿中提取信息的代码。它本质上是重置原始工作簿,然后再次运行代码。这是在不关闭原始工作簿的情况下完成的。 (用户可以刷新 Web 服务数据并重新运行该工具)。

我面临的问题是,当我重新运行两个模块工作簿的进程时,Excel 在重新运行期间代码尝试打开模块工作簿时崩溃。其他 3 个模块的工作簿效果很好。我可以运行、重新运行、重新运行...另外 2 个每次都会崩溃。

我对这些文件进行了大量研究,以了解为什么会发生这种情况,但还没有找到原因。进程运行后主工作簿中没有留下任何链接,没有数据连接,没有坏链接等。

此外,有趣的是,我存储在 UAT 环境文件夹中的文件始终工作正常。生产文件夹中的文件失败。我什至直接将文件从UAT环境文件夹复制到生产环境文件夹,但仍然失败。我还排除了文件夹级别的权限和安全性。

我也可以在第一次提交代码后手动打开文件。

我意识到这可能有点超出了SO的范围,而且有点含糊,但希望有人可能有类似的经历并能提供一些线索。

更新

相关代码如下。根据 @Ralph 的评论,我通过添加行 Set wbLOB = Nothing 强制删除内存,但不幸的是,问题仍然发生。

Function LoadLOB(sLOB As String, sXMLFile As String) As Boolean

Dim sLOBFile As String
sLOBFile = wsReference.Range("ModuleFolder").Value2 & sLOB & "\" & sLOB & ".xlsb"

Dim wbLOB As Workbook
Set wbLOB = Workbooks.Open(sLOBFile) '--> 2nd run crashes on this line.

If TieXMLToExcel(wbLOB, sXMLFile, sLOB) Then

MapXMLFieldsToExcelCells wbLOB, sLOB

Select Case sLOB

Case Is = "Property"
SortTableByAscendingColumn wbLOB, "xml" & sLOB, "tCommonLocationProperty", "Location_ID"
SortTableByAscendingColumn wbLOB, "xml" & sLOB, "tLocationByCoverageTypeProperty", "Location_ID"
Case Is = "GeneralLiability": SortTableByAscendingColumn wbLOB, "xml" & sLOB, "tClassCodesByLocationGeneralLiability", "Location_ID"
Case Is = "CommercialAuto": SortTableByAscendingColumn wbLOB, "xml" & sLOB, "tVehicleSummaryCommercialAuto", "AuVehicleNo"
Case Is = "Crime": SortTableByAscendingColumn wbLOB, "xml" & sLOB, "tCommonLocationCrime", "Location_ID"

End Select

Application.Run wbLOB.Name & "!PrepareSheetForMasterFile", ThisWorkbook

wbLOB.Close False

LoadLOB = True

End If

Set wbLOB = Nothing

End Function

最佳答案

我怀疑这就是答案,但我认为这是一个更好的论坛,可以用来交换我对此问题的想法。我所做的是抓取一些 Windows API 来检查该文件是否已打开,然后再尝试再次打开它。我还添加了一个关闭文件的方法,并使 SaveChanges 参数更加明确。我还在其中添加了一些 DoEvents,以防有事情等待完成。

希望这是其他想法的起点。我希望其中一些有所帮助。

'Determine whether a file is already open or not
#If VBA7 And Win64 Then
Private Declare PtrSafe Function lOpen Lib "kernel32" Alias "_lopen" (ByVal lpPathName As String, ByVal iReadWrite As Long) As Long
Private Declare PtrSafe Function lClose Lib "kernel32" Alias "_lclose" (ByVal hFile As Long) As Long
#Else
Private Declare Function lOpen Lib "kernel32" Alias "_lopen" (ByVal lpPathName As String, ByVal iReadWrite As Long) As Long
Private Declare Function lClose Lib "kernel32" Alias "_lclose" (ByVal hFile As Long) As Long
#End If

Function LoadLOB(ByVal sLOB As String, _
ByVal sXMLFile As String) As Boolean

Dim sLOBFile As String
Dim wbLOB As Workbook
sLOBFile = wsReference.Range("ModuleFolder").Value2 & sLOB & "\" & sLOB & ".xlsb"

'Make sure the file is closed before processing
If Not isFileOpen(sLOBFile) Then
Set wbLOB = Workbooks.Open(sLOBFile, 0, False)
Else
'Close it if it is open
closeWB sLOBFile
Set wbLOB = Workbooks.Open(sLOBFile, 0, False)
End If

If TieXMLToExcel(wbLOB, sXMLFile, sLOB) Then

MapXMLFieldsToExcelCells wbLOB, sLOB

Select Case sLOB

Case Is = "Property"
SortTableByAscendingColumn wbLOB, "xml" & sLOB, "tCommonLocationProperty", "Location_ID"
SortTableByAscendingColumn wbLOB, "xml" & sLOB, "tLocationByCoverageTypeProperty", "Location_ID"
Case Is = "GeneralLiability": SortTableByAscendingColumn wbLOB, "xml" & sLOB, "tClassCodesByLocationGeneralLiability", "Location_ID"
Case Is = "CommercialAuto": SortTableByAscendingColumn wbLOB, "xml" & sLOB, "tVehicleSummaryCommercialAuto", "AuVehicleNo"
Case Is = "Crime": SortTableByAscendingColumn wbLOB, "xml" & sLOB, "tCommonLocationCrime", "Location_ID"

End Select

Application.Run wbLOB.Name & "!PrepareSheetForMasterFile", ThisWorkbook
DoEvents
wbLOB.Close SaveChanges:=False
LoadLOB = True
End If

Set wbLOB = Nothing
End Function

Sub closeWB(ByVal FilePath As String)
Dim wb As Workbook

For Each wb In Application.Workbooks
If wb.FullName = FilePath Then
wb.Close SaveChanges:=False
Set wb = Nothing
DoEvents
Exit For
End If
Next

End Sub

Function isFileOpen(ByVal FileName As String) As Boolean
Dim FileNumb As Long: FileNumb = -1
Dim lastErr As Long

FileNumb = lOpen(FileName, &H10)

'Determine if we can open the file
If FileNumb = -1 Then
lastErr = Err.LastDllError
Else
lClose (FileNumb)
End If

' Check if there is a sharing violation and report back status
isFileOpen = (FileNumb = -1) And (lastErr = 32)
End Function

关于vba - 第二次打开文件时 Excel 崩溃,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/42790521/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com