Autoback up

[Greg B]

I have excel back up my file throught the code which I got from this forum.
Just one problem with it is I have it run every couple of hours so I dont
lose the work I have completed and it work perfectly except it wont open
because I have personal.xls already open. It continues fine once I click
the read-only tab. I am using the task manager to fire up the workbook at
certain times so it loads another version of excel.

Can i get this macro to run at a certain time if I put it in the
personal.xls

here is the code

Dim oFSO As Object
Dim sFolder As String
Application.DisplayAlerts = False

Set oFSO = CreateObject("Scripting.FileSystemObject")
sFolder = "I:\"
If Not oFSO.FolderExists(sFolder) Then
MkDir sFolder
End If
If Not oFSO.FolderExists(sFolder & Format(Date, "yyyy-mm-dd")) Then
MkDir sFolder & Format(Date, "yyyy-mm-dd")
End If
oFSO.CopyFolder "C:\IDSC", sFolder & Format(Date, "yyyy-mm-dd")
Set oFSO = Nothing
Application.DisplayAlerts = True
ActiveWorkbook.Close

Any help would be great

Thanks again

Greg

 

[Jim Rech]

It sounds as if you're saying that the problem would go away of you opened
Personal.xls read-only. I always open my Personal RO because I do not like
to get the RO message from Excel when I open a second Excel instance.

So in my Personal.xls. from Sub Auto_Open, I call this sub:

Sub SetRO()
On Error Resume Next
ThisWorkbook.Saved = True
ThisWorkbook.ChangeFileAccess Mode:=xlReadOnly
End Sub


--
Jim Rech
Excel MVP
|I have excel back up my file throught the code which I got from this forum.
| Just one problem with it is I have it run every couple of hours so I dont
| lose the work I have completed and it work perfectly except it wont open
| because I have personal.xls already open. It continues fine once I click
| the read-only tab. I am using the task manager to fire up the workbook
at
| certain times so it loads another version of excel.
|
| Can i get this macro to run at a certain time if I put it in the
| personal.xls
|
| here is the code
|
| Dim oFSO As Object
| Dim sFolder As String
| Application.DisplayAlerts = False
|
| Set oFSO = CreateObject("Scripting.FileSystemObject")
| sFolder = "I:\"
| If Not oFSO.FolderExists(sFolder) Then
| MkDir sFolder
| End If
| If Not oFSO.FolderExists(sFolder & Format(Date, "yyyy-mm-dd")) Then
| MkDir sFolder & Format(Date, "yyyy-mm-dd")
| End If
| oFSO.CopyFolder "C:\IDSC", sFolder & Format(Date, "yyyy-mm-dd")
| Set oFSO = Nothing
| Application.DisplayAlerts = True
| ActiveWorkbook.Close
|
| Any help would be great
|
| Thanks again
|
| Greg
|
|

 

[Greg B]

Hi Jim thanks for the answer, The trouble I have is I made a file called
"backup' and I get windows to open this every hour. So where would I put
this code as such? Do I put it with the macro that runs with "backup"

Thanks

Greg

 

[Jim Rech]

I'm confused. I thought you wrote that the problem was that when a second
Excel instance opened you were prompted "Personal.xls is already open, do
you want to open it read-only?".

If so, then my code would go in your Personal.xls, and you would add a call
to it in your Sub Auto_Open or WorksbooksOpen event in Personal. When
Personal opens it will run this code, making itself RO. Since it's RO a
second instance of Excel will not prompt you that Personal is already open.

--
Jim Rech
Excel MVP
| Hi Jim thanks for the answer, The trouble I have is I made a file called
| "backup' and I get windows to open this every hour. So where would I put
| this code as such? Do I put it with the macro that runs with "backup"
|
| Thanks
|
| Greg
|
|

 

[Greg B]

Thanks that is right I have a habit of not being able to write what I mean.

Thank you for your help

Greg