VBA to find account number that resides in one of several files
 

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!

VBA to find account number that resides in one of several files
 

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!

VBA to find account number that resides in one of several file
 

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!

VBA to find account number that resides in one of several file
 

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!

VBA to find account number that resides in one of several file
 

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!

VBA to find account number that resides in one of several file
 

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

VBA to find account number that resides in one of several file
 

Thanks for the response....

Fixed Number 1 below. Per Number 2, altering the underlying data to become
concatenated is really too much work. However, if I could just alter the
existing code to return the row 50 lines below where it finds the account
number (OFFSET?), I would be in business......

What do you think? TIA!

"Rowan Drummond" wrote:

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

VBA to find account number that resides in one of several file
 

Great! Just what I had in mind .... To put a fork in this thing, I have
one last issue: the directory in which it is directed to cull through has
over 100 large files to sift through. More importantly, that directory has
files that are both at the territory and region level = the account appears
in more than one file. To get around this, the name scheme is such that I
could use wildcards to have it look in only those files desired (below).

I am struggling how to add this option into your code... I know I am asking
a lot, but I do appreciate it.

Example:

Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
MyPath = "Y:\Sales\January"

ChDrive MyPath
ChDir MyPath

'FNames = Dir("*.xls")
FNames = Dir("REGION***.xls")



"Rowan Drummond" wrote:

Hi Jeff

If you are sure the Totla row will always be 50 lines below the first
account number then you could try:

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.offset(50,0).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

Regards
Rowan

JEFF wrote:
Thanks for the response....

Fixed Number 1 below. Per Number 2, altering the underlying data to become
concatenated is really too much work. However, if I could just alter the
existing code to return the row 50 lines below where it finds the account
number (OFFSET?), I would be in business......

What do you think? TIA!

"Rowan Drummond" wrote:


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

VBA to find account number that resides in one of several file
 

Hi Jeff

You can check the filename before opening so this line:

If objFile.Type = "Microsoft Excel Worksheet" Then

becomes:

If objFile.Type = "Microsoft Excel Worksheet" _
And Left(objFile.Name, 6) = "REGION" Then

This will process any file where the names starts with REGION.

Regards
Rowan

JEFF wrote:
Great! Just what I had in mind .... To put a fork in this thing, I have
one last issue: the directory in which it is directed to cull through has
over 100 large files to sift through. More importantly, that directory has
files that are both at the territory and region level = the account appears
in more than one file. To get around this, the name scheme is such that I
could use wildcards to have it look in only those files desired (below).

I am struggling how to add this option into your code... I know I am asking
a lot, but I do appreciate it.

Example:

Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
MyPath = "Y:\Sales\January"

ChDrive MyPath
ChDir MyPath

'FNames = Dir("*.xls")
FNames = Dir("REGION***.xls")



"Rowan Drummond" wrote:

VBA to find account number that resides in one of several file
 

So close I can taste it...... However, it accepts the change but doesn't do
anything. Below is the change: Not sure what it doesn't like......
Also, can I use wildcards (*) here?

TIA!





If objFile.Type = "Microsoft Excel Worksheet" _
And Left(objFileName, 5) = "CO1TR" Then


"Rowan Drummond" wrote:

Hi Jeff

You can check the filename before opening so this line:

If objFile.Type = "Microsoft Excel Worksheet" Then

becomes:

If objFile.Type = "Microsoft Excel Worksheet" _
And Left(objFile.Name, 6) = "REGION" Then

This will process any file where the names starts with REGION.

Regards
Rowan

JEFF wrote:
Great! Just what I had in mind .... To put a fork in this thing, I have
one last issue: the directory in which it is directed to cull through has
over 100 large files to sift through. More importantly, that directory has
files that are both at the territory and region level = the account appears
in more than one file. To get around this, the name scheme is such that I
could use wildcards to have it look in only those files desired (below).

I am struggling how to add this option into your code... I know I am asking
a lot, but I do appreciate it.

Example:

Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
MyPath = "Y:\Sales\January"

ChDrive MyPath
ChDir MyPath

'FNames = Dir("*.xls")
FNames = Dir("REGION***.xls")



"Rowan Drummond" wrote:

VBA to find account number that resides in one of several file
 

You are missing a . inbetween objFile and Name ie

