vba - save a file based on a cell reference

[jessiegeorgia]

Hi All,

I'm trying to save a file with a name based on a cell reference (which is a date that will rarely have any bearing on the current date so can't relate it to that).
I'm getting a runtime error 1004, method 'SaveAs' of object '_Workbook' failed on the last line of this code, can anyone help?


Sheets("Sheet1").Select
Dim FName As String
Dim FPath As String
FPath = "\\bradsrv01\CCsupport\P&A Team\Zoe\Temp Costs"
FName = Sheets("Sheet1").Range("E4").Text
ThisWorkbook.SaveAs Filename:=FPath & "\" & FName

The cell I'm referencing is populated by a data validation list with a custom format (mmm yy), is that as issue? Or is there just a better bit of code to do this???

Thanks in advance,
Jess

 

[Zakkaroo]

Could you give us an example of what FName might be? You said it's a date ... so could FName = 01/01/2015 ? In which case, wouldn't excel struggle to save a file with that as it's name? try saving a file manually and calling it 01/01/2015.xlsx and I'm sure it will cause you problems!

 

[jessiegeorgia]

FName is currently 'May 15' but only as a result of formatting, it's chosen from a list of dates in dd/mm/yy, presumably that's the issue? Can I get the code to use the formatted name mmm yy or do I have to fiddle about with the cell reference so there's no forward slashes in it?

 

[jessiegeorgia]

Or can code prompt the user to enter a date to name the file?

 

[Zakkaroo]

What you could do, if you have space elsewhere on the sheet is use the formula:

=TEXT(E4,"mmm yy")

^^ then whichever cell has that formula in, becomes FPath instead of E4

 

[jessiegeorgia]

What you could do, if you have space elsewhere on the sheet is use the formula:

=TEXT(E4,"mmm yy")

^^ then whichever cell has that formula in, becomes FPath instead of E4

Oooh, thank you, getting there, a file is saving but is named a number rather than the text, do I need to prompt the code to use a text format too?

 

[Zakkaroo]

Not sure why that isn't working for you, I just created a blank workbook where:

A1 = 01/03/2015
A2 =TEXT(A1,"mmm yy")

Code =

 Dim FName As String
 Dim FPath As String
 
 FPath = "V:\My Docs\Excel"
 FName = Range("A2").Text
 ThisWorkbook.SaveAs Filename:=FPath & "\" & FName

And my file got saved as 'Mar 15.xlsm'

Is your version any different to what I described above?

 

[jessiegeorgia]

I was referring to the wrong sheet

 

[jessiegeorgia]

Thank you for all your help Zakkaroo