VBScript Help

_-J-_ · 25 Aug 2014 at 13:20

Hoping someone can help on the below script, basically, when it runs, it echo's all the files but does not then delete anything or echo the file name at the section when it checks if its a full.

Code:

Option Explicit


Dim intNum, intMax2, intMax1, FSO, File, str
Set FSO = CreateObject("Scripting.FileSystemObject")

str = (Left(File.Name, 4))


' First pass: Find the two latest FULLs...
For Each File In FSO.GetFolder("c:\mds").Files
wscript.echo File.Name
wscript.echo str

    ' Is this a FULL?
    If Right(File.Name, 8) = "FULL.ZIP" Then
wscript.echo File.Name

        ' Get the numeric value from the file name (6 digits starting as pos 6)...
        intNum = CLng(Mid(File, 6, 6))
wscript.echo intNum
        ' Maintain the two latest FULLs...
        If intNum > intMax1 Then
            intMax2 = intMax1
            intMax1 = intNum
        ElseIf intNum > intMax2 Then
            intMax2 = intNum
        End If

    End If

Next

' Second pass: Delete anything prior to the second-latest FULL...
For Each File In FSO.GetFolder("c:\mds").Files

    intNum = CLng(Mid(File.Name, 6, 6))
    If intNum < intMax2 Then File.Delete

Next

king edwards

Basically what I am trying to achieve is to write a script to delete files after a certain filename, so based on the below file list

FILE_000001_FULL.ZIP
FILE_000002_FULL.ZIP
FILE_000003_FULL.ZIP
FILE_000004_FULL.ZIP
FILE_000005_FULL.ZIP
FILE_000006_DELTA.ZIP
FILE_000007_DELTA.ZIP
FILE_000008_FULL.ZIP

Everything up until FILE_000005_FULL.ZIP would be deleted. The files are created using a tool and will be sorted by file name, so highest number first. Basically need the 2 latest FULL files kept and the DELTA's (if any) between them. I hope that makes sense.

Cheers for any help in advance!

dazzerd · 25 Aug 2014 at 15:09

Are you sure this even compiles, this should bring up an error

str = (Left(File.Name, 4))

as it is not within the loop structure

file doesn't refer to any object yet and from memory file is a reserved word too.

and I don't like this either

intNum = CLng(Mid(File, 6, 6))

nor this

Dim intNum, intMax2, intMax1, FSO, File, str

they will just all be variants if you do this and the complier will try and figure out which is best. It will treat the first part of intNum = CLng(Mid(File, 6, 6)) as a string before trying to convert it to a long (which it may or may not do), personally I would just keep it as a string.

Try sticking it in a vba module and see if it compiles correctly. I suspect it won't. It wont like the wscipt.echo bit so you may want to change it to debug.print

andshrew · 25 Aug 2014 at 23:01

dazzerd said:
Dim intNum, intMax2, intMax1, FSO, File, str

they will just all be variants if you do this and the complier will try and figure out which is best. It will treat the first part of intNum = CLng(Mid(File, 6, 6)) as a string before trying to convert it to a long (which it may or may not do), personally I would just keep it as a string.

Pretty sure in VBScript you can't declare a type and every variable is a variant anyway.

_-J-_ · 26 Aug 2014 at 01:18

dazzerd said:
Are you sure this even compiles, this should bring up an error

str = (Left(File.Name, 4))

as it is not within the loop structure

file doesn't refer to any object yet and from memory file is a reserved word too.

and I don't like this either

intNum = CLng(Mid(File, 6, 6))

nor this

Dim intNum, intMax2, intMax1, FSO, File, str

they will just all be variants if you do this and the complier will try and figure out which is best. It will treat the first part of intNum = CLng(Mid(File, 6, 6)) as a string before trying to convert it to a long (which it may or may not do), personally I would just keep it as a string.

Try sticking it in a vba module and see if it compiles correctly. I suspect it won't. It wont like the wscipt.echo bit so you may want to change it to debug.print

The str = (Left(File.Name, 4)) can be removed, was me trying to test something.

Not sure why a vbscript would need compiling, have done quite a fwe and never had to compile them?

_-J-_ · 26 Aug 2014 at 01:18

andshrew said:
Pretty sure in VBScript you can't declare a type and every variable is a variant anyway.

I did think that setting them in the dim set them as a useable variable.

bremen1874 · 26 Aug 2014 at 02:18

Shouldn't...

' Get the numeric value from the file name (6 digits starting as pos 6)...
intNum = CLng(Mid(File, 6, 6))

actually be...

' Get the numeric value from the file name (6 digits starting as pos 6)...
intNum = CLng(Mid(File.Name, 6, 6))

or does it default to that property automatically? (not done any vbscript in years)

edit: just realised this has already been flagged.

Six6siX · 26 Aug 2014 at 14:18

Code:

Option Explicit
Dim FSO, File, Path, fCount, dCount, d, f
Set FSO = CreateObject("Scripting.FileSystemObject")
Path = "C:\mds\"
fCount = 0
dCount = 0

