Logo 
Search:

MS Office Answers

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds
  Question Asked By: Keana Schmidt   on Dec 08 In MS Office Category.

  
Question Answered By: Eloise Lawrence   on Dec 08

I assume multiple users could access this file  it must be on a network
server. There are a couple things I have done to track user:

1. It should be possible to put your code  in the autoopen sub to get
the user's id and machine id, etc. then write that information to a
text file somewhere so you could look at it anytime you wish.

I use vbscript to quickly create the report file like this:

'******* watch out for word wrap & missing line breaks **************
Function create_Report(strRepFullName As String, strHeader As String, _
strColumnHeader As String, ByVal intCnt As
Integer, strMainList As String)
Dim FSO, a, os

'create a temp file for the report
Set FSO = CreateObject("Scripting.FileSystemObject")

Set a = FSO.CreateTextFile(strRepFullName, True)


a.WriteLine strHeader
a.WriteLine Format(Now, "mmmm d, yyyy") & "; " & Format(Now,
"h:mm ampm")
a.WriteLine ""
a.WriteLine "Total sites found: " & intCnt
a.WriteLine ""
a.WriteLine strColumnHeader
a.WriteLine strMainList
a.Close

Set FSO = Nothing
Set a = Nothing

End Function

'use this fn to open  the report
Function OpenReport(strFullFileName as string)
Set os = CreateObject("WScript.Shell")
os.Run "Notepad " & strFullFileName
Set os = Nothing
End Function



2. Same information as above, but write them to the file properties.
Then just write a small vbscript to read the file attributes.

Here's the vbscript I wrote to read an xl file properties...

'******* watch out for word wrap & missing line breaks **************
'VBScript file to get the author and comments
'Tested with Excel 2000, XP
'All rights reserved by Tou X. Yang
' www.modernfalconry.com

Option Explicit

Dim objExcel, xlFile, strAuthor, strComments, xlProp, x, tempProp

Set objExcel = CreateObject("Excel.Application") 'Open an instance of
Excel

'Open a Excel document and return the "Documents" object
'set xlFile = objExcel.GetOpenFilename("Excel File (*.xls), *.xls")
' "C:\Documents and Settings\DOTTXY\Desktop\Copy of 76100005
misq_junk.xls")

'objExcel.Workbooks.open strPathExcel

set xlFile = objExcel.Workbooks.Open(_
"F:\Dad's Stuff\Hawk Migration\dev
version_multiple_life_time_download\test.xls")
set xlProp = xlFile.BuiltInDocumentProperties
for x = 0 to xlProp.count
err.clear: on error resume next
tempProp = x & ". " & xlFile.BuiltInDocumentProperties(x)
'wdPropertyAuthor
'strComments =
objExcel.ActiveWorkbook.BuiltInDocumentProperties(5) 'wdPropertyComments
if err=0 then _
strAuthor = strAuthor & tempProp & vbcrlf
next
'MsgBox "This document's author is " & strAuthor & vbCrLf &" Comments:
" & strComments
msgbox strAuthor
'msgbox objExcel.Version
tempProp= xlFile.BuiltInDocumentProperties(18)
xlFile.BuiltInDocumentProperties(18) = "test changing ..." & tempProp
'To avoid the "Do you want to save your changes?" dialog box, the script
'sets the Saved property to TRUE before terminating Excel.
xlFile.Saved = TRUE
xlFile.close

objExcel.Quit
set objExcel= nothing

Share: 

 
 
Didn't find what you were looking for? Find more on Who has the file open? Or get search suggestion and latest updates.


Tagged: