Save File name to a cell specific location

razor_raef

New Member
Joined
Sep 15, 2019
Messages
13
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 :)
 

razor_raef

New Member
Joined
Sep 15, 2019
Messages
13
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,573
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.
You cannot use a / character as part of a file name.
 
Last edited:

razor_raef

New Member
Joined
Sep 15, 2019
Messages
13
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>
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,573
Office Version
365
Platform
Windows
I notice that in your op there is a space between Coaching & History, but it's gone in you last post.
 

razor_raef

New Member
Joined
Sep 15, 2019
Messages
13
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?)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,573
Office Version
365
Platform
Windows
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
 

Forum statistics

Threads
1,085,166
Messages
5,382,083
Members
401,768
Latest member
katana_flyer

Some videos you may like

This Week's Hot Topics

Top