CheckFiles

[kreshnr]

Hi All,
I am using below mentioned Code for checking if certain file exist on Path.
Its working on my home PC (XP SP2 Excel 2003).
BUT its not working on my office PC (XP SP2 Excel 2003).

Can somebody tell me why its not working in my office PC ( or if its missing something, then from where can i download it.)

Sub CheckFiles()
Const strFolder = "C:\Documents and Settings\kreshnr\Desktop\Test"
Dim fso, msg, i
Dim rngData As Range

Set fso = CreateObject("Scripting.FileSystemObject")
Set rngData = Sheets("Sheet1").Range("A1")

With rngData
Do While .Offset(i, 0).Value <> ""
If (fso.FileExists(strFolder & .Offset(i, 0).Value & ". ")) Then
.Offset(i, 2).Value = "Yes"
Else
.Offset(i, 2).Value = "No"
End If
i = i + 1
Loop
End With
End Sub

Regards

Kreshnr

 

[Kenneth Hobson]

The easiest method to get the Owner is to use WMI. You may not have that option as your IS department may have disabled WScript. In that case, even vba and vb.net methods would probably fail as it has to do with security.

While you can leave the Value property off as it is the default property for a Range, it is best to set it. When referencing objects like Ranges or building variables like Strings, do it once. It will speed things along.

Notice that I demonstrated two method for Chip's 2nd parameter. I used the Enumeration defined in his Module and the number.

Public Sub show_author_owner()
    'assumes path is in column A and file name is in column b

    ' By Chip Pearson, 5-Jan-2008, chip@cpearson.com
    ' http://www.cpearson.com/Excel/DocProp.aspx
    Dim last_row As Long, fName As String
    With ActiveSheet
        last_row = .Range("A" & Rows.Count).End(xlUp).Row
        For my_loop = 3 To last_row
            fName = .Range("A" & my_loop) & .Range("b" & my_loop)
            .Range("DC" & my_loop).Value = ReadPropertyFromClosedFile(fName, "author", PropertyLocation.PropertyLocationBOth)
            .Range("DD" & my_loop).Value = ReadPropertyFromClosedFile(fName, "Lastsavedby", 3)
            .Range("DE" & my_loop).Value = FileOwner(fName)
        Next my_loop
    End With
End Sub

Sub Test()
    MsgBox "Owner of: " & ThisWorkbook.Path & vbCrLf & _
        FileOwner(ThisWorkbook.Path)
    MsgBox "Owner of: " & ThisWorkbook.FullName & vbCrLf & _
        FileOwner(ThisWorkbook.FullName)
End Sub


'http://www.microsoft.com/technet/scriptcenter/resources/qanda/oct04/hey1007.mspx
Function FileOwner(strFile) As String
    Dim strComputer As String
    Dim objWMIService As Object
    Dim colItems As Object
    Dim objItem As Variant
    
    On Error Resume Next
    
    strComputer = "."
    Set objWMIService = GetObject("winmgmts:" _
          & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
    
    Set colItems = objWMIService.ExecQuery _
        ("ASSOCIATORS OF {Win32_LogicalFileSecuritySetting='" & strFile & "'}" _
            & " WHERE AssocClass=Win32_LogicalFileOwner ResultRole=Owner")
    
    For Each objItem In colItems
        'FileOwner = objItem.ReferencedDomainName
        FileOwner = objItem.AccountName
    Next
End Function

If you want to skip the dsofile method, you can use FSO (makes API methods easier to use) or API methods directly. See API Guide's GetFileTime for the API methods for file times.

 

[kreshnr]

Hi Kenneth,
Thank you very much for your solution.. it works on my machine.. again i am checking it on several machines and across servers and networks.

And after that i will try to customize it as per my requirement / format . i will will keep u posted on my development.


Thanx and Regards

Kreshnr