If objFile.Type = "Microsoft Excel Worksheet" _
And Left(objFile.Name, 5) = "CO1TR" Then

You can use wilcards eg:

If objFile.Type = "Microsoft Excel Worksheet" _
And Left(objFile.Name, 5) like "CO1T*" Then

Regards
Rowan

JEFF wrote:
So close I can taste it...... However, it accepts the change but doesn't do
anything. Below is the change: Not sure what it doesn't like......
Also, can I use wildcards (*) here?

TIA!





If objFile.Type = "Microsoft Excel Worksheet" _
And Left(objFileName, 5) = "CO1TR" Then


"Rowan Drummond" wrote:


Hi Jeff

You can check the filename before opening so this line:

If objFile.Type = "Microsoft Excel Worksheet" Then

becomes:

If objFile.Type = "Microsoft Excel Worksheet" _
And Left(objFile.Name, 6) = "REGION" Then

This will process any file where the names starts with REGION.

Regards
Rowan

JEFF wrote:

Great! Just what I had in mind .... To put a fork in this thing, I have
one last issue: the directory in which it is directed to cull through has
over 100 large files to sift through. More importantly, that directory has
files that are both at the territory and region level = the account appears
in more than one file. To get around this, the name scheme is such that I
could use wildcards to have it look in only those files desired (below).

I am struggling how to add this option into your code... I know I am asking
a lot, but I do appreciate it.

Example:

Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
MyPath = "Y:\Sales\January"

ChDrive MyPath
ChDir MyPath

'FNames = Dir("*.xls")
FNames = Dir("REGION***.xls")



"Rowan Drummond" wrote:

VBA to find account number that resides in one of several file
 

Sweet Success! Thanks a ton.

"Rowan Drummond" wrote:

You are missing a . inbetween objFile and Name ie

If objFile.Type = "Microsoft Excel Worksheet" _
And Left(objFile.Name, 5) = "CO1TR" Then

You can use wilcards eg:

If objFile.Type = "Microsoft Excel Worksheet" _
And Left(objFile.Name, 5) like "CO1T*" Then

Regards
Rowan

JEFF wrote:
So close I can taste it...... However, it accepts the change but doesn't do
anything. Below is the change: Not sure what it doesn't like......
Also, can I use wildcards (*) here?

TIA!





If objFile.Type = "Microsoft Excel Worksheet" _
And Left(objFileName, 5) = "CO1TR" Then


"Rowan Drummond" wrote:


Hi Jeff

You can check the filename before opening so this line:

If objFile.Type = "Microsoft Excel Worksheet" Then

becomes:

If objFile.Type = "Microsoft Excel Worksheet" _
And Left(objFile.Name, 6) = "REGION" Then

This will process any file where the names starts with REGION.

Regards
Rowan

JEFF wrote:

Great! Just what I had in mind .... To put a fork in this thing, I have
one last issue: the directory in which it is directed to cull through has
over 100 large files to sift through. More importantly, that directory has
files that are both at the territory and region level = the account appears
in more than one file. To get around this, the name scheme is such that I
could use wildcards to have it look in only those files desired (below).

I am struggling how to add this option into your code... I know I am asking
a lot, but I do appreciate it.

Example:

Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
MyPath = "Y:\Sales\January"

ChDrive MyPath
ChDir MyPath

'FNames = Dir("*.xls")
FNames = Dir("REGION***.xls")



"Rowan Drummond" wrote:

VBA to find account number that resides in one of several file
 

You're welcome. Glad we got there in the end!

JEFF wrote:
Sweet Success! Thanks a ton.

VBA to find account number that resides in one of several file
 

I hate to take this off the "Done" pile, but I have one final (I swear)
question so I can use this in another context:

What if I have all the accounts in a single workbook (same worksheet) --
rather than pointing this at a directory with the one workbook (this causes
it to open and close the same workbook unnecessarily), can I tweak this to
reference only one workbook?

Thanks again.





"Rowan Drummond" wrote:

You're welcome. Glad we got there in the end!

JEFF wrote:
Sweet Success! Thanks a ton.

VBA to find account number that resides in one of several file
 

If you just need to reference one workbook then you can use the
GotOpenFileName dialog to open that file something like this:

Sub AcNos2()
Dim AcNo As String
Dim eAc As Long
Dim eRow As Long
Dim i As Long
Dim fndAc As Range
Dim flToOpen As Variant
Dim fFilter As String
Dim thisBk As Workbook
Dim acBk As Workbook

