none
Check if Worksheets names match strings in a range RRS feed

  • Question

  • From a Range of strings, want to see it these strings have matching (by name) worksheets, and if not creating new worksheet.  Also if worksheet exists that is not in the original range, deleting them out.  For example, in golf, one name is pro golfer Charlie Wi and he has been added to the range, but need code to check if there is a Charlie Wi worksheet; Also Payne Stewart (RIP) was removed from range - has his worksheet been deleted.

    Thank you.

    Saturday, February 11, 2012 10:04 PM

Answers

  • Hi

    Here is a hint

    Sub CheckAndDeleteFiles()
    
    Dim sPath As String
    Dim sFile As String
    Dim sDir As String
    Dim oLB As Workbook
    Dim i1 As Long
    Dim iMax As Long
    
    
    
    sPath = "C:\Users\Om\Downloads\"
    
    sDir = Dir(sPath & "*.xls", vbNormal)
    Do Until LenB(sDir) = 0
       Debug.Print sDir
        sFile = Replace(sDir, ".xls", "")
        
        If ActiveWorkbook.Sheets(1).Range("A:A").Find(sFile) Is Nothing Then
            Kill sPath & sDir
        End If
        
        
        ' -- Do Something
        sDir = Dir$
    Loop
    
    
    End Sub
    
    

    The above snippet checks a Folder for all .XLS files and then checks if the Filename is present in Column A in First Sheet of the Open Workbook, You can tweak this for your requirement

    Cheers

    Shasur


    http://www.vbadud.blogspot.com http://www.dotnetdud.blogspot.com

    Sunday, February 12, 2012 1:49 AM

All replies

  • Hi

    Here is a hint

    Sub CheckAndDeleteFiles()
    
    Dim sPath As String
    Dim sFile As String
    Dim sDir As String
    Dim oLB As Workbook
    Dim i1 As Long
    Dim iMax As Long
    
    
    
    sPath = "C:\Users\Om\Downloads\"
    
    sDir = Dir(sPath & "*.xls", vbNormal)
    Do Until LenB(sDir) = 0
       Debug.Print sDir
        sFile = Replace(sDir, ".xls", "")
        
        If ActiveWorkbook.Sheets(1).Range("A:A").Find(sFile) Is Nothing Then
            Kill sPath & sDir
        End If
        
        
        ' -- Do Something
        sDir = Dir$
    Loop
    
    
    End Sub
    
    

    The above snippet checks a Folder for all .XLS files and then checks if the Filename is present in Column A in First Sheet of the Open Workbook, You can tweak this for your requirement

    Cheers

    Shasur


    http://www.vbadud.blogspot.com http://www.dotnetdud.blogspot.com

    Sunday, February 12, 2012 1:49 AM
  • Thanks I will check it out!

    Sunday, February 12, 2012 3:29 AM