Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to autofilter other file and copy in opened file | Excel Programming | |||
code to check file size everytime an Excel file is opened | Excel Programming | |||
stop excel file opened as read only if already opened by another u | Excel Programming | |||
Is Workbook read-only because of file permission or because file is already opened? | Excel Programming | |||
How set file open path to filepath of file opened with Explorer ? | Excel Programming |