On Error GoTo Errorhandler
Application.ScreenUpdating = False

Set thisBk = ThisWorkbook

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

fFilter = "Excel Files (*.xls), *.xls"
flToOpen = Application.GetOpenFilename(fFilter)

If Not flToOpen = False Then
Set acBk = Workbooks.Open(Filename:=flToOpen)
For i = 2 To eAc
AcNo = thisBk.Sheets("Sheet1").Cells(i, 1).Value
With acBk.Sheets(1).Columns(3)
Set fndAc = .Find(AcNo)
End With
If Not fndAc Is Nothing Then
fndAc.Offset(50, 0).EntireRow.Copy _
thisBk.Sheets("Sheet2") _
.Cells(eRow, 1)
eRow = eRow + 1
End If
Next i
acBk.Close False
Else
MsgBox "Cancelled"
End If

Errorhandler:
Application.ScreenUpdating = True
End Sub

Regards
Rowan

JEFF wrote:
I hate to take this off the "Done" pile, but I have one final (I swear)
question so I can use this in another context:

What if I have all the accounts in a single workbook (same worksheet) --
rather than pointing this at a directory with the one workbook (this causes
it to open and close the same workbook unnecessarily), can I tweak this to
reference only one workbook?

Thanks again.





"Rowan Drummond" wrote:


You're welcome. Glad we got there in the end!

JEFF wrote:

Sweet Success! Thanks a ton.

VBA to find account number that resides in one of several file
 

I apologize for dragging this out, but I can't seem to tweak this to:

1. Look for the specified accounts listed in Sheet 1 of "This Workbook" (as
before)

2. to be found in C:\All Accounts.xls (a change - all available accounts
are now in Sheet 1 of this workbook)

3. Returned into Sheet 2 of "This Workbook" (as before)


Hopefully I haven't pushed my luck as fixing this would be huge for me....





"Rowan Drummond" wrote:

If you just need to reference one workbook then you can use the
GotOpenFileName dialog to open that file something like this:

Sub AcNos2()
Dim AcNo As String
Dim eAc As Long
Dim eRow As Long
Dim i As Long
Dim fndAc As Range
Dim flToOpen As Variant
Dim fFilter As String
Dim thisBk As Workbook
Dim acBk As Workbook

On Error GoTo Errorhandler
Application.ScreenUpdating = False

Set thisBk = ThisWorkbook

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

fFilter = "Excel Files (*.xls), *.xls"
flToOpen = Application.GetOpenFilename(fFilter)

If Not flToOpen = False Then
Set acBk = Workbooks.Open(Filename:=flToOpen)
For i = 2 To eAc
AcNo = thisBk.Sheets("Sheet1").Cells(i, 1).Value
With acBk.Sheets(1).Columns(3)
Set fndAc = .Find(AcNo)
End With
If Not fndAc Is Nothing Then
fndAc.Offset(50, 0).EntireRow.Copy _
thisBk.Sheets("Sheet2") _
.Cells(eRow, 1)
eRow = eRow + 1
End If
Next i
acBk.Close False
Else
MsgBox "Cancelled"
End If

Errorhandler:
Application.ScreenUpdating = True
End Sub

Regards
Rowan

JEFF wrote:
I hate to take this off the "Done" pile, but I have one final (I swear)
question so I can use this in another context:

What if I have all the accounts in a single workbook (same worksheet) --
rather than pointing this at a directory with the one workbook (this causes
it to open and close the same workbook unnecessarily), can I tweak this to
reference only one workbook?

Thanks again.





"Rowan Drummond" wrote:


You're welcome. Glad we got there in the end!

JEFF wrote:

Sweet Success! Thanks a ton.

VBA to find account number that resides in one of several file
 

Hi Jeff

That is pretty much exactly what this version does. When prompted to
open a file select C:\All Accounts.xls. Is this file still in the same
format i.e account numbers in column C and the total row 50 below the
first instance of the account number?

Regards
Rowan

JEFF wrote:
I apologize for dragging this out, but I can't seem to tweak this to:

1. Look for the specified accounts listed in Sheet 1 of "This Workbook" (as
before)

2. to be found in C:\All Accounts.xls (a change - all available accounts
are now in Sheet 1 of this workbook)

