Saving Files - Naming Conventions

[medieval]

Firstly thanks for looking.

I am using the code ActiveWorkbook.SaveAs Filename:=Fanta
where Fanta is a variable string.

However in an instance where the string contains illegal characters like "/" or "\" or "&" for example is there a
script which will remove these illegal characters ?

For example if Fanta = "Oslo Company & Sons A/S", how
could you remove these offending characters ?

cheers,
Medieval.

 

[Rodie]

Use this function

Private Function Remove_invalid_character(file_name As String) As String
' A file name can not contain any of the following characters /\:*?"<>|
'   This function replaces them by a dash
  file_name = Replace(file_name, "/", "-")
  file_name = Replace(file_name, "\", "-")
  file_name = Replace(file_name, ":", "-")
  file_name = Replace(file_name, "*", "-")
  file_name = Replace(file_name, "?", "-")
  file_name = Replace(file_name, Chr(34), "-")
  file_name = Replace(file_name, "<", "-")
  file_name = Replace(file_name, ">", "-")
  file_name = Replace(file_name, "|", "-")
  file_name = Replace(file_name, " ", "-")
  Remove_invalid_character = file_name
End Function

This last line is not necessary. It's just to avoid having spaces ...
Have a nice day
Rodie

 

[SkipVought]

Medieval,

Trouble is the path WILL contain \ What are you going to do about that?

You could use the REPLACE function.

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[medieval]

Hi Rodie,

Thanks for the reply I will give that a go.

Hi Skip,

I intend to apply the script given by Rodie on the file name itself and not on the path which can be excluded from the process. Thanks for your tip too.

ta ever so.
Medieval.

 

[SkipVought]

When you stated

 ActiveWorkbook.SaveAs Filename:=Fanta

Fanta INCLUDES THE PATH.

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[medieval]

Hi Skip,

Fanta is a composite file name to which one of the consituted variable strings is denoted as "c:\crg".
This will be excluded from the process.

cheers,
Medieval