VBA Code Required

[hrayani]

Hello Friends,

I need a VBA code which could do the following

1) Go to each file (regardless of the name) in a folder named "Test Folder" located at "D: Drive"
2) Delete a worksheet named "123" from that file
3) And select worksheet named "abc"

In case if the file "123" which is supposed to deleted is not found then close that file as it is

Any help would be appreciated

Regards,

Humayun

 

[Marc L]

Hello,​

​

as you forgot to elaborate which kind of files … (file extension or format ?)​

 

[hrayani]

Hello,​

​

as you forgot to elaborate which kind of files … (file extension or format ?)​

Hi Marc,
Yes I should have mentioned in my initial post
its "xlsx"

 

[Marc L]

A VBA demonstrration for starters :​

Function ExistWorkbookSheet(BOOK, SHEET) As Boolean
                      Dim V
                          V = Evaluate("ISREF('[" & BOOK & "]" & SHEET & "'!A1)")
         ExistWorkbookSheet = IIf(IsError(V), False, V)
End Function

Sub Demo1()
  Const P = "D:\Test Folder\", D = "123", S = "abc"
    Dim F$, B As Boolean
        F = Dir$(P & "*.xlsx"):  If F = "" Then Beep: Exit Sub
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    Do
        With Workbooks.Open(P & F, 0)
               B = ExistWorkbookSheet(F, D)
            If B Then
               .Sheets(D).Delete
                If ExistWorkbookSheet(F, S) Then Application.Goto .Sheets(S).[A1], True
            End If
               .Close B
        End With
               F = Dir$
    Loop Until F = ""
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
End Sub

 

[hrayani]

Hi Marc

I tried the code and it works just PERFECT
Thanks...
Just 2 more points
1) Is it possible to pick folder location mentioned in Sheet1 range A1
2) close the file when the code is over... savechanges = true

 

[Marc L]

​

1) Yes, just replace the variable P with your cell reference …​

​

2) Unclear …​

 

[hrayani]

1) Yes, just replace the variable P with your cell reference …

I tired but it did not work.

error with below "Constant expression required"

 Const P = Sheets("event").Range("A1").Value, D = "123", S = "abc"

and this is what I have in cell A1 = D:\Test Folder\

I am pretty sure that I am doing something wrong here. But I do not know how to make it work
Can you help pls

 

[Marc L]

​

Can't be anymore a constant but just a variable like F & B …​

 

[hrayani]

​

Can't be anymore a constant but just a variable like F & B …​

I did not get it - sorry its way beyond my little knowledge of VBA

Can you just amend the code for me please

Sheet name is "event" and range A1 will be having the folder path D:\Test Folder\

 

[hrayani]

Hi Marc,

After tons of experiment I came up with this & it works fine

Sub Demo1()

  Const D = "123", S = "abc"
    Dim F$, B As Boolean
        F = Dir$(Sheets("event").Range("A1").Value & "*.xlsx"):  If F = "" Then Beep: Exit Sub
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    Do
        With Workbooks.Open(Sheets("event").Range("A1").Value & F, 0)
               B = ExistWorkbookSheet(F, D)
            If B Then
               .Sheets(D).Delete
                If ExistWorkbookSheet(F, S) Then Application.Goto .Sheets(S).[A1], True
            End If
               .Close B
        End With
               F = Dir$
    Loop Until F = ""
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
    
End Sub