workbook saved in different position

[Li Jianyong]

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

 

[SeanC UK]

Hi Li,

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

bk.SaveAs Filename:= fname

with the colon after Filename.

Sean.

 

[john]

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

 

[Li Jianyong]

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

 

[Dave Peterson]

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