3. Returned into Sheet 2 of "This Workbook" (as before)


Hopefully I haven't pushed my luck as fixing this would be huge for me....





"Rowan Drummond" wrote:


If you just need to reference one workbook then you can use the
GotOpenFileName dialog to open that file something like this:

Sub AcNos2()
Dim AcNo As String
Dim eAc As Long
Dim eRow As Long
Dim i As Long
Dim fndAc As Range
Dim flToOpen As Variant
Dim fFilter As String
Dim thisBk As Workbook
Dim acBk As Workbook

On Error GoTo Errorhandler
Application.ScreenUpdating = False

Set thisBk = ThisWorkbook

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

fFilter = "Excel Files (*.xls), *.xls"
flToOpen = Application.GetOpenFilename(fFilter)

If Not flToOpen = False Then
Set acBk = Workbooks.Open(Filename:=flToOpen)
For i = 2 To eAc
AcNo = thisBk.Sheets("Sheet1").Cells(i, 1).Value
With acBk.Sheets(1).Columns(3)
Set fndAc = .Find(AcNo)
End With
If Not fndAc Is Nothing Then
fndAc.Offset(50, 0).EntireRow.Copy _
thisBk.Sheets("Sheet2") _
.Cells(eRow, 1)
eRow = eRow + 1
End If
Next i
acBk.Close False
Else
MsgBox "Cancelled"
End If

Errorhandler:
Application.ScreenUpdating = True
End Sub

Regards
Rowan

JEFF wrote:

I hate to take this off the "Done" pile, but I have one final (I swear)
question so I can use this in another context:

What if I have all the accounts in a single workbook (same worksheet) --
rather than pointing this at a directory with the one workbook (this causes
it to open and close the same workbook unnecessarily), can I tweak this to
reference only one workbook?

Thanks again.





"Rowan Drummond" wrote:



You're welcome. Glad we got there in the end!

JEFF wrote:


Sweet Success! Thanks a ton.

VBA to find account number that resides in one of several file
 

Hi Rowan,

Yes, the data is in the same format.... I was hoping for no user input
(i.e. the code goes straight to C:\All Accounts.xls)


Thanks again.




"Rowan Drummond" wrote:

Hi Jeff

That is pretty much exactly what this version does. When prompted to
open a file select C:\All Accounts.xls. Is this file still in the same
format i.e account numbers in column C and the total row 50 below the
first instance of the account number?

Regards
Rowan

JEFF wrote:
I apologize for dragging this out, but I can't seem to tweak this to:

1. Look for the specified accounts listed in Sheet 1 of "This Workbook" (as
before)

2. to be found in C:\All Accounts.xls (a change - all available accounts
are now in Sheet 1 of this workbook)

3. Returned into Sheet 2 of "This Workbook" (as before)


Hopefully I haven't pushed my luck as fixing this would be huge for me....





"Rowan Drummond" wrote:


If you just need to reference one workbook then you can use the
GotOpenFileName dialog to open that file something like this:

Sub AcNos2()
Dim AcNo As String
Dim eAc As Long
Dim eRow As Long
Dim i As Long
Dim fndAc As Range
Dim flToOpen As Variant
Dim fFilter As String
Dim thisBk As Workbook
Dim acBk As Workbook

On Error GoTo Errorhandler
Application.ScreenUpdating = False

Set thisBk = ThisWorkbook

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

fFilter = "Excel Files (*.xls), *.xls"
flToOpen = Application.GetOpenFilename(fFilter)

If Not flToOpen = False Then
Set acBk = Workbooks.Open(Filename:=flToOpen)
For i = 2 To eAc
AcNo = thisBk.Sheets("Sheet1").Cells(i, 1).Value
With acBk.Sheets(1).Columns(3)
Set fndAc = .Find(AcNo)
End With
If Not fndAc Is Nothing Then
fndAc.Offset(50, 0).EntireRow.Copy _
thisBk.Sheets("Sheet2") _
.Cells(eRow, 1)
eRow = eRow + 1
End If
Next i
acBk.Close False
Else
MsgBox "Cancelled"
End If

Errorhandler:
Application.ScreenUpdating = True
End Sub

Regards
Rowan

JEFF wrote:

I hate to take this off the "Done" pile, but I have one final (I swear)
question so I can use this in another context:

