Pulling data from multiple files

[Whois Clinton]

Hi,
I need to make a worksheet pull information from several other files on a
network. The sheet and cell location would be the same for each file but
they would have their own paths.

In more detail, I am trying to pull monthly totals from a row of cells in
reports made for each person. So I want all of January's scores for all
people. Each persaon has their own file location, but once you get to their
file the workbook is set up universally the same. I am just becoming fluent
with excel and have set up everyting else we need around it. Can excel do
this too or do I need access or something else?

Thanks in advance,
Clint

 

[Joel]

You really need a macro. Need a little bit more info. You root directory and
any subdirectories where the files are located. Can you pull all the *.xls
files or is there some other criteria I can use.

for example
Z:\source data

Z:\source data\january
Z:\source data\February
....
Z:\source data\December


code can be written to go down all subdirectories and find all xls files or
any requirements that you need.

I also need cells you want to move. Sometimes it is helpful to record a
macro performing the operations you want manually. then posting the macro
and asking for additional info. You posted your request on the Genral Excel
Help. Look at some of the requests on the Programming Help and ask for
similar type

 

[Ron de Bruin]

Hi Whois

Start here
http://www.rondebruin.nl/copy3.htm

 

[Whois Clinton]

Thanks for both of your replies. Ron, the link you offered would help
however, I could only find the options for pulling multiple files in the same
folder. These files are in different folders throughout the network. The
filenames will even conatin client last names so they will be individually
specific in some areas. Joel, I took your advice and I have some answers for
you below.

Some other archive posts led me to the following macro.

Sub Summary_cells_from_Different_Workbooks_RowMove()
Dim FileNameXls As Variant
Dim SummWks As Worksheet
Dim ColNum As Integer
Dim myCell As Range, Rng As Range
Dim RwNum As Long, FNum As Long, FinalSlash As Long
Dim ShName As String, PathStr As String
Dim SheetCheck As String, JustFileName As String
Dim JustFolder As String

ShName = "Sheet1" '<---- Change
Set Rng = Range("A24:L24") '<---- Change

'Select the files with GetOpenFilename
FileNameXls = Application.GetOpenFilename(filefilter:="Excel Files,
*.xl*", _
MultiSelect:=True)

If IsArray(FileNameXls) = False Then
'do nothing
Else
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'Add a new workbook with one sheet for the Summary
Set SummWks = Workbooks.Add(1).Worksheets(1)

'The links to the first workbook will start in row 2
RwNum = 1

