Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving Problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving Problem
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 "don" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving Problem
On Jul 31, 10:46*am, "Bob Phillips" wrote:
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 "don" wrote in message ... 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- Hide quoted text - - 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 ? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving Problem
On Jul 31, 11:16*am, don wrote:
On Jul 31, 10:46*am, "Bob Phillips" wrote: 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 "don" wrote in message .... 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- Hide quoted text - - 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 ?- 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving Problem
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 "don" wrote in message ... On Jul 31, 11:16 am, don wrote: On Jul 31, 10:46 am, "Bob Phillips" wrote: 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 "don" wrote in message ... 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- Hide quoted text - - 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 ?- 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving Problem
On Jul 31, 1:02*pm, "Bob Phillips" wrote:
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 "don" wrote in message ... On Jul 31, 11:16 am, don wrote: On Jul 31, 10:46 am, "Bob Phillips" wrote: 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 "don" wrote in message .... 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- Hide quoted text - - 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 ?- 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.- Hide quoted text - - Show quoted text - Thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with Saving | Excel Discussion (Misc queries) | |||
saving xla problem | Excel Programming | |||
Problem saving a spreadsheet | New Users to Excel | |||
Toolbar saving problem | Excel Discussion (Misc queries) | |||
Saving problem | Excel Programming |