What if I have all the accounts in a single workbook (same worksheet) --
rather than pointing this at a directory with the one workbook (this causes
it to open and close the same workbook unnecessarily), can I tweak this to
reference only one workbook?

Thanks again.





"Rowan Drummond" wrote:



You're welcome. Glad we got there in the end!

JEFF wrote:


Sweet Success! Thanks a ton.

VBA to find account number that resides in one of several file
 

Hi Jeff

The prompt allows the flexability to change your mind about the name and
location of the file without having to make any changes to your code but
if you want to hardcode the name try it like this:

Sub AcNos2()
Dim AcNo As String
Dim eAc As Long
Dim eRow As Long
Dim i As Long
Dim fndAc As Range
Dim flToOpen As String
Dim thisBk As Workbook
Dim acBk As Workbook

On Error GoTo Errorhandler
Application.ScreenUpdating = False

Set thisBk = ThisWorkbook

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

flToOpen = "C:\All Accounts.xls"

If Dir(flToOpen) < "" Then
Set acBk = Workbooks.Open(Filename:=flToOpen)
For i = 2 To eAc
AcNo = thisBk.Sheets("Sheet1").Cells(i, 1).Value
With acBk.Sheets(1).Columns(3)
Set fndAc = .Find(AcNo)
End With
If Not fndAc Is Nothing Then
fndAc.Offset(50, 0).EntireRow.Copy _
thisBk.Sheets("Sheet2") _
.Cells(eRow, 1)
eRow = eRow + 1
End If
Next i
acBk.Close False
Else
MsgBox "Not a valid file"
End If

Errorhandler:
Application.ScreenUpdating = True
End Sub

Regards
Rowan

JEFF wrote:
Hi Rowan,

Yes, the data is in the same format.... I was hoping for no user input
(i.e. the code goes straight to C:\All Accounts.xls)


Thanks again.




"Rowan Drummond" wrote:


Hi Jeff

That is pretty much exactly what this version does. When prompted to
open a file select C:\All Accounts.xls. Is this file still in the same
format i.e account numbers in column C and the total row 50 below the
first instance of the account number?

Regards
Rowan

JEFF wrote:

I apologize for dragging this out, but I can't seem to tweak this to:

1. Look for the specified accounts listed in Sheet 1 of "This Workbook" (as
before)

2. to be found in C:\All Accounts.xls (a change - all available accounts
are now in Sheet 1 of this workbook)

3. Returned into Sheet 2 of "This Workbook" (as before)


Hopefully I haven't pushed my luck as fixing this would be huge for me....





"Rowan Drummond" wrote:



If you just need to reference one workbook then you can use the
GotOpenFileName dialog to open that file something like this:

Sub AcNos2()
Dim AcNo As String
Dim eAc As Long
Dim eRow As Long
Dim i As Long
Dim fndAc As Range
Dim flToOpen As Variant
Dim fFilter As String
Dim thisBk As Workbook
Dim acBk As Workbook

On Error GoTo Errorhandler
Application.ScreenUpdating = False

Set thisBk = ThisWorkbook

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

fFilter = "Excel Files (*.xls), *.xls"
flToOpen = Application.GetOpenFilename(fFilter)

If Not flToOpen = False Then
Set acBk = Workbooks.Open(Filename:=flToOpen)
For i = 2 To eAc
AcNo = thisBk.Sheets("Sheet1").Cells(i, 1).Value
With acBk.Sheets(1).Columns(3)
Set fndAc = .Find(AcNo)
End With
If Not fndAc Is Nothing Then
fndAc.Offset(50, 0).EntireRow.Copy _
thisBk.Sheets("Sheet2") _
.Cells(eRow, 1)
eRow = eRow + 1
End If
Next i
acBk.Close False
Else
MsgBox "Cancelled"
End If

Errorhandler:
Application.ScreenUpdating = True
End Sub

Regards
Rowan

JEFF wrote:


I hate to take this off the "Done" pile, but I have one final (I swear)
question so I can use this in another context:

What if I have all the accounts in a single workbook (same worksheet) --
rather than pointing this at a directory with the one workbook (this causes
it to open and close the same workbook unnecessarily), can I tweak this to
reference only one workbook?

Thanks again.





"Rowan Drummond" wrote:




You're welcome. Glad we got there in the end!

JEFF wrote:



Sweet Success! Thanks a ton.

VBA to find account number that resides in one of several file
 

PERFECTION! Thanks for your help and patience -- it really makes a difference

"Rowan Drummond" wrote:

Hi Jeff

The prompt allows the flexability to change your mind about the name and
location of the file without having to make any changes to your code but
if you want to hardcode the name try it like this:

Sub AcNos2()
Dim AcNo As String
Dim eAc As Long
Dim eRow As Long
Dim i As Long
Dim fndAc As Range
Dim flToOpen As String
Dim thisBk As Workbook
Dim acBk As Workbook

On Error GoTo Errorhandler
Application.ScreenUpdating = False

Set thisBk = ThisWorkbook

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

flToOpen = "C:\All Accounts.xls"

If Dir(flToOpen) < "" Then
Set acBk = Workbooks.Open(Filename:=flToOpen)
For i = 2 To eAc
AcNo = thisBk.Sheets("Sheet1").Cells(i, 1).Value
With acBk.Sheets(1).Columns(3)
Set fndAc = .Find(AcNo)
End With
If Not fndAc Is Nothing Then
fndAc.Offset(50, 0).EntireRow.Copy _
thisBk.Sheets("Sheet2") _
.Cells(eRow, 1)
eRow = eRow + 1
End If
Next i
acBk.Close False
Else
MsgBox "Not a valid file"
End If

Errorhandler:
Application.ScreenUpdating = True
End Sub

Regards
Rowan

JEFF wrote:
Hi Rowan,

Yes, the data is in the same format.... I was hoping for no user input
(i.e. the code goes straight to C:\All Accounts.xls)


Thanks again.




"Rowan Drummond" wrote:


Hi Jeff

That is pretty much exactly what this version does. When prompted to
open a file select C:\All Accounts.xls. Is this file still in the same
format i.e account numbers in column C and the total row 50 below the
first instance of the account number?

Regards
Rowan

JEFF wrote:

I apologize for dragging this out, but I can't seem to tweak this to:

1. Look for the specified accounts listed in Sheet 1 of "This Workbook" (as
before)

2. to be found in C:\All Accounts.xls (a change - all available accounts
are now in Sheet 1 of this workbook)

3. Returned into Sheet 2 of "This Workbook" (as before)


Hopefully I haven't pushed my luck as fixing this would be huge for me....





"Rowan Drummond" wrote:



If you just need to reference one workbook then you can use the
GotOpenFileName dialog to open that file something like this:

Sub AcNos2()
Dim AcNo As String
Dim eAc As Long
Dim eRow As Long
Dim i As Long
Dim fndAc As Range
Dim flToOpen As Variant
Dim fFilter As String
Dim thisBk As Workbook
Dim acBk As Workbook

On Error GoTo Errorhandler
Application.ScreenUpdating = False

Set thisBk = ThisWorkbook

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

fFilter = "Excel Files (*.xls), *.xls"
flToOpen = Application.GetOpenFilename(fFilter)

If Not flToOpen = False Then
Set acBk = Workbooks.Open(Filename:=flToOpen)
For i = 2 To eAc
AcNo = thisBk.Sheets("Sheet1").Cells(i, 1).Value
With acBk.Sheets(1).Columns(3)
Set fndAc = .Find(AcNo)
End With
If Not fndAc Is Nothing Then
fndAc.Offset(50, 0).EntireRow.Copy _
thisBk.Sheets("Sheet2") _
.Cells(eRow, 1)
eRow = eRow + 1
End If
Next i
acBk.Close False
Else
MsgBox "Cancelled"
End If

Errorhandler:
Application.ScreenUpdating = True
End Sub

Regards
Rowan

JEFF wrote:


I hate to take this off the "Done" pile, but I have one final (I swear)
question so I can use this in another context:

What if I have all the accounts in a single workbook (same worksheet) --
rather than pointing this at a directory with the one workbook (this causes
it to open and close the same workbook unnecessarily), can I tweak this to
reference only one workbook?

Thanks again.





"Rowan Drummond" wrote:




You're welcome. Glad we got there in the end!

JEFF wrote:



Sweet Success! Thanks a ton.

VBA to find account number that resides in one of several file
 

You're welcome...again <g.

JEFF wrote:
PERFECTION! Thanks for your help and patience -- it really makes a difference