Save File Name with Cell Value

[Sg2209]

Hi Friends ,

i am trying to save my sheet ( Temp) as based on a cell value , there is a date in Cell J2, I need each time when sheet is getting emailed through Outlook , it should be send as per the name in Cell J2, Currently i have set it to the Current date , however i need this as per Cell Value , below is my code

Dim TempFilePath As String
Dim TempFileName As String

'Save the new workbook/Mail it/Delete it
    TempFilePath = Environ$("temp") & "\"
    TempFileName = Sheets("Do-Not-Change").Range("J2").Text
    ThisWorkbook.SaveAs Filename:=TempFilePath & "\" & TempFileName

please review and share the feedback where i am incorrect the above code .

thank you .

 

[Chihiro]

What format is date in?

If you are using "/" as part of date format, that's an illegal character in file name and can't be used. Use format function or some other function to convert it to usable format.

As well, you should have file extension (ex: .xlsb) at end of TempFilName.
And specify file format argument in .SaveAs.

Read link.
https://msdn.microsoft.com/en-us/vba/excel-vba/articles/workbook-saveas-method-excel

 

[Hui]

Try:

Dim TempFilePath As String
Dim TempFileName As String

    'Save the new workbook/Mail it/Delete it
    TempFilePath = Environ$("temp") & "\"
    TempFileName = Sheets("Do-Not-Change").Range("J2").Text & ".xlsm"
    ThisWorkbook.SaveAs Filename:=TempFilePath & TempFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled

 

[Sg2209]

Still getting error subscript out of range on the below line

TempFileName = Sheets("Do-Not-Change").Range("J2").Text & ".xlsm"

 

[Marc L]

Hi !

Check for a bad worksheet name in active workbook
or a bad cell value (error) …

If worksheet is in code workbook,
better is to use its codename instead of its name.
​

 

[Hui]

What Marc means is


so the code becomes:

Dim TempFilePath As String
Dim TempFileName As String

    'Save the new workbook/Mail it/Delete it
    TempFilePath = Environ$("temp") & "\"
    'TempFileName = Sheets("Do-Not-Change").Range("J2").Text & ".xlsm"
    TempFileName = Sheet3.Range("J2").Text & ".xlsm"
    ThisWorkbook.SaveAs Filename:=TempFilePath & TempFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled

 

[Hui]

I'd also check what value is in cell J2
check it for leading or trailing spaces and non-printable characters

 

[Hui]

Are you using a PC or Mac ?