PDA

View Full Version : Backing up Query



thomas.szwed
12-11-2007, 04:55 AM
Before you tell me this post is good but my requirements slighly differ.

http://www.vbaexpress.com/forum/showthread.php?t=10029&highlight=backing+up

I need some code that will create a backup of my workbook named by the current date. It is based on a shared network drive...which is constantly on. Therefore it could be automatically done say at 1am in the morning....is this possible?

Secondly if this proves undoable can i get some code that backs it up the first time it is opened up by a user each day?

Finally....is there anyway each backup replaces its predecessor as opposed to creating multiple files...?

Bob Phillips
12-11-2007, 05:33 AM
Try this

In a standard code module



Public nTime As Double

Public Sub BackMeUp()
Dim mpPath As String
Dim mpNewFile As String
Dim mpFile As String

mpPath = ThisWorkbook.Path & Application.PathSeparator & "Backups" & Application.PathSeparator
Do
mpFile = Dir(mpPath & "*.*")
If mpFile <> "" Then
Kill mpPath & mpFile
End If
Loop Until mpFile = ""
mpNewFile = mpPath & Format(Date, "yyyymmdd ") & ThisWorkbook.Name
ActiveWorkbook.SaveCopyAs mpNewFile

nTime = Date + 1 + TimeSerial(1, 0, 0)
End Sub


In ThisWorkbook



Private Sub Workbook_Open()
Call BackMeUp
End Sub

thomas.szwed
12-11-2007, 06:43 AM
So i assume the top bit goes in the 'Module 1' and the second bit in the 'This Workbook'?

Could you perhaps put some comments in like 'this to inform we what does each part of the code is doing?

Many Thanks

thomas.szwed
12-12-2007, 02:59 AM
R u out there XLD??? help!

thomas.szwed
12-12-2007, 03:35 AM
Code works fine.....but when you open the backup....it tries to run the 'BackMeUp' sub again......resulting in a runtime error. Can this be stopped?

It also runs everytime you open the workbook. Could it be coded to run at nite?

Bob Phillips
12-12-2007, 04:20 AM
Try this adjustment



Private Sub Workbook_Open()
Dim mpFilename As String
On Error Resume Next
If ThisWorkbook.Names("_filename").RefersTo = ThisWorkbook.Name Then
nTime = Date + 1 + TimeSerial(1, 0, 0)
Application.OnTime nTime, "BackMeUp"
End If
End Sub


and



Option Explicit

Public nTime As Double

Public Sub BackMeUp()
Dim mpPath As String
Dim mpNewFile As String
Dim mpFile As String

mpPath = ThisWorkbook.Path & Application.PathSeparator & "Backups" & Application.PathSeparator
Do
mpFile = Dir(mpPath & "*.*")
If mpFile <> "" Then
Kill mpPath & mpFile
End If
Loop Until mpFile = ""
mpNewFile = mpPath & Format(Date, "yyyymmdd ") & ThisWorkbook.Name
ThisWorkbook.Names.Add Name:="_filename", RefersTo:=ThisWorkbook.Name
ActiveWorkbook.SaveCopyAs mpNewFile

nTime = Date + 1 + TimeSerial(1, 0, 0)
Application.OnTime nTime, "BackMeUp"
End Sub

thomas.szwed
12-12-2007, 04:28 AM
Now it appears not to create a backup at all?

XLD just to check.....this creates a backup everytime the user opens the sheet. But if a backup in todays date has already been made then it doesnt make another one until it is opened tomorrow?

Bob Phillips
12-12-2007, 04:52 AM
No, it will schedule it and create it tomorrow at 1.00am.

Bob Phillips
12-12-2007, 04:53 AM
BTW, it assumes that you won't open the file yourself, otherwise it will just pile scheduled job on top of scheduled job. There is no way to get at the list of scheduled jobs.

thomas.szwed
12-12-2007, 04:57 AM
How does it schedule it? So it will do it even when the workbook is closed later tonight?

