Retrieving data in multiple closed workbooks

ijswalker

New Member
Joined
Nov 22, 2005
Messages
31
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
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Nimrod

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259
hello ijswalker:
Yep ! :wink:

Here's an example ...


Code:
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

New Member
Joined
Nov 22, 2005
Messages
31
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

New Member
Joined
Nov 22, 2005
Messages
31
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

New Member
Joined
Nov 22, 2005
Messages
31
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,477
Members
414,070
Latest member
DuncanLucas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top