Retrieving data in multiple closed workbooks

[ijswalker]

Hello,

Is it possible to writing a macro that looks up data in another workbook retrieves it from the workbook and moves onto the next workbook in the directory and so on until it has looked at all of the workbooks? All workbooks are identical other than the information contained in them. I could have up to 100 workbooks.

Please help

 

[Nimrod]

hello ijswalker:
Yep ! :wink:

Here's an example ...

Public Sub GetDirXlsContents()
' Source sheet name, Source directory path, Source cell Range
Call CopyFromEachFileInPath("Sheet1", "C:\test", "A1:I500")
End Sub

Private Sub CopyFromEachFileInPath(SheetName, Path, Rng)
    Dim fs, f, f1, fc, s
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(Path & "\")
    Set fc = f.Files
    
    ' make a temp sheet
    Application.ScreenUpdating = False
    TargSh = ActiveSheet.Name
    Sheets.Add
    TempSh = ActiveSheet.Name
    Sheets(TargSh).Activate
    Application.ScreenUpdating = True
    
    For Each f1 In fc
     With Sheets(TempSh)
     
    ' clear temp sheet and start again
    .Cells.ClearContents
     
    ' Place Src Info on Temp Targ Sheet
    If Right(f1.Name, 3) = "xls" Then
    fName = Left(f1.Name, Len(f1.Name) - 4)
    .Range(Rng).FormulaArray = "='" & Path & "\[" & fName & "]" & SheetName & "'!" & Rng
    .Range(Rng).Value = .Range(Rng).Value
    
     'GetValuesFromAClosedWorkbook Path, f1.Name, SheetName, "A1:K30"
    End If
    
    ' if columD = 1 copy over
    For Each A In .Columns("A:A").SpecialCells(xlCellTypeConstants, 3)
        NxRw = Cells(65536, 1).End(xlUp).Row + 1
        If Not A.Value = 0 And A.Offset(1, 0).Value = 0 Then   ' copy to final sheet
        Range("A" & NxRw & ":I" & NxRw).Value = .Range("A" & A.Row & ":Z" & A.Row).Value
        Range("J" & NxRw).Value = fName
        End If
    Next A
    End With
    ' have user see list build, so know not frozen
    Cells(NxRw, 1).Select
    Next ' workbook
    
    ' get rid of temp sheet
    Application.DisplayAlerts = False
    Sheets(TempSh).Delete
    Application.DisplayAlerts = True

       
End Sub

 

[ijswalker]

Data Row

Unbelievable, That is quite amazing. It pulls every Cell that is populated. Is there a way to select the individual Cells in the workbook rather than a range. I am trying to pull the data from certain Cells to end up with one row of data that relates to one workbook.

Once again - quite amazing!!!!

 

[ijswalker]

Since this last posting I have figured out a way of putting my data in rows. So disregard that last question. However, this leads me to a new problem.

Is there a way of Retrieving text from text boxes in closed excel files and putting it into rows in order for my form to pick up the text?

E.g, I have 4 text boxes in my excel workbook. I want to pull the info from each of the text boxes and have the text put into different cells all in the same row. Can I also keep the formatting from the text box.

Thanks

ijswalker

 

[ijswalker]

I have one more question relating to the solution I was furnished with before. The code allows me to select a range from the closed workbooks which is great. However, the workbooks I have now have different data ranges that I am trying to transfer in order into my master database.

Is there a way of looking into each of the workbooks - extracting the rows of data and then moving onto the next workbook and extracting a different number of rows of data?

For illustration purposes. My first workbook will have 5 rows of data and my second workbook will have 6 rows of data. I would like to have 11 rows of data in my master workbook.

Hope you can help?

Thanks

Ian