How to catch error during zipping

RobK

New Member
Joined
Apr 4, 2021
Messages
18
Office Version
  1. 365
Platform
  1. Windows
I use an existing procedure I found on another forum to zip a folder. Works fine in general, but if one of the copied files has a filename with for example a € character the routine crashes.
I would like to detect the error and handle it in a proper way.
However On error goto Error_ to handle it still shows the eror message and crash althoigh on Error got line should prevent the message and the crash an should provide an error code
Has somebody an idea how to do this on this level?


The following code I use:

Sub CreateZipFile(folderToZipPath As Variant, zippedFileFullName As Variant)

Dim ShellApp As Object

'Create an empty zip file
Open zippedFileFullName For Output As #1
Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)
Close #1

'Copy the files & folders into the zip file
Set ShellApp = CreateObject("Shell.Application")
ShellApp.Namespace(zippedFileFullName).CopyHere ShellApp.Namespace(folderToZipPath).items

'Zipping the files may take a while, create loop to pause the macro until zipping has finished.
On Error Resume Next
Do Until ShellApp.Namespace(zippedFileFullName).items.Count = ShellApp.Namespace(folderToZipPath).items.Count
Application.Wait (Now + TimeValue("0:00:01"))
Loop
On Error GoTo 0

End Sub
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
My guess is that when it encounters invalid characters you're not going to be able to stop a crash with an error handler. This would be one of those cases where it might be better to prevent the problem rather than deal with it after the fact. If you have a limited set of invalid characters and know what they are, you could use vba Replace function to replace them with "". However, that would require checking the string x times for x characters. Another approach might be to use RegEx to find characters that are not [a-z] or [0-9], unless you're going to allow certain non numeric or non alpha characters. In that case you'll need something more complicated. Can't help you with that as I gave up trying to learn RegEx but you could research it to see what you think.
 
Upvote 0
My guess is that when it encounters invalid characters you're not going to be able to stop a crash with an error handler. This would be one of those cases where it might be better to prevent the problem rather than deal with it after the fact. If you have a limited set of invalid characters and know what they are, you could use vba Replace function to replace them with "". However, that would require checking the string x times for x characters. Another approach might be to use RegEx to find characters that are not [a-z] or [0-9], unless you're going to allow certain non numeric or non alpha characters. In that case you'll need something more complicated. Can't help you with that as I gave up trying to learn RegEx but you could research it to see what you think.
Hello
Yes this is exactly what I did. I made a Filenamefilter that indeed replace the characters and I recursively scan the whole tree and I change the names of the files with : Name oldName as NewName
But I need to extend the set with charactres as Nobody seems to know exactly whicxh caharacters are illegal.

Thanks for your answer.


Public Sub CorrectFileNamesInFolder(FullFolderName As String)

'Variable declaration
Dim oFile As Object
Dim oFolder As Object
Dim oSubFolder As Object

If (FullFolderName = "") Or (FullFolderName = ".") Or (FullFolderName = "..") Then Exit Sub
'Check for slash
If Right(FullFolderName, 1) <> "\" Then FullFolderName = FullFolderName & "\"

'Check Specified Folder exists or not
If FSO.FolderExists(FullFolderName) Then
Set oFolder = FSO.GetFolder(FullFolderName)
'Loop through each file in a specified folder
For Each oFile In oFolder.Files
If oFile.Name <> ThisWorkbook.Name Then
'Rename
Name FullFolderName & oFile.Name As FullFolderName & FilterFileName(oFile.Name)
End If
Next
'Check Subfolders in specified folder
For Each oSubFolder In oFolder.SubFolders
'Recursive method
'Loop through all subfolders
CorrectFileNamesInFolder (FullFolderName & oSubFolder.Name)
Next
End If
End Sub

Public Function FilterFileName(FileName As String) As String

FileName = Replace(FileName, "<", "", 1, -1, vbTextCompare)
FileName = Replace(FileName, ">", "", 1, -1, vbTextCompare)
FileName = Replace(FileName, ":", "", 1, -1, vbTextCompare)
FileName = Replace(FileName, Chr(34), "", 1, -1, vbTextCompare)
FileName = Replace(FileName, "?", "", 1, -1, vbTextCompare)
FileName = Replace(FileName, "\", "", 1, -1, vbTextCompare)
FileName = Replace(FileName, "|", "", 1, -1, vbTextCompare)
FileName = Replace(FileName, "!", "", 1, -1, vbTextCompare)
FileName = Replace(FileName, "*", "", 1, -1, vbTextCompare)
FileName = Replace(FileName, "€", "Eur", 1, -1, vbTextCompare)

FilterFileName = FileName
End Function
 
Upvote 0
Rather than write code for every case you know, then have to edit code to deal with new found cases, keep a list and edit the list. If this is in a sheet, you can easily retrieve from new rows. Then loop over the range of values using Replace. To the list of characters invalid for windows you can add your own.

Other than that, you'd need an error handler to see if trapping it will work. Your OP suggested you were using one, but in that code, you're not. On Error GoTo 0 turns off error handling for the current procedure. You need something like this:
Under Dim statements, first line of code:
VBA Code:
On Error GoTo errHandler
at end of code that will run if all is OK:
VBA Code:
exitHere:
  Exit Sub
at the end
VBA Code:
errHandler:
  MsgBox err.Number & ": " & err.Description
  Resume exitHere

End Sub
That example is unconditional. If you need conditional error handling, use Select Case block or IF blocks to present different messages or return to different spots in the code.

Note: please use code tags (vba button on posting toolbar) with proper indentation for more than a few of lines of code.
 
Upvote 0
If sticking with code, I might use method 2 or 3 here

You could make the call to that function here like this
VBA Code:
If oFile.Name <> ThisWorkbook.Name And ValidFileName(oFile.Name) = True Then
  Name...
Else
  'do nothing
End If
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top