Bob Phillips
12-12-2007, 06:33 AM
Using OnTime.

Yes it will open it as long as the machine is not/has not been turned off.

thomas.szwed
12-12-2007, 06:38 AM
Its saved on a shared drive.......on a server that is always on? Every user here turns off their computers when the leave work.....will it still do it then?

2. How can i nominate where it saves the backup file to, which part of the code is this?

Bob Phillips
12-12-2007, 06:41 AM
1. Hardly. If the client machine is off, nothing runs.

2. Where it sets nTime.

thomas.szwed
12-12-2007, 06:45 AM
Ok......so your saying that the workbook has to be open on the clients machine? Or can it be closed?

And i have multiple users..........so wont it try and run for all of them?

How can i change to 4pm?

Bob Phillips
12-12-2007, 07:04 AM
I am saying that the machine that the workbook that will initiate the backup has to be on at all times, and then it will schedule a job to run at the pre-determined time. The workbook does not have to be open at that pre-determined time, it will get opened, but it might as well be; but Excel must be running continuously.

The multiple users can all run it if they like, migt be fun and games when they all try and tidy up the directory and then save, results might be a tad unpredictable.

Could you run the backup job on the server?



nTime = TimeSerial(16, 0, 0)

thomas.szwed
12-12-2007, 07:07 AM
Thanks.....thats something i could look into.....could you explain the basics as to how it would be run on the server?

thomas.szwed
12-12-2007, 07:11 AM
I think this may be a tad hard to automate..........

I think i might resort to a msg box as a reminder. I know that i can schedule a msg reminder for a day but can i schedule it to run every day say between the hours 16-17:00 everytime a user opens the workbook in that time window?

Bob Phillips
12-12-2007, 07:12 AM
Have a simple Excel workbook that runs on the server using Ontime make the backup. As long as you have Excel on the server ...

figment
12-12-2007, 07:19 AM
it looks like the best option is to just check to see if a backup file exists with todays date, and if it dose then do nothing, if it dosn't then make one. then you only need to decide if you wish to run this code when the workbook opens or closes.

Private Sub Workbook_Open()
Dim fil As Object
Dim fpath As String
Dim today As Long, thismonth As Long, thisyear As Long
today = day(Now)
thismonth = month(Now)
thisyear = year(Now)
fpath = "\\ukyorw09\HR\IAC New Starter Tracking\IAC\Application\Backup\" & ActiveWorkbook.Name
Set fil = CreateObject("Scripting.FileSystemObject")
If fil.fileexists(Left(fpath, Len(fpath) - 4) & today & thismonth & thisyear & Right(fpath, 4)) = False Then
Else
ActiveWorkbook.SaveCopyAs (Left(fpath, Len(fpath) - 4) & today & thismonth & thisyear & Right(fpath, 4))
End If
End Sub

Xld, i am sorry if this is efectivly what you have already posted, i am still in the process of looking up a few of the functions you are using.

thomas.szwed
12-12-2007, 07:37 AM
This code is exactly what i want but.....its not saving even where there is no other spreadsheet there with that date on it???

figment
12-12-2007, 08:09 AM
sorry i was doing some cut and paste coding, and left the else in the the if statment that should work

Private Sub Workbook_Open()
Dim fil As Object
Dim fpath As String
Dim today As Long, thismonth As Long, thisyear As Long
today = Day(Now)
thismonth = Month(Now)
thisyear = Year(Now)
fpath = "\\ukyorw09\HR\IAC New Starter Tracking\IAC\Application\Backup\" & ActiveWorkbook.Name
Set fil = CreateObject("Scripting.FileSystemObject")
If fil.fileexists(Left(fpath, Len(fpath) - 4) & today & thismonth & thisyear & Right(fpath, 4)) = False Then
ActiveWorkbook.SaveCopyAs (Left(fpath, Len(fpath) - 4) & today & thismonth & thisyear & Right(fpath, 4))
End If
End Sub

thomas.szwed
12-12-2007, 08:21 AM
THis is perfecto - thanku