(Solved) – I need some help modifying excel vba code


I have a code below, which i cant seem to get to work.

The process is to search a specific paths subfolders for an excel book with a particular name,
once its found, i need to check if the document has a particular worksheet present within, if it does, do noting, if it doesnt, insert a sheet from another file i have and close the document, i need this to loop through every folder withing a specific path (approx 300 files in total)

this is the code i have so far

Public strDestinationPath As String
Public strSearch As Variant

Sub SearchFolders()

Range("B1").Value = "Name"
Range("C1").Value = "Path"
Range("D1").Value = "Size (KB)"
Range("E1").Value = "DateLastModified"
Range("F1").Value = "Attributes"
Range("G1").Value = "DateCreated"
Range("H1").Value = "DateLastAccessed"
Range("I1").Value = "Drive"
Range("J1").Value = "ParentFolder"
Range("K1").Value = "ShortName"
Range("L1").Value = "ShortPath"
Range("M1").Value = "Type"

Dim strPath As String
strPath = UserGetFolder & ""

strSearch = InputBox("Enter Search Criteria (Case Sensitive)")

Dim OBJ As Object
Dim Folder As Object
Dim File As Object
Set OBJ = CreateObject("Scripting.FileSystemObject")
Set Folder = OBJ.GetFolder(strPath)

Call ListFiles(Folder)

Dim SubFolder As Object

For Each SubFolder In Folder.SubFolders
    Call ListFiles(SubFolder)
    Call GetSubFolders(SubFolder)
Next SubFolder

If Range("B2").Value = "" Then
    MsgBox "No Files Found", vbInformation
End If


End Sub

Private Sub ListFiles(ByRef Folder As Object)

For Each File In Folder.Files

    If InStr(File.Name, strSearch) <> 0 Then

        ActiveCell.Offset(1, 0).Select
        ActiveCell = File.Name
        ActiveCell.Offset(0, 1) = File.Path
        ActiveCell.Offset(0, 2) = (File.Size / 1024) 'IN KB
        ActiveCell.Offset(0, 3) = File.DateLastModified
        ActiveCell.Offset(0, 4) = File.Attributes
        ActiveCell.Offset(0, 5) = File.DateCreated
        ActiveCell.Offset(0, 6) = File.DateLastAccessed
        ActiveCell.Offset(0, 7) = File.Drive
        ActiveCell.Offset(0, 8) = File.ParentFolder
        ActiveCell.Offset(0, 9) = File.ShortName
        ActiveCell.Offset(0, 10) = File.ShortPath
        ActiveCell.Offset(0, 11) = File.Type

    End If

Next File

End Sub

Private Sub GetSubFolders(ByRef SubFolder As Object)

Dim FolderItem As Object

For Each FolderItem In SubFolder.SubFolders
    Call ListFiles(FolderItem)
    Call GetSubFolders(FolderItem)

    If File = Survey_Additional_Info Then
    **Call WorksheetExists
    Call CopySheetToClosedWB**
    'do nothing
    End If

Next FolderItem

End Sub

Function UserGetFolder() As String
    Dim fldr As FileDialog
    Dim sItem As String
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        .InitialFileName = Application.DefaultFilePath
        If .Show <> -1 Then GoTo NextCode
        sItem = .SelectedItems(1)
    End With
    UserGetFolder = sItem
    Set fldr = Nothing
End Function

Function WorksheetExists(ByVal WorksheetName As String) As Boolean 'Code to find sheet in a file - added by me not part of original
Dim Sht As Worksheet

    For Each Sht In closedBook.Worksheets
        If Application.Proper(Time_Slots) = Application.Proper(Time_Slots) Then
            WorksheetExists = True
            Exit Function
            Else: Call CopySheetToClosedWB

        End If
    Next Sht
WorksheetExists = False
End Function

Sub CopySheetToClosedWB() 'Copy Worksheet to a Closed Workbook
Application.ScreenUpdating = False

    Set closedBook = Workbooks.Open("S:AccordantSUSNewTimeSlotTab.xlsx")
    Sheets("Time_Slots").Copy Before:=closedBook.Sheets(Alternative_Locations)
    closedBook.Close SaveChanges:=True

Application.ScreenUpdating = True
End Sub

my code works well up to the point it gets to

call worksheet exists

can anyone point me in the right direction?

Leave a Reply

Your email address will not be published. Required fields are marked *