Username Log

[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!

 

[Bob Phillips]

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)

 

[Guest]

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]

So what is happening in your tests?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

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]

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)

 

[Guest]

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]

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)

 

[Guest]

Fantastic! Thanks very much Bob

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)