VBA读取Zip文件 [英] VBA Read a Zip File

问题描述

我已经看到一些代码来做到这一点，但是对我来说这是不行的。我正在运行Windows 7和excel 2010（从Office 2010）。我的代码：

  Public Sub GetZipContents（）
 Dim oApp As Shell32.Shell 
 Set oApp = New Shell32.Shell 
 Dim strFile As String 
 Dim xFname 
 Dim xRow As Long 
 Dim newRow As Long 
 Dim rNew As Range 
 Dim fileNameInZip 
 Dim oFolder As Variant 
 Dim i As Integer 
i = 1 
 xRow = 0 
设置fd = Application.FileDialog（msoFileDialogFilePicker）
 fd.Title =选择zip从
 fd.Filters.Clear 
 fd.Filters.AddZip Files，* .zip
 fd.FilterIndex = 1 
获取文件名如果fd .Show = -1然后
 strFile = fd.SelectedItems（1）
 oFolder = oApp.Namespace（strFile）.Items 
 Range（A& i）.Value = strFile 
i = i + 1 
对于每个fileNameInZip在oFolder 
范围（A& i）.Value = fileNameInZip 
i = i + 1 
下一个
设置oApp = Nothing 
结束如果
 
 End Sub 
  

我还使用fileNameInZip作为变体，但输出是一样的。不管我选择的zip文件，我的输出（附带的文本版本，屏幕截图都比较好，但是我无法附加图像，因为这是我的第一篇文章...第一行是zip文件的名称，接下来是来自命名空间调用的项目）始终相同。我很失落，因为我看到的每一个网站都有类似的答案。任何想法发生了什么（文件一般是pdf，不是& Open等）？

C：\Users\PGibson\Downloads\ CW985786-T-00136.zip

& Open

Cu& t

&复制

&删除

P& roperties

解决方案

在标记行上缺少设置

  Public Sub GetZipContents（）
 Dim oApp As Shell32.Shell 
设置oApp =新建Shell32.Shell 
 Dim strFile As String 
 Dim xFname 
 Dim xRow As Long 
 Dim newRow As Long 
 Dim rNew As Range 
 Dim fileNameInZip，fd 
 Dim oFolder As Variant 
 Dim i As Integer 
 
i = 1 
 xRow = 0 
设置fd = Application.FileDialog（msoFileDialogFilePicker）
 fd.Title =选择要获取的zip文件名从
 fd.Filters.Clear 
 fd.Filters.AddZip Files，* .zi p
 fd.FilterIndex = 1 
如果fd.Show = -1然后
 strFile = fd.SelectedItems（1）
设置oFolder = oApp.Namespace（strFile）.Items <组！ 
 Range（A& i）.Value = strFile 
i = i + 1 
对于每个fileNameInZip在oFolder 
范围（A& i）.Value = fileNameInZip 
i = i + 1 
下一个
设置oApp = Nothing 
如果
 
 End Sub 
  / pre> 
I have seen some code to do this, but for me it is not working.  I am running Windows 7, and excel 2010 (from office 2010).  My code:
Public Sub GetZipContents()
    Dim oApp As Shell32.Shell
    Set oApp = New Shell32.Shell
    Dim strFile As String
    Dim xFname
    Dim xRow As Long
    Dim newRow As Long
    Dim rNew As Range
    Dim fileNameInZip
    Dim oFolder As Variant
    Dim i As Integer
    i = 1
    xRow = 0
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    fd.Title = "Select the zip to get file names from"
    fd.Filters.Clear
    fd.Filters.Add "Zip Files", "*.zip"
    fd.FilterIndex = 1
    If fd.Show = -1 Then
        strFile = fd.SelectedItems(1)
        oFolder = oApp.Namespace(strFile).Items
        Range("A" & i).Value = strFile
        i = i + 1
        For Each fileNameInZip In oFolder
            Range("A" & i).Value = fileNameInZip
            i = i + 1
        Next
        Set oApp = Nothing
    End If

End Sub
I have also used fileNameInZip as a Variant, but the output is the same.  Regardless of the zip file I chose, my output  (text version attached, the screen shot is better but I can't attach images as this is my first post ... the first line is the name of the zip file, the next are the Items from the Namespace call) is always the same.  I am at a loss because every site I have seen has similar code as the answer.  Any ideas what is going on (the files within are generally pdfs, not &Open etc.)?

C:\Users\PGibson\Downloads\CW985786-T-00136.zip

&Open

Cu&t

&Copy

&Delete

P&roperties
解决方案
Missing Set on the marked line...
Public Sub GetZipContents()
    Dim oApp As Shell32.Shell
    Set oApp = New Shell32.Shell
    Dim strFile As String
    Dim xFname
    Dim xRow As Long
    Dim newRow As Long
    Dim rNew As Range
    Dim fileNameInZip, fd
    Dim oFolder As Variant
    Dim i As Integer

    i = 1
    xRow = 0
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    fd.Title = "Select the zip to get file names from"
    fd.Filters.Clear
    fd.Filters.Add "Zip Files", "*.zip"
    fd.FilterIndex = 1
    If fd.Show = -1 Then
        strFile = fd.SelectedItems(1)
        Set oFolder = oApp.Namespace(strFile).Items '< Set!
        Range("A" & i).Value = strFile
        i = i + 1
        For Each fileNameInZip In oFolder
            Range("A" & i).Value = fileNameInZip
            i = i + 1
        Next
        Set oApp = Nothing
    End If

End Sub


                        
这篇关于VBA读取Zip文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！