Cannot save a file in excel

Question

I have an excel file containing info about 10-11 sessions. I want to create separate excel files for different sessions with format "ProgramNo. ProgramName - Date". Now, I have got the names from the sheet, but am unable to create and save an excel file with that name.

For Each b In list3
    Set n = X.Sheets("2023 Feedback Master").Range("A1:A" & CStr(lastRow)).Find(What:=b, LookAt:=xlWhole)
    name = CStr(X.Sheets("2023 Feedback Master").Range("A" & CStr(n.Row)).Value) & ". " & CStr(X.Sheets("2023 Feedback Master").Range("B" & CStr(n.Row)).Value) & " - " & CStr(X.Sheets("2023 Feedback Master").Range("E" & CStr(n.Row)).Value)
    MsgBox folderName & name
    Workbooks.Add.SaveAs Filename:=folderName & name & ".xlsx"
Next b

In the MsgBox foldername & name, I am getting the proper name format. But when using it in the next line, it gives an error

enter image description here

It looks to me like `name` contains characters that are not allowed in a filename. https://stackoverflow.com/a/50848245/8422953 — braX, May 09 '23 at 08:07
Try using `Replace(everything you placed in name, "/", ".")` to accommodate what brax and FunThomas said (or only on the last part where the date comes from). I'm also not sure why you convert the n.Row to a string there, the date also doesn't need to be converted to string iirc. — Notus_Panda, May 09 '23 at 08:33
@braX it is working, but the any / or \ gets removed from the filename. The filename comes out to be "CUsersrichakrDocumentswork202337. TA - Self Awareness is the new superpower - 4272023.xlsx" I want it to be C\Users\richakr\Documents\work\2023\37. TA - Self Awareness is the new superpower - 4/27/2023.xlsx — Ritam Chakrabarty, May 09 '23 at 08:40
What you want is not possible. You cannot use a slash in the name. You need to replace them with something else (like a dash or a period) in your code as shown above. — braX, May 09 '23 at 09:04
You need to remove and / and \ from the file**name** (which you call `Name`), but only `/` from the file**path** which you call `folderName`. — CLR, May 09 '23 at 09:25

score 3 · Accepted Answer · answered May 09 '23 at 10:05

As already written in the comments, there are some characters that are not allowed as file/folder name, among them the slash /. All you have to do is format the date in a different way. Assuming that column E contains real dates, best is to use the Format-function.

The following example will write the date in the form yyyy-mm-dd (eg 2023-05-08). I have chosen to use that because writing the date in that order help to sort file names, but you can use any other format you like - just avoid the slash (or any other forbidden character, see https://stackoverflow.com/a/50848245/7599798.

Don't remove the backslashes from your save path, and be sure that either FolderName contains a trailing backslash or that you add it between FolderName and Name in the SaveAs-command.

Have a look to the following snippet, it also makes your code more readable. I have added a With-statement so that you don't have to repeat the sheetname over and over again, and I have removed all the CStr-calls: If you use & for string concatenation in VBA, there will be an implicit conversion anyhow.

With x.Sheets("2023 Feedback Master")
    For Each b In list3
        Set n = .Range("A1:A" & lastRow).Find(What:=b, LookAt:=xlWhole)
        Name = .Cells(n.row, "A") _
             & ". " & .Cells(n.row, "B").Value _
             & " - " & Format(.Cells(n.row, "E").Value, "YYYY-MM-DD")

        MsgBox FolderName & Name
        Workbooks.Add.SaveAs Filename:=FolderName & Name & ".xlsx"
    Next b
End With

Cannot save a file in excel

1 Answers1