software

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


hi Chris,

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

Sub SavePayrollForClient ()
'to save template file as quot;\payroll\COMPANY\YEAR\MONTH\file.xlsquot; amp;
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 = quot;?:?\Payroll\quot;
ClientCompany = worksheets(quot;?quot;).range(quot;a1quot;)
ClientMonth = worksheets(quot;?quot;).range(quot;A2quot;)
ClientYear = worksheets(quot;?quot;).range(quot;A3quot;)
ClientFileName = quot;?.xlsquot;
ClientFullPathName = StartOfPath amp; ClientCompany amp; quot;\quot; amp; ClientYear amp;
quot;\quot; amp; _
ClientMonth amp; quot;\quot; amp; ClientFileName

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

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

nb: Just replace the question marks as needed amp; look up quot;save asquot; in
the VBE help files ([alt F11] amp; 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 amp; 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 amp; the best way to learn is to experience...--
broro183
------------------------------------------------------------------------
broro183's Profile: www.excelforum.com/member.php...oamp;userid=30068
View this thread: www.excelforum.com/showthread...hreadid=522105HI 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

quot;broro183quot; wrote:

gt;
gt; hi Chris,
gt;
gt; The following may need some fine tuning b/c it hasn't been tested amp; has
gt; no error checking (eg does path/file actually/already exist?):
gt;
gt; Sub SavePayrollForClient ()
gt; 'to save template file as quot;\payroll\COMPANY\YEAR\MONTH\file.xlsquot; amp;
gt; reopen original file.
gt; dim TemplateFile as string
gt; Dim ClientFullPathName as string
gt; dim StartOfPath as string
gt; dim ClientCompany as string
gt; dim ClientMonth as string
gt; dim ClientYear as string
gt; dim ClientFileName as string
gt;
gt; TemplateFile = ActiveWorkbook.FullName
gt; StartOfPath = quot;?:?\Payroll\quot;
gt; ClientCompany = worksheets(quot;?quot;).range(quot;a1quot;)
gt; ClientMonth = worksheets(quot;?quot;).range(quot;A2quot;)
gt; ClientYear = worksheets(quot;?quot;).range(quot;A3quot;)
gt; ClientFileName = quot;?.xlsquot;
gt; ClientFullPathName = StartOfPath amp; ClientCompany amp; quot;\quot; amp; ClientYear amp;
gt; quot;\quot; amp; _
gt; ClientMonth amp; quot;\quot; amp; ClientFileName
gt;
gt; ActiveWorkbook.SaveAs Filename:= _
gt; ClientFullPathName, FileFormat:=xlNormal, _
gt; Password:=quot;quot;, WriteResPassword:=quot;quot;, ReadOnlyRecommended:=False,
gt; _
gt; CreateBackup:=False
gt;
gt; Workbooks.Open FileName:= TemplateFile
gt; Workbooks.close FileName:= ClientFullPathName
gt; end sub
gt;
gt; nb: Just replace the question marks as needed amp; look up quot;save asquot; in
gt; the VBE help files ([alt F11] amp; then F1) if you want to see the other
gt; possible arguments for the save as method.
gt;
gt; I have included lines to reopen the Template file amp; close the Client's
gt; file at the end of the macro. If they are not needed just delete them.
gt; There may be a tidier way of doing this but I don't know it.
gt;
gt; I'm off to bed now but will check tomorrow to see if you had any
gt; problems.
gt;
gt; Hth
gt; Rob Brockett
gt; NZ
gt; Always learning amp; the best way to learn is to experience...
gt;
gt;
gt; --
gt; broro183
gt; ------------------------------------------------------------------------
gt; broro183's Profile: www.excelforum.com/member.php...oamp;userid=30068
gt; View this thread: www.excelforum.com/showthread...hreadid=522105
gt;
gt;


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

Rob Brockett
NZ
Always learning amp; the best way to learn is to experience...--
broro183
------------------------------------------------------------------------
broro183's Profile: www.excelforum.com/member.php...oamp;userid=30068
View this thread: www.excelforum.com/showthread...hreadid=522105
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:
gt;
gt; gt; ... reopen the Template file amp; close the Client's
gt; gt; file at the end of the macro. If they are not needed just delete
gt; them.
gt; gt; There may be a tidier way of doing this but I don't know it.
gt; [/color]

in a recent post by Ron DeBruin
(excelforum.com/showthread.php?t=518959) amp; 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 quot;\payroll\COMPANY\YEAR\MONTH\file.xlsquot;.
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 = quot;?:\?\quot;
ClientCompany = Worksheets(quot;?quot;).Range(quot;a1quot;)
ClientMonth = Worksheets(quot;?quot;).Range(quot;A2quot;)
ClientYear = Worksheets(quot;?quot;).Range(quot;A3quot;)
ClientFileName = quot;?.xlsquot;
ClientFullPathName = StartOfPath amp; ClientCompany amp; quot;\quot; amp; ClientYear amp;
quot;\quot; amp; _
ClientMonth amp; quot;\quot; amp; 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 amp; then closing the
ClientFullPathName b/c it is all done in the single line
quot;TemplateFile.SaveCopyAs ClientFullPathNamequot;. The new line keeps the
template file open (with no changes being saved to it) amp; saves the
Client's file in the chosen directory.

Rob Brockett
NZ
Always learning amp; the best way to learn is to experience...--
broro183
------------------------------------------------------------------------
broro183's Profile: www.excelforum.com/member.php...oamp;userid=30068
View this thread: www.excelforum.com/showthread...hreadid=522105