Username Log

Hi,
I'm using the following macro in the ThisWorkbook module which creates a log
when somebody opens a file:

Private Sub Workbook_Open()
Open ThisWorkbook.Path & "\usage.log" For Append As #1
Print #1, Environ("username"), Now, ThisWorkbook.FullName
Close #1
End Sub

It works perfectly, but I only have it in a template. My question
therefore, is can the macro be adapted so that if someone opens the template,
does File - Save As and renames it, this new file is also listed on the log
without having to re-open it?

Hope this makes sense!

Try this

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim sFilename As String
If SaveAsUI Then
Cancel = True
sFilename = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Excel Files (*.xls), *.xls")
If sFilename <> "False" Then
ThisWorkbook.SaveAs sFilename
Open ThisWorkbook.Path & "\usage.log" For Append As #1
Print #1, Environ("username"), Now, ThisWorkbook.FullName
Close #1
End If
End If
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"PaulJ" <PaulJ@discussions.microsoft.com> wrote in message
news:63BCA8FD-4BCD-4590-B6C6-99DC2429A380@microsoft.com...
> Hi,
> I'm using the following macro in the ThisWorkbook module which creates a
log
> when somebody opens a file:
>
> Private Sub Workbook_Open()
> Open ThisWorkbook.Path & "\usage.log" For Append As #1
> Print #1, Environ("username"), Now, ThisWorkbook.FullName
> Close #1
> End Sub
>
> It works perfectly, but I only have it in a template. My question
> therefore, is can the macro be adapted so that if someone opens the
template,
> does File - Save As and renames it, this new file is also listed on the
log
> without having to re-open it?
>
> Hope this makes sense!

Thanks Bob, but it's not working as I would like just yet. I don't know much
VBA so the syntax below doesn't mean a great deal to me, but maybe I should
explain further...

If the code was in a file named Template, then on opening this file a log is
created containing my username, time and filename. If I then saved this
Template as Template2 I require the details of this file to also appear on
the same log as soon as it is created.

Thanks :-)
"Bob Phillips" wrote:

> Try this
>
> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> Boolean)
> Dim sFilename As String
> If SaveAsUI Then
> Cancel = True
> sFilename = Application.GetSaveAsFilename( _
> fileFilter:="Microsoft Excel Files (*.xls), *.xls")
> If sFilename <> "False" Then
> ThisWorkbook.SaveAs sFilename
> Open ThisWorkbook.Path & "\usage.log" For Append As #1
> Print #1, Environ("username"), Now, ThisWorkbook.FullName
> Close #1
> End If
> End If
> End Sub
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "PaulJ" <PaulJ@discussions.microsoft.com> wrote in message
> news:63BCA8FD-4BCD-4590-B6C6-99DC2429A380@microsoft.com...
> > Hi,
> > I'm using the following macro in the ThisWorkbook module which creates a
> log
> > when somebody opens a file:
> >
> > Private Sub Workbook_Open()
> > Open ThisWorkbook.Path & "\usage.log" For Append As #1
> > Print #1, Environ("username"), Now, ThisWorkbook.FullName
> > Close #1
> > End Sub
> >
> > It works perfectly, but I only have it in a template. My question
> > therefore, is can the macro be adapted so that if someone opens the
> template,
> > does File - Save As and renames it, this new file is also listed on the
> log
> > without having to re-open it?
> >
> > Hope this makes sense!
>
>
>

