Excel Naming comvention.

[Jagdev]

Hi Experts,

We have a working macro which runs and generates the excel file with the list of names provided in it. The issue is with the special character used in the name - \,"" etc.. When ever the macro encounter such character in the name, it stop working and throws the error.

The error code is

FPath = "C:\Users\Sinderjt\Desktop\New folder (3)\"
strNomeFicheiro = Range("b2").Value
ActiveWorkbook.SaveAs Filename:=FPath & strNomeFicheiro & ".xlsx"

Specially in this line - ActiveWorkbook.SaveAs Filename:=FPath & strNomeFicheiro & ".xlsx"

Please let me know how to deal with special characters used in the file name.

Regards,
JD

[ashleyuk1984]

I use a piece of code to take out all of the invalid characters within a filename.

Try this.

Put this before the code for FPath, because you want to process the string before trying to save it.


ReplaceCharsForFileName strNomeFicheiro, ""

Then place this after your "End Sub"


Private Sub ReplaceCharsForFileName(strNomeFicheiro As String)

'Removes any invalid characters within the filename
strNomeFicheiro = Replace(strNomeFicheiro, "/", "")
strNomeFicheiro = Replace(strNomeFicheiro, "\", "")
strNomeFicheiro = Replace(strNomeFicheiro, ":", "")
strNomeFicheiro = Replace(strNomeFicheiro, "?", "")
strNomeFicheiro = Replace(strNomeFicheiro, Chr(34), "")
strNomeFicheiro = Replace(strNomeFicheiro, "<", "")
strNomeFicheiro = Replace(strNomeFicheiro, ">", "")
strNomeFicheiro = Replace(strNomeFicheiro, "|", "")

End Sub

Hope this helps.

[Jagdev]

Hi Ashleyuk

I am getting run time error and find the screen shot for your reference. When I tried adding the code first it throw error of ByRef, I defined the "Dim strNomeFicheiro as sting" and try running it again. This time the other error.


ReplaceCharsForFileName strNomeFicheiro, ""
FPath = "C:\Users\Sinderjt\Desktop\New folder (3)\"
strNomeFicheiro = VBA.Replace(Range("B2").Value, "/", ChrW(&H2215))
ActiveWorkbook.SaveAs Filename:=FPath & strNomeFicheiro & ".xlsx"
End With
End Sub
Private Sub ReplaceCharsForFileName(strNomeFicheiro As String)
'Removes any invalid characters within the filename
strNomeFicheiro = Replace(strNomeFicheiro, "/", "")
strNomeFicheiro = Replace(strNomeFicheiro, "\", "")
strNomeFicheiro = Replace(strNomeFicheiro, ":", "")
strNomeFicheiro = Replace(strNomeFicheiro, "?", "")
strNomeFicheiro = Replace(strNomeFicheiro, Chr(34), "")
strNomeFicheiro = Replace(strNomeFicheiro, "<", "")
strNomeFicheiro = Replace(strNomeFicheiro, ">", "")
strNomeFicheiro = Replace(strNomeFicheiro, "|", "")
End Sub

[ashleyuk1984]

Hi Jagdev,
I see that you have a "End With" in that piece of code, that's most likely the cause of the issue your having.
Without seeing the full code that you have, it's hard to debug. My guess by looking at what you have supplied above, it's the End With that's causing the issue. You should probably be putting that further up.
Maybe above " - ReplaceCharsForFileName strNomeFicheiro, "" - ", but like I said, it's hard to debug without looking at all of the code, or at least where the WITH command starts.

[SamT]

Try this.

Put this before the code for FPath, because you want to process the string before trying to save it.


ReplaceCharsForFileName strNomeFicheiro, ""

The comma and quotes are a typo, the comma accidentally tells VBA that the quotations are an argument.

Tthe code line should be

ReplaceCharsForFileName strNomeFicheiro


In this sub, there is only the one argument which is "strNomeFicheiro", so you get the error "Wrong number of arguments"

Private Sub ReplaceCharsForFileName(strNomeFicheiro As String)

[Jagdev]

Hi SamT

This worked well, but the issue is the amount of files to be renamed is more than 700. This option is increasing the turnaround time of each file. Each file runs throught this function and this increase the overrall time per file.

Please find the below code for your better understanding.


Sub LoadForm2()
Workbooks("data.xlsm").Activate
Application.ScreenUpdating = False
With ThisWorkbook.ActiveSheet
R = .Range("A65536").End(xlUp).Row
For a = 2 To R
Sheets("Raw Data").Select
ActiveSheet.Range("A:T").autofilter Field:=7, Criteria1:=.Cells(a, 1), Operator:=xlAnd
Workbooks.Open ("C:\Users\Sinderjt\Desktop\New folder (3)\Statement.xlsx")
Windows("Statement.xlsx").Activate
Sheets("Data").Select
Cells.Select
Selection.ClearContents
Workbooks("data.xlsm").Activate
Sheets("Raw Data").Select
Range("G1").Activate
Rows("1:1").Select
Range("G1").Activate
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Statement.xlsx").Activate
Sheets("Data").Select
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
Sheets("Summary").Select
Range("C15").Select
Application.CutCopyMode = False
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
Sheets("Statement").Select
Range("C10").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
Sheets("Summary").Select
FPath = "C:\Users\Sinderjt\Desktop\New folder (3)\"
strNomeFicheiro = Range("B2")
ActiveWorkbook.SaveAs Filename:=FPath & strNomeFicheiro & ".xlsx"
ActiveWorkbook.Close
Windows("data.xlsm").Activate
Sheets("Macro").Select
Next a
End With
Application.ScreenUpdating = True
End Sub

[Jagdev]

Hi Experts

Any views on the above thread.

Regards
JD

[ashleyuk1984]

Hi Jagdev,
You could you significantly reduce the amount of code that you have.
I presume that you used the macro recorder to achieve the majority of the code that you have.
The macro recorder, puts in lines such as


Sheets("Data").Select
Cells.Select
Selection.ClearContents

So that's three lines of code. Whereas this could be modified to become one line.


Sheets("Data").Cells.ClearContents

I would work on eliminating the lines of code that simply aren't required.

And also maybe put "Application.Screenupdating = False" at the beginning & "Application.Screenupdating = True" at the end.

Doing things like this can half the time taken to complete the task.

[Jagdev]

Hi Ashleyuk1984 and SamT

After amending the above code it really reduced the output time to larger extent. Thanks for your help.

Regards,
JD