Username Log

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)
 
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 :-)
 
So what is happening in your tests?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
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?
 
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)
 
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
 
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)
 
Fantastic! Thanks very much Bob :)

Bob Phillips said:
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)
Click to expand...
 
Back
Top