VBA - foundfiles and referencing specific filenames

[steimel386]

Hey everyone,

When I used foundfiles to pull data from a directory and I pull the names of said files it seems like that it pulls the data, and THEN pulls the names. What happens is that later in the macro it is set up to delete out rows that have an empty value for coulmn A, which it does, but leaves the names produced by foundfiles giving me incorrect data.... Does this just happen or is there a way to link the filenames (I don't care if the directory is there or no, whatever is easier) to the corresponding data? I've included part of the macro below, and thanks for your help. (these all run at once by one larger macro with the call function)

Sub Jan09_NoSpace()
Sheets("Jan09_NoSpace").Select
n = 3
 On Error Resume Next
 With Application.FileSearch
     .LookIn = ThisWorkbook.Path
     .FileType = msoFileTypeExcelWorkbooks
     .SearchSubFolders = True
     .Execute
     For i = 1 To (.FoundFiles.Count - 1)
         Workbooks.Open .FoundFiles(i)
         n = n + 1
         ThisWorkbook.Sheets(1).Cells(n, 164).Value = .FoundFiles(i)
'this is where the name problem seems to come from, and if I move this to the end of the list it simply doesn't pull anything
         With Sheets("WORK(OPT#1)")
ThisWorkbook.Sheets("Jan09_NoSpace").Cells(n, 1).Value = .Range("F1").Value
'etc--------------------------------------------
ThisWorkbook.Sheets("Jan09_NoSpace").Cells(n, 162).Value = .Range("T71").Value
ThisWorkbook.Sheets("Jan09_NoSpace").Cells(n, 163).Value = .Range("Jan '09").Value
         End With
    
         ActiveWorkbook.Close False
NotMe:
     Next
 End With
 
 
Sub Delete_Empty()
    Dim lLastRow As Long, rngToCheck As Range
    With ActiveSheet
        'find the last row in column A
        lLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        
        Set rngToCheck = .Range(.Cells(1, 1), .Cells(lLastRow, 1))
    End With
    
    On Error Resume Next
    rngToCheck.SpecialCells(xlCellTypeBlanks).ListRow.Delete
    On Error GoTo 0
End Sub

 

[steimel386]

Bump....