0

In order to share files and folders in SharePoint I have to make sure folder and file names don't contain illegal characters, otherwise the file transfer just stops without transferring the rest of the files.

I have searched for solutions using VBA and CMD scripts, but I can't seem to find something usable for my exact situation.

Using Windows 7 Explorer search option seems to do the job, but then I'd have to make a search for each of the 12 characters (# % & * : < > ? / { | }) for each folder which is very time consuming.

Instead, I'm thinking VBA with something like

sub Screenfolder()

Dim myValue As Variant
myValue = InputBox("Path to screen")
Range("A1").Value = myValue

For Range("A1")
    If file/folder name contains # % & * : < > ? / { | } then
    Replace "filename#&{" with "filename123"
    else
    Endif
Next

End Sub

I am aware, that the code probably is going to be a lot more complex, but this is the form I'm thinking.

Is it possible using VBA or CMD?

Mikkel Stave
  • 1
  • 1
  • Do you know how to use `Array()` in VBA? – Vityata May 18 '17 at 09:06
  • 1
    Possible duplicate of [Removing special characters VBA Excel](http://stackoverflow.com/questions/24356993/removing-special-characters-vba-excel) – Pᴇʜ May 18 '17 at 09:06
  • Vityata, I'm not sure, do you have a suggestion? Peh, not exactly the same, since the characters I want to remove are in physical file names. – Mikkel Stave May 18 '17 at 09:47
  • @MikkelStave So the question is not clear to me then. (1) Do you just need the files to be renamed? Then you don't need VBA, use a mass/batch file renamer to replace the characters in filenames; several products can be found on Google. (2) Or do you need to replace these characters within Excel? – Pᴇʜ May 18 '17 at 10:07
  • `#%&{}` are legal in filenames, so you can use Windows to strip those characters out of filenames. OTOH, the wildcard characters `*?<>"` and file-system reserved characters ``/\:|`` are illegal in Windows filenames, so even if you're using a file-system driver in Windows that allows listing a directory containing such filenames (e.g. a redirector or maybe one of the Ext 2/3 drivers), you won't be able to actually open a handle to the file to do anything with it (e.g. rename, delete), even with the ``\\?\`` prefix. – Eryk Sun May 18 '17 at 23:56

1 Answers1

0

I am assuming you are uploading to an on-premise SharePoint system since on SharePoint Online the only illegal characters are # and %.

You can probably find a script that allows you to replaces the characters. Please note that the modification date of the items will also change. In some scenarios this is important to retain when uploading the content to SharePoint. You may also encounter problems uploading blocked files. This only manifests itself on SharePoint On-premise systems.

An alternative way is to use 3rd party tools to upload the content. These tools (e.g. Add-in) cater for illegal characters, blocked files and long item names. Paul

Paul
  • 1
  • A 3rd party software probably would be a solution, but I would like to solve it using CMD or VBA if possible, to avoid purchasing software. I must admit that I don't know a whole lot about SharePoint, but I'd think it's on-premise, since the sites are on our own servers. I think I first have to transfer files and find the troublemakers as they come. Some people have even put a dot at the end of the filename (example..pdf) , which does not work with SP either, making it even harder to isolate problems. If the problem seems to be too severe, I'll have to turn to some sort of software. – Mikkel Stave May 23 '17 at 10:03