VBA So Workbook Copy can't be saved

[shelby55]

Hi

I am using Excel 2003. Thanks to Skip I've been able to save a current workbook as a copy and remove links to a database and ensure that users aren't prompted to restore links.

Now I just need to do one more thing: is there code I can add so the copy will not prompt for saving on closing? Better yet, ensure that the copy can't be saved at all even from the file save or save as menu?

Code to date:

Sub copyWorkbook()
Dim ws As Worksheet
Dim NewWB As Workbook
Dim qt As QueryTable

'this keeps the copy from being displayed
    Application.ScreenUpdating = False
    Sheets(Array("WSA", "WSB", "WSC", "WSD", "WSE", "WSF")).Copy
    Set NewWB = ActiveWorkbook
    Application.DisplayAlerts = False
    Application.CutCopyMode = False
    With NewWB.Sheets("WSA")
'unlock ALL cells
       With .Cells
          .Locked = False
          .FormulaHidden = False
       End With
'lock these cells
       With .Range("F4:M40")
           .Locked = True
           .FormulaHidden = False
       End With
       .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
  End With
  With NewWB.Sheets("WSDd")
    For Each qt In .QueryTables
      qt.Delete
    Next
    NewWB.Sheets("WSD").Visible = xlVeryHidden
  End With
  NewWB.Sheets("WSC").Select
  NewWB.Sheets("WSB").Select
  NewWB.Sheets("WSA").Select
  NewWB.Sheets("WSA").Range("F4").Select
  NewWB.UpdateLinks = xlUpdateLinksNever
    
    NewWB.SaveCopyAs Filename:="E:\My Documents\MyWorkbook_" & Format(Now(), "YYYYMMDD") & ".xls"
    ActiveWindow.Close
    Application.ScreenUpdating = True
End Sub

Note also that the line of code:

NewWB.Sheets("WSA").Range("F4").Select

is supposed to be so that when the user opens the copy they open it up to the first worksheet and the cell F4 but it doesn't work - it does open to the first worksheet as I want but to the bottom of the page so any assistance with that code would also be appreciated.

Thanks.

 

[SkipVought]

Application.displayalerts=false

but you already have that.

Why are you using the SaveCopyAs method, rather than the SaveAs method?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

Thanks for replying.

The document where the above code rests is essentially a template which will never be saved with data in it - it links to the database and updates. The copy is for users so they won't be able to have access to the "raw data" sheet or be able to edit the cells. The copy is also being saved to a special folder while the original with the Access db is saved somewhere else.

 

[Gavona]

Save As" the original as a template will open it read only and force a Save As if user tries to save it. Or you can use Windows explorer to make the file read only, or you can specify a password to modify (users without the password open it read only).

....sorry on re-reading I see you want to do that with the copy you have created not the original....

Using Save As instead of Save Copy As you could add the Read Only recommended or password. Equally if you did not want the end user prompts then you could save as a template or, I guess, if you continue to use Save Copy As then the saved copy will not be locked so you could use the FSO to write protect the file.



Gavin

 

[combo]

To avoid prompt for saving, tell excel that the workbook has not been changed:

NewWB.Saved=True

You need to scroll the window to set given cell at top-left corner:

NewWB.Sheets("WSA").Select
NewWB.Sheets("WSA").Range("F4").Select
NewWb.Windows(1).ScrollRow=NewWB.Sheets("WSA").Range("F4").Row
NewWb.Windows(1).ScrollColumn=NewWB.Sheets("WSA").Range("F4").Column

combo

 

[SkipVought]

Yet another way...

With NewWB.Sheets("WSA")
   .Select
   .Range("A1").Select
   .Range("F4").Select
End with

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi

Thanks so much for the replies.

Gavin: I'm not sure what you mean by FSO to write protect the file? If I choose Save As instead of SaveCopyAs, can I still do all that I've done - I really like the current results except for the fact that it prompts for a save.

Combo: I'd already tried NewWB.saved = true but it didn't work...perhaps I had it in the wrong part of the code above? Please advise where it should go (I had it placed before the savecopyas).

Skip and Combo: strangely I took out the code of

NewWB.Sheets("WSC").Select
  NewWB.Sheets("WSB").Select
  NewWB.Sheets("WSA").Select
  NewWB.Sheets("WSA").Range("F4").Select

and now it opens up to the first page at the top so that's good.

Thanks.

 

[combo]

It should be placed after all changes are made in the target workbook, at the end of the procedure for instance.
This property keeps information (boolean) if there are changes in the workbook that were not saved. After Saved=True there is no need to update file, so no prompt for saving. If Saved=False, excel asks for saving workbook, any change or recalculation sets the 'Saved' property to False.

combo

 

[Gavona]

I'm not sure what you mean by FSO to write protect the file?

If you google VBA FSO you will get many links that will tell you about the File System Object. However when I did this I also found a post or three that suggested a better approach:
Have a look at VBA help for the SetAttr statement.

If I choose Save As instead of SaveCopyAs, can I still do all that I've done - I really like the current results except for the fact that it prompts for a save.

I believe that the only differences are:
With SaveAs the name of the file in memory is not changed so further changes affect the original file not the copy
With Save Copy as you don't get so many optional parameters:

SaveCopyAs:
expression.SaveCopyAs(Filename)


SaveAs:
expression.SaveAs(FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local)

Gavin