'Consolidate' Macro stopped working

Hi Guys,

I have been using a bit of code I got on here, it is:

Sub Consolidate()

'Summary:    Open all Excel files in a specific folder and imports
'            key date into a Summary sheet, one row of data per workbook
Dim fName As String, fPath As String, fpath2 As String, OldDir As String, strsubaddress As String, strname As String

Dim NR As Long
Dim wbData As Workbook, wbkNew As Workbook
Dim ws As Worksheet

    Dim wkb As Workbook
    Dim wkbcount As Integer

    wkbcount = 0

    For Each wkb In Workbooks
        wkbcount = wkbcount + 1
    Next

    If wkbcount > 1 Then
        MsgBox "Please close all other workbooks."
        Exit Sub
    End If


'Setup
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.DisplayAlerts = False
   
    Set wbkNew = ThisWorkbook
    wbkNew.Activate
    Sheets("Sheet1").Activate   'sheet report is built into, edit to correct sheet name
    If MsgBox("Import new data to this report?", vbYesNo) = vbNo Then Exit Sub
    
    strsubaddress = "'Costs Summary'!A1"
    strname = InputBox(prompt:="SEARCH PARAMETER", Title:="Enter Search Parameter", Default:="-")
   
    Cells.Clear
    Range("A1:L1").Value = [{"","Description","per L/Kg","250ml","500ml","1L","2L","5L","20L","25L","200L","1000L"}]
    NR = 3

'Path and filename (edit this section to suit)
    fPath = "D:\XXXXX\Recipes & Costs\Mixes\"       'remember final \ in this string
    OldDir = CurDir                     'memorizes the users current working path
    ChDir fPath                        'activate the filepath with files to import
    fName = Dir("*" & strname & "*.xls")              'start a listing of desired files, edit the filter as desired

'Import a sheet from found file
    Do While Len(fName) > 0
        If fName <> wbkNew.Name Then    'make sure this file isn't accidentally reopened
        'Open file
            Set wbData = Workbooks.Open(fName)

        'This is the section to customize, replace with your own action code as needed
            With wbkNew.Sheets("sheet1")
                Dim strTemp As String
                    strTemp = wbData.Name
                
                .Range("A" & NR) = Replace(strTemp, ".xls", "")
                .Hyperlinks.Add anchor:=.Range("A" & NR), Address:=fPath & fName, subaddress:=strsubaddress
                .Range("B" & NR) = Sheets("Data list").Range("B2")
                .Range("C" & NR) = Format(Sheets("Costs Summary").Range("C3"), "0.00")
                .Range("D" & NR) = Format(Sheets("Costs Summary").Range("C6"), "0.00")
                .Range("E" & NR) = Format(Sheets("Costs Summary").Range("C9"), "0.00")
                .Range("F" & NR) = Format(Sheets("Costs Summary").Range("C12"), "0.00")
                .Range("G" & NR) = Format(Sheets("Costs Summary").Range("C15"), "0.00")
                .Range("H" & NR) = Format(Sheets("Costs Summary").Range("C18"), "0.00")
                .Range("I" & NR) = Format(Sheets("Costs Summary").Range("C21"), "0.00")
                .Range("J" & NR) = Format(Sheets("Costs Summary").Range("C24"), "0.00")
                .Range("K" & NR) = Format(Sheets("Costs Summary").Range("C27"), "0.00")
                .Range("L" & NR) = Format(Sheets("Costs Summary").Range("C30"), "0.00")
                .Range("M" & NR) = Format(Sheets("Costs Summary").Range("C33"), "0.00")
                
                
            End With
            
        'close file
            wbData.Close False
        'Next row
            NR = NR + 1
        'ready next filename
            fName = Dir
        End If
    Loop



ErrorExit:    'Cleanup
    Range("A1:M1").Select
    Selection.Font.Bold = True
    Range("A1:M200").Select
    With Selection.Font
        .Name = "Arial"
        .Size = 15
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
    End With
    
    ActiveSheet.Columns.AutoFit


    
    
    Range("A2:M200").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=MOD(ROW(),2)=1"
    Selection.FormatConditions(1).Interior.ColorIndex = 34
    
    Range("A1:A200,C1:C200,E1:E200,G1:G200,I1:I200,K1:K200,M1:M200").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range("A1:M200").Select
    Range("M200").Activate
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    Range("A1:M1").Select
    With Selection.Interior
        .ColorIndex = 15
        .Pattern = xlSolid
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    Application.DisplayAlerts = True         'turn system alerts back on
    Application.EnableEvents = True          'turn other macros back on
    Application.ScreenUpdating = True        'refreshes the screen
    ChDir OldDir                             'restores users original working path

End Sub

Excuse the bit at the end, that is just tidying up formatting.

Anyhow, this has all worked well but I have recently re-installed my system and changed the filepath (it is right, I can get a macro to open it) but the above macros isn't doing anything other than setting up my consolidate sheet and formatting it - it isn't opening any files.

Any ideas why?

Thanks

I have managed to make it work but don't understand the reason it now needs this. I guess the problem is coming in about a third of the way down with:

   ChDir fPath                        'activate the filepath with files to import

The next line is:

 fName = Dir("*" & strname & "*.xls")

But this isn't giving me any results (I put in a bit which gives me an error code if fname = 0). If I amend it to be:

 fName = Dir(fpath & "*" & strname & "*.xls")

It gives me results. However it fails at:

            Set wbData = Workbooks.Open(fName)

Again, it works if I roll in the fpath to give:

Set wbData = Workbooks.Open(fPath & fName)

Now it seems to work. But why!? Why is the Chdir not bringing me to the specified directory? Why is my workbook open function not working with the fname I have specified (I presume fname is taking on only the name and not the path, even though I have specified path...).

I am confused as to why this has changed...