Dim fArray()
Dim dArray()

'Get all files in arrays by name/type
For Each file in FSO.GetFolder(Path).Files
	If Right(File.Name, 8) = "FULL.ZIP" Then
		ReDim Preserve fArray(fcount)
		fArray(fCount) = file.Name
		fCount = fCount + 1
	End If
	
	If Right(File.Name, 9) = "DELTA.ZIP" Then
		ReDim Preserve dArray(dCount)
		dArray(dCount) = file.Name
		dCount = dCount + 1
	End If
Next 

'Check if DELTA value is less than the second last FULL zip
'Delete all other DELTA values
If UBound(fArray) > 1 Then
	For Each d in dArray
		If Mid(d, 6, 6) < Mid(fArray(UBound(fArray) - 2), 6, 6) Then
			FSO.deleteFile(Path & d)
		End If
	Next
End If

'Remove last two FULL zips from array and delete the rest
ReDim Preserve fArray(UBound(fArray) - 2)
For Each f in fArray
	FSO.deleteFile(Path & f)
Next

I've not done any significant error handling or anything on the above, so use with caution!

dazzerd · 26 Aug 2014 at 21:17

andshrew said:
Pretty sure in VBScript you can't declare a type and every variable is a variant anyway.

Ahh my be the case I only have experience via VB

_-J-_ · 26 Aug 2014 at 23:38

Six6siX said:

Code:

Option Explicit
Dim FSO, File, Path, fCount, dCount, d, f
Set FSO = CreateObject("Scripting.FileSystemObject")
Path = "C:\mds\"
fCount = 0
dCount = 0

Dim fArray()
Dim dArray()

'Get all files in arrays by name/type
For Each file in FSO.GetFolder(Path).Files
	If Right(File.Name, 8) = "FULL.ZIP" Then
		ReDim Preserve fArray(fcount)
		fArray(fCount) = file.Name
		fCount = fCount + 1
	End If
	
	If Right(File.Name, 9) = "DELTA.ZIP" Then
		ReDim Preserve dArray(dCount)
		dArray(dCount) = file.Name
		dCount = dCount + 1
	End If
Next 

'Check if DELTA value is less than the second last FULL zip
'Delete all other DELTA values
If UBound(fArray) > 1 Then
	For Each d in dArray
		If Mid(d, 6, 6) < Mid(fArray(UBound(fArray) - 2), 6, 6) Then
			FSO.deleteFile(Path & d)
		End If
	Next
End If

'Remove last two FULL zips from array and delete the rest
ReDim Preserve fArray(UBound(fArray) - 2)
For Each f in fArray
	FSO.deleteFile(Path & f)
Next

I've not done any significant error handling or anything on the above, so use with caution!

Thats great, but is flinging an error of Subscript out of range on the line

Code:

If UBound(fArray) > 1 Then

Folder is populated with files etc.

From googling the error could be the array is 0, however there are files there. Quite new to working with Array's and trying to get my had around it!

Cheers

HungryHippos · 27 Aug 2014 at 00:31

As good as vbscript can be (and I used to use it a fair bit), any reason you would not want to use PowerShell for this?

_-J-_ · 27 Aug 2014 at 01:17

Eulogy said:
As good as vbscript can be (and I used to use it a fair bit), any reason you would not want to use PowerShell for this?

PowerShell would be ideal and much easier, however the system it is running on does not have PowerShell.

Six6siX · 27 Aug 2014 at 08:24

_-J-_ said:
Thats great, but is flinging an error of Subscript out of range on the line

Code:

If UBound(fArray) > 1 Then

Folder is populated with files etc.

From googling the error could be the array is 0, however there are files there. Quite new to working with Array's and trying to get my had around it!

Cheers

I think that bit needs revisiting...

When you are getting the error, what does the file structure look like - what is the sequence of DELTA and FULL files?

Washout · 27 Aug 2014 at 10:45

Had a go at this by using an access form and whacking a command button on it to fire the event but the code seems to work ok I think. Borrowed a bit from six6six.

Basically got all the files into the array and then cycle through each item using a loop with a variable so you can find the position in the array.
If it finds a file with FULL in it set the pointer lastFULL to it.
Before that though, if not on the first item in the array set the previousFULL pointer to equal lastFULL.

Then just set another loop that cycles through to the position of the 2nd last full -1 and delete everything it finds. This should naturally clear up any delta files and old FULL ones but not touch the delta inbetween or any that might be on the end of the list.

Stuck some debug lines in there and not sure if vbscript will like those?

