• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Delete workbooks with specific prefix in its name

Manish Sharma

Manish Sharma

Member
Hi friends,

I have a small question, is it possible to close and delete open workbook with some specific prefix in the file name.

Suppose i have 4 open workbooks, say Workbook 1, Data 2, Data 3 (Number will keep on changing), RawData 4

Now from Workbook 1, i want to close and delete the second and third workbook with file name prefix "Data" (The numerical values will keep on changing so i cant give the full workbook name to close and delete).

Please let me know if it is possible and how.


Thanks & Regards
 
Hi Manish,

Add this code to the module . This closes only the Workbooks beginning with "Data".

Code: 
Sub CloseOpenDataWkbk()
    Dim wkbk As Workbook
    For Each wkbk In Workbooks
        If Left(wkbk.Name, 4) = "Data" Then
           wkbk.Close True
        End If
    Next wkbk
End Sub
[/CODE=vb]
 
Hi Manish,

Look the below code which close all the workbooks except the main, then it will delete all the files in the particular folder, only the file name start with "DATA".


Sub test()

'******close all workbooks except the main one**********

Dim WB As Workbook

For Each WB In Workbooks

If Not WB.Name = ThisWorkbook.Name Then
WB.Close SaveChanges:=True
End If

Next WB


'******Delete all workbooks which match with DATA in the beginning**********

Dim targetfile As String

targetfile = Dir("c:\data*.xlsx") ' enter your path

Do While targetfile <> ""

Kill targetfile

targetfile = Dir()
Loop

End Sub




Regards
Abdul Matheen
 

Hi !

Better is to directly read the path of opened "Data" workbooks …

And please use code tags ‼​
 
lohithsriram said:
Hi Manish,

Add this code to the module . This closes only the Workbooks beginning with "Data".

Code: 
Sub CloseOpenDataWkbk()
    Dim wkbk As Workbook
    For Each wkbk In Workbooks
        If Left(wkbk.Name, 4) = "Data" Then
           wkbk.Close True
        End If
    Next wkbk
End Sub
[/CODE=vb]
Click to expand...

Hi Lohit, Code is working fine but i want to delete those file not just close.

I tried to replace "close" with "kill" or "delete" but its not working.

Can you please tell me what changes i have to do to delete the file.
 
Abdul Matheen said:
Hi Manish,

Look the below code which close all the workbooks except the main, then it will delete all the files in the particular folder, only the file name start with "DATA".


Sub test()

'******close all workbooks except the main one**********

Dim WB As Workbook

For Each WB In Workbooks

If Not WB.Name = ThisWorkbook.Name Then
WB.Close SaveChanges:=True
End If

Next WB


'******Delete all workbooks which match with DATA in the beginning**********

Dim targetfile As String

targetfile = Dir("c:\data*.xlsx") ' enter your path

Do While targetfile <> ""

Kill targetfile

targetfile = Dir()
Loop

End Sub




Regards
Abdul Matheen
Click to expand...


Thanks a lot Abdul Matheen, your code is working fine.

But in the first example for "close all workbooks except the main one" instead of just closing is it possible to delete the file? I tried it from my end but its not happening.
 
Hi, Manish Sharma!
Taking lohithsriram code I updated it slightly to do the job. This is the code, which I prefer over Abdul Matheen's once since it doesn't close unnecessary workbooks nor needs updating the code whenever the files location change:
Code: 
Option Explicit

Sub CloseOpenDataWkbk()
    Dim wkbk As Workbook, pth As Page
    For Each wkbk In Workbooks
        If wkbk.Name <> ThisWorkbook.Name Then
            If Left(wkbk.Name, 4) = "Data" Then
                pth = wkbk.FullName
                wkbk.Close True
                DoEvents
                Kill pth
            End If
        End If
    Next wkbk
End Sub

Regards!
 
SirJB7 said:
Hi, Manish Sharma!
Taking lohithsriram code I updated it slightly to do the job. This is the code, which I prefer over Abdul Matheen's once since it doesn't close unnecessary workbooks nor needs updating the code whenever the files location change:
Code: 
Option Explicit

Sub CloseOpenDataWkbk()
    Dim wkbk As Workbook, pth As Page
    For Each wkbk In Workbooks
        If wkbk.Name <> ThisWorkbook.Name Then
            If Left(wkbk.Name, 4) = "Data" Then
                pth = wkbk.FullName
                wkbk.Close True
                DoEvents
                Kill pth
            End If
        End If
    Next wkbk
End Sub

Regards!
Click to expand...

Thanks SirJB7,

I am getting Run-time error 91, while trying this code. Can you please help me with this.
 
Hi, Manish Sharma!
NARAYANK991 is right, change type to String and it should work. It was a typo error, sorry.
Regards!

@NARAYANK991
Hi!
Thanks for the catch.
Regards!
 
Hi, Manish Sharma!
Glad you solved it. Credit to lohithsriram, I just added an If sentence. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!
 
You must log in or register to reply here.
Back
Top