I Have a text list of city names and I need to check and see if those names are the names of worksheets and if not create a new worksheet with the said name.
On the other side if there are any worksheets without a name on the list then I need to delete those worksheets.
I've been trying this problem for a few hours and am very lost, its probably something simple, so if someone can help that would be very appreciated!
Here's just about what a code for this would look like. Here's what my text file looks like so there are no confusions.
Sub Macro1()
Dim sFileName, tmp As String
Dim dict As New Dictionary
Dim dictCopy As New Dictionary
Dim ws As Worksheet
sFileName = "C:\Users\Mango\Desktop\names.txt"
'^ Specify the location of the txt file that you want read
With New Scripting.FileSystemObject
With .OpenTextFile(sFileName, ForReading)
Do Until .AtEndOfStream
tmp = .ReadLine
'^ This step should be noted. Calling .Readline more than once
' even in loop cause it to move to the next one.
dict.Add tmp, tmp
dictCopy.Add tmp, tmp
'^ add to our dictionary objects
Loop
End With
End With
This is a text reading code that I picked up form here: Read lines from a text file but skip the first two lines. I find it more elegant than other vba text reading code. But you need to have a "Microsoft Scripting Runtime" selected as a reference (from the tools menu) to use it. This code also makes use of the Dictionary object which also requires that reference to use it. I used this object as opposed to an array or other collection for reasons that I will explain below.
For Each ws In ActiveWorkbook.Worksheets
If dict.Exists(ws.Name) = True Then
dict.Remove (ws.Name)
End If
Next
For Each j In dict.Items
Set ws = Worksheets.Add(after:=Worksheets(Worksheets.Count))
ws.Name = j
Next
Depending on how long your list is you might find the Dictionary object favorable to double looping and the like. This is because it has the .Exists method to see if it contains a specific value within itself. I don't know the mechanics inside it, but it is MUCH faster than the double looping technique, especially when you have a lot of entries to compare to. You should note that alot of these dictionary methods make use of the keys info (and not values).
Final part:
Application.DisplayAlerts = False 'removes annoying save notification
For Each ws In ActiveWorkbook.Worksheets
If dictCopy.Exists(ws.Name) = False Then
ws.Delete
End If
Next
Application.DisplayAlerts = True
End Sub
I chose to do it like this because excel will not allow you to delete the last sheet in a workbook. So if you chose to delete the wrongly named sheets or at least combine the method, you might run into issues if none of the sheet names mactches those in the collection all of them must be deleted.
