none
Help with application.filesearch (2003) code conversion to 2010 RRS feed

  • Question

  • Hi - I inherited this workbook (and extensive macros) from someone else. It no longer works now that our organization has upgraded to 2010. We are a small non-profit and no one else knows how to do this. I'm having trouble getting the code converted to a 2010-compatible macro. Would someone be able to help??

    On Error Resume Next
            Set wbCodeBook = ThisWorkbook
                With Application.FileSearch
                    .NewSearch
                
                    'Change path to suit
                    .LookIn = myPath
                    .SearchSubFolders = False
                    .FileType = msoFileTypeExcelWorkbooks
                
                        If .Execute > 0 Then 'Workbooks in folder
                            For lCount = 1 To .FoundFiles.Count 'Loop through all
                                
                                'Set xlBook = Workbooks.Open(myPath & "\NCC_Scheduling-" & lCount)
                                Set xlBook = Workbooks.Open(FileName:=.FoundFiles(lCount), UpdateLinks:=0)
                                Set xlSheet = xlBook.Worksheets("Ministry_Dates")
                            
                                'count number of date entry rows in the Non-NCC event columns (A, B and C)
                                Sheets(xlSheet).Activate
                                y1 = Application.WorksheetFunction.CountA(Range("A5:A500"))
                            
                                'count number of date entries in the NCC event columns (E, F and G)
                                r1 = Application.WorksheetFunction.CountA(Range("E5:E500"))
                                                    
                                'Set x1, the variable that sets the last cell in the Non-NCC event range to copy
                                If y1 = 0 Then
                                    x1 = 5
                                Else
                                    x1 = 4 + y1
                                End If
                               
                                'Set x2, the variable that sets the last cell in the NCC event range to copy
                                If r1 = 0 Then
                                    x2 = 5
                                Else
                                    x2 = 4 + r1
                                End If
                                                    
                                xlSheet.Range("A5:C" & x1).Copy
                                xlThisSheet.Range("Z" & z1).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                                False, Transpose:=False
                            
                                xlSheet.Range("E5:G" & x2).Copy
                                xlThisSheet.Range("AD" & z2).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                                False, Transpose:=False
                            
                                RowCounter1 = RowCounter1 + y1
                                RowCounter2 = RowCounter2 + r1
                            
                                z1 = RowCounter1
                                z2 = RowCounter2
                                                        
                                xlBook.Close
                            
                            Next lCount
                        End If
                End With
    -Carly


    Thursday, September 4, 2014 7:54 PM

Answers

  • Code could look like this:

        Dim myFile As String
    
        ' Get name of first Excel file in folder
        myFile = Dir(myPath & "*.xls?")
    
        ' Loop through the Excel files
        Do While myFile <> ""
            ' Open workbook
            Set xlBook = Workbooks.Open(Filename:=myPath & myFile, UpdateLinks:=False)
            Set xlSheet = xlBook.Worksheets("Ministry_Dates")
    
            'count number of date entry rows in the Non-NCC event columns (A, B and C)
            y1 = Application.WorksheetFunction.CountA(xlSheet.Range("A5:A500"))
    
            'count number of date entries in the NCC event columns (E, F and G)
            r1 = Application.WorksheetFunction.CountA(xlSheet.Range("E5:E500"))
    
            'Set x1, the variable that sets the last cell in the Non-NCC event range to copy
            If y1 = 0 Then
                x1 = 5
            Else
                x1 = 4 + y1
            End If
    
            'Set x2, the variable that sets the last cell in the NCC event range to copy
            If r1 = 0 Then
                x2 = 5
            Else
                x2 = 4 + r1
            End If
    
            ' Copy data to sheet in code workbook
            xlSheet.Range("A5:C" & x1).Copy
            xlThisSheet.Range("Z" & z1).PasteSpecial Paste:=xlValues
    
            xlSheet.Range("E5:G" & x2).Copy
            xlThisSheet.Range("AD" & z2).PasteSpecial Paste:=xlValues
    
            ' Increase counters
            RowCounter1 = RowCounter1 + y1
            RowCounter2 = RowCounter2 + r1
    
            z1 = RowCounter1
            z2 = RowCounter2
    
            ' Close workbook
            xlBook.Close SaveChanges:=False
    
            ' On to the next workbook
            myFile = Dir
        Loop

    Make sure that myPath ends in a backslash, otherwise the code won't work.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, September 4, 2014 10:28 PM

All replies

  • Code could look like this:

        Dim myFile As String
    
        ' Get name of first Excel file in folder
        myFile = Dir(myPath & "*.xls?")
    
        ' Loop through the Excel files
        Do While myFile <> ""
            ' Open workbook
            Set xlBook = Workbooks.Open(Filename:=myPath & myFile, UpdateLinks:=False)
            Set xlSheet = xlBook.Worksheets("Ministry_Dates")
    
            'count number of date entry rows in the Non-NCC event columns (A, B and C)
            y1 = Application.WorksheetFunction.CountA(xlSheet.Range("A5:A500"))
    
            'count number of date entries in the NCC event columns (E, F and G)
            r1 = Application.WorksheetFunction.CountA(xlSheet.Range("E5:E500"))
    
            'Set x1, the variable that sets the last cell in the Non-NCC event range to copy
            If y1 = 0 Then
                x1 = 5
            Else
                x1 = 4 + y1
            End If
    
            'Set x2, the variable that sets the last cell in the NCC event range to copy
            If r1 = 0 Then
                x2 = 5
            Else
                x2 = 4 + r1
            End If
    
            ' Copy data to sheet in code workbook
            xlSheet.Range("A5:C" & x1).Copy
            xlThisSheet.Range("Z" & z1).PasteSpecial Paste:=xlValues
    
            xlSheet.Range("E5:G" & x2).Copy
            xlThisSheet.Range("AD" & z2).PasteSpecial Paste:=xlValues
    
            ' Increase counters
            RowCounter1 = RowCounter1 + y1
            RowCounter2 = RowCounter2 + r1
    
            z1 = RowCounter1
            z2 = RowCounter2
    
            ' Close workbook
            xlBook.Close SaveChanges:=False
    
            ' On to the next workbook
            myFile = Dir
        Loop

    Make sure that myPath ends in a backslash, otherwise the code won't work.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, September 4, 2014 10:28 PM
  • Hans, thank you so much! We had to tweak the code a bit to make it work, but everything is functioning great now. Thank you again!
    Sunday, September 7, 2014 4:46 AM