在VBA中复制资源管理器行为：无需手动清理临时文件从Zip打开CSV

你的核心需求是复刻资源管理器打开Zip内CSV的行为：自动将文件提取到系统临时目录，打开后关闭文件时自动清理临时文件。之前的代码失败是因为Excel无法识别Shell FolderItem对象或其虚拟路径，下面提供两种可行方案：

方案一：手动提取到临时目录+绑定关闭清理逻辑 #

这个方案手动控制临时文件的创建与清理，完全匹配你的需求：

Function OpenZipCSV(zipPath As String, csvName As String) As Workbook
    Dim shellApp As Object
    Dim tempFolder As String
    Dim extractedCsvPath As String
    Dim wb As Workbook
    
    ' 创建Shell对象
    Set shellApp = CreateObject("Shell.Application")
    
    ' 创建唯一的临时目录（基于系统Temp路径）
    tempFolder = Environ("TEMP") & "\Temp1_" & Replace(Dir(zipPath), ".zip", "") & "_" & Format(Now(), "YYYYMMDDHHMMSS")
    MkDir tempFolder
    
    ' 从Zip中提取指定CSV到临时目录
    shellApp.Namespace(tempFolder).CopyHere shellApp.Namespace(zipPath).Items.Item(csvName)
    
    ' 等待文件提取完成（避免文件被占用）
    Do While Dir(tempFolder & "\" & csvName) = ""
        DoEvents
    Loop
    
    ' 打开提取后的CSV
    extractedCsvPath = tempFolder & "\" & csvName
    Set wb = Workbooks.Open(extractedCsvPath)
    
    ' 绑定BeforeClose事件，关闭时清理临时文件和目录
    Dim clsCleanup As New TempCleanup
    Set clsCleanup.TargetWorkbook = wb
    Set clsCleanup.TempPath = tempFolder
    Set wb.UserData("CleanupHandler") = clsCleanup
    
    Set OpenZipCSV = wb
    Set shellApp = Nothing
End Function

配套类模块（需新建类模块并命名为TempCleanup） #

Public WithEvents TargetWorkbook As Workbook
Public TempPath As String

Private Sub TargetWorkbook_BeforeClose(Cancel As Boolean)
    ' 关闭后删除临时目录及文件
    If Dir(TempPath, vbDirectory) <> "" Then
        Kill TempPath & "\*.*"
        RmDir TempPath
    End If
End Sub

方案二：调用系统ShellExecute让系统自动处理临时文件 #

这个方案更贴近资源管理器的原生行为，由系统自动创建临时目录并在关闭时清理：

Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _
    ByVal hwnd As LongPtr, ByVal lpOperation As String, _
    ByVal lpFile As String, ByVal lpParameters As String, _
    ByVal lpDirectory As String, ByVal nShowCmd As Long) As LongPtr

Function OpenZipCSVByShell(zipPath As String, csvName As String) As Workbook
    Dim zipItemPath As String
    Dim wb As Workbook
    
    ' 构造Zip内文件的Shell路径格式
    zipItemPath = "zip://" & Replace(zipPath, "\", "/") & "!" & csvName
    
    ' 调用系统打开该文件（和资源管理器双击行为一致）
    ShellExecute 0, "open", zipItemPath, vbNullString, vbNullString, 1
    
    ' 等待文件打开，然后匹配Workbook对象
    Do
        DoEvents
        On Error Resume Next
        Set wb = Workbooks(csvName)
        On Error GoTo 0
    Loop Until Not wb Is Nothing
    
    Set OpenZipCSVByShell = wb
End Function

为什么之前的代码失败？ #

你之前使用的oSrc.Path返回的是虚拟路径（格式类似zip::C:\test.zip\file.csv），Excel的Workbooks.Open方法不支持这种特殊路径格式，因此抛出1004错误。同时，Shell的FolderItem对象不能直接作为Workbooks.Open的参数，必须使用实际的本地文件路径。

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