I have workbook named "test.xls" which has various sheets.
The workbook is not protected and can be accessed by any
user on the network. I need to log spesific cell information
on a seperate xl workbook automatically (log.xls) without
any indication of the process each and every time before
the "test.xls" workbook is closed.
Here are the columns of "test.xls" that needs to be logged:
Sheet: 14 Cell: A4
Sheet: 14 Cell: A10
Sheet: 15 Cell: A9
Sheet: 12 Cell: A9
---------------------------
How should I code?
TIA

Hi
put the following code in your workbook module. You may have to change
the workbook/worksheet names and also have to adapt the specific
logging ranges to your needs:

-----

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim log_wbk As Workbook
Dim log_wks As Worksheet
Dim last_log_row As Long
Dim path As String
Dim log_filename As String
Dim source_wbk As Workbook
Dim source_wks As Worksheet

'Initialization
Application.ScreenUpdating = False
path = "D:\Temp\" 'change this
log_filename = "logging.xls" 'change this
Set source_wbk = ActiveWorkbook
Set source_wks = source_wbk.Worksheets("Files") 'change this

'check if logging workbook is open / if not open it
On Error Resume Next
Set log_wbk = Workbooks(log_filename)
On Error GoTo 0
If log_wbk Is Nothing Then
Workbooks.Open filename:=path & log_filename
Set log_wbk = Workbooks(log_filename)
End If
Set log_wks = log_wbk.Worksheets("sheet1") 'change this

'get last used row
last_log_row = log_wks.Cells(Rows.Count, "A").End(xlUp).row

'log data - change to your needs
With log_wks
.Cells(last_log_row + 1, 1).Value = Application.UserName
.Cells(last_log_row + 1, 2).Value = Format(Now, "MM/DD/YYYY
hh:mm:ss")
.Cells(last_log_row + 1, 3).Value = source_wks.Range("A1").Value
.Cells(last_log_row + 1, 4).Value = source_wks.Range("B1").Value
.Cells(last_log_row + 1, 5).Value = source_wks.Range("C1").Value
End With


' save the changes
Application.DisplayAlerts = True
log_wbk.Save
log_wbk.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


------


--
Regards
Frank Kabel
Frankfurt, Germany

Martyn wrote:
I have workbook named "test.xls" which has various sheets.
The workbook is not protected and can be accessed by any
user on the network. I need to log spesific cell information
on a seperate xl workbook automatically (log.xls) without
any indication of the process each and every time before
the "test.xls" workbook is closed.
Here are the columns of "test.xls" that needs to be logged:
Sheet: 14 Cell: A4
Sheet: 14 Cell: A10
Sheet: 15 Cell: A9
Sheet: 12 Cell: A9
---------------------------
How should I code?
TIA

Hi
forgot to add some points:
- The logging workbook has to exist - otherwise an error would occur
- The logging workbook should not contain any macros (as this could -
depending on the security settings - cause a warning dialog)
- You may add a check that the maximum row limit of 65536 rows is not
exceeded

On a side-note: Depending oy your company / country this kind of
logging could require that employees are informed about this activity
logging (at least in Germany this would be the case / and employee
delegates have to be informed)!. Sou you may check your specific legal
restrictions for doing this!!


--
Regards
Frank Kabel
Frankfurt, Germany

Hi Frank,
Thank you for the code and advise on legal issues which surely I will be
consider...
But need a little bit more explanation on the code if you can.
In your code you have commented for some lines as " 'change this "
referring to my spesific needs. But some confused me a little
(especially this one:
Set source_wks = source_wbk.Worksheets("Files") 'change this)

because the book name and sheets and cells which are going to be used are
stationary.
Thus I'd appreciate if you can use my names. As I have given in my question:
------------------------
The workbook which is going to be tracked is: "test.xls", it resides on:
"C:\"
The sheets of "test.xls" and cells which are to be read and saved from a
Sheet: 14 of "test.xls" Cell: A4
Sheet: 14 of "test.xls" Cell: A10
Sheet: 15 of "test.xls" Cell: A9
Sheet: 12 of "test.xls" Cell: A9
The workbook which'll be used for logging is: "log.xls" and it may reside
on: "D:\Temp"
----------------------------
Hope I've not asked too much.
Thanks a lot
Martyn


"Frank Kabel" wrote in message
...
Hi
forgot to add some points:
- The logging workbook has to exist - otherwise an error would occur
- The logging workbook should not contain any macros (as this could -
depending on the security settings - cause a warning dialog)
- You may add a check that the maximum row limit of 65536 rows is not
exceeded

On a side-note: Depending oy your company / country this kind of
logging could require that employees are informed about this activity
logging (at least in Germany this would be the case / and employee
delegates have to be informed)!. Sou you may check your specific legal
restrictions for doing this!!


--
Regards
Frank Kabel
Frankfurt, Germany

Hi
try the following - not fully tested as I didn't have the same
environment as you have and to be honest i'm a little bit lazy to
re-create it :-)
Put the following code in your test.xls workbook module. Create the
log.xls workbook prior to running this code. You may have to change the
names of your sorce worksheet as I'm not so sure about the names of
these sheets

-----
Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim log_wbk As Workbook
Dim log_wks As Worksheet
Dim last_log_row As Long
Dim path As String
Dim log_filename As String
Dim source_wbk As Workbook
Dim source_wks As Worksheet


'Initialization
Application.ScreenUpdating = False
path = "D:\Temp\"
log_filename = "log.xls"
Set source_wbk = ActiveWorkbook


'check if logging workbook is open / if not open it
On Error Resume Next
Set log_wbk = Workbooks(log_filename)
On Error GoTo 0
If log_wbk Is Nothing Then
Workbooks.Open filename:=path & log_filename
Set log_wbk = Workbooks(log_filename)
End If
Set log_wks = log_wbk.Worksheets("sheet1") 'change this

'get last used row
last_log_row = log_wks.Cells(Rows.Count, "A").End(xlUp).row

'log data - change to your needs
With log_wks
.Cells(last_log_row + 1, 1).Value = Application.UserName
.Cells(last_log_row + 1, 2).Value = _
Format(Now, "MM/DD/YYYY hh:mm:ss")
Set source_wks = source_wbk.Worksheets("sheet14")
.Cells(last_log_row + 1, 3).Value = source_wks.Range("A4").Value
.Cells(last_log_row + 1, 4).Value = source_wks.Range("A10").Value

Set source_wks = source_wbk.Worksheets("sheet15")
.Cells(last_log_row + 1, 5).Value = source_wks.Range("A9").Value

Set source_wks = source_wbk.Worksheets("sheet12")
.Cells(last_log_row + 1, 6).Value = source_wks.Range("A9").Value
End With


' save the changes
Application.DisplayAlerts = True
log_wbk.Save
log_wbk.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


------


--
Regards
Frank Kabel
Frankfurt, Germany

Hi,
log.xls and test.xls created but when trying to close test.xls as intended
I get a "Subscript out of range" error on line
---------
Set log_wbk = Workbooks(log_filename)
-----------
will let you know from there on
Ragards
Martyn


"Frank Kabel" wrote in message
...
Hi
try the following - not fully tested as I didn't have the same
environment as you have and to be honest i'm a little bit lazy to
re-create it :-)
Put the following code in your test.xls workbook module. Create the
log.xls workbook prior to running this code. You may have to change the
names of your sorce worksheet as I'm not so sure about the names of
these sheets

