Macro to delete file?

Topic

New Reply

Tried to create a macro that will delete a file. Does anyone know how to go about this using VB? Using mS Excel 97.

Reply | Quote

Replies

Try Kill Pathname where pathname is the full path and filename of the file you wish to delete, example

Kill “C:My DocumentsFile1.xls”

Andrew

Reply | Quote

HELP: I use MS Excel 97, Access97
Tried this your suggestion and it will not work in my case. I need to use the following network path: prodSecurityMasterTerm2001.xls

I keep getting “RunTime Error: 70, Permission denied” when it comes to the KILL statement at the end.

With ActiveSheet.QueryTables.Add(Connection:= _
“FINDER;tabsproddatabaseEdgarSecurityTermination.dqy”, Destination:=Range(“A5”))
.FieldNames = True
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = True
.SaveData = True
End With
Rows(“5:5”).Select
Selection.Delete Shift:=xlUp
Range(“A5:F38”).Select
Selection.Sort Key1:=Range(“D5”), Order1:=xlDescending, Key2:=Range(“A5”) _
, Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom

‘Save Spreadsheet
Application.DisplayAlerts = False
Kill “tabsproddatabaseEdgarSecurityMasterTerm2001.xls”
ActiveWorkbook.SaveAs FileName:=”tabsproddatabaseEdgarSecurityMasterTerm2001.xls”, _
FileFormat:=xlNormal, Password:=””, WriteResPassword:=””, _
ReadOnlyRecommended:=False, CreateBackup:=False
Application.DisplayAlerts = True
End Sub

Reply | Quote

Have you tried to delete the file manually, and if so did you succeed ?

Reply | Quote

Be working on something else for a while and just go back to this problem. If I manualy delete “Runterm2001.xls” it works fine. How can I get it to overwrite the file? Starting to get desperate.

‘Save Spreadsheet
Application.DisplayAlerts = False
‘Kill “J:EdgarSecurityMasterTerm2001.xls”

ActiveWorkbook.SaveAs FileName:=”tabsproddatabaseEdgarSecurityRunTerm2001.xls”, _
FileFormat:=xlNormal, Password:=””, WriteResPassword:=””, _
ReadOnlyRecommended:=False, CreateBackup:=False
Application.DisplayAlerts = True
End Sub

Reply | Quote

I use this macro to collect files from a directoy…

Sub GetFiles()
Dim i as integer
Dim myFile as string
i=1
myFile=Dir(“*.xls”)
Do until myFile = “”
Cells(i,1).Value = myFile
i=i+1
myFile = Dir
Loop
End Sub

My question is, after I have modified the filenames collected, can I send the name back to the directory again? Is there code to rename like there is code to “Kill”
TX

Reply | Quote

The VBA instruction to rename (and/or move) a file is

Name "OldName" As "NewName"

Example 1:

Name "C:ExcelInvoice.xls" As "C:ExcelInvoice2007.xls"

Since the path in the old and new names is the same, the file is simply renamed.

Example 2:

Name "C:ExcelInvoice.xls" As "C:BackupInvoice.xls"

Since the path in the old and new names is different, the file is moved; it retains the same file name.

Example 3:

Name "C:ExcelInvoice.xls" As "C:BackupInvoice2007.xls"

The file is moved to a different folder and renamed at the same time.

Reply | Quote

TX professor!

Reply | Quote