VBA: Save Files based on cell value. Apply Trim function when saving

[TheHack22]

Hi All,

I have this code below: It works fine, except for the cell value in A2 (Downloaded Files) contains leading and trailing spaces. So the SaveAs will fail if I don't manually resolve all the leading and Trailing spaces in all the files in A2.

Is there a way to incorporate the TRIM Function into my code?


Sub FileNameAsCellContent()
Dim FileName As String
Dim Path As String
Application.DisplayAlerts = False
Path = "C:\Users\C-TLG\Box\Amp_Page_Views\"
FileName = Range("A2").Value & ".xlsx"
ActiveWorkbook.SaveAs Path & FileName, xlOpenXMLWorkbook
Application.DisplayAlerts = True
ActiveWorkbook.Close
End Sub

Imran

 

[TheHack22]

Yes. The path is correct. I'll change the path to a local folder and retest.

same issue with a different folder path

 

[JEC]

dont use Path as variable. Change it to strPath for example. “Path” conflicts with vba names.
Change Filenames as well

 

[Joe4]

dont use Path as variable. Change it to strPath for example. “Path” conflicts with vba names.
Change Filenames as well

Yes, I already explained that back in post 8.

Try:

Sub FileNameAsCellContent()

    Dim fName As String
    Dim fPath As String

    Application.DisplayAlerts = False

    fPath = "C:\Users\C-TLG\Box\Amp_Page_Views\"
    fName = Application.Trim(Range("A2").Value) & ".xlsx"
    ActiveWorkbook.SaveAs fPath & fName, xlOpenXMLWorkbook
    Application.DisplayAlerts = True
    ActiveWorkbook.Close
    
End Sub

 

[TheHack22]

dont use Path as variable. Change it to strPath for example. “Path” conflicts with vba names.
Change Filenames as well

Changed it to this code below. same issues. See screenshot error message.

Is there a line of code that I can use here to get rid of all non-allowed characters?
I have a feeling there are some invisible character(s), that are causing the issue.



Sub FileNameAsCellContent()
Dim Wbfilename As String
Dim StrPath As String
Application.DisplayAlerts = False
StrPath = "Desktop\"
Wbfilename = Range("A2").Value & ".xlsx"
ActiveWorkbook.SaveAs StrPath & Wbfilename, xlOpenXMLWorkbook
Application.DisplayAlerts = True
ActiveWorkbook.Close
End Sub

 

[JEC]

Use a normal path instead of desktop only. Try it with your initial path in post 1

 

[TheHack22]

Use a normal path instead of desktop only. Try it with your initial path in post 1

Still no luck.

 

[Joe4]

You can use the LEN function to see the

I have a feeling there are some invisible character(s), that are causing the issue.

This can easily be confimed with the LEN function, i.e. you can use:

MsgBox Len(Wbfilename)

to verify the length of that entry, and make sure it appears correct.

If it returns a number larger than the number of characters you manually count in the file name, then that would suggest that you do have extra characters that are not visible.

How is cell A2 being populated with this value that is being used for the file name?
Is it manually entered in?
Is it being copied and pasted from somwewhere?
Does it come from a import of some kind?

You can also try to change A2 to some simple manual entry with no spaces or characters, just to test it out to verify that is the issue or not.

 

[GWteB]

Is there a line of code that I can use here to get rid of all non-allowed characters?

When the function below is used you're in most cases on the safe side.

' Usage example
Wbfilename = ValidateFileName(Wbfilename)


Public Function ValidateFileName(ByVal argFileName As String) As String
    Const ILLEGAL As String = "<>""/:\|?*"
    Dim Drop As String: Drop = Chr(10) & Chr(13)
    Dim Result As String, i As Long
    Result = argFileName
    For i = 1 To Len(Drop)
        Result = VBA.Replace(Result, Mid(Drop, i, 1), "")
    Next
    For i = 1 To Len(ILLEGAL)
        Result = VBA.Replace(Result, Mid(ILLEGAL, i, 1), "_")
    Next
    ValidateFileName = Result
End Function

 

[TheHack22]

You can use the LEN function to see the

This can easily be confimed with the LEN function, i.e. you can use:

MsgBox Len(Wbfilename)

to verify the length of that entry, and make sure it appears correct.

If it returns a number larger than the number of characters you manually count in the file name, then that would suggest that you do have extra characters that are not visible.

How is cell A2 being populated with this value that is being used for the file name?
Is it manually entered in?
Is it being copied and pasted from somwewhere?
Does it come from a import of some kind?

You can also try to change A2 to some simple manual entry with no spaces or characters, just to test it out to verify that is the issue or not.

@Joe4

Thanks for your tips. The filename(to use) in Cell A2 comes from daily downloaded files. It's not only A2 - all the data fields (cells) contain numerous leading and trailing Spaces.
When I manually Trim cell A2 the code runs perfectly. But I have quite a few files. So I would like to see if there is any way I can trim it programmatically.

See screenshot. This is one example of what a file name looks like. (red highlight)

 

[Joe4]

When I manually Trim cell A2 the code runs perfectly.

Can you record how you do that (with the Macro Recorder), and incorporate that into your code, as part of data clean-up, before trying to save the files?