Macro to create "path" for save
 

Hi
I am using a template spreadsheet to do the payroll for various clients.
When it comes to saving it on out system I would like to save it
automatically in a specific folder for each client.
In the sheet I have 3 cells with the following info:
Company (A1)
Month (A2)
Year (A3)


I would like to create a macro to save the file under
\payroll\COMPANY\YEAR\MONTH\file.xls

Thanks

Macro to create "path" for save
 

hi Chris,

The following may need some fine tuning b/c it hasn't been tested & has
no error checking (eg does path/file actually/already exist?):

Sub SavePayrollForClient ()
'to save template file as "\payroll\COMPANY\YEAR\MONTH\file.xls" &
reopen original file.
dim TemplateFile as string
Dim ClientFullPathName as string
dim StartOfPath as string
dim ClientCompany as string
dim ClientMonth as string
dim ClientYear as string
dim ClientFileName as string

TemplateFile = ActiveWorkbook.FullName
StartOfPath = "?:?\Payroll\"
ClientCompany = worksheets("?").range("a1")
ClientMonth = worksheets("?").range("A2")
ClientYear = worksheets("?").range("A3")
ClientFileName = "?.xls"
ClientFullPathName = StartOfPath & ClientCompany & "\" & ClientYear &
"\" & _
ClientMonth & "\" & ClientFileName

ActiveWorkbook.SaveAs Filename:= _
ClientFullPathName, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False,
_
CreateBackup:=False

Workbooks.Open FileName:= TemplateFile
Workbooks.close FileName:= ClientFullPathName
end sub

nb: Just replace the question marks as needed & look up "save as" in
the VBE help files ([alt + F11] & then F1) if you want to see the other
possible arguments for the save as method.

I have included lines to reopen the Template file & close the Client's
file at the end of the macro. If they are not needed just delete them.
There may be a tidier way of doing this but I don't know it.

I'm off to bed now but will check tomorrow to see if you had any
problems.

Hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=522105

Macro to create "path" for save
 

HI Rob

That did just the trick. I was aware of the file path issue (ie: needs to
exist) but that is fine as I didn't want users to create directories all over
the place. That way I keep the file structure as I want it.

Thank a lot

"broro183" wrote:


hi Chris,

The following may need some fine tuning b/c it hasn't been tested & has
no error checking (eg does path/file actually/already exist?):

Sub SavePayrollForClient ()
'to save template file as "\payroll\COMPANY\YEAR\MONTH\file.xls" &
reopen original file.
dim TemplateFile as string
Dim ClientFullPathName as string
dim StartOfPath as string
dim ClientCompany as string
dim ClientMonth as string
dim ClientYear as string
dim ClientFileName as string

TemplateFile = ActiveWorkbook.FullName
StartOfPath = "?:?\Payroll\"
ClientCompany = worksheets("?").range("a1")
ClientMonth = worksheets("?").range("A2")
ClientYear = worksheets("?").range("A3")
ClientFileName = "?.xls"
ClientFullPathName = StartOfPath & ClientCompany & "\" & ClientYear &
"\" & _
ClientMonth & "\" & ClientFileName

ActiveWorkbook.SaveAs Filename:= _
ClientFullPathName, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False,
_
CreateBackup:=False

Workbooks.Open FileName:= TemplateFile
Workbooks.close FileName:= ClientFullPathName
end sub

nb: Just replace the question marks as needed & look up "save as" in
the VBE help files ([alt + F11] & then F1) if you want to see the other
possible arguments for the save as method.

I have included lines to reopen the Template file & close the Client's
file at the end of the macro. If they are not needed just delete them.
There may be a tidier way of doing this but I don't know it.

I'm off to bed now but will check tomorrow to see if you had any
problems.

Hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=522105

Macro to create "path" for save
 

Hi Chris,
Beauty - thanks for the feedback, I'm pleased I could help.

Rob Brockett
NZ
Always learning & the best way to learn is to experience...


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=522105

Macro to create "path" for save
 

Hi Chris

I know you are happy with what you have but I just saw the syntax for
how to prevent the need of having to...
Chris Wrote:

... reopen the Template file & close the Client's
file at the end of the macro. If they are not needed just delete
them.
There may be a tidier way of doing this but I don't know it.
[/color]

in a recent post by Ron DeBruin
(http://excelforum.com/showthread.php?t=518959) & thought you may like
it to tidy your code up.

With a little adaptation I have incorporated it into your code as
follows:

Sub RevisedSavePayrollForClient()
'to save template file as "\payroll\COMPANY\YEAR\MONTH\file.xls".
Dim TemplateFileFullPath As String 'renamed
Dim TemplateFile As Workbook 'new/redefined variable
Dim ClientFullPathName As String
Dim StartOfPath As String
Dim ClientCompany As String
Dim ClientMonth As String
Dim ClientYear As String
Dim ClientFileName As String

TemplateFileFullPath = ActiveWorkbook.FullName 'renamed
Set TemplateFile = ActiveWorkbook 'new/redefined variable
StartOfPath = "?:\?\"
ClientCompany = Worksheets("?").Range("a1")
ClientMonth = Worksheets("?").Range("A2")
ClientYear = Worksheets("?").Range("A3")
ClientFileName = "?.xls"
ClientFullPathName = StartOfPath & ClientCompany & "\" & ClientYear &
"\" & _
ClientMonth & "\" & ClientFileName

TemplateFile.SaveCopyAs ClientFullPathName 'new line
End Sub

The key is the second last line. This prevents the need for saving the
ClientFullPathName, reopening the Template & then closing the
ClientFullPathName b/c it is all done in the single line
"TemplateFile.SaveCopyAs ClientFullPathName". The new line keeps the
template file open (with no changes being saved to it) & saves the
Client's file in the chosen directory.

Rob Brockett
NZ
Always learning & the best way to learn is to experience...


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=522105