Delete open book

Question

I want to create a new work book add a list into it and then save this book. If it is exist I need to delete previous variant and then save current. Here is my code:

Private Sub GenerateReport_Click()

    Dim wb As Workbook
    Dim FilePath As String


    FilePath = ThisWorkbook.Path + "\CommonReport.xls"
    Set wb = Workbooks.Add
    ActiveCell.FormulaR1C1 = "a1"
    wb.ActiveSheet.Range("B1").Select
    ActiveCell.FormulaR1C1 = "b1b"
    wb.ActiveSheet.Range("C1").Select
    ActiveCell.FormulaR1C1 = "3"
    wb.ActiveSheet.Range("D1").Select
    ActiveCell.FormulaR1C1 = "4"
    wb.ActiveSheet.Range("E1").Select
    ActiveCell.FormulaR1C1 = "5"
    wb.ActiveSheet.Range("F1").Select
    ActiveCell.FormulaR1C1 = "6"
    wb.ActiveSheet.Range("G1").Select
    ActiveCell.FormulaR1C1 = "7"
    wb.ActiveSheet.Range("A1:G1").Select
    wb.ActiveSheet.ListObjects.Add(xlSrcRange, wb.ActiveSheet.Range("$A$1:$G$1"), , xlYes).Name = "Ñïèñîê1"
    wb.ActiveSheet.Range("A1:G2").Select

    Application.DisplayAlerts = False

        If Dir(FilePath) = vbNullString Then
           wb.SaveAs Filename:=FilePath
        Else
        SetAttr FilePath, vbNormal
           Kill FilePath
        End If



End Sub

Is it possible to delete book when it is open. How can I handle an error when I want to delete opened book?

as a side note. It's probably better to do string concatenation with the ampersand. `ThisWorkbook.Path + "\CommonReport.xls"` --> `ThisWorkbook.Path & "\CommonReport.xls"` See: http://stackoverflow.com/a/7750689/726127 — Brad, Sep 25 '12 at 14:16

score 3 · Accepted Answer · answered Sep 25 '12 at 14:21

I think it is not necessary to delete the workbook, just overwrite it. If it is opened, close it first. Example:

Option Explicit

Private Const NEW_BOOK_NAME As String = "CommonReport"

Public Sub Test()
    Dim newWorkbook As Workbook

    Set newWorkbook = Workbooks.Add
    With newWorkbook.ActiveSheet
        [a1] = "a1"
        [b1] = "b1"
        [c1] = "c1"
    End With

    Dim filePath As String
    filePath = ThisWorkbook.Path & "\" & NEW_BOOK_NAME

    On Error Resume Next
    Workbooks(NEW_BOOK_NAME).Close

    On Error GoTo 0
    Application.DisplayAlerts = False
    newWorkbook.SaveAs filePath
End Sub

score 0 · Answer 2 · answered Sep 25 '12 at 14:08

You cannot delete an open workbook.

Check to see if a file with the same name is open using something like this: http://www.exceltip.com/st/Determine_if_a_workbook_is_already_open_using_VBA_in_Microsoft_Excel/472.html

If such a file is open, check to see if it has the same path as the file you want to delete by comparing FilePath with the open workbook (eg Workbooks("CommonReport.xls").FullName) and if so, close it by using the close command:

Workbooks("CommonReport.xls").Close SaveChanges:=False

score 0 · Answer 3 · answered Sep 26 '12 at 09:15

0

Olle Sjögren Idea sohould work.

But it could be easier to simply use the worksheet.usedrange.clear method to empty the existing worksheet. Then you could insert your new Data. This should be a lot faster than the delete-approach.

answered Sep 26 '12 at 09:15

Christian Sauer

10,351
10
53
85

Delete open book

3 Answers3