For FNum = LBound(FileNameXls) To UBound(FileNameXls)
ColNum = 1
RwNum = RwNum + 1
FinalSlash = InStrRev(FileNameXls(FNum), "\")
JustFileName = Mid(FileNameXls(FNum), FinalSlash + 1)
JustFolder = Left(FileNameXls(FNum), FinalSlash - 1)

'copy the workbook name in column A
SummWks.Cells(RwNum, 1).Value = JustFileName

'build the formula string
JustFileName = WorksheetFunction.Substitute(JustFileName, "'",
"''")
PathStr = "'" & JustFolder & "\[" & JustFileName & "]" & ShName
& "'!"

On Error Resume Next
SheetCheck = ExecuteExcel4Macro(PathStr & Range("A1").Address(,
, xlR1C1))
If Err.Number <> 0 Then
'If the sheet not exist in the workbook the row color will
be Yellow.
SummWks.Cells(RwNum, 1).Resize(1, Rng.Cells.Count + 1) _
.Interior.Color = vbYellow
Else
For Each myCell In Rng.Cells
ColNum = ColNum + 1
SummWks.Cells(RwNum, ColNum).Formula = _
"=" & PathStr & myCell.Address
Next myCell
End If
On Error GoTo 0
Next FNum

' Use AutoFit to set the column width in the new workbook
SummWks.UsedRange.Columns.AutoFit

MsgBox "The Summary is ready, save the file if you want to keep it"

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End If
End Sub


This works great, but only pulls one file at a time. I want it to pull
every file with a path similar to z:My Documents\Education Files\Student
Files\ 'specific teacher names' \ 'student names' \ PT Scores\2008

The other trouble I am having with the macro above is that it always opens
in a new workbook. My goal is to pull this row of data for many people and
have them show up the same sheet.

i hope this helps you help me better. Any help is greatly appreciated.

Thanks,
Clint

 

[Ron de Bruin]

Hi

You can change this line

'Add a new workbook with one sheet for the Summary
Set SummWks = Workbooks.Add(1).Worksheets(1)


To

'Add a new workbook with one sheet for the Summary
Set SummWks = Worksheets("Yousheetname)


You can select more then one file in the browse dialog but
If they are in different folders then use my add-in (there is a subfolder option)
http://www.rondebruin.nl/merge.htm

I think this is the best way






--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Thanks for both of your replies. Ron, the link you offered would help
however, I could only find the options for pulling multiple files in the same
folder. These files are in different folders throughout the network. The
filenames will even conatin client last names so they will be individually
specific in some areas. Joel, I took your advice and I have some answers for
you below.

Some other archive posts led me to the following macro.

Sub Summary_cells_from_Different_Workbooks_RowMove()
Dim FileNameXls As Variant
Dim SummWks As Worksheet
Dim ColNum As Integer
Dim myCell As Range, Rng As Range
Dim RwNum As Long, FNum As Long, FinalSlash As Long
Dim ShName As String, PathStr As String
Dim SheetCheck As String, JustFileName As String
Dim JustFolder As String

ShName = "Sheet1" '<---- Change
Set Rng = Range("A24:L24") '<---- Change

'Select the files with GetOpenFilename
FileNameXls = Application.GetOpenFilename(filefilter:="Excel Files,
*.xl*", _
MultiSelect:=True)

If IsArray(FileNameXls) = False Then
'do nothing
Else
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'Add a new workbook with one sheet for the Summary
Set SummWks = Workbooks.Add(1).Worksheets(1)

'The links to the first workbook will start in row 2
RwNum = 1

For FNum = LBound(FileNameXls) To UBound(FileNameXls)
ColNum = 1
RwNum = RwNum + 1
FinalSlash = InStrRev(FileNameXls(FNum), "\")
JustFileName = Mid(FileNameXls(FNum), FinalSlash + 1)
JustFolder = Left(FileNameXls(FNum), FinalSlash - 1)

'copy the workbook name in column A
SummWks.Cells(RwNum, 1).Value = JustFileName

'build the formula string
JustFileName = WorksheetFunction.Substitute(JustFileName, "'",
"''")
PathStr = "'" & JustFolder & "\[" & JustFileName & "]" & ShName
& "'!"

On Error Resume Next
SheetCheck = ExecuteExcel4Macro(PathStr & Range("A1").Address(,
, xlR1C1))
If Err.Number <> 0 Then
'If the sheet not exist in the workbook the row color will
be Yellow.
SummWks.Cells(RwNum, 1).Resize(1, Rng.Cells.Count + 1) _
.Interior.Color = vbYellow
Else
For Each myCell In Rng.Cells
ColNum = ColNum + 1
SummWks.Cells(RwNum, ColNum).Formula = _
"=" & PathStr & myCell.Address
Next myCell
End If
On Error GoTo 0
Next FNum

' Use AutoFit to set the column width in the new workbook
SummWks.UsedRange.Columns.AutoFit

MsgBox "The Summary is ready, save the file if you want to keep it"

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End If
End Sub


This works great, but only pulls one file at a time. I want it to pull
every file with a path similar to z:My Documents\Education Files\Student
Files\ 'specific teacher names' \ 'student names' \ PT Scores\2008

The other trouble I am having with the macro above is that it always opens
in a new workbook. My goal is to pull this row of data for many people and
have them show up the same sheet.

i hope this helps you help me better. Any help is greatly appreciated.

Thanks,
Clint

Hi Whois

Start here
http://www.rondebruin.nl/copy3.htm

 

[Whois Clinton]

WOW!!! Thanks Ron your Add-in was just what I needed, and it was so basic
and user friendly I could easily get everythinig I need. You are the Excel
Man!
Much Thanks,
Clint

Hi

You can change this line

'Add a new workbook with one sheet for the Summary
Set SummWks = Workbooks.Add(1).Worksheets(1)


To

'Add a new workbook with one sheet for the Summary
Set SummWks = Worksheets("Yousheetname)


You can select more then one file in the browse dialog but
If they are in different folders then use my add-in (there is a subfolder option)
http://www.rondebruin.nl/merge.htm

I think this is the best way






--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Thanks for both of your replies. Ron, the link you offered would help
however, I could only find the options for pulling multiple files in the same
folder. These files are in different folders throughout the network. The
filenames will even conatin client last names so they will be individually
specific in some areas. Joel, I took your advice and I have some answers for
you below.

Some other archive posts led me to the following macro.

Sub Summary_cells_from_Different_Workbooks_RowMove()
Dim FileNameXls As Variant
Dim SummWks As Worksheet
Dim ColNum As Integer
Dim myCell As Range, Rng As Range
Dim RwNum As Long, FNum As Long, FinalSlash As Long
Dim ShName As String, PathStr As String
Dim SheetCheck As String, JustFileName As String
Dim JustFolder As String

ShName = "Sheet1" '<---- Change
Set Rng = Range("A24:L24") '<---- Change

'Select the files with GetOpenFilename
FileNameXls = Application.GetOpenFilename(filefilter:="Excel Files,
*.xl*", _
MultiSelect:=True)

If IsArray(FileNameXls) = False Then
'do nothing
Else
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'Add a new workbook with one sheet for the Summary
Set SummWks = Workbooks.Add(1).Worksheets(1)

'The links to the first workbook will start in row 2
RwNum = 1

For FNum = LBound(FileNameXls) To UBound(FileNameXls)
ColNum = 1
RwNum = RwNum + 1
FinalSlash = InStrRev(FileNameXls(FNum), "\")
JustFileName = Mid(FileNameXls(FNum), FinalSlash + 1)
JustFolder = Left(FileNameXls(FNum), FinalSlash - 1)

'copy the workbook name in column A
SummWks.Cells(RwNum, 1).Value = JustFileName

'build the formula string
JustFileName = WorksheetFunction.Substitute(JustFileName, "'",
"''")
PathStr = "'" & JustFolder & "\[" & JustFileName & "]" & ShName
& "'!"

On Error Resume Next
SheetCheck = ExecuteExcel4Macro(PathStr & Range("A1").Address(,
, xlR1C1))
If Err.Number <> 0 Then
'If the sheet not exist in the workbook the row color will
be Yellow.
SummWks.Cells(RwNum, 1).Resize(1, Rng.Cells.Count + 1) _
.Interior.Color = vbYellow
Else
For Each myCell In Rng.Cells
ColNum = ColNum + 1
SummWks.Cells(RwNum, ColNum).Formula = _
"=" & PathStr & myCell.Address
Next myCell
End If
On Error GoTo 0
Next FNum

' Use AutoFit to set the column width in the new workbook
SummWks.UsedRange.Columns.AutoFit

MsgBox "The Summary is ready, save the file if you want to keep it"

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End If
End Sub


This works great, but only pulls one file at a time. I want it to pull
every file with a path similar to z:My Documents\Education Files\Student
Files\ 'specific teacher names' \ 'student names' \ PT Scores\2008

The other trouble I am having with the macro above is that it always opens
in a new workbook. My goal is to pull this row of data for many people and
have them show up the same sheet.

i hope this helps you help me better. Any help is greatly appreciated.

Thanks,
Clint

Hi Whois

Start here
http://www.rondebruin.nl/copy3.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


Hi,
I need to make a worksheet pull information from several other files on a
network. The sheet and cell location would be the same for each file but
they would have their own paths.

In more detail, I am trying to pull monthly totals from a row of cells in
reports made for each person. So I want all of January's scores for all
people. Each persaon has their own file location, but once you get to their
file the workbook is set up universally the same. I am just becoming fluent
with excel and have set up everyting else we need around it. Can excel do
this too or do I need access or something else?

Thanks in advance,
Clint