How to catch error during zipping

[RobK]

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

 

[Micron]

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.

 

[RobK]

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

 

[Micron]

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:

On Error GoTo errHandler

at end of code that will run if all is OK:

exitHere:
  Exit Sub

at the end

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.

 

[Micron]

If sticking with code, I might use method 2 or 3 here

VBA Function To Determine If An Excel File Name Is Valid

I've written a few macros over the past few days that have required the user to submit a name for a newly saved Excel file. If you've tried saving files with certain special characters, you know that you can get rejected if you don't provide a name within the range of allowed characters.

www.thespreadsheetguru.com

You could make the call to that function here like this

If oFile.Name <> ThisWorkbook.Name And ValidFileName(oFile.Name) = True Then
  Name...
Else
  'do nothing
End If