find other workbooks

[smiler44]

A search of the internet did not lead me anywhere so I don't know if this is possible.

I want to run a macro from a workbook called "appt". I want the macro to find all other workbooks in the My Documents directory or sub directories called "appt" or have "appt" in their name and kill them. My workbook "appt" will also be in the My Documents directory or one of the sub directories but must not be killed.
If possible I only want the macro to run once, I don't want it to run each time my "appt" book is opened.

any help appreciated

thank you in advance

smiler44

 

[Uncle Gizmo]

I note that you have yet to receive a reply to your question. On reading your question I get the impression that it's a lot of little questions and requirements. In effect it's a statement of what you would like with little substance in the sense that you don't say what you've tried or code you've seen that you like.

You could break your requirement down into simpler steps.

 

[Gasman]

I would approach it in this way.

Place a file in your document folder called something like DeleteAppt.txt
When this file exists you will delete all the appt files except your workbook appt file.

Then use the Dir() function to look through the document folder firstly for that file, and if it exists then Dir() on the appt files, checking for your appt file that you wish to keep and kill all the others.

The macro would have to run every time you open the workbook, but if the DeleteAppt file is not present would exit immediately.

Then when you want it to execute again, just put the file back in that folder.

HTH

 

[Brianwarnock]

Isn't there code here that could be modified to do what you want?

http://word.mvps.org/faqs/macrosvba/DeleteFiles.htm

Brian

 

[smiler44]

I note that you have yet to receive a reply to your question. On reading your question I get the impression that it's a lot of little questions and requirements. In effect it's a statement of what you would like with little substance in the sense that you don't say what you've tried or code you've seen that you like.

You could break your requirement down into simpler steps.

When I first read your reply I had no idea how to go about the task and so had not written any code. Here I am 5 minutes later and I have an idea how to make the macro "run" the once. I have not created the code but that's "easy" but here is my idea. I wi try to get this to work on the my documents folder and sub folders

I will send the work book out with the tab colour to sheet 1 Red.
macro in workbook open section
sub onetimerun()
if sheet 1 tab colour <> green then
find files and delete them
loop

sheet 1 tab colour = green

active workbook save as name in my documents directory
end sub

smiler44

 

[smiler44]

Isn't there code here that could be modified to do what you want?

http://word.mvps.org/faqs/macrosvba/DeleteFiles.htm

Brian

Brian,
thank you yes I think you are right. if I can go from folder to folder I may be able to adapt this code. The path will be a variable where below it has been hard coded

 'To delete all the [B]Word documents [/B]in a given directory, leaving other files 'alone: 
 [COLOR=#00007f]Dim [/COLOR]MyFile [COLOR=#00007f]As String[/COLOR]
MyFile = Dir$("c:\temp\*.doc")
[COLOR=#00007f]Do While [/COLOR]MyFile <> ""
    KillProperly "c:\temp\" & MyFile
[COLOR=#007f00]    [/COLOR]MyFile = Dir$("c:\temp\*.doc")
[COLOR=#00007f]Loop[/COLOR] 
 [/code

  
 smiler44