Code that generates a unique save path base on specific user

[matrixpom]

Hi all once again,

Not too sure if I can explain this exactly but here goes, is it possible to have a command button with a save code attached that can generate a path & save the workbook based on an individual user account, e.g. not all staff will have the same path, would it be possible for me to ask each user to create a generic file (one that would be the same file name used nationally) in there ideal drive, then create a code to search for this particular file, wherever or whichever drive it may be? Thank you guys & have a great weekend! - Marco

[Mayda89]

Hi Marco,

Before providing a solution, I need to understand the details.
1-So you want to save the workbook to that users computer, right?
2-How should the path name be determined? Should excel detect the path of the active-workbook, or should the user input a path?
3-How should the file name be determined? Have you already decided on the filename, or each time the macro is ran, should the user determine a filename?

Cheers

[matrixpom]

Hi Mayda,

Sorry for the delayed response, first point is correct, the path will be generated on different user accounts. I am based in Brisbane & my path would start in "K" drive, however my colleagues in Perth, Melbourne or Sydney would start with a completely different path, what I need is a code that when requested will search for a particular file, in this case the file would be called "SWMSRecords". The actual saved file name is generated by the workbook when saved so I just require excel to find the save path to the requested file e.g. SWMSRecords? Hope this answers your questions? Thanks for your time Mayda, greatly appreciated - Marco

[Mayda89]

Hey Maco,
The code below retrieves the path of the workbook:

ActiveWorkbook.Path

Let's you want the name of your file to be "Marco's File.xlsx"

So to save the activeworkbook as Marco's File to that path, you can create this string variable, which stores both the address and name of your file.

Dim fName as String
fName = ActiveWorkbook.Path & Application.PathSeparator & "Marco's File.xlsx"
ActiveWorkbook.SaveAs fName

P.S: Note that instead of saying ActiveWorkbook, you could use the code name of the workbook.
P.S-2: Note that I used Application.PathSeparator instead of using "/", because I thaught maybe some of your users could use Mac, where the separator is ":".