A question in need of an answer

[ME]

I have put some VBA code into a workbook to enable the ladies in the office
to back things up without having to remember more than "left-click the
button marked 'Backup' ". I have come to realize this was a bit more than
they can handle, so I'm rewriting the code to try to make it even easier.
While doing so, I came across an idea that I need info on...is it possible,
using vba, to create a new folder (say, naming it the date of the backup)
for each time they make the backups? If so, any examples of how?

I've posted this before doing much research, but will see what I can find
other places.

TIA,

Michael

 

[Nicky]

this code prompts the user to create a sub directory in "c:\backups\
named with today's date, and saves a copy of the current file into tha
directory

Sub backup_file()

pth = "c:\backups\" & Format(Now(), "dd_mmm_yy") & "\"
pth = InputBox("Enter a directory to backup the files", "typ
directory", pth)
On Error Resume Next
ChDir pth
If Error = "Path not found" Then
md = MsgBox("Directory does not exist. Do you want to creat
it?", vbYesNo)
End If
pths = Left(pth, Len(pth) - 1)
If md = 6 Then MkDir pth

f_new = pth & ActiveWorkbook.Name
ActiveWorkbook.SaveCopyAs f_new
End Su

 

[Bob Phillips]

If the users can't handle a button click, the 2 inputs of this routine are
asking too much. Plus there is an error in the variables (why don't you use
Option Explicit and declare the variables), so I have amended the routine
slight;y

Sub backup_file()
Dim pth As String

pth = "c:\backups\" & Format(Date, "dd_mmm_yyyy") & "\"
On Error Resume Next
ChDir pth
If Error = "Path not found" Then
MkDir pth
End If
On Error GoTo 0
ActiveWorkbook.SaveCopyAs pth & ActiveWorkbook.Name
End Sub

It cam also be automated by putting the code in the Workbok_BeforeClose
event.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[ME]

If the users can't handle a button click, the 2 inputs of this routine
are asking too much. Plus there is an error in the variables (why
don't you use Option Explicit and declare the variables), so I have
amended the routine slight;y

Sub backup_file()
Dim pth As String

pth = "c:\backups\" & Format(Date, "dd_mmm_yyyy") & "\"
On Error Resume Next
ChDir pth
If Error = "Path not found" Then
MkDir pth
End If
On Error GoTo 0
ActiveWorkbook.SaveCopyAs pth & ActiveWorkbook.Name
End Sub

It cam also be automated by putting the code in the
Workbok_BeforeClose event.

This is EXACTLY what I needed! Thanks alot! (My server never got the
reply you replied to, but I feel confident that I would have come close
to your solution if I had.)

Your automation idea was along the lines of what I was thinking of, and
so I give you 1,000,000 bonus points for perfect answer to my problem.
Congrats, and again, a hearty thank you and God bless


Michael