User defined file name on save
Thank's Bob, this is sensational. You're a true gentleman.
I'd send some bubbly liquid if I new where!
PS:I'm still going to read that book.
Cheers
--
Jim
"Bob Phillips" wrote:
Jim,
That should be easy. I have to go carpet shopping soon, but I will knock up
a userform that does this later today and post it on the net for you and we
can work it through.
Sub Testing()
Const sBackup As String = "C:\Backup\" '<=== change to suit
Dim sDir As String
Dim sFileFirst As String
Dim sFileDate As String
Dim sFilename As String
Dim dteFile As Date
Dim fExitDo As Boolean
MsgBox "Select the start directory, " & vbNewLine & _
"then supply first part of file name, " & vbNewLine & _
"and finally the date suffix"
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.Show
sDir = .SelectedItems(1)
End With
'<<<<< next line commented out (you can remove)
'sDir = sDir & "\" & Format(Date, "mmmyy")
sFileFirst = InputBox("File prefix")
fExitDo = False
Do
sFileDate = InputBox("File date suffix (in the form 25/10/2006)")
On Error Resume Next
dteFile = CDate(sFileDate)
On Error GoTo 0
If sFileDate = "" Then
fExitDo = True
ElseIf dteFile < 0 Then
fExitDo = True
Else
MsgBox "Invalid date, please re-submit"
End If
Loop Until dteFile < 0 Or sFileDate = ""
If sFileDate = "" Then Exit Sub
'<<<<<< and added here a little differently
sDir = sDir & "\" & Format(CDate(sFileDate), "mmmyy")
On Error Resume Next
MkDir sDir
On Error GoTo 0
sFilename = sFileFirst & Format(CDate(sFileDate), "yyyymmdd") & ".xls"
If Dir(sDir & "\" & sFilename) < "" Then
If MsgBox("Overwrite file?", vbYesNo) = vbYes Then
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs sDir & "\" & sFilename
Application.DisplayAlerts = True
ActiveWorkbook.SaveCopyAs sBackup & sFilename
End If
Else
ActiveWorkbook.SaveAs sDir & "\" & sFilename
ActiveWorkbook.SaveCopyAs sBackup & sFilename
End If
End Sub
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"Jim G" wrote in message
...
Hehe...it looks like I've been set some homework.
I'll have a look at the Filedialog help. I like the idea of a form to set
the correct parameters/choices. Is this in the VBA help area as well?
I've
purchased the book "Microsoft Excel VBA Porgramming-for the absolute
beginner". Looks like I'll need to read it now! I would appreciate any
pointers on other leaning resources. Thanks to Microsoft and generous
souls
such as yourself, I've learned much from these forums and bookmarked many
excellent web sites.
In the meantime, and to get my template working ASAP, would it be possible
to create the Dir from the input rather than the current date?
Thanks again.
--
Jim
"Bob Phillips" wrote:
Jim,
I would personally create the new directory automatically as we now do,
and
then let them go find the save directory, using Application.Filedialog.
Of
course it does mean that they could choose Feb directory in September,
but
how far can you legislate for sloppiness. There is a good example on
Filedialog in VBA help, take a look, and if you get stuck, post back.
Another (better IMO) option is to build a userform with all the inputs
required and then you can check it all in one block and then release the
code to do its stuff. You could even have a checkbox to say 'Save to
this
month or previous month' and take the possibility of an error away
completely.
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"Jim G" wrote in message
...
Bob, you're a legend!
This is powerful stuff. I'm having quite bit of fun changing the bits
I
understand.
One issue I have is with the creation of the default directory.
My users often continue to populate the template after the end of the
month
the transaction relates to (It's a credit card expense form that is
imported
to the accounts). They have a choice of progressively entering
expenses
through the month or complete all at once at the end. You can guess
the
popular option.
When the code runs in the same month it creates a Dir once for that
month
and saves that and subsequent files to the same Dir and a back-up in
the
parent Dir. If a file has the same name it asks to overwrite as an
alert.
This is all good. However, when the month rolls over, the working
file
from
the old month is saved into the Dir created for the new month. Is
there a
solution to this or would it be simpler to have the user create the
new
period Dir in the first instance, and then simply choose the location
on
subsequent saves?
Is there a way to have the saved copy save in a location other than
the
parent directory? I'm considering a location that the users would not
be
aware of.
Thanks again Bob, I've learned a lot from this.
--
Jim
"Bob Phillips" wrote:
Sure can Jim.
Sub Testing()
Const sBackup As String = "C:\Backup\" '<=== change to suit
Dim sDir As String
Dim sFileFirst As String
Dim sFileDate As String
Dim sFilename As String
Dim dteFile As Date
Dim fExitDo As Boolean
MsgBox "Select the start directory, " & vbNewLine & _
"then supply first part of file name, " & vbNewLine & _
"and finally the date suffix"
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.Show
sDir = .SelectedItems(1)
End With
sDir = sDir & "\" & Format(Date, "mmmyy")
sFileFirst = InputBox("File prefix")
fExitDo = False
Do
sFileDate = InputBox("File date suffix (in the form
25/10/2006)")
On Error Resume Next
dteFile = CDate(sFileDate)
On Error GoTo 0
If sFileDate = "" Then
fExitDo = True
ElseIf dteFile < 0 Then
fExitDo = True
Else
MsgBox "Invalid date, please re-submit"
End If
Loop Until dteFile < 0 Or sFileDate = ""
If sFileDate = "" Then Exit Sub
On Error Resume Next
MkDir sDir
On Error GoTo 0
sFilename = sFileFirst & Format(CDate(sFileDate), "yyyymmdd") &
".xls"
If Dir(sDir & "\" & sFilename) < "" Then
If MsgBox("Overwrite file?", vbYesNo) = vbYes Then
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs sDir & "\" & sFilename
Application.DisplayAlerts = True
ActiveWorkbook.SaveCopyAs sBackup & sFilename
End If
Else
ActiveWorkbook.SaveAs sDir & "\" & sFilename
ActiveWorkbook.SaveCopyAs sBackup & sFilename
End If
End Sub
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"Jim G" wrote in message
...
Works great Bob thanks,
I added an example to the input box message "eg; 25/10/06" to
esure a
full
date.
Is there a way to trap the error and remind the user to use the
correct
format?
--
Jim
"Bob Phillips" wrote:
My code assumed a full date and worked out the format. I think
that
is
safer
rather than expecting users to get the format right.
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing
direct)
"Jim G" wrote in message
...
Thanks Bob,
I ran the code and was presented with the Bowser to select the
directory
(I
created a C:\Backup to test). I'll need to instruct users to
select
this
parent directory.
I then was asked for file prefix, which I supplied, and a date
prefix
where
I entered Oct06. Error 13 occured indcating a 'type mismatch'
|