-----
Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim log_wbk As Workbook
Dim log_wks As Worksheet
Dim last_log_row As Long
Dim path As String
Dim log_filename As String
Dim source_wbk As Workbook
Dim source_wks As Worksheet


'Initialization
Application.ScreenUpdating = False
path = "D:\Temp\"
log_filename = "log.xls"
Set source_wbk = ActiveWorkbook


'check if logging workbook is open / if not open it
On Error Resume Next
Set log_wbk = Workbooks(log_filename)
On Error GoTo 0
If log_wbk Is Nothing Then
Workbooks.Open filename:=path & log_filename
Set log_wbk = Workbooks(log_filename)
End If
Set log_wks = log_wbk.Worksheets("sheet1") 'change this

'get last used row
last_log_row = log_wks.Cells(Rows.Count, "A").End(xlUp).row

'log data - change to your needs
With log_wks
.Cells(last_log_row + 1, 1).Value = Application.UserName
.Cells(last_log_row + 1, 2).Value = _
Format(Now, "MM/DD/YYYY hh:mm:ss")
Set source_wks = source_wbk.Worksheets("sheet14")
.Cells(last_log_row + 1, 3).Value = source_wks.Range("A4").Value
.Cells(last_log_row + 1, 4).Value = source_wks.Range("A10").Value

Set source_wks = source_wbk.Worksheets("sheet15")
.Cells(last_log_row + 1, 5).Value = source_wks.Range("A9").Value

Set source_wks = source_wbk.Worksheets("sheet12")
.Cells(last_log_row + 1, 6).Value = source_wks.Range("A9").Value
End With


' save the changes
Application.DisplayAlerts = True
log_wbk.Save
log_wbk.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


------


--
Regards
Frank Kabel
Frankfurt, Germany