Export based on Value in Column, Illegal Character for Windows file path

I found this neat piece of code and it does what I want: Export based on Values in a specific value in a column.

I inspected the code and this line here is responsible for generating the file name:

Please Login or Register  to view this content.

THE PROBLEM is this: Sometimes the value in the column which gets expoerted after which the file name is created, has an illegal character that cannot be in a windows file path, like / or :

I know I could replace the values in the column with other characters such as "-", however, the problem is that this column is also used in a VLOOKUP-Formula, and if I change the value in it, VLOOKUP cannot find the corresponding values.

So my current workaround is to create another column in which I remove the illegal characters, export the files and delete the column afterwards, but this is awkward.

Is there a way to have the export-skript omit or replace these illegal characters right in th VBA Code?

Thanks!

Use this function in your module, like

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I use the attached (Sucuri flagged it as SQL injection and I have no idea why so I have to attach it as a text file)

You could use it like this. The default replacement character is "-" but you can call it specifying any character or string you want.

Please Login or Register  to view this content.

Hi Jeff !

As this weird Securi does not like a double replace see the trick with your code revamped with just a single character :

Please Login or Register  to view this content.

Works also with a space instead of $ …

Make sure that you don't use \ in whatever function you use (which deletes it) - I think you are passing the entire path and file name to it, and \ is a necessary part of that value.

You have this line:

Please Login or Register  to view this content.

This part

Please Login or Register  to view this content.

creates a string that PROBABLY is structured like


"C:\Folder1\SubFolder\Filename 2022-02-24.xlsx"

The function ValidWindowsFileName that was posted has this:

Please Login or Register  to view this content.

Which you should change to this, to keep the \ characters that are needed for the filesave:

Please Login or Register  to view this content.

No - you just need to handle your file name in parts, like

ActiveWorkbook.SaveAs SavePath & ValidWindowsFileName(ArrayOfUniqueValues(ArrayItem)) & Format(Now(), " YYYY-MM-DD hhmmss") & ".xlsx", 51


That will work as long as SavePath ends in a \ character

Thanks to Bernie Deitrick. My code was designed to just look at the file name, not the whole path. I overlooked that.