Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing Data to an External File
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing Data to an External File
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing Data to an External File
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing Data to an External File
Marcus,
Thanks for responding to my message. I have not tried your code yet. I will let you know after I try it. Thanks. "marcus" wrote: 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Writing a macro in Excel that Refreshes External Imported Text File | Excel Programming | |||
Open external data (varying file names) and closing file once data is retrieved | Excel Programming | |||
Writing streaming data to txt file | Excel Programming | |||
writing data to a tab delimited text file? | Excel Programming | |||
Writing to a text file some data | Excel Programming |