Solved

convert vba to vb script

Posted on 2013-01-03
2
355 Views
Last Modified: 2013-01-17
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

0
Comment
Question by:thenelson
2 Comments
 
LVL 12

Accepted Solution

by:
HugoHiasl earned 500 total points
ID: 38739413
You are already using a FileSystemObject.

You could use the methods of this object:

msdn.microsoft.com/en-us/library/aa262402(v=vs.60).aspx
0
 
LVL 39

Author Closing Comment

by:thenelson
ID: 38789525
thanks!
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will show, step by step, how to integrate R code into a R Sweave document
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question