エクセルマクロについての質問です。回答内容によって、５００ポイント差し上げます。

以下のマクロをaaaa.xlsに置いて利用してみてください。

・bbbb.xls、ccccフォルダはaaaa.xlsと同じパスにあると想定して作成しました。

　定数値を変更することで調整が可能です。

・ccccフォルダのファイルは、最大３つまで(h,i,j列)であると想定して作成しました。

　４つ目以降のファイル名は無視されます。

Option Explicit

Enum ROW_NUM
    a = 0
    b
    c
    d
    e
    f
    g
    h
    i
    j
    k
End Enum

Const START_CELL = "a1"
Const FILE_BBBB = "bbbb.xls"
Const PATH_CCCC = "cccc"
Dim FSO As Object

' まとめた結果シートを作成する
Sub CreateSummary()
    ' 初期化
    ' ------------------------------------------------------------------------------------------
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    ' bbbb open
    ' ActiveWorkbookが変わらないように、後でActiveWorkbookを元に戻す
    Dim bookBackup As Workbook
    Set bookBackup = ActiveWorkbook
        Dim bookDataEx As Workbook
        Set bookDataEx = Workbooks.Open(ActiveWorkbook.Path & "\" & FILE_BBBB)
        
        Dim sheetDataEx As Worksheet
        Set sheetDataEx = bookDataEx.Sheets(1)
    bookBackup.Activate
    Set bookBackup = Nothing    ' もうこの変数は使わない
    
    ' aaaa のシートを得る
    Dim sheetData As Worksheet
    Set sheetData = ActiveSheet
    
    ' まとめた結果シートを空シートとして作成
    Dim sheetSummary As Worksheet
    Set sheetSummary = Worksheets.Add(after:=Worksheets(Worksheets.Count))
    
    ' まとめデータ作成処理
    ' ------------------------------------------------------------------------------------------
    Dim nSequence As Long
    nSequence = 1
    
    ' 1行ずつ下に移動しながら各行を処理
    Dim rngActDataCell As Range
    Set rngActDataCell = sheetData.Range(START_CELL)
    Do While Trim(rngActDataCell.Text) <> ""
        ' bbbb の処理行を取得
        Dim rngActDataExCell As Range
        Set rngActDataExCell = sheetDataEx.Cells(rngActDataCell.Row, 1)
        
        ' まとめた結果シート の処理行を取得
        Dim rngSummary As Range
        Set rngSummary = sheetSummary.Cells(rngActDataCell.Row, 1)
        
        ' 条件判定
        Dim bConditionFlg As Boolean
        bConditionFlg = IsCondition(rngActDataExCell.Offset(0, ROW_NUM.b))
        
        ' ファイル名取得
        Dim sFilenameList() As String
        sFilenameList = GetFilenameList(rngActDataCell.Offset(0, ROW_NUM.a))
        
        ' 書き出し
        rngSummary.Offset(0, ROW_NUM.a).Value = nSequence
        rngSummary.Offset(0, ROW_NUM.b).Value = rngActDataCell.Offset(0, ROW_NUM.a)
        rngSummary.Offset(0, ROW_NUM.c).Value = _
            "【都道府県名】" & vbLf & _
            rngActDataCell.Offset(0, ROW_NUM.b) & vbLf & _
            "【市町村名】" & vbLf & _
            rngActDataCell.Offset(0, ROW_NUM.c)
        If bConditionFlg = True Then
            rngSummary.Offset(0, ROW_NUM.d).Value = rngActDataExCell.Offset(0, ROW_NUM.a)
            rngSummary.Offset(0, ROW_NUM.e).Value = rngActDataExCell.Offset(0, ROW_NUM.b)
        Else
            rngSummary.Offset(0, ROW_NUM.f).Value = rngActDataExCell.Offset(0, ROW_NUM.a)
            rngSummary.Offset(0, ROW_NUM.g).Value = rngActDataExCell.Offset(0, ROW_NUM.b)
        End If
        rngSummary.Offset(0, ROW_NUM.h).Value = sFilenameList(0)
        rngSummary.Offset(0, ROW_NUM.i).Value = sFilenameList(1)
        rngSummary.Offset(0, ROW_NUM.j).Value = sFilenameList(2)
        rngSummary.Offset(0, ROW_NUM.k).Value = _
            rngActDataCell.Offset(0, ROW_NUM.a) & rngActDataCell.Offset(0, ROW_NUM.d)
        
        
        Set rngActDataCell = rngActDataCell.Offset(1, 0)  '次の行へ
        nSequence = nSequence + 1
    Loop
    
    ' 後始末
    ' ------------------------------------------------------------------------------------------
    ' bbbb close
    bookDataEx.Close SaveChanges:=False
    
    MsgBox "処理終了"
    
End Sub

' 条件1.URLの最後の拡張子が.pdf､.xlsである場合
' 条件2.URLに「true」という文字が含まれている場合
' ...trueを戻す。
Function IsCondition(sUrl As String) As Boolean
    Dim sUrlLowwer As String
    sUrlLowwer = LCase(sUrl)
    
    If Len(sUrlLowwer) < 4 Then
        IsCondition = False
        Exit Function
    End If
    If Right(sUrlLowwer, 4) = ".pdf" Then
        IsCondition = True
        Exit Function
    End If
    If Right(sUrlLowwer, 4) = ".xls" Then
        IsCondition = True
        Exit Function
    End If
    If InStr(sUrlLowwer, "true") > 0 Then
        IsCondition = True
        Exit Function
    End If
    
    IsCondition = False
End Function

' ccccフォルダのファイル取得
Function GetFilenameList(sId As String) As String()
    Dim sList(2) As String
    
    Dim sTargetFolder As String
    sTargetFolder = ActiveWorkbook.Path & "\" & PATH_CCCC & "\" & sId
    
    If FSO.FolderExists(sTargetFolder) = False Then
        GetFilenameList = sList
        Exit Function
    End If
    
    Dim oFolder As Object
    Set oFolder = FSO.GetFolder(sTargetFolder)
    
    Dim nIdx As Integer
    nIdx = 0
    Dim oFile As Object
    For Each oFile In oFolder.Files
        sList(nIdx) = oFile.Name
        nIdx = nIdx + 1
        If nIdx > UBound(sList) Then Exit For
    Next
    
    GetFilenameList = sList
End Function