Save File name to a cell specific location

razor_raef

New Member
Joined
Sep 15, 2019
Messages
19
Hey all, i've done searches on this forum without finding much luck as to why my macro isn't working.

I'm trying to have a macro assigned to an object that once hit will save the pre-filled file name (as shown in the example below).

Here is my coding so far:

Sub SaveFile ()
Dim FN As String
FN = Range ("C69").Value
ActiveWorkbook.SaveAs filename:=FN
End Sub

Cell C69 would have something like this:

\\mau.group\corporate\Homefolders$\johnsmith\Desktop\Coaching History\Karen Rose - 15/09/19.xlsm

Thank you to anyone who can figure this one out for me :)
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Use this:

Code:
ActiveWorkbook.SaveAs FN, xlOpenXMLWorkbookMacroEnabled

Hey Tom,
Looks like I got a bit further because now i'm coming up with the following error:

Run-time error '1004':
The file could not be accessed. Try one of the following:
- Make sure the specified folder exists.
- Make sure the folder that contains the file is not read-only.
- Make sure the file name does not contain any of the following characters: <>?[]:| or *
- Make sure the file/path name doesn't contain more than 218

I can confirm that all of these have been met
 
Upvote 0
Hi & welcome to MrExcel.
You cannot use a / character as part of a file name.
 
Last edited:
Upvote 0
Hi & welcome to MrExcel.
You cannot use a / character as part of a file name.
Hi and thank you for the welcome.
I presume that would be the case and so I made the save file target as shown below but it's still not working:
\\mau.group\corporate\Homefolders$\jsmith\Desktop\CoachingHistory\karen.xlsm

<tbody>
</tbody>
 
Upvote 0
I notice that in your op there is a space between Coaching & History, but it's gone in you last post.
 
Upvote 0
I notice that in your op there is a space between Coaching & History, but it's gone in you last post.

Yeah i got rid of the space in case that was an issue as well :P hehehe. Folder name is replicated on desktop so that shouldn't be an issue. (ps. if there wasn't a coaching history file on desktop at all, would this macro automatically create it or the folder has to be existing?)
 
Upvote 0
All folders must exist & must be named exactly the same as the code.

Can't test this as not on a network, but what if anything does the msgbox say if you run this
Code:
Sub chk()
   MsgBox Dir("\\mau.group\corporate\Homefolders$\johnsmith\Desktop\CoachingHistory", vbDirectory)
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

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
Back
Top