Writing Data to an External File

[Souny]

Hello all,

I have an Excel file (userfile.xls) that users make a copy and use it to
make analysis. I want to track who uses it and the date that uses the file.

I need some help with the code, and the code will be part of Workbook_Open
code and will execute when the userfile.xls file is opened.

I am looking for the code that will put the username in Cell A1 of Sheet1 in
the userfile.xls file. Then it will populate the username and the date (e.g.
now()) into an external Excel tracking file (trackfile.xls) under columns A
and B for username and date, respectively. If Cell A1 of Sheet1 in the
userfile.xls is already populated with a username, the code will not execute
(populate Cell A1 of Sheet1 in the userfile.xls nor the trackfile.xls).
Since there will be more than one user, I would like the code to populate the
username and date in the trackfile.xls on the next (empty) row of previously
recorded username and date, instead of overwriting the previous ones.

Since the trackfile.xls file will be on the network and the userfile.xls can
be used without being on the network, I would like the code not to execute.
The code will only execute when the userfile.xls file is used while
connecting to the network.

Lastly I would like the code to execute without being realized by the users
that the usage is being tracked.

Please help. Thanks.

 

[marcus]

Hi Souny

This worked for me. Just change the path to suit. Adds the data from
the userfile.xls to the bottom of the used range in the trackfile.xls.

Take care

Marcus

Option Explicit
Option Compare Text
Sub Open_Import1()

Dim oWbk As Workbook
Dim sFil As String
Dim sPath As String
Dim twbk As Workbook
Dim lr As Integer
Dim strFullName As String


Set twbk = ActiveWorkbook
Application.DisplayAlerts = False
Application.ScreenUpdating = False

twbk.Sheets("Sheet1").Range("A1:B1").Copy
sPath = "R:\" 'Cell B2 of Cal sheet, location of files
ChDir sPath
sFil = Dir("trackfile.xls") 'change or add formats
strFullName = sPath & sFil

Set oWbk = Workbooks.Open(strFullName)
lr = oWbk.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
oWbk.Sheets("Sheet1").Range("A" & lr).PasteSpecial xlPasteValues
oWbk.Close True 'close the workbook, saving changes
sFil = Dir

End Sub

 

[marcus]

Souny

The first part of the procedure to capture the user name is thus.
Place this code in a normal module.

Option Explicit
Function UserNameOffice() As String
UserNameOffice = Application.UserName
End Function

In the Workbook module place the following code.

Option Explicit

Private Sub Workbook_Open()
Worksheets("Sheet1").Cells(1, 1).Value = "=UserNameOffice()"
Worksheets("Sheet1").Cells(1, 2).Value = Date & Time

End Sub

Upon open it will place the user name and date and time in A1 and B1
respectively.

Take care

Marcus

 

[Souny]

Marcus,

Thanks for responding to my message. I have not tried your code yet. I
will let you know after I try it. Thanks.