I am working on a workbook, when I close the book, I want to save it into
two path. I have programmed following macro,but when I close the workbook, it
doesn't save a copy as I suppose.

my workbook is named "payment.xls"


Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim bk As Workbook

Dim fname As String

fname = "H:\2009\payment\payment.xls"

Set bk = Workbooks("payment.xls")


bk.Save


bk.SaveAs Filename = fname


End Sub

Hi Li,

I think there is a problem with your SaveAs line. Try:

bk.SaveAs Filename:= fname

with the colon after Filename.

Sean.

--
(please remember to click yes if replies you receive are helpful to you)


"Li Jianyong" wrote:


I am working on a workbook, when I close the book, I want to save it into
two path. I have programmed following macro,but when I close the workbook, it
doesn't save a copy as I suppose.

my workbook is named "payment.xls"


Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim bk As Workbook

Dim fname As String

fname = "H:\2009\payment\payment.xls"

Set bk = Workbooks("payment.xls")


bk.Save


bk.SaveAs Filename = fname


End Sub

when making a copy of active workbook you should use savecopyas - see help
file.

following routine makes backup copy of activeworkbook which you may be able
adapt & use in your application:

Sub MakeBackupCopy()
Const ATitle = "Make Backup Copy"
Const Folder = "C:\Backups\" 'Change to your needs
Dim NewName As String

With ThisWorkbook
NewName = Folder & .Name & " " & Format(Now, "dd-mm-yyyy hh-mm-ss")
& ".Xls"

If vbYes = MsgBox("Do You Want To make a Backup Copy?" & Chr(10) & _
Chr(10) & NewName, 36, ATitle) Then

On Error Resume Next
MkDir (Folder)
On Error GoTo 0

.SaveCopyAs NewName
.Save
Else
msg = MsgBox("NewName: " & NewName & Chr(10) & _
Chr(10) & " " & _
"Backup Copy Not Has Not Been Saved.", 16, ATitle)
End If
End With
End Sub

hope helpful
--
jb


"Li Jianyong" wrote:


I am working on a workbook, when I close the book, I want to save it into
two path. I have programmed following macro,but when I close the workbook, it
doesn't save a copy as I suppose.

my workbook is named "payment.xls"


Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim bk As Workbook

Dim fname As String

fname = "H:\2009\payment\payment.xls"

Set bk = Workbooks("payment.xls")


bk.Save


bk.SaveAs Filename = fname


End Sub

Hello Sean,

thanks for your help. It works now. But there is a alert message show like
" are you sure to replace the existed file in H:\2009\payment\ ? my office is
Chinese version,so I can just try to translate the alert in my own words,hope
you can understand.

could you please help me to close this alerts and answer yes always?

Li Jianyong


"SeanC UK" wrote:

Hi Li,

I think there is a problem with your SaveAs line. Try:

bk.SaveAs Filename:= fname

with the colon after Filename.

Sean.

--
(please remember to click yes if replies you receive are helpful to you)


"Li Jianyong" wrote:


I am working on a workbook, when I close the book, I want to save it into
two path. I have programmed following macro,but when I close the workbook, it
doesn't save a copy as I suppose.

my workbook is named "payment.xls"


Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim bk As Workbook

Dim fname As String

fname = "H:\2009\payment\payment.xls"

Set bk = Workbooks("payment.xls")


bk.Save


bk.SaveAs Filename = fname


End Sub

First, I would use a dedicated macro that I ran when I wanted to do this. I
wouldn't want to use the _Beforeclose event--just in case I screwed up the
workbook and didn't want to save it!

But you could try this untested, uncompiled code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim fname As String
fname = "H:\2009\payment\payment.xls"
me.Save
me.SavecopyAs Filename:=fname
End Sub

Li Jianyong wrote:

I am working on a workbook, when I close the book, I want to save it into
two path. I have programmed following macro,but when I close the workbook, it
doesn't save a copy as I suppose.

my workbook is named "payment.xls"

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim bk As Workbook

Dim fname As String

fname = "H:\2009\payment\payment.xls"

Set bk = Workbooks("payment.xls")


bk.Save


bk.SaveAs Filename = fname


End Sub

--

Dave Peterson