Hello,

Got a tricky one: I have a list of account numbers for which I want to
retrieve associated data. The account number and its data I seek is found in
one of several files. To re-cap the commands: For each account found in the
list, go through the files in folder A, find the account number and return
the entire row of data for it.... then on to the next account number.

Any hope? TIA!

Is there any pattern as to where that account number may be in the files
eg first sheet column C or do you need to search every cell in every
sheet for the account number?

Regards
Rowan

JEFF wrote:
Hello,

Got a tricky one: I have a list of account numbers for which I want to
retrieve associated data. The account number and its data I seek is found in
one of several files. To re-cap the commands: For each account found in the
list, go through the files in folder A, find the account number and return
the entire row of data for it.... then on to the next account number.

Any hope? TIA!

Hi Rowan,

Yes, it is always in the first worksheet (there is only one worksheet) and
it is always in column C..... Hope this helps, thanks!

"Rowan Drummond" wrote:

Is there any pattern as to where that account number may be in the files
eg first sheet column C or do you need to search every cell in every
sheet for the account number?

Regards
Rowan

JEFF wrote:
Hello,

Got a tricky one: I have a list of account numbers for which I want to
retrieve associated data. The account number and its data I seek is found in
one of several files. To re-cap the commands: For each account found in the
list, go through the files in folder A, find the account number and return
the entire row of data for it.... then on to the next account number.

Any hope? TIA!

I must be psychic <g.

Anyway, assumptions:
Your account numbers are on Sheet1, column A starting in Row 2
You want the data copied to Sheet2
This macro is in the file with the account numbers
The files are in folder C:\Temp - easily changed
The file with this macro is not in c:\temp (or whatever)

Sub AcNos()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim AcNo As String
Dim eAc As Long
Dim eRow As Long
Dim i As Long
Dim fndAc As Range

On Error GoTo Errorhandler
Application.ScreenUpdating = False

eAc = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
eRow = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row + 1

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("c:\Temp") 'change directory

For i = 2 To eAc
AcNo = Sheets("Sheet1").Cells(i, 1).Value

For Each objFile In objFolder.Files
If objFile.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=objFolder.Path _
& "\" & objFile.Name

With Workbooks(objFile.Name)
With Sheets(1).Columns(3)
Set fndAc = .Find(AcNo)
End With
If Not fndAc Is Nothing Then
fndAc.EntireRow.Copy _
ThisWorkbook.Sheets("Sheet2") _
.Cells(eRow, 1)
eRow = eRow + 1
End If
.Close False
End With
Set objFile = Nothing
End If
Next
Next i

Set objFSO = Nothing
Set objFolder = Nothing

Errorhandler:
Application.ScreenUpdating = True
Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing
End Sub

Hope this helps
Rowan


JEFF wrote:
Hi Rowan,

Yes, it is always in the first worksheet (there is only one worksheet) and
it is always in column C..... Hope this helps, thanks!

"Rowan Drummond" wrote:


Is there any pattern as to where that account number may be in the files
eg first sheet column C or do you need to search every cell in every
sheet for the account number?

Regards
Rowan

JEFF wrote:

Hello,

Got a tricky one: I have a list of account numbers for which I want to
retrieve associated data. The account number and its data I seek is found in
one of several files. To re-cap the commands: For each account found in the
list, go through the files in folder A, find the account number and return
the entire row of data for it.... then on to the next account number.

Any hope? TIA!

Rowan,

This is great! At the risk of pushing my luck, here are some slight
adjustments I'm trying to make:

1. If I have five accounts on Sheet1, it starts returning data on row six
in Sheet 2. If 10 accounts, row 11 on sheet 2, and so on.... Ideally, it
would like it to always return the data starting in row two in Sheet2

2. I failed to mention that there about fifty rows for each account number
(the account number appears / repeats in column B for each of those fifty
rows). Thus the code finds the account and returns the first associated row.
However, I really need to find the account, go down 30 rows, return that
row. Better yet, find the account number in column B, find the row called
"TOTAL" (column G) that goes with it, and return that row (this would prevent
mistakes if the number of rows per account change). In other words, return
the row where the account number requested is found in column B AND "Total"
appears in column G.....

Hope that makes sense and I really, really appreciate the help!

"Rowan Drummond" wrote:

I must be psychic <g.

Anyway, assumptions:
Your account numbers are on Sheet1, column A starting in Row 2
You want the data copied to Sheet2
This macro is in the file with the account numbers
The files are in folder C:\Temp - easily changed
The file with this macro is not in c:\temp (or whatever)

Sub AcNos()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim AcNo As String
Dim eAc As Long
Dim eRow As Long
Dim i As Long
Dim fndAc As Range

