Saving Problem

[don]

Hi Group,

Hi have a custom save routine which saves to a local machine, then
creates a backup save to a network drive the path of which is defined
within the worksheet. During testing it has worked many times over
the network but users report that it doesn't always save, and more
peculiarly that only part of the information changed is saved! The
time a backup save takes is around 4 minutes over the network.

I have identified some occurrences of user error but am unable to
explain all. I've posted code used below and would appreciate any
comments.

Don


Sub BkupSve()

Application.ScreenUpdating = False
ActiveSheet.Protect DrawingObjects:=False, Contents:=False,
Scenarios:=False

Range("C69").Select
Selection.Copy
ActiveSheet.Paste
Application.CutCopyMode = False

On Error Resume Next

Call Hide

Application.DisplayAlerts = False

ThisWorkbook.Save


With ActiveWorkbook
.SaveAs Filename:="" _
& .Worksheets("Confidence Levels").Range("C69").Value &
".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
_
ReadOnlyRecommended:=False, CreateBackup:=False
End With

Call firefighter
MsgBox "File successfully backed up. Now in Read Only. Select Change
User to Edit"

End Sub

 

[Bob Phillips]

You save ThisWorkbook, but SaveAs Activeworkbook. Can you be sure they are
the same workook?

It would also be better to use SaveCopyAs than SaveAs, the original book
stays in memory then.

 

[don]

You save ThisWorkbook, but SaveAs Activeworkbook. Can you be sure they are
the same workook?

It would also be better to use SaveCopyAs than SaveAs, the original book
stays in memory then.

--
__________________________________
HTH

Bob















- Show quoted text -

Bob,

Thanks for reply.

There could be four worbooks open red white blue green, but on the
occasion I'm now investigating only one was open when the backup was
done. I presume from what you have said it would be better to change
this workbook for activeworkbook?

Could the save as be responsible for not all changes being saved?


Revised code


Sub BkupSve()

Application.ScreenUpdating = False
ActiveSheet.Protect DrawingObjects:=False, Contents:=False,
Scenarios:=False

Range("C69").Select
Selection.Copy
ActiveSheet.Paste
Application.CutCopyMode = False


On Error Resume Next

Call Hide



Application.DisplayAlerts = False

ActiveWorkbook.Save


With ActiveWorkbook
.SaveCopyAs Filename:="" _
& .Worksheets("Confidence Levels").Range("C69").Value &
".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
_
ReadOnlyRecommended:=False, CreateBackup:=False
End With

Call firefighter
MsgBox "File successfully backed up. Now in Read Only. Select Change
User to Edit"


End Sub




?

 

[don]

Bob,

Thanks for reply.

There could be four worbooks open red white blue green, but on the
occasion I'm now investigating only one was open when the backup was
done.  I presume from what you have said it would be better to change
this workbook for activeworkbook?

Could the save as be responsible for not all changes being saved?

Revised code

Sub BkupSve()

Application.ScreenUpdating = False
ActiveSheet.Protect DrawingObjects:=False, Contents:=False,
Scenarios:=False

Range("C69").Select
    Selection.Copy
    ActiveSheet.Paste
    Application.CutCopyMode = False

On Error Resume Next

Call Hide

Application.DisplayAlerts = False

ActiveWorkbook.Save

With ActiveWorkbook
   .SaveCopyAs Filename:="" _
            & .Worksheets("Confidence Levels").Range("C69").Value &
".xls", _
            FileFormat:=xlNormal, Password:="", WriteResPassword:="",
_
            ReadOnlyRecommended:=False, CreateBackup:=False
 End With

Call firefighter
MsgBox "File successfully backed up.  Now in Read Only. Select Change
User to Edit"

End Sub

?- Hide quoted text -

- Show quoted text -

Using my sample in last message generated a Named argument not found
error on the File format line, Can you let me know what I missed out
please.

 

[Bob Phillips]

You don't need all of that extraneous gumpf, it inherits it from the
original workbook

Sub BkupSve()

Application.ScreenUpdating = False
ActiveSheet.Protect DrawingObjects:=False, _
Contents:=False, _
Scenarios:=False

Range("C69").Copy
ActiveSheet.Paste
Application.CutCopyMode = False

On Error Resume Next

Call Hide

Application.DisplayAlerts = False

With ActiveWorkbook

.Save
.SaveCopyAs Filename:=.Worksheets("Confidence
Levels").Range("C69").Value & ".xls"
End With

Call firefighter
MsgBox "File successfully backed up. Now in Read Only. " & _
"Select Change User to Edit"

End Sub


--
__________________________________
HTH

Bob

Bob,

Thanks for reply.

There could be four worbooks open red white blue green, but on the
occasion I'm now investigating only one was open when the backup was
done. I presume from what you have said it would be better to change
this workbook for activeworkbook?

Could the save as be responsible for not all changes being saved?

Revised code

Sub BkupSve()

Application.ScreenUpdating = False
ActiveSheet.Protect DrawingObjects:=False, Contents:=False,
Scenarios:=False

Range("C69").Select
Selection.Copy
ActiveSheet.Paste
Application.CutCopyMode = False

On Error Resume Next

Call Hide

Application.DisplayAlerts = False

ActiveWorkbook.Save

With ActiveWorkbook
.SaveCopyAs Filename:="" _
& .Worksheets("Confidence Levels").Range("C69").Value &
".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
_
ReadOnlyRecommended:=False, CreateBackup:=False
End With

Call firefighter
MsgBox "File successfully backed up. Now in Read Only. Select Change
User to Edit"

End Sub

?- Hide quoted text -

- Show quoted text -

Using my sample in last message generated a Named argument not found
error on the File format line, Can you let me know what I missed out
please.

 

[don]

You don't need all of that extraneous gumpf, it inherits it from the
original workbook

Sub BkupSve()

    Application.ScreenUpdating = False
    ActiveSheet.Protect DrawingObjects:=False, _
                        Contents:=False, _
                        Scenarios:=False

    Range("C69").Copy
    ActiveSheet.Paste
    Application.CutCopyMode = False

    On Error Resume Next

    Call Hide

    Application.DisplayAlerts = False

    With ActiveWorkbook

        .Save
        .SaveCopyAs Filename:=.Worksheets("Confidence
Levels").Range("C69").Value & ".xls"
    End With

    Call firefighter
    MsgBox "File successfully backed up.  Now in Read Only. " & _
           "Select Change User to Edit"

End Sub

--
__________________________________
HTH

Bob

















Using my sample in last message generated a Named argument not found
error on the File format line, Can you let me know what I missed out
please.- Hide quoted text -

- Show quoted text -

Thank you