Replacing file in a random named directory

rollis13 · Mar 21st 2012

I need to substitute an Excel master file in many PC and would like to do this via macro. I know the names of the PC, all the
usernames and all are accessable via LAN.
The path is always "C:\Documents and Settings\Username\Documents\Master\random_named_folder\master.xls" but my problem is that
the only certain date is that the pattern of the "random_named_folder" is "1d34c6b8.default" (8 random numbers/letters dot default)
and that it is the only subfolder of the "Master" folder.
There is a command in VB that reads the subfolders whatever their name is but how can I do this with Excel 2003.

Any idea for a script to retrieve the random folder names ? Thank you in advance.

jindon · Mar 22nd 2012

Re: Replacing file in a random named directory

try

Code

Sub test()
    Dim BaseFolder As String, fn As String
    BaseFolder = CreateObject("wscript.shell") _
    .specialfolders("mydocuments") & "\Master\"
    fn = SearchFile(BaseFolder, "[!0-9a-z]*", 8 _
    , ".default", "master.xls")
    If fn <> "" Then
        Workbooks.Open fn
    Else
        MsgBox "not found"
    End If
End Sub
 
 
Private Function SearchFile(myDir As String _
    , myPtn As String, myLen As Long, default As String _
    , filename As String) As String
    Dim fso As Object, myFolder As Object, temp
    Set fso = CreateObject("Scripting.FileSystemObject")
    For Each myFolder In fso.getfolder(myDir).subfolders
        If (Not Left$(myFolder.Name, myLen) Like myPtn) * _
            (Mid$(myFolder.Name, myLen + 1) = default) Then
            temp = Dir(myDir & myFolder.Name & "\" & filename)
            If temp <> "" Then
                SearchFile = myDir & myFolder.Name & "\" & temp
                Exit For
            End If
        End If
    Next
End Function

Display More

rollis13 · Mar 22nd 2012

Re: Replacing file in a random named directory

Thank you very much, does the job.
Now all I have to do is integrate it with the LAN access.

Replacing file in a random named directory

Participate now!

Share