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]

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

@GWteB

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?

@Joe4

I would like to give @GWteB code a try first.
Imran

 

[TheHack22]

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

@GWteB

Thanks very much for this. I have a stupid question. How do I organize your code and mine?
a) Do I place mine as the first sub and yours as the second?
b) where do I place this line? "Wbfilename = ValidateFileName(Wbfilename)"

Imran

 

[GWteB]

Thanks very much for this. I have a stupid question. How do I organize your code and mine?
a) Do I place mine as the first sub and yours as the second?

The order of individual procedures within a code module is irrelevant.

b) where do I place this line? "Wbfilename = ValidateFileName(Wbfilename)"

Based on the code you provided in a previous post within this thread:

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

 

[TheHack22]

The order of individual procedures within a code module is irrelevant.


Based on the code you provided in a previous post within this thread:

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

@GWteB

Thanks very much. I'll provide feedback soon

 

[TheHack22]

The order of individual procedures within a code module is irrelevant.


Based on the code you provided in a previous post within this thread:

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

I'm having the same issues.See screenshots below(Yellow and error message)

 

[GWteB]

It works for me, provided the worksheet with the filename in cell A2 is the active sheet!
You didn't qualify the source range, so you might getting a value from a cell on another worksheet, or even from within another workbook. Would recommend to always qualify ranges and other objects in your code, like:

ThisWorkbook.Worksheets("Sheet1").Range("A2")

 

[TheHack22]

It works for me, provided the worksheet with the filename in cell A2 is the active sheet!
You didn't qualify the source range, so you might getting a value from a cell on another worksheet, or even from within another workbook. Would recommend to always qualify ranges and other objects in your code, like:

ThisWorkbook.Worksheets("Sheet1").Range("A2")

@GWteB
Thanks. This works perfectly. You're awesome. I'm not sure what post number I should mark as a solution since this solution was derived from multiple back and forth.

Can you please designate one as the solution for me?
Imran

 

[GWteB]

You are welcome and thanks for letting us know.

Can you please designate one as the solution for me?

IMO that wouldn't be appropriate. From distance I can't determine if there weren't multiple causes of the problem on your end (as other forum members previously suggested) and that pure coincidentally my solution removed the last cause.
In short, you decide for yourself what you found the most suitable solution.

 

[TheHack22]

You are welcome and thanks for letting us know.



IMO that wouldn't be appropriate. From distance I can't determine if there weren't multiple causes of the problem on your end (as other forum members previously suggested) and that pure coincidentally my solution removed the last cause.
In short, you decide for yourself what you found the most suitable solution.

@GWteB

I must give some credit for your post #27 (See below). I was working on something else earlier today and I couldn't figure why it wasn't working and then I remembered your tips from yesterday. This really helped me solve it.


"You didn't qualify the source range, so you might getting a value from a cell on another worksheet, or even from within another workbook. Would recommend to always qualify ranges and other objects in your code, like:
VBA Code:
ThisWorkbook.Worksheets("Sheet1").Range("A2")"

Imran

 

[GWteB]

Glad I could be of some help.