So what is happening in your tests?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"PaulJ" <PaulJ@discussions.microsoft.com> wrote in message
news:C803C4E7-5B68-4317-B3AB-15A89C7FD9A9@microsoft.com...
> Thanks Bob, but it's not working as I would like just yet. I don't know
much
> VBA so the syntax below doesn't mean a great deal to me, but maybe I
should
> explain further...
>
> If the code was in a file named Template, then on opening this file a log
is
> created containing my username, time and filename. If I then saved this
> Template as Template2 I require the details of this file to also appear on
> the same log as soon as it is created.
>
> Thanks :-)
> "Bob Phillips" wrote:
>
> > Try this
> >
> > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> > Boolean)
> > Dim sFilename As String
> > If SaveAsUI Then
> > Cancel = True
> > sFilename = Application.GetSaveAsFilename( _
> > fileFilter:="Microsoft Excel Files (*.xls), *.xls")
> > If sFilename <> "False" Then
> > ThisWorkbook.SaveAs sFilename
> > Open ThisWorkbook.Path & "\usage.log" For Append As #1
> > Print #1, Environ("username"), Now, ThisWorkbook.FullName
> > Close #1
> > End If
> > End If
> > End Sub
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "PaulJ" <PaulJ@discussions.microsoft.com> wrote in message
> > news:63BCA8FD-4BCD-4590-B6C6-99DC2429A380@microsoft.com...
> > > Hi,
> > > I'm using the following macro in the ThisWorkbook module which creates
a
> > log
> > > when somebody opens a file:
> > >
> > > Private Sub Workbook_Open()
> > > Open ThisWorkbook.Path & "\usage.log" For Append As #1
> > > Print #1, Environ("username"), Now, ThisWorkbook.FullName
> > > Close #1
> > > End Sub
> > >
> > > It works perfectly, but I only have it in a template. My question
> > > therefore, is can the macro be adapted so that if someone opens the
> > template,
> > > does File - Save As and renames it, this new file is also listed on
the
> > log
> > > without having to re-open it?
> > >
> > > Hope this makes sense!
> >
> >
> >

Nothing is going into the log at all right now. Maybe the problem is the
Private Sub title, which is showing up in red text? When I open the file I'm
not getting the macro security warning, so I guess the macro is not being
recognised?

"Bob Phillips" wrote:

> So what is happening in your tests?
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "PaulJ" <PaulJ@discussions.microsoft.com> wrote in message
> news:C803C4E7-5B68-4317-B3AB-15A89C7FD9A9@microsoft.com...
> > Thanks Bob, but it's not working as I would like just yet. I don't know
> much
> > VBA so the syntax below doesn't mean a great deal to me, but maybe I
> should
> > explain further...
> >
> > If the code was in a file named Template, then on opening this file a log
> is
> > created containing my username, time and filename. If I then saved this
> > Template as Template2 I require the details of this file to also appear on
> > the same log as soon as it is created.
> >
> > Thanks :-)
> > "Bob Phillips" wrote:
> >
> > > Try this
> > >
> > > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> > > Boolean)
> > > Dim sFilename As String
> > > If SaveAsUI Then
> > > Cancel = True
> > > sFilename = Application.GetSaveAsFilename( _
> > > fileFilter:="Microsoft Excel Files (*.xls), *.xls")
> > > If sFilename <> "False" Then
> > > ThisWorkbook.SaveAs sFilename
> > > Open ThisWorkbook.Path & "\usage.log" For Append As #1
> > > Print #1, Environ("username"), Now, ThisWorkbook.FullName
> > > Close #1
> > > End If
> > > End If
> > > End Sub
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove nothere from email address if mailing direct)
> > >
> > > "PaulJ" <PaulJ@discussions.microsoft.com> wrote in message
> > > news:63BCA8FD-4BCD-4590-B6C6-99DC2429A380@microsoft.com...
> > > > Hi,
> > > > I'm using the following macro in the ThisWorkbook module which creates
> a
> > > log
> > > > when somebody opens a file:
> > > >
> > > > Private Sub Workbook_Open()
> > > > Open ThisWorkbook.Path & "\usage.log" For Append As #1
> > > > Print #1, Environ("username"), Now, ThisWorkbook.FullName
> > > > Close #1
> > > > End Sub
> > > >
> > > > It works perfectly, but I only have it in a template. My question
> > > > therefore, is can the macro be adapted so that if someone opens the
> > > template,
> > > > does File - Save As and renames it, this new file is also listed on
> the
> > > log
> > > > without having to re-open it?
> > > >
> > > > Hope this makes sense!
> > >
> > >
> > >
>
>
>

Paul,

I think it is just NG wrap-around problems. It certainly works, I tested it
(after you replied <vbg>).

