excel vba

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Excel VBA

Excel VBA

resolvedResolved · Low Priority · Version 2003

Frazer has attended:
Excel VBA Intro Intermediate course

Excel VBA

I have written a mcro to delete files from a shared drive. The macro selects files according to name criteria provided by the user but I also want to select by the file's last modified date. How can I do this ? Here is the code so far:

Sub Killer()
'macro to select & delete specific files
Dim Response As String
Dim datmod As Date
'Dim varNow As Variant 'the user form is shown & each button returns a different varNow value


Set fs = Application.FileSearch
With fs
.LookIn = InputBox("Enter the string for folder selection.", "Search folder criteria.", "S:\Operations\Analysts\text files")
Response = MsgBox("Search subfolders ?", vbYesNo + vbDefaultButton1 + vbQuestion, "Subfolders ?")
If Response = vbYes Then
.SearchSubFolders = True
Else
.SearchSubFolders = False
End If
'.LastModified <= InputBox("Include files last modified on or before date:", "Search folder criteria.", Date - 60)
.Filename = "*" & InputBox("Enter the string for file selection.", "Search criteria.") & "*" '"*0407.*"

If .Execute > 0 Then
MsgBox "There were " & .FoundFiles.Count & " file(s) found."

ListDeleteOrCancel.Show
'##this is a user form with buttons to list the files (varnow =1), ##
'##delete them varnow = 2) or cancel (3)##

If varnow = 1 Then 'list files
Workbooks.Add
For i = 1 To .FoundFiles.Count
ActiveCell.Value = .FoundFiles(i)

ActiveCell.Offset(1, 0).Select
Next i
ElseIf varnow = 2 Then
For i = 1 To .FoundFiles.Count
Kill .FoundFiles(i)
Next i
MsgBox "There were " & .FoundFiles.Count & " files deleted."
Else
Unload ListDeleteOrCancel
Exit Sub
End If
Else
MsgBox "There were no files found."
End If
End With

End Sub

RE: Excel VBA

Hi Frazer

Thanks for your post and comprehensive detail, in these instances we can reach a solution for you but it is beyond the scope of the forum.

We would ask you to send us a copy of the files you are woring on with a guide list of areas to address. We will then get back to you with the scope of work and related costs, if you are happy to proceed we will then start work.

I shall email you direct so if you wish to progress this then please reply to me.

Kind regards

Jacob

Wed 3 Mar 2010: Automatically marked as resolved.

 

Training courses

 

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Creating a range of monthly payments as text

You could use a formula to create a range of payment ie. payment amount for x% to y% rate with fixed terms and principle.

The text that would be "between Xamount and Yamount".

Here is how to do it.

1 Use the PMT function to get your monthly payments figure or whatever frequency of payments that you choose he start range.

See PMT under Excel Help

2. Nest these in the ROUND function to round decimals see ROUND under Excel Help


3. Concatenate this using "&" and concatenate " to " and concatenate "Between ".

4. Concatenate the above to PMT function for the end range

ie.

="Between "&ROUND((PMT1),decimal places)&" and "&ROUND((PMT2),decimal places)

View all Excel hints and tips


Server loaded in 0.08 secs.