How To Use Saved As File 1

[shelby55]

Hi

I am using Excel 2003 and I'm quite new to VBA.

I have an Excel document that I want to "save as" a copy once it's been updated which I can do by using:

ActiveWorkbook.SaveCopyAs Filename:="E:\My Documents\myfolder\ExcelFile.xls"

Note that the code above is in Sub Workbook_Open()

But if I then try to access ExcelFile.xls of course I can't because the code above is in the document because it "saved as". How do I write this code so it doesn't apply to the the saved copy?

Thanks.

 

[Gavona]

After running your code your original workbook will still be in its original location called whatever you called it. There will be a copy of it, including the macro at E:\My Documents\myfolder\ExcelFile.xls.

Not sure I understand the question or what you are trying to do. Surely when the workbook is opening it will be the active workbook. So all this macro will do is save a copy of the workbook in the state it was in when you opened it. The next time you open it then the previous copy will be overwritten (suprisingly I was not prompted to allow overwitting when I tested this)

You may want to make the original file read only (either as a file property or by requiring a password if someone wants to open to make changes to the original). That might actually meet your entire requirement, perhaps with ChDir in your On_Open macro.

How do I write this code so it doesn't apply to the the saved copy?

Here is an answer to your specific question but if you post more clearly what you are trying to achieve then I am sure someone here will help you 'cos I don't reckon this is it!

Private Sub Workbook_Open()
If ThisWorkbook.Name = "ExcelFile.xls" Then Exit Sub
ActiveWorkbook.SaveCopyAs Filename:="E:\My Documents\myfolder\ExcelFile.xls"
End Sub

If your original file is called the same as your copy then the above may need modifying:
If ThisWorkbook.FullName = "E:\My Documents\myfolder\ExcelFile.xls" Then Exit Sub
but this may give you another issue as I think that you will need to specify the UNC filename (\\ComputerName\My Documents\myfolder\ExcelFile.xls). Insert this into your code to find out:
MsgBox Prompt:=ThisWorkbook.FullName

Now, You asked how to stop the code from applying to the saved copy. The code I have posted does this. But if a further copy is made of the copy (with a different name) then the code would apply to that further copy. You might have been better asking how to make the code apply only to the original copy.

Regards,

Gavin

 

[shelby55]

Hi Gavin

You rock...that is exactly what I wanted!! Thanks very much!!!