Try this update

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim sFilename As String
If SaveAsUI Then
Cancel = True
sFilename = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Excel Files (*.xls), *.xls")
If sFilename <> "False" Then
ThisWorkbook.SaveAs sFilename
Open ThisWorkbook.Path & "\usage.log" For Append As #1
Print #1, Environ("username"), Now, ThisWorkbook.FullName
Close #1
End If
End If
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"PaulJ" <PaulJ@discussions.microsoft.com> wrote in message
news:EDC16B21-423F-4CE1-B52E-C1917C3C45C8@microsoft.com...
> Nothing is going into the log at all right now. Maybe the problem is the
> Private Sub title, which is showing up in red text? When I open the file
I'm
> not getting the macro security warning, so I guess the macro is not being
> recognised?
>
> "Bob Phillips" wrote:
>
> > So what is happening in your tests?
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "PaulJ" <PaulJ@discussions.microsoft.com> wrote in message
> > news:C803C4E7-5B68-4317-B3AB-15A89C7FD9A9@microsoft.com...
> > > Thanks Bob, but it's not working as I would like just yet. I don't
know
> > much
> > > VBA so the syntax below doesn't mean a great deal to me, but maybe I
> > should
> > > explain further...
> > >
> > > If the code was in a file named Template, then on opening this file a
log
> > is
> > > created containing my username, time and filename. If I then saved
this
> > > Template as Template2 I require the details of this file to also
appear on
> > > the same log as soon as it is created.
> > >
> > > Thanks :-)
> > > "Bob Phillips" wrote:
> > >
> > > > Try this
> > > >
> > > > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> > > > Boolean)
> > > > Dim sFilename As String
> > > > If SaveAsUI Then
> > > > Cancel = True
> > > > sFilename = Application.GetSaveAsFilename( _
> > > > fileFilter:="Microsoft Excel Files (*.xls), *.xls")
> > > > If sFilename <> "False" Then
> > > > ThisWorkbook.SaveAs sFilename
> > > > Open ThisWorkbook.Path & "\usage.log" For Append As #1
> > > > Print #1, Environ("username"), Now,
ThisWorkbook.FullName
> > > > Close #1
> > > > End If
> > > > End If
> > > > End Sub
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (remove nothere from email address if mailing direct)
> > > >
> > > > "PaulJ" <PaulJ@discussions.microsoft.com> wrote in message
> > > > news:63BCA8FD-4BCD-4590-B6C6-99DC2429A380@microsoft.com...
> > > > > Hi,
> > > > > I'm using the following macro in the ThisWorkbook module which
creates
> > a
> > > > log
> > > > > when somebody opens a file:
> > > > >
> > > > > Private Sub Workbook_Open()
> > > > > Open ThisWorkbook.Path & "\usage.log" For Append As #1
> > > > > Print #1, Environ("username"), Now, ThisWorkbook.FullName
> > > > > Close #1
> > > > > End Sub
> > > > >
> > > > > It works perfectly, but I only have it in a template. My question
> > > > > therefore, is can the macro be adapted so that if someone opens
the
> > > > template,
> > > > > does File - Save As and renames it, this new file is also listed
on
> > the
> > > > log
> > > > > without having to re-open it?
> > > > >
> > > > > Hope this makes sense!
> > > >
> > > >
> > > >
> >
> >
> >

Thanks Bob that certainly seems to have done the trick. Just one final thing
though! The saved file is appearing in the log but not the original
template...is there a way to have both?

Thanks for your patience!
Paul

"Bob Phillips" wrote:

> Paul,
>
> I think it is just NG wrap-around problems. It certainly works, I tested it
> (after you replied <vbg>).
>
> Try this update
>
> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
> Cancel As Boolean)
> Dim sFilename As String
> If SaveAsUI Then
> Cancel = True
> sFilename = Application.GetSaveAsFilename( _
> fileFilter:="Microsoft Excel Files (*.xls), *.xls")
> If sFilename <> "False" Then
> ThisWorkbook.SaveAs sFilename
> Open ThisWorkbook.Path & "\usage.log" For Append As #1
> Print #1, Environ("username"), Now, ThisWorkbook.FullName
> Close #1
> End If
> End If
> End Sub
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "PaulJ" <PaulJ@discussions.microsoft.com> wrote in message
> news:EDC16B21-423F-4CE1-B52E-C1917C3C45C8@microsoft.com...
> > Nothing is going into the log at all right now. Maybe the problem is the
> > Private Sub title, which is showing up in red text? When I open the file
> I'm
> > not getting the macro security warning, so I guess the macro is not being
> > recognised?
> >
> > "Bob Phillips" wrote:
> >
> > > So what is happening in your tests?
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove nothere from email address if mailing direct)
> > >
> > > "PaulJ" <PaulJ@discussions.microsoft.com> wrote in message
> > > news:C803C4E7-5B68-4317-B3AB-15A89C7FD9A9@microsoft.com...
> > > > Thanks Bob, but it's not working as I would like just yet. I don't
> know
> > > much
> > > > VBA so the syntax below doesn't mean a great deal to me, but maybe I
> > > should
> > > > explain further...
> > > >
> > > > If the code was in a file named Template, then on opening this file a
> log
> > > is
> > > > created containing my username, time and filename. If I then saved
> this
> > > > Template as Template2 I require the details of this file to also
> appear on
> > > > the same log as soon as it is created.
> > > >
> > > > Thanks :-)
> > > > "Bob Phillips" wrote:
> > > >
> > > > > Try this
> > > > >
> > > > > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> > > > > Boolean)
> > > > > Dim sFilename As String
> > > > > If SaveAsUI Then
> > > > > Cancel = True
> > > > > sFilename = Application.GetSaveAsFilename( _
> > > > > fileFilter:="Microsoft Excel Files (*.xls), *.xls")
> > > > > If sFilename <> "False" Then
> > > > > ThisWorkbook.SaveAs sFilename
> > > > > Open ThisWorkbook.Path & "\usage.log" For Append As #1
> > > > > Print #1, Environ("username"), Now,
> ThisWorkbook.FullName
> > > > > Close #1
> > > > > End If
> > > > > End If
> > > > > End Sub
> > > > >
> > > > > --
> > > > > HTH
> > > > >
> > > > > Bob Phillips
> > > > >
> > > > > (remove nothere from email address if mailing direct)
> > > > >
> > > > > "PaulJ" <PaulJ@discussions.microsoft.com> wrote in message
> > > > > news:63BCA8FD-4BCD-4590-B6C6-99DC2429A380@microsoft.com...
> > > > > > Hi,
> > > > > > I'm using the following macro in the ThisWorkbook module which
> creates
> > > a
> > > > > log
> > > > > > when somebody opens a file:
> > > > > >
> > > > > > Private Sub Workbook_Open()
> > > > > > Open ThisWorkbook.Path & "\usage.log" For Append As #1
> > > > > > Print #1, Environ("username"), Now, ThisWorkbook.FullName
> > > > > > Close #1
> > > > > > End Sub
> > > > > >
> > > > > > It works perfectly, but I only have it in a template. My question
> > > > > > therefore, is can the macro be adapted so that if someone opens
> the
> > > > > template,
> > > > > > does File - Save As and renames it, this new file is also listed
> on
> > > the
> > > > > log
> > > > > > without having to re-open it?
> > > > > >
> > > > > > Hope this makes sense!
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim sFilename As String
If SaveAsUI Then
Cancel = True
sFilename = Application.GetSaveAsFilename( _
fileFilter:="Microsoft Excel Files (*.xls), *.xls")
If sFilename <> "False" Then
Open ThisWorkbook.Path & "\usage.log" For Append As #1
Print #1, Environ("username"), Now, ThisWorkbook.FullName
ThisWorkbook.SaveAs sFilename
Print #1, Environ("username"), Now, ThisWorkbook.FullName
Close #1
End If
End If
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:%23OydACSPGHA.2828@TK2MSFTNGP12.phx.gbl...
> Paul,
>
> I think it is just NG wrap-around problems. It certainly works, I tested
it
> (after you replied <vbg>).
>
> Try this update
>
> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
> Cancel As Boolean)
> Dim sFilename As String
> If SaveAsUI Then
> Cancel = True
> sFilename = Application.GetSaveAsFilename( _
> fileFilter:="Microsoft Excel Files (*.xls), *.xls")
> If sFilename <> "False" Then
> ThisWorkbook.SaveAs sFilename
> Open ThisWorkbook.Path & "\usage.log" For Append As #1
> Print #1, Environ("username"), Now, ThisWorkbook.FullName
> Close #1
> End If
> End If
> End Sub
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "PaulJ" <PaulJ@discussions.microsoft.com> wrote in message
> news:EDC16B21-423F-4CE1-B52E-C1917C3C45C8@microsoft.com...
> > Nothing is going into the log at all right now. Maybe the problem is
the
> > Private Sub title, which is showing up in red text? When I open the
file
> I'm
> > not getting the macro security warning, so I guess the macro is not
being
> > recognised?
> >
> > "Bob Phillips" wrote:
> >
> > > So what is happening in your tests?
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove nothere from email address if mailing direct)
> > >
> > > "PaulJ" <PaulJ@discussions.microsoft.com> wrote in message
> > > news:C803C4E7-5B68-4317-B3AB-15A89C7FD9A9@microsoft.com...
> > > > Thanks Bob, but it's not working as I would like just yet. I don't
> know
> > > much
> > > > VBA so the syntax below doesn't mean a great deal to me, but maybe I
> > > should
> > > > explain further...
> > > >
> > > > If the code was in a file named Template, then on opening this file
a
> log
> > > is
> > > > created containing my username, time and filename. If I then saved
> this
> > > > Template as Template2 I require the details of this file to also
> appear on
> > > > the same log as soon as it is created.
> > > >
> > > > Thanks :-)
> > > > "Bob Phillips" wrote:
> > > >
> > > > > Try this
> > > > >
> > > > > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel
As
> > > > > Boolean)
> > > > > Dim sFilename As String
> > > > > If SaveAsUI Then
> > > > > Cancel = True
> > > > > sFilename = Application.GetSaveAsFilename( _
> > > > > fileFilter:="Microsoft Excel Files (*.xls), *.xls")
> > > > > If sFilename <> "False" Then
> > > > > ThisWorkbook.SaveAs sFilename
> > > > > Open ThisWorkbook.Path & "\usage.log" For Append As #1
> > > > > Print #1, Environ("username"), Now,
> ThisWorkbook.FullName
> > > > > Close #1
> > > > > End If
> > > > > End If
> > > > > End Sub
> > > > >
> > > > > --
> > > > > HTH
> > > > >
> > > > > Bob Phillips
> > > > >
> > > > > (remove nothere from email address if mailing direct)
> > > > >
> > > > > "PaulJ" <PaulJ@discussions.microsoft.com> wrote in message
> > > > > news:63BCA8FD-4BCD-4590-B6C6-99DC2429A380@microsoft.com...
> > > > > > Hi,
> > > > > > I'm using the following macro in the ThisWorkbook module which
> creates
> > > a
> > > > > log
> > > > > > when somebody opens a file:
> > > > > >
> > > > > > Private Sub Workbook_Open()
> > > > > > Open ThisWorkbook.Path & "\usage.log" For Append As #1
> > > > > > Print #1, Environ("username"), Now, ThisWorkbook.FullName
> > > > > > Close #1
> > > > > > End Sub
> > > > > >
> > > > > > It works perfectly, but I only have it in a template. My
question
> > > > > > therefore, is can the macro be adapted so that if someone opens
> the
> > > > > template,
> > > > > > does File - Save As and renames it, this new file is also listed
> on
> > > the
> > > > > log
> > > > > > without having to re-open it?
> > > > > >
> > > > > > Hope this makes sense!
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>

