Link to home
Start Free TrialLog in
Avatar of thenelson
thenelson

asked on

convert vba to vb script

I have the following VBA code that works fine. How do I convert it to run in VB script? I understand Dir is not supported in VB script. Is there a substitute?  

Basically, I am looking for Microsoft Word documents in all subfolders on all connected network drives that have  the phrases "Progress Report" or "Consultation Report" in their name.  If there are two files with matching names except one file has "Signed" appended to it, delete the file without "Signed".  Also delete any files that start with "~$".

TIA
Sub ClearAllUneededFiles()
Dim fs

Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists("\\Jane-pc\jane's files\Patent Records\FindUnsignedDocuments.doc") Then
    ClearUneededFiles "\\Jane-pc\jane's files\Patent Records\"
    ClearUneededFiles "\\Jane-pc\jane's files\Patent Records\_NOT SEEN\"
End If
If fs.FileExists("\\FUJITU\My Documents\JANE'S FILES\Patent Records\FindUnsignedDocuments.doc") Then
    ClearUneededFiles "\\FUJITU\My Documents\JANE'S FILES\Patent Records\"
    ClearUneededFiles "\\FUJITU\My Documents\JANE'S FILES\Patent Records\_NOT SEEN\"
End If
If fs.FileExists("\\DESKTOP\My Documents\Jane's files\Patent Records\FindUnsignedDocuments.doc") Then
    ClearUneededFiles "\\DESKTOP\My Documents\Jane's files\Patent Records\"
    ClearUneededFiles "\\DESKTOP\My Documents\Jane's files\Patent Records\_NOT SEEN\"
End If
ActiveDocument.SaveAs Environ("temp") & "\temp.doc"
Application.Quit wdPromptToSaveChanges
End Sub


Sub ClearUneededFiles(mypath)
Dim DirList()
Dim counter
Dim MyName
Dim fs

Set fs = CreateObject("Scripting.FileSystemObject")

counter = 0
ReDim DirList(1)

On Error Resume Next
MyName = Dir(mypath, vbDirectory)
If Err = 53 Then
    Exit Sub
End If
On Error GoTo 0

Do While MyName <> ""   ' Start the loop to find subfolders
    If (GetAttr(mypath & MyName) And vbDirectory) = vbDirectory Then
        If MyName <> "." And MyName <> ".." And InStr(1, MyName, "_") = 0 Then
            ReDim Preserve DirList(counter)
            DirList(counter) = mypath & MyName
            counter = counter + 1
        End If
    End If
    MyName = Dir    ' Get next entry.
Loop
For counter = 0 To UBound(DirList)
    MyName = Dir(DirList(counter) & "\*.doc")

    Do While MyName <> ""   ' Start the loop to find files in each subfolder
        If InStr(1, MyName, "Consultation Report") > 0 Or InStr(1, MyName, "Progress Report") > 0 Then
            If InStr(1, MyName, "Signed") = 0 Then
                If fs.FileExists(DirList(counter) & "\" & Left(MyName, Len(MyName) - 4) & " Signed.doc") Then
                    Kill DirList(counter) & "\" & MyName
                ElseIf Left(MyName, 2) = "~$" Then
                    Kill DirList(counter) & "\" & MyName
                End If
            End If
        End If
    MyName = Dir    ' Get next entry.
    Loop
Next
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of HugoHiasl
HugoHiasl

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of thenelson
thenelson

ASKER

thanks!