Who last opened file?
 

Hi All...........

John Walkenbach has an article on his site that describes how to use WordPad
to see who was the last person to open a particular .xls file.......it's here

http://j-walk.com/ss/excel/odd/odd26.htm

My question is, how to use VBA to interrogate a .xls file and extract
this information?

TIA
Vaya con Dios,
Chuck, CABGx3

Who last opened file?
 

You can do this with the DSOFile DLL available at
http://support.microsoft.com/kb/224351/en-us . This DLL allows you to read
and modify built-in and custom properties of OLE file, which includes most
Office files (Excel, PowerPoint, Visio, etc).


Function LastUser(FileName As String) As String
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
' Returns the Excel Application.UserName (as set in the Options
' dialog) of the user who last saved the file named by
' FileName. This file must NOT be open.
' Requires a reference to DSOFile, DSOFile.dll. See
' http://support.microsoft.com/kb/224351/en-us
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
Dim DSO As DSOFile.OleDocumentProperties
Set DSO = New DSOFile.OleDocumentProperties

If Dir(FileName, vbNormal + vbSystem + vbHidden) = vbNullString Then
LastUser = vbNullString
Else
DSO.Open FileName
LastUser = DSO.SummaryProperties.LastSavedBy
End If
End Function

There are two versions of the DSOFile DLL floating around, "DSO OLE Document
Properties Reader 2.1" and "DS: OLE Document Properties 1.4 Object Library".
The code above is for version 2.1, the version you should be using. This
will also work on XLSM and XLSX files.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"CLR" wrote in message
...
Hi All...........

John Walkenbach has an article on his site that describes how to use
WordPad
to see who was the last person to open a particular .xls file.......it's
here

http://j-walk.com/ss/excel/odd/odd26.htm

My question is, how to use VBA to interrogate a .xls file and extract
this information?

TIA
Vaya con Dios,
Chuck, CABGx3

Who last opened file?
 

Chuck

I think it will be rather difficult to get to the text string that
shows up when you use Notepad to be viewable in an Excel file. It
would be easy to write the name of the user who opens a file into a
text file whenever a file is opened. The reading of that text file
would be easy. If you are interested in that approach, you should
repost, as I am sure there are plenty on instances of code available
to write to text files to track activity in a spreadsheet.

Ken

On Jan 4, 9:36*am, CLR wrote:
Hi All...........

John Walkenbach has an article on his site that describes how to use WordPad
to see who was the last person to open a particular .xls file.......it's here

http://j-walk.com/ss/excel/odd/odd26.htm

My question is, *how to use VBA to *interrogate a *.xls file and extract
this information? *

TIA
Vaya con Dios,
Chuck, CABGx3

Who last opened file?
 

Thanks Ken, but that solution requires that I previously set code within the
file. I want to be able to check ANY .xls file.

Vaya con Dios,
Chuck, CABGx3



"Ken" wrote:

Chuck

I think it will be rather difficult to get to the text string that
shows up when you use Notepad to be viewable in an Excel file. It
would be easy to write the name of the user who opens a file into a
text file whenever a file is opened. The reading of that text file
would be easy. If you are interested in that approach, you should
repost, as I am sure there are plenty on instances of code available
to write to text files to track activity in a spreadsheet.

Ken

On Jan 4, 9:36 am, CLR wrote:
Hi All...........

John Walkenbach has an article on his site that describes how to use WordPad
to see who was the last person to open a particular .xls file.......it's here

http://j-walk.com/ss/excel/odd/odd26.htm

My question is, how to use VBA to interrogate a .xls file and extract
this information?

TIA
Vaya con Dios,
Chuck, CABGx3

Who last opened file?
 

Thanks Chip, but the comments say that it "Returns the Excel
Application.UserName (as set in the Options dialog) of the user who last
SAVED the file named by FileName."

The signature I am after does not require that the last "opener" save the
file. It shows up in WordPad, I just don't know how to get it over into
Excel......or, alternatively, if I open the file, how to retrieve it from
there.

Vaya con Dios,
Chuck, CABGx3



"Chip Pearson" wrote:

You can do this with the DSOFile DLL available at
http://support.microsoft.com/kb/224351/en-us . This DLL allows you to read
and modify built-in and custom properties of OLE file, which includes most
Office files (Excel, PowerPoint, Visio, etc).


Function LastUser(FileName As String) As String
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
' Returns the Excel Application.UserName (as set in the Options
' dialog) of the user who last saved the file named by
' FileName. This file must NOT be open.
' Requires a reference to DSOFile, DSOFile.dll. See
' http://support.microsoft.com/kb/224351/en-us
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
Dim DSO As DSOFile.OleDocumentProperties
Set DSO = New DSOFile.OleDocumentProperties

If Dir(FileName, vbNormal + vbSystem + vbHidden) = vbNullString Then
LastUser = vbNullString
Else
DSO.Open FileName
LastUser = DSO.SummaryProperties.LastSavedBy
End If
End Function

There are two versions of the DSOFile DLL floating around, "DSO OLE Document
Properties Reader 2.1" and "DS: OLE Document Properties 1.4 Object Library".
The code above is for version 2.1, the version you should be using. This
will also work on XLSM and XLSX files.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"CLR" wrote in message
...
Hi All...........

John Walkenbach has an article on his site that describes how to use
WordPad
to see who was the last person to open a particular .xls file.......it's
here

http://j-walk.com/ss/excel/odd/odd26.htm

My question is, how to use VBA to interrogate a .xls file and extract
this information?

TIA
Vaya con Dios,
Chuck, CABGx3

Who last opened file?
 

In the testing I've done, it appears the value i seek is always preceeded by
the following, if that will help find it in the file.

_p____

that's Underscore, lowercase p, and four more underscores......

Vaya con Dios,
Chuck, CABGx3



"CLR" wrote:

Hi All...........

John Walkenbach has an article on his site that describes how to use WordPad
to see who was the last person to open a particular .xls file.......it's here

http://j-walk.com/ss/excel/odd/odd26.htm

My question is, how to use VBA to interrogate a .xls file and extract
this information?

TIA
Vaya con Dios,
Chuck, CABGx3