vartanu
asked on
Macro to save file copy on close
Hi guys,
I'm using the folowing code to create a copy of an excel (in a separate folder) file everytime this gets data changes.
"
Private Sub Workbook_BeforeClose(Cance
Dim wb As Workbook, sName As String, sDir As String
sName = Format(Date, "dd-mm-yyyy")
sDir = "P:\starpro\Plateau 1\11 Table Upload\System Static Data\Functional Design\FS Bak"
Set wb = ThisWorkbook
ChDir "P:\starpro\Plateau 1\11 Table Upload\System Static Data\Functional Design\FS Bak\Tables Bak"
With wb
.SaveCopyAs .Name & (sName) & ".xls"
End With
End Sub
"
The problem is that this is creating a file "file1 and the date.xls" but when I go back in to that file and change some data, this actual code will overwrite the copy everytime.
So I want to have a diferent copy everytime a data is change and not to overwrite the file.
Example:
I go once on "File3.xls" i do a change and i get saved as a copy "File3_august6.xls" in ABC folder
I go again to "File3.xls" i do another change and i get saved something like "File3_august6_2.xls" in ABC folder
And keep saving copies everytime data is change.
Thank you very much in advance.
I'm using the folowing code to create a copy of an excel (in a separate folder) file everytime this gets data changes.
"
Private Sub Workbook_BeforeClose(Cance
Dim wb As Workbook, sName As String, sDir As String
sName = Format(Date, "dd-mm-yyyy")
sDir = "P:\starpro\Plateau 1\11 Table Upload\System Static Data\Functional Design\FS Bak"
Set wb = ThisWorkbook
ChDir "P:\starpro\Plateau 1\11 Table Upload\System Static Data\Functional Design\FS Bak\Tables Bak"
With wb
.SaveCopyAs .Name & (sName) & ".xls"
End With
End Sub
"
The problem is that this is creating a file "file1 and the date.xls" but when I go back in to that file and change some data, this actual code will overwrite the copy everytime.
So I want to have a diferent copy everytime a data is change and not to overwrite the file.
Example:
I go once on "File3.xls" i do a change and i get saved as a copy "File3_august6.xls" in ABC folder
I go again to "File3.xls" i do another change and i get saved something like "File3_august6_2.xls" in ABC folder
And keep saving copies everytime data is change.
Thank you very much in advance.
ASKER
Thanks for the advice but something went wrong in my case:
I get the folowing message:
"Run-time error "1004"
Module.xls07-082008 15:16.xls could not be found. Make sure the speling and the filename location are corect.""
After that i pres "debug" and the below bold text gets highlited.
-----------------
Private Sub Workbook_BeforeClose(Cance
Dim wb As Workbook, sName As String, sDir As String
sName = Format(Now(), "dd-mm-yyyy hh:mm")
sDir = "P:\starpro\Plateau 1\11 Table Upload\System Static Data\Functional Design\FS Bak"
Set wb = ThisWorkbook
ChDir "P:\starpro\Plateau 1\11 Table Upload\System Static Data\Functional Design\FS Bak\Tables Bak"
With wb
.SaveCopyAs .Name & (sName) & ".xls"
End With
End Sub
--------------
So what I'm doing wrong?
I get the folowing message:
"Run-time error "1004"
Module.xls07-082008 15:16.xls could not be found. Make sure the speling and the filename location are corect.""
After that i pres "debug" and the below bold text gets highlited.
-----------------
Private Sub Workbook_BeforeClose(Cance
Dim wb As Workbook, sName As String, sDir As String
sName = Format(Now(), "dd-mm-yyyy hh:mm")
sDir = "P:\starpro\Plateau 1\11 Table Upload\System Static Data\Functional Design\FS Bak"
Set wb = ThisWorkbook
ChDir "P:\starpro\Plateau 1\11 Table Upload\System Static Data\Functional Design\FS Bak\Tables Bak"
With wb
.SaveCopyAs .Name & (sName) & ".xls"
End With
End Sub
--------------
So what I'm doing wrong?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
You make my day so bright man, tnx again.
Yw...thanks for the Grade...
Saurabh...
Saurabh...
Replace this
sName = Format(Date, "dd-mm-yyyy")
With
sName = Format(NOW(), "dd-mm-yyyy hh:mm")
HTH...
Saurabh...