使用VBA Shell命令解压缩文件夹中的所有.gz文件 [英] Extract all .gz file in folder using VBA Shell command

问题描述

  Sub extractAllFiles（）
 
 Dim MyObj As Object，MySource As Object，file As Variant 
 Dim shellStr As String 
 
 file = Dir（C：\Downloads\）
 While（file<>）
如果InStr（file，.gz）> 0然后
 shellStr =winzip32 -e C：\Downloads\&档案& C：\Downloads\
调用Shell（shellStr，vbHide）
如果
文件= Dir 
 Wend 
 
 End Sub 
  

当我执行这个子例程时，我得到一个运行时错误53找不到文件错误。当我复制shellStr ...示例： winzip32 -e C：\Downloads\file1.gz C：\Downloads\ 并从命令提示符执行，它的工作完美！我将文件file1.gz中的文本文件解压缩到downloads目录下。但是从VBA运行它似乎不起作用。

任何人都可以散发光？

解决方案

您应该尝试使用shell命令的完整路径，这对我有用：

  Sub extractAllFiles（）
 
 Dim MyObj As Object，MySource As Object，file As Variant 
 Dim shellStr As String 
 
 file = Dir（C：\\ 
 While（file<>）
如果InStr（1，file，.gz）> 0然后
 shellStr =C：\Program文件（x86）\WinZip\winzip32 -e C：\Downloads\&档案& C：\Downloads\
调用Shell（shellStr，vbHide）
如果
文件= Dir 
 Wend 
 
 End Sub $ b我的winzip安装为C：\Program Files（x86）\WinZip \winzip32。你应该使用你的
您的安装路径可能是：
  C：\Program Files\WinZip\winzip32 
  
 
I have the following VBA code to extract all the files within a given directory.
Sub extractAllFiles()

    Dim MyObj As Object, MySource As Object, file As Variant
    Dim shellStr As String

    file = Dir("C:\Downloads\")
    While (file <> "")
        If InStr(file, ".gz") > 0 Then
            shellStr = "winzip32 -e C:\Downloads\" & file & " C:\Downloads\"
            Call Shell(shellStr, vbHide)
        End If
        file = Dir
    Wend

End Sub
When I execute this sub routine I get a Run-Time error 53, "File Not Found" error. When I copy the shellStr... Example: winzip32 -e C:\Downloads\file1.gz C:\Downloads\ and execute it from command prompt, it works perfectly! I get the text file within file1.gz extracted to the downloads directory. However running it from VBA doesn't seem to work.

Can anyone shed some light?
解决方案
You should try with the full path of the shell command, like this, that worked for me:
Sub extractAllFiles()

  Dim MyObj As Object, MySource As Object, file As Variant
  Dim shellStr As String

  file = Dir("C:\Downloads\")
  While (file <> "")
    If InStr(1, file, ".gz") > 0 Then
      shellStr = "C:\Program Files (x86)\WinZip\winzip32 -e C:\Downloads\" & file & " C:\Downloads\"
      Call Shell(shellStr, vbHide)
    End If
    file = Dir
  Wend

End Sub
My winzip is installed as C:\Program Files (x86)\WinZip\winzip32. You should use yours.
Your install path may be:
C:\Program Files\WinZip\winzip32


                        
这篇关于使用VBA Shell命令解压缩文件夹中的所有.gz文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！