vba - save a file based on a cell reference

jessiegeorgia

Board Regular
Joined
Aug 19, 2011
Messages
78
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
 
Last edited:

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Zakkaroo

Active Member
Joined
Jul 6, 2009
Messages
383
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! :)
 
Last edited:

jessiegeorgia

Board Regular
Joined
Aug 19, 2011
Messages
78
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?
 

Zakkaroo

Active Member
Joined
Jul 6, 2009
Messages
383

ADVERTISEMENT

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

Board Regular
Joined
Aug 19, 2011
Messages
78
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

Active Member
Joined
Jul 6, 2009
Messages
383

ADVERTISEMENT

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 =

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?
 

Watch MrExcel Video

Forum statistics

Threads
1,128,107
Messages
5,628,724
Members
416,333
Latest member
Time2Learn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top