Folders and Sub folder

[adeel1]

Hi All
I have one folder say “AA” there is 10 sub folders.

My Qes.

1> Does all sub folders can be deleted without loop? (AA will not delete)
2> Does Main “AA” can be deleted if it has subfolders(directly).
3> If sub folder has files etc. then still can be deleted directly (without loop)
Adeel

[HansV]

The answer to all three questions is Yes.
Let's say AA is C:\MyFolder\AA.
To delete all subfolders of AA together with their files and deeper subfolders:

Code: Select all

Sub DeleteSubfolders()
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    fso.DeleteFolder "C:\MyFolder\AA\*.*"
End Sub

To delete AA itself, with all its files and subfolders:

Code: Select all

Sub DeleteAA()
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    fso.DeleteFolder "C:\MyFolder\AA"
End Sub

[adeel1]

thx Worked!! perfectly


Another Qes, sorry its not as per title but if you don’t mind then
What is difference between below

Dim s1 As Worksheet
Set s1 = Sheet1
Set s1 = Worksheets("Sheet1")

Frist one sometimes doesn’t work when I change with second one its works, I want to know why
Adeel

[HansV]

A sheet has two names:
1) The name on the sheet tab.
2) The so-called code name. It is only available in VBA. You can see it in the Properties pane as the (Name) property.

Initially, the two names are usually the same:

S1849.png

But when you rename the tab name, they can be different:

S1850.png

You refer to the sheet by its tab name as Sheets("Sheet1") or Worksheets("Sheet1")
To refer to its code name, simply use Sheet1.

If the sheet has been renamed, you have to use Sheets("Sales 2022") or Worksheets("Sales 2022"), but you'd still use Sheet1 to refer to its code name.

[adeel1]

Many thx for info, I will keep it in mind for future
Adeel

[DocAElstein]

Hello adeel1,
Because you seem keen to learn, maybe this extra info about referencing worksheets may be of interest to you.
Its all some more information about Sheets and Worksheets referencing.

_1) The first thing is not important. Just syntax.
_2) The second can be useful to use if you do not know a worksheet Name, but still want to reference it in VBA coding: Like many things in VBA
, you can refer to a worksheet by a number.

_3) The third may only be useful to know if you both
have workbooks with different sort of sheets in it, and you are using ( _2) ) the number way to refer to the sheets


_1) Syntax
The full syntax for referencing a worksheet is like Worksheets.Item("Sheet1")
But often in VBA, the default property is the Item property, so if we miss out the .Item , then Excel will "guess" that we want the item


_2) Worksheet number ( item number )
This can be useful if you want to reference a worksheet but do not know its name.
Many things in VBA are listed in a known order and sometimes we can reference them by their number
The first worksheet has the number 1 , the second worksheet number 2 etc.

VBA knows if we want to reference by the name or the number by whether or not you include the " " pair:
2 means item number, and will always be the second thing
"2" means a Name of 2, and it may or may not be the second thing. (For some things in VBA you may only be able to refer to it by its number. In those cases, if you give a "2", VBA may assume that you meant the number 2, but that is not the case here for worksheet referencing: For refering to worksheets, "2" and 2 are read by VBA as meaning two different things)

To demonstrate this _2) I have deliberately chosen some interesting Names , ( tab names ) , in the attached file, WorksheetNumberNames.xls , like this:


Run these macros in that workbook, and you may understand what I am talking about:

Code: Select all

'   https://eileenslounge.com/viewtopic.php?p=299730#p299730
Sub NumberNames()
Dim Cnt As Long
    For Cnt = 1 To ThisWorkbook.Worksheets.Count ' will loop for  1  and  2
     MsgBox prompt:="This workbooks worksheet Item number " & Cnt & " has a name of " & ThisWorkbook.Worksheets.Item(Cnt).Name
    Next Cnt
End Sub
Sub Worksheet_1()
' Use worksheet item number 1  - In this workbook it will referrence the first worksheet
 ThisWorkbook.Worksheets.Item(1).Activate
 Let ThisWorkbook.Worksheets.Item(1).Range("A1").Value = "This is first cell in first worksheet"
Dim Won As Long: Let Won = 1
 Let ThisWorkbook.Worksheets.Item(Won).Range("A1").Value = "This is still first cell in first worksheet"

' Use worksheet name "1"  - In this workbook it will referrence the second worksheet
 ThisWorkbook.Worksheets.Item("" & 1 & "").Activate
 Let ThisWorkbook.Worksheets.Item("" & 1 & "").Range("A1").Value = "This is first cell in second worksheet"
 Let ThisWorkbook.Worksheets.Item("" & Won & "").Range("A1").Value = "This is still first cell in second worksheet"
End Sub

_3) Sheets or Worksheets
Often Sheets and Worksheets are the same, but not always
Worksheets refers only to worksheets. Sheets can refer to worksheets and other things. ( I do not have much experience with the other things, but I do know that there are other things which can be referenced by Sheets. That is mostly all I know about it)

I have done a second workbook for you to help demonstrate, WorksheetsAndSheets.xls
If you look in the Excel spreadsheet it appears to be showing you 3 worksheets:


But it is not showing you 3 worksheets: There are not 3 worksheets there. Only the first and the third are worksheets. The second thing is something else. You can check that by looking in the VB Editor Project explorer window and you will see just two worksheets:


If you run these 2 macros in that workbook then you will get different results

Code: Select all

 '   https://eileenslounge.com/viewtopic.php?p=299730#p299730
Sub MySheets()
Dim Cnt As Long
     For Cnt = 1 To ThisWorkbook.Sheets.Count
      MsgBox prompt:="Sheet number " & Cnt & " has a name of " & ThisWorkbook.Sheets.Item(Cnt).Name
     Next Cnt
End Sub
Sub MyWorksheets()
Dim Cnt As Long
     For Cnt = 1 To ThisWorkbook.Worksheets.Count
      MsgBox prompt:="Worksheet number " & Cnt & " has a name of " & ThisWorkbook.Worksheets.Item(Cnt).Name
     Next Cnt
End Sub 

The second tab thing, Sheet2, does not have
a CodeName
, and as you saw in the last screenshot it does not have a worksheets object code module
, and if you right click on the second tab you will see that the View Code option is not available: its greyed out



I personally prefer to use Worksheets for referring to worksheets, but its just personal choice. Most people use Sheets .
If you use Sheets and always use the Name, then you will probably never have any issues,


( My example is not so good since the second sheet is an old sort of thing that you may never need to know about. Somebody who knows more about the different sorts of sheets could probably give a better example using different sorts of sheets, using sheets that you are more likely to come across at some time. I mostly only know about worksheet sheets and the strange old thing I have in the file )




Alan

[adeel1]

thx Alan sir for your input here and much more info regarding this.

Adeel