Delete excel files in a given folder

Hi,

I have macro that creates a new workbook based on a sample sheet within the workbook containing the macro. The macro names the file with a correlative number (based on the value of a cell that increases by one everytime a new workbook is created). So, for example, the first file created will have the name "name 1.xlsx", the second "name 2.xlsx", the third, "name 3.xlsx" and so on.

I'd like to have a separate macro that deletes the last workbook created so that if the user made a mistake when creating a file, say file "name 13.xlsx", he could run this macro and have it deleted. I can't figure out how to tell the macro that the name of the file is "name (cell value).xlsx". "Cell value" being the correlative number of the last workbook created. Is this even possible?

Thanks in advanced

please, paste here your code

Hi, patel45.

I don't have a code yet for deleting the file. The macro I want would be separate from the macro that creates the file. All I want it to do is look for the last file created (using the correlative number found in cell A2) and delete it.

If it helps. This is the code that creates the file:

Sub Crear_y_registrar()

Application.ScreenUpdating = False
i = Range("a2") + 1



Workbooks.Open FileName:="Z:\AÑO 2013\PUNTOS DE CUENTAS 2013\NUEVO PUNTO.xlsx"
Windows("PUNTO DE CUENTA (CREADOR).xlsm").Activate
Sheets("HojaCopia").Copy Before:=Workbooks("NUEVO PUNTO.xlsx").Sheets(1)
ActiveWorkbook.SaveAs "Z:\AÑO 2013\PUNTOS DE CUENTAS 2013\PUNTO DE CUENTA " & i & ".xlsx", FileFormat:=51


Call Formula_Zapper


Range("A1").Select
Dim Comprobar

    Comprobar = ArchivoAbierto("Z:\AÑO 2013\PUNTOS DE CUENTAS 2013\Relación.xlsm")

    If Comprobar = True Then
        MsgBox "El archivo -Relación.xlsx- está abierto o está siendo utilizado por otro usuario. Asegúrese de que esté cerrado e intente de nuevo"
    Else

Workbooks.Open FileName:="Z:\AÑO 2013\PUNTOS DE CUENTAS 2013\Relación.xlsm"
Workbooks("PUNTO DE CUENTA (CREADOR).xlsm").Activate
Range("C46:F46").Select
    Selection.Copy
    Range("C48").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Relación.xlsm").Activate
    Range("A3").Select
    Selection.Insert Shift:=xlDown
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    Workbooks("PUNTO DE CUENTA " & i & ".xlsx").Activate
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    
    
   Windows("PUNTO DE CUENTA (CREADOR).xlsm").Activate
   
    Range("C18:N18,C20:N23").Select
    Range("C20").Activate
    Range("C18:N18,C20:N23,C28:G28,H28:K28,L28:N28,C30:N30").Select
    Range("C30").Activate
    Range("C18:N18,C20:N23,C28:G28,H28:K28,L28:N28,C30:N30,C33:L36,M34:N43").Select
    Range("M34").Activate
    Selection.ClearContents
    ActiveWorkbook.Save
    Range("A2").Select
    
    Range("a2") = Range("a2") + 1
    
      
    Application.ScreenUpdating = True
   
     MsgBox "Punto de Cuenta creado y registrado con éxito"
      
      End If
   
   
End Sub

Function ArchivoAbierto(FileName As String)
    Dim ff As Long, ErrNo As Long

    On Error Resume Next
    ff = FreeFile()
    Open FileName For Input Lock Read As #ff
    Close ff
    ErrNo = Err
    On Error GoTo 0

    Select Case ErrNo
    Case 0:    ArchivoAbierto = False
    Case 70:   ArchivoAbierto = True
    Case Else: Error ErrNo
    End Select
End Function

I know it's a crazy looking and messy code. I really have no training in VBA at all, all i know, I've learned along the way.

As I said, I need the macro that deletes the file to be separate from this one. What I really don't know how to do is give the macro a varying file name (based on the value of the cell [A2] containing the correlative number)

Thanks

Hi, adrianrff,

maybe something like this (you need to change the name of the sheet and range as well as the location to suit):

Sub Ef949235()
With Sheets("Data").Range("B2")
  Kill ThisWorkbook.Path & "\name " & .Value & ".xlsx"
  .Value = .Value - 1
End With
End Sub

Ciao,
Holger

Thanks, HaHoBe!

Worked like a charm.

I struggled at first because I'm not familiar with ThisWorkbook.Path format. I failed to realise that ThisWorkbook.Path meant "This workbook's path", duh! I kept typing the whole path and, of course, it couldn't find the file.

Thanks again.