MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Deleteing a file through VBA

  Asked By: Lorraine    Date: Aug 18    Category: MS Office    Views: 935

I am currently using a procedure that uses a blank Excel file to
write to. The file is an intermediate step used for a short while and
saved under a different name after it is written to. I have run into
a problem where people will delete the file from the folder and the
procedure fails when it can't find the file to open. The file is
used to hold a copy of a page from an existing file. I can easily use
the copy function, but need to go back to the file I created and
rename it later on. The name I will use to save the file with is not
available at the time I copy the data to it. I need to know the name
of the "blank" file so I can reopen it and save it under the
appropriate name. I don't necessarily know the name if I merely copy
the data to a new file since it could be book1, 2, etc. If I just
create a new file with the copied information in it I can rename the
file, but that leaves the file in place all the time with the
resulting possibility of someone deleting the file and making the
procedure fail.

How can I delete a file in a folder through VBA so it doesn't give a
warning that the file already exists when I try useing that same name
again? I have tried using the recorder and get no results when I
delete the file. I am using version 2001 of Excel.



7 Answers Found

Answer #1    Answered By: Geena Ma.     Answered On: Aug 18

Here is an example of what I use. It's probably not the best way, but works
for me.

Dim wbName As String
Dim Wb1 As Workbook
Set Wb1 = ActiveWorkbook
Dim Wb2 As Workbook

wbName = Wb1.Sheets("Your Sheet").Range("Your Range")

Set Wb2 = ActiveWorkbook


'Set Wb2 = Workbooks.Open("C:\Your XLS File.xls")

Wb1.Sheets("Your Sheet").Range("Your Range").Copy
Destination:=Wb2.Sheets("Your Sheet").Range("Your Range")
Application.DisplayAlerts = False
Wb1.Close False
ActiveWorkbook.SaveAs (wbName)
Application.DisplayAlerts = True

Answer #2    Answered By: Garrett Brooks     Answered On: Aug 18

If you use the FileSystemObject there is a FileExists method.

You can also delete  files using the FileSystemObject.

Answer #3    Answered By: Willis Hill     Answered On: Aug 18

You may find  this link useful.


Apologies for not including it previously.

Answer #4    Answered By: James Williams     Answered On: Aug 18

There's also a slightly easier, though older-fashioned way to delete  a file
in VB, which is to just use the KILL command, like

Kill "C:\Documents and Settings\David\My Documents\See Ya!.doc"

But then you need to do an error trap in case the file  doesn't exist, etc.

I prefer use of FileSystemObject.

Answer #5    Answered By: Albert Ellis     Answered On: Aug 18

Thanks to everyone who sent an answer to me. I will try all of them and
have to see what works best. At least I now have a very good idea of how to
do what is needed and to try and keep out of trouble!!

Trouble is so easy to get into and very hard to get out of. Best to avoid
it if you can and prevent it happening.

Answer #6    Answered By: Dannon Jones     Answered On: Aug 18
Answer #7    Answered By: Walborgd Fischer     Answered On: Aug 18

You could also use the Win32 API - which allows one to execute powerful windows
commands in VBA. see http://abstractvb.com/code.asp?A=658

Didn't find what you were looking for? Find more on Deleteing a file through VBA Or get search suggestion and latest updates.