Fantastic! Thanks very much Bob

"Bob Phillips" wrote:

> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
> Cancel As Boolean)
> Dim sFilename As String
> If SaveAsUI Then
> Cancel = True
> sFilename = Application.GetSaveAsFilename( _
> fileFilter:="Microsoft Excel Files (*.xls), *.xls")
> If sFilename <> "False" Then
> Open ThisWorkbook.Path & "\usage.log" For Append As #1
> Print #1, Environ("username"), Now, ThisWorkbook.FullName
> ThisWorkbook.SaveAs sFilename
> Print #1, Environ("username"), Now, ThisWorkbook.FullName
> Close #1
> End If
> End If
> End Sub
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:%23OydACSPGHA.2828@TK2MSFTNGP12.phx.gbl...
> > Paul,
> >
> > I think it is just NG wrap-around problems. It certainly works, I tested
> it
> > (after you replied <vbg>).
> >
> > Try this update
> >
> > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
> > Cancel As Boolean)
> > Dim sFilename As String
> > If SaveAsUI Then
> > Cancel = True
> > sFilename = Application.GetSaveAsFilename( _
> > fileFilter:="Microsoft Excel Files (*.xls), *.xls")
> > If sFilename <> "False" Then
> > ThisWorkbook.SaveAs sFilename
> > Open ThisWorkbook.Path & "\usage.log" For Append As #1
> > Print #1, Environ("username"), Now, ThisWorkbook.FullName
> > Close #1
> > End If
> > End If
> > End Sub
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "PaulJ" <PaulJ@discussions.microsoft.com> wrote in message
> > news:EDC16B21-423F-4CE1-B52E-C1917C3C45C8@microsoft.com...
> > > Nothing is going into the log at all right now. Maybe the problem is
> the
> > > Private Sub title, which is showing up in red text? When I open the
> file
> > I'm
> > > not getting the macro security warning, so I guess the macro is not
> being
> > > recognised?
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > So what is happening in your tests?
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (remove nothere from email address if mailing direct)
> > > >
> > > > "PaulJ" <PaulJ@discussions.microsoft.com> wrote in message
> > > > news:C803C4E7-5B68-4317-B3AB-15A89C7FD9A9@microsoft.com...
> > > > > Thanks Bob, but it's not working as I would like just yet. I don't
> > know
> > > > much
> > > > > VBA so the syntax below doesn't mean a great deal to me, but maybe I
> > > > should
> > > > > explain further...
> > > > >
> > > > > If the code was in a file named Template, then on opening this file
> a
> > log
> > > > is
> > > > > created containing my username, time and filename. If I then saved
> > this
> > > > > Template as Template2 I require the details of this file to also
> > appear on
> > > > > the same log as soon as it is created.
> > > > >
> > > > > Thanks :-)
> > > > > "Bob Phillips" wrote:
> > > > >
> > > > > > Try this
> > > > > >
> > > > > > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel
> As
> > > > > > Boolean)
> > > > > > Dim sFilename As String
> > > > > > If SaveAsUI Then
> > > > > > Cancel = True
> > > > > > sFilename = Application.GetSaveAsFilename( _
> > > > > > fileFilter:="Microsoft Excel Files (*.xls), *.xls")
> > > > > > If sFilename <> "False" Then
> > > > > > ThisWorkbook.SaveAs sFilename
> > > > > > Open ThisWorkbook.Path & "\usage.log" For Append As #1
> > > > > > Print #1, Environ("username"), Now,
> > ThisWorkbook.FullName
> > > > > > Close #1
> > > > > > End If
> > > > > > End If
> > > > > > End Sub
> > > > > >
> > > > > > --
> > > > > > HTH
> > > > > >
> > > > > > Bob Phillips
> > > > > >
> > > > > > (remove nothere from email address if mailing direct)
> > > > > >
> > > > > > "PaulJ" <PaulJ@discussions.microsoft.com> wrote in message
> > > > > > news:63BCA8FD-4BCD-4590-B6C6-99DC2429A380@microsoft.com...
> > > > > > > Hi,
> > > > > > > I'm using the following macro in the ThisWorkbook module which
> > creates
> > > > a
> > > > > > log
> > > > > > > when somebody opens a file:
> > > > > > >
> > > > > > > Private Sub Workbook_Open()
> > > > > > > Open ThisWorkbook.Path & "\usage.log" For Append As #1
> > > > > > > Print #1, Environ("username"), Now, ThisWorkbook.FullName
> > > > > > > Close #1
> > > > > > > End Sub
> > > > > > >
> > > > > > > It works perfectly, but I only have it in a template. My
> question
> > > > > > > therefore, is can the macro be adapted so that if someone opens
> > the
> > > > > > template,
> > > > > > > does File - Save As and renames it, this new file is also listed
> > on
> > > > the
> > > > > > log
> > > > > > > without having to re-open it?
> > > > > > >
> > > > > > > Hope this makes sense!
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
>
>
>