Because you have a common file I would copy the file rather than open the
workbook and perform a SaveAs. The code opens a dialog Box to select the
Templet from the common drive. then creates a local directory GeneratedFiles
if the directory doesn't exist. And finally copies the templet to the
Generated Files directory.


Sub CopyFile()
RootFolder = "C:\"
DestinationFolder = "GeneratedFiles"

Set ScriptObj = CreateObject("Scripting.FileSystemObject")

'Get Templete from Networkfile
filetocopy = Application _
.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", _
Title:="Get Templete")
If filetocopy = False Then
MsgBox ("Cannot Open Templete - Exiting Sub")
Exit Sub
End If

'Check if Folder already exists
Found = False
Set F = ScriptObj.GetFolder(RootFolder)
For Each Folder In F.Subfolders
If Folder.Name = DestinationFolder Then
Found = True
Exit For
End If
Next Folder

'if folder does not exist then create
If Found = False Then
'Generate Folder
ScriptObj.CreateFolder (RootFolder & DestinationFolder)
End If

DestFolder = RootFolder & "GeneratedFiles\"

'get source filename from full path name
Set FileObj = ScriptObj.getfile(filetocopy)
BaseName = FileObj.Name

SourceFile = filetocopy
DestinationFile = DestFolder & BaseName
FileCopy SourceFile, DestinationFile ' Copy source to target.


End Sub


"Arnie" wrote:

Morning all firstly thanks in advance for any answers.

I have a spreadsheet that i want to hold in a shared location when anyone
uses it they copy it to there own PC and place it whereever they want.

I need my Macro to be able to get the filename path so that it can be used
1. to carryout a saveAs and request the new filename
2. to reference the file in other macros
3. to get the file path and create a folder called "GeneratedFiles"

an awfull lot i know but no point in posting lots of questions

Regards Arnie