0

I've used the code from Extract all .gz file in folder using VBA Shell command, to extract .gz files.The problem is that if there is a gap in the filepath, code doesn't work, if there is no gap, it works, as illustrated below: Notice in first example, there is no '_' but a gap ' ', between 'K' and 'L', therefore file path has gaps, whereas example that works, there is an '_', and the whole filepath has no gaps

'Example that doesn't work:

Sub extractAllFiles()

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

File = Dir("Z:\A_B_C\D_E_F\G_H_I\J_K L\_M_N_O\P_Q_R\*.gz")
While (File <> "")
If InStr(1, File, ".gz") > 0 Then
  shellStr = "C:\Program Files\WinZip\winzip32 -e Z:\A_B_C\D_E_F\G_H_I\J_K L\_M_N_O\P_Q_R\" & File & " Z:\A_B_C\D_E_F\G_H_I\J_K L\_M_N_O\P_Q_R\"
  Call Shell(shellStr, vbHide)
End If
File = Dir
Wend
End Sub




'Example that works:

Sub extractAllFiles()

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

File = Dir("Z:\A_B_C\D_E_F\G_H_I\J_K_L\_M_N_O\P_Q_R\*.gz")
While (File <> "")
If InStr(1, File, ".gz") > 0 Then
  shellStr = "C:\Program Files\WinZip\winzip32 -e Z:\A_B_C\D_E_F\G_H_I\J_K_L\_M_N_O\P_Q_R\" & File & " Z:\A_B_C\D_E_F\G_H_I\J_K_L\_M_N_O\P_Q_R\"
  Call Shell(shellStr, vbHide)
End If
File = Dir
Wend
End Sub

I want the first example to work, but why doesn't it?

There are no errors. The code runs, opens winzip, but it's empty, no file is unzipped! Many thanks.

Community
  • 1
  • 1
user2952447
  • 139
  • 3
  • 16

1 Answers1

0

Try putting quotation marks around the paths in your shell string:

shellStr = "C:\Program Files\WinZip\winzip32 -e ""Z:\A_B_C\D_E_F\G_H_I\J_K L\_M_N_O\P_Q_R\" & File & """ ""Z:\A_B_C\D_E_F\G_H_I\J_K L\_M_N_O\P_Q_R\"""

In case you didn't already know, two double quotes ("") evaluates to a single double quote inside the string. Compare to languages like C where the backslash would be used to escape the quotation mark (\").

Blackhawk
  • 5,984
  • 4
  • 27
  • 56
  • I have tried this without success. I'm trying to work out the logic to your suggestion. Why did you suggest this if the error solely lies on the gapy between K and L? It's useful for me to know going forward. Thanks. – user2952447 Dec 27 '13 at 19:27
  • @user2952447 The reasoning is that when running commandline programs, the path parameters are often parsed by whitespace. By putting quotation marks around them, you force the entire path to be treated as one token. Try it manually at the command prompt and see what you get! – Blackhawk Dec 27 '13 at 19:34