Code:

    Dim fs As FileSystemObject
    Dim fileArray() As Variant
    Dim arraySize As Integer, i As Integer, previousFull As Integer, lastFull As Integer
    Dim folderPath As String

    Set fs = CreateObject("Scripting.FileSystemObject")
    folderPath = "c:\Test\"
    arraySize = 0
    
    'Get the filenames into an array
    Debug.Print "List of files found:"
    For Each File In fs.GetFolder(folderPath).Files
        ReDim Preserve fileArray(arraySize)
        fileArray(arraySize) = File.Name
        Debug.Print File.Name
        arraySize = arraySize + 1
    Next File
    arraySize = arraySize - 1 'Get rid of last iteration
    
    Debug.Print "Array Size:"; arraySize
    
    'Find the last and 2nd last FULL files
    For i = 0 To arraySize
        If Mid(fileArray(i), 13, 4) = "FULL" Then
            Debug.Print fileArray(i)
            If i > 0 Then
                previousFull = lastFull
            End If
            lastFull = i
        End If
    Next i
    Debug.Print "Second last FULL file at array position: "; previousFull
    Debug.Print "Last FULL file at array position: "; lastFull
    
    'Delete everything before the 2nd last FULL file
    For i = 0 To (previousFull - 1)
        Debug.Print "Deleting: "; fileArray(i)
        fs.DeleteFile (folderPath & fileArray(i))
    Next i

HungryHippos · 27 Aug 2014 at 22:11

_-J-_ said:
PowerShell would be ideal and much easier, however the system it is running on does not have PowerShell.

If you can install it, then I could probably mock something up in PowerShell relatively easily, don't want to spend the time though if you have no capability to do it

_-J-_ · 27 Aug 2014 at 22:54

Six6siX said:
I think that bit needs revisiting...

When you are getting the error, what does the file structure look like - what is the sequence of DELTA and FULL files?

Thank you for your help, the structure was correct, but Washout seems to of hit the money!

Washout said:
Had a go at this by using an access form and whacking a command button on it to fire the event but the code seems to work ok I think. Borrowed a bit from six6six.

Basically got all the files into the array and then cycle through each item using a loop with a variable so you can find the position in the array.
If it finds a file with FULL in it set the pointer lastFULL to it.
Before that though, if not on the first item in the array set the previousFULL pointer to equal lastFULL.

Then just set another loop that cycles through to the position of the 2nd last full -1 and delete everything it finds. This should naturally clear up any delta files and old FULL ones but not touch the delta inbetween or any that might be on the end of the list.

Stuck some debug lines in there and not sure if vbscript will like those?

Code:

Dim fs As FileSystemObject Dim fileArray() As Variant Dim arraySize As Integer, i As Integer, previousFull As Integer, lastFull As Integer Dim folderPath As String Set fs = CreateObject("Scripting.FileSystemObject") folderPath = "c:\Test\" arraySize = 0 'Get the filenames into an array Debug.Print "List of files found:" For Each File In fs.GetFolder(folderPath).Files ReDim Preserve fileArray(arraySize) fileArray(arraySize) = File.Name Debug.Print File.Name arraySize = arraySize + 1 Next File arraySize = arraySize - 1 'Get rid of last iteration Debug.Print "Array Size:"; arraySize 'Find the last and 2nd last FULL files For i = 0 To arraySize If Mid(fileArray(i), 13, 4) = "FULL" Then Debug.Print fileArray(i) If i > 0 Then previousFull = lastFull End If lastFull = i End If Next i Debug.Print "Second last FULL file at array position: "; previousFull Debug.Print "Last FULL file at array position: "; lastFull 'Delete everything before the 2nd last FULL file For i = 0 To (previousFull - 1) Debug.Print "Deleting: "; fileArray(i) fs.DeleteFile (folderPath & fileArray(i)) Next i

This worked, had to tweak it a little bit and came up with the below which works. Thanks again!

Code:

Option Explicit	

Dim fs
    	Dim fileArray()
    	Dim arraySize
	Dim i
	Dim previousFull
	Dim lastFull
    	Dim folderPath
	Dim file
dim archivepath





    Set fs = CreateObject("Scripting.FileSystemObject")
    folderPath = "c:\MDS\"
archivepath = "C:\MDS\Archive\"
    arraySize = 0
    wscript.echo "Cleanup starting, please do not cancel or run again."
  
   
   If (fs.FolderExists("C:\MDS\Archive")) Then
      
   Else
      fs.CreateFolder("C:\MDS\Archive")
   End If




    'Get the filenames into an array
    
    For Each File In fs.GetFolder(folderPath).Files
        ReDim Preserve fileArray(arraySize)
        fileArray(arraySize) = File.Name
        'wscript.echo File.Name
        arraySize = arraySize + 1
    Next
    arraySize = arraySize - 1 'Get rid of last iteration
    
    
    
    'Find the last and 2nd last FULL files
    For i = 0 To arraySize
        If Mid(fileArray(i), 13, 4) = "FULL" Then
            'wscript.echo fileArray(i)
            If i > 0 Then
                previousFull = lastFull
            End If
            lastFull = i
        End If
    Next 
    
    
    'Delete everything before the 2nd last FULL file
    For i = 0 To (previousFull - 1)
        
        fs.MoveFile (folderPath & fileArray(i)), archivepath
    Next 
wscript.echo "Cleanup complete."