Excel And Word File Deletion In Macro

[Chuckles123]

I have several Excel files and several Word files that I want to delete after data has been extracted and deposited into a master workbook.

For the Excel files, I am using the following (sometimes this works and sometimes it does not):

sWB.Activate 'sWB previously specifed via a 'Set' stmt (path & file name)
ActiveWindow.Close
Kill "MyFile.xls"

For the Word files, I am using the following (this is not working):

WordDoc.Close 'WordDoc previously specified & opened (path & file name)
Kill "MyFile2.rtf"
WordApp.Quit

Please help.
Thanks in Advance,
Chuckles123

[anilsolipuram]

sWB.Activate 'sWB previously specifed via a 'Set' stmt (path & file name)
ActiveWindow.Close
Kill "MyFile.xls"


MyFile.xls should be in the same directory as the file where the macro is present.

[Chuckles123]

The macro is stored in an .xlA file (NOT installed as an Add-In in Excel) that is on a shared network drive. Myfile.xls is stored on the shared drive as well, but in a different folder; I can certainly move Myfile.xls to the same folder, and I will.

The Kill stmt does not appear to work on Word files. Any comments?

Chuckles123

[anilsolipuram]

command for deleting file is

kill pathname & filename


ex:

kill "c:\program\x.xls"

it should work for any file

[Chuckles123]

Moved all of the Myfile.xls's and Myfile2.rtf's to the same folder on the shared drive where the .xlA file is stored.

In the Kill commands for the Myfile2.rtf's, I used the full pathname; in the Kill commands for the Myfile.xls's, I used the filename only in quotes.

As you indicated, the rtf files were deleted, but the xls files were not.

Is it possible to use a stmt like: myPath = "S:\ blah ... blah\filename.xls" and then use myPath in the Kill command?

Thanks AGAIN for a response.
Chuckles123

[anilsolipuram]

If .xla is in same directory as the files to be deleted, you neednot specify the complete path. If it is able to delete .rtf it should be able to delete .xls.

Type in exact code you are using for deleting files, don't skip any line.

[Chuckles123]

Have To Leave For An Appointment, But Will Try Again Tomorrow.

Code Is Exactly As Shown In My First Post, Except That I Added Pathname For The Rtf Kill Stmt.

I Did Get A Chance To Run The Macro Without The Pathname For The Rtf Kill Stmt ==> Came Up With A 'file Not Found' Error.

Ciao,
Chuckles123

[Chuckles123]

I now have the following all in one folder:

- the xlA file containing the code;
- the xlS files (new files daily) containing data to be copied into the Master xlS file;
- the Master xlS file; and
- the rtf file (new file daily) containing data to be copied into the Master xlS file.

I replaced the pathname back into the Kill rtf command line; the rtf file is deleted, as I wish.

Currently, the Kill xlS command lines do not contain the pathname; the macro runs fine without any errors, but the files are not deleted.

Is there a way to use a myPath definition stmt in the Kill command lines?

Thanks in advance,
Chuckles123

[theDude]

Here's one way:

Please Login or Register  to view this content.

Hope this helps,
theDude

[Chuckles123]

Thanks very much. The main thing that I was looking for was how to use the myPath stmt; you showed me how to do that.

I did have to make one change: I could not get 'fileName = ActiveWorkbook.Name' to work. I had to define fileName with a string that includes a variable 'j'.

Thanks again,
Chuckles123