On Error GoTo Errorhandler
Application.ScreenUpdating = False

eAc = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
eRow = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row + 1

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("c:\Temp") 'change directory

For i = 2 To eAc
AcNo = Sheets("Sheet1").Cells(i, 1).Value

For Each objFile In objFolder.Files
If objFile.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=objFolder.Path _
& "\" & objFile.Name

With Workbooks(objFile.Name)
With Sheets(1).Columns(3)
Set fndAc = .Find(AcNo)
End With
If Not fndAc Is Nothing Then
fndAc.EntireRow.Copy _
ThisWorkbook.Sheets("Sheet2") _
.Cells(eRow, 1)
eRow = eRow + 1
End If
.Close False
End With
Set objFile = Nothing
End If
Next
Next i

Set objFSO = Nothing
Set objFolder = Nothing

Errorhandler:
Application.ScreenUpdating = True
Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing
End Sub

Hope this helps
Rowan


JEFF wrote:
Hi Rowan,

Yes, it is always in the first worksheet (there is only one worksheet) and
it is always in column C..... Hope this helps, thanks!

"Rowan Drummond" wrote:


Is there any pattern as to where that account number may be in the files
eg first sheet column C or do you need to search every cell in every
sheet for the account number?

Regards
Rowan

JEFF wrote:

Hello,

Got a tricky one: I have a list of account numbers for which I want to
retrieve associated data. The account number and its data I seek is found in
one of several files. To re-cap the commands: For each account found in the
list, go through the files in folder A, find the account number and return
the entire row of data for it.... then on to the next account number.

Any hope? TIA!

Hi Jeff, see in line response:

JEFF wrote:
Rowan,

This is great! At the risk of pushing my luck, here are some slight
adjustments I'm trying to make:

1. If I have five accounts on Sheet1, it starts returning data on row six
in Sheet 2. If 10 accounts, row 11 on sheet 2, and so on.... Ideally, it
would like it to always return the data starting in row two in Sheet2

The code given should start returning data from the first blank row on
sheet 2. It the whole sheet is blank it will start at row 2 as
requested. Check that you haven't mixed up either the sheet references in:
eAc = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
eRow = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row + 1

or the use of the variable eRow in:
fndAc.EntireRow.Copy _
ThisWorkbook.Sheets("Sheet2") _
.Cells(eRow, 1)
eRow = eRow + 1


2. I failed to mention that there about fifty rows for each account number
(the account number appears / repeats in column B for each of those fifty
rows). Thus the code finds the account and returns the first associated row.
However, I really need to find the account, go down 30 rows, return that
row. Better yet, find the account number in column B, find the row called
"TOTAL" (column G) that goes with it, and return that row (this would prevent
mistakes if the number of rows per account change). In other words, return
the row where the account number requested is found in column B AND "Total"
appears in column G.....
The easiest way would be to add another column (I am using J) which can
be hidden. In this column concatenate the account number from column B
and the data from column G so that your in the required row it reads
xxxxTotal where xxxxx is the account number. Then change this line:

AcNo = Sheets("Sheet1").Cells(i, 1).Value
to
AcNo = Sheets("Sheet1").Cells(i, 1).Value & "Total"

and then change the column to be searched so that:
With Sheets(1).Columns(3)
now reads
With Sheets(1).Columns(10)


Hope that makes sense and I really, really appreciate the help!

Regards
Rowan


"Rowan Drummond" wrote:


I must be psychic <g.

Anyway, assumptions:
Your account numbers are on Sheet1, column A starting in Row 2
You want the data copied to Sheet2
This macro is in the file with the account numbers
The files are in folder C:\Temp - easily changed
The file with this macro is not in c:\temp (or whatever)

Sub AcNos()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim AcNo As String
Dim eAc As Long
Dim eRow As Long
Dim i As Long
Dim fndAc As Range

On Error GoTo Errorhandler
Application.ScreenUpdating = False

eAc = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
eRow = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row + 1

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("c:\Temp") 'change directory

For i = 2 To eAc
AcNo = Sheets("Sheet1").Cells(i, 1).Value

For Each objFile In objFolder.Files
If objFile.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=objFolder.Path _
& "\" & objFile.Name

With Workbooks(objFile.Name)
With Sheets(1).Columns(3)
Set fndAc = .Find(AcNo)
End With
If Not fndAc Is Nothing Then
fndAc.EntireRow.Copy _
ThisWorkbook.Sheets("Sheet2") _
.Cells(eRow, 1)
eRow = eRow + 1
End If
.Close False
End With
Set objFile = Nothing
End If
Next
Next i

Set objFSO = Nothing
Set objFolder = Nothing

Errorhandler:
Application.ScreenUpdating = True
Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing
End Sub

Hope this helps
Rowan