Dave, the following will do as requested.

You may need to tweek the dir function or consider just a loop if you have a
fixed file name structure (ie week01.xls, week02.xls....).

Option Explicit

Sub getdata()
' directory where the data is with the trailing \
Const cszDir As String = "C:\data\"
' prefix for the file name
Const cszFilePrefix As String = "file"
' extension for the file name
Const cszFileExtension As String = ".xls"

Dim wsd As Worksheet
Dim wbc As Workbook
Dim lRowDst As Long
Dim szFileCur As String
Dim szDir As String

Set wsd = ActiveSheet
lRowDst = 2
szFileCur = Dir(cszDir & cszFilePrefix & _
"*" & cszFileExtension)
Do While szFileCur < ""
Set wbc = Workbooks.Open(szFileCur, , True)
'get data'
wsd.Cells(lRowDst, 1) = wbc.Worksheets(1).Range("A1")
wsd.Cells(lRowDst, 2) = wbc.Worksheets(1).Range("C1")
wsd.Cells(lRowDst, 3) = wbc.Worksheets(1).Range("D1")
'....
wbc.Close False
szFileCur = Dir
lRowDst = lRowDst + 1
Loop
End Sub

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"Dave" wrote:

I need to find a solution to the folowing problem:

There is a network folder with an Exel workbook for every week in the year.
Each Friday it is updated with one new sheet for that week. All the data
contained is in the same format. I need to create a single file that when
one presses a button, will go through all of the workbooks in the network
drive and pull specific information and populate it into the one file. Is
this possible? Any help would be appreciated.

Marin,

This looks great. A few more questions:

I am new to VBA and Macros.

Is there a way to specify criteria using an array? (i.e. if row 10="one",
then only pull information from rows 1-9 and put that information in the new
workbook.) If this is too difficult it is not absolutely necessary, though
would be extremely helpful.

Where do I paste this code once it is complete?

Is there a way to create a button that when pressed will update this new
workbook?

I really do appreciate your help.

"Martin Fishlock" wrote:

Dave, the following will do as requested.

You may need to tweek the dir function or consider just a loop if you have a
fixed file name structure (ie week01.xls, week02.xls....).

Option Explicit

Sub getdata()
' directory where the data is with the trailing \
Const cszDir As String = "C:\data\"
' prefix for the file name
Const cszFilePrefix As String = "file"
' extension for the file name
Const cszFileExtension As String = ".xls"

Dim wsd As Worksheet
Dim wbc As Workbook
Dim lRowDst As Long
Dim szFileCur As String
Dim szDir As String

Set wsd = ActiveSheet
lRowDst = 2
szFileCur = Dir(cszDir & cszFilePrefix & _
"*" & cszFileExtension)
Do While szFileCur < ""
Set wbc = Workbooks.Open(szFileCur, , True)
'get data'
wsd.Cells(lRowDst, 1) = wbc.Worksheets(1).Range("A1")
wsd.Cells(lRowDst, 2) = wbc.Worksheets(1).Range("C1")
wsd.Cells(lRowDst, 3) = wbc.Worksheets(1).Range("D1")
'....
wbc.Close False
szFileCur = Dir
lRowDst = lRowDst + 1
Loop
End Sub

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"Dave" wrote:

I need to find a solution to the folowing problem:

There is a network folder with an Exel workbook for every week in the year.
Each Friday it is updated with one new sheet for that week. All the data
contained is in the same format. I need to create a single file that when
one presses a button, will go through all of the workbooks in the network
drive and pull specific information and populate it into the one file. Is
this possible? Any help would be appreciated.

My posts seem to have got a little messed up....and there seems to bew two
answers to the original question, never mind they are both the same.

In answer to your questions

Q: Is there a way to specify criteria using an array? (i.e. if row 10="one",
then only pull information from rows 1-9 and put that information in the new
workbook.) If this is too difficult it is not absolutely necessary, though
would be extremely helpful.

A: Yes it is. You don't need to use arrays because it then gets difficult to
control the output but you would deal with the different treatments in this
type of manner:

if wbc.Worksheets(1).Range("A1") = "Widgets" then
wsd.Cells(lRowDst, 1) = wbc.Worksheets(1).Range("A1")
elseif wbc.Worksheets(1).Range("A1") = "Acme" then
wsd.Cells(lRowDst, 2) = wbc.Worksheets(1).Range("A1")
elseif wbc.Worksheets(1).Range("B1") = "Gold" then
wsd.Cells(lRowDst, 2) = wbc.Worksheets(1).Range("A1")
'.....
endif

ie testing the conditions and then dealing with the specific data.

Where do I paste this code once it is complete?

You paste the code into a module in the visual basic editior.

1. Tools|Macros|Visual Basic Editor or Alt+F11
2. In the edotor insert a module file.
you needt to ensure that the project explorer window is displayed
(View|Project Explorer) and then select the workbook that you are using.
3.Then insert a module file (Insert|Module)
4. Paste the code in the module window.
5. Tweek the code as required
6. Check it (Debug|Compile VBA Project)
7. Go back to excel workbook
8. Run the macro (Tools|Macros) and select the macro and run it.
9. Hopefully it should work.

Is there a way to create a button that when pressed will update this new
workbook?

It is possible to create a button and one way to do this is with the forms
toolbar.

With the 'button' button on the forms toolbar select it and then draw the
button on the worksheet and them assign the macro to it.

Note in this situation you need to modify the code and before

Set wsd = ActiveSheet

you need to insert

workbooks.add

to make a new workbook.

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"Dave" wrote:

Marin,

This looks great. A few more questions:

I am new to VBA and Macros.

Is there a way to specify criteria using an array? (i.e. if row 10="one",
then only pull information from rows 1-9 and put that information in the new
workbook.) If this is too difficult it is not absolutely necessary, though
would be extremely helpful.

Where do I paste this code once it is complete?

Is there a way to create a button that when pressed will update this new
workbook?

I really do appreciate your help.

"Martin Fishlock" wrote:

Dave, the following will do as requested.

You may need to tweek the dir function or consider just a loop if you have a
fixed file name structure (ie week01.xls, week02.xls....).

Option Explicit

Sub getdata()
' directory where the data is with the trailing \
Const cszDir As String = "C:\data\"
' prefix for the file name
Const cszFilePrefix As String = "file"
' extension for the file name
Const cszFileExtension As String = ".xls"

Dim wsd As Worksheet
Dim wbc As Workbook
Dim lRowDst As Long
Dim szFileCur As String
Dim szDir As String

Set wsd = ActiveSheet
lRowDst = 2
szFileCur = Dir(cszDir & cszFilePrefix & _
"*" & cszFileExtension)
Do While szFileCur < ""
Set wbc = Workbooks.Open(szFileCur, , True)
'get data'
wsd.Cells(lRowDst, 1) = wbc.Worksheets(1).Range("A1")
wsd.Cells(lRowDst, 2) = wbc.Worksheets(1).Range("C1")
wsd.Cells(lRowDst, 3) = wbc.Worksheets(1).Range("D1")
'....
wbc.Close False
szFileCur = Dir
lRowDst = lRowDst + 1
Loop
End Sub

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"Dave" wrote:

I need to find a solution to the folowing problem:

There is a network folder with an Exel workbook for every week in the year.
Each Friday it is updated with one new sheet for that week. All the data
contained is in the same format. I need to create a single file that when
one presses a button, will go through all of the workbooks in the network
drive and pull specific information and populate it into the one file. Is
this possible? Any help would be appreciated.

Martin,

Thank you so much. This is starting to come together perfectly. The code
apears to work, but I think the tweaks I made are causing problems.

Where do I instert the "IF" criteria statements in the code?

Do I have to insert the name of every workbook in the folder into the code?
The workbook names are actually dates (i.e. 12.1.06.xlc and so forth). Will
this code look through every book in the specified folder and return the
specified information regardless of it's name?

I am sorry to keep bothering, but I am almost there. I have learned more
about VBA in the past ten minute than I have in the past three days of
working on this. Thanks.

"Martin Fishlock" wrote:

My posts seem to have got a little messed up....and there seems to bew two
answers to the original question, never mind they are both the same.

In answer to your questions

Q: Is there a way to specify criteria using an array? (i.e. if row 10="one",
then only pull information from rows 1-9 and put that information in the new
workbook.) If this is too difficult it is not absolutely necessary, though
would be extremely helpful.

A: Yes it is. You don't need to use arrays because it then gets difficult to
control the output but you would deal with the different treatments in this
type of manner:

if wbc.Worksheets(1).Range("A1") = "Widgets" then
wsd.Cells(lRowDst, 1) = wbc.Worksheets(1).Range("A1")
elseif wbc.Worksheets(1).Range("A1") = "Acme" then
wsd.Cells(lRowDst, 2) = wbc.Worksheets(1).Range("A1")
elseif wbc.Worksheets(1).Range("B1") = "Gold" then
wsd.Cells(lRowDst, 2) = wbc.Worksheets(1).Range("A1")
'.....
endif

ie testing the conditions and then dealing with the specific data.

Where do I paste this code once it is complete?

You paste the code into a module in the visual basic editior.

1. Tools|Macros|Visual Basic Editor or Alt+F11
2. In the edotor insert a module file.
you needt to ensure that the project explorer window is displayed
(View|Project Explorer) and then select the workbook that you are using.
3.Then insert a module file (Insert|Module)
4. Paste the code in the module window.
5. Tweek the code as required
6. Check it (Debug|Compile VBA Project)
7. Go back to excel workbook
8. Run the macro (Tools|Macros) and select the macro and run it.
9. Hopefully it should work.

Is there a way to create a button that when pressed will update this new
workbook?

It is possible to create a button and one way to do this is with the forms
toolbar.

With the 'button' button on the forms toolbar select it and then draw the
button on the worksheet and them assign the macro to it.

Note in this situation you need to modify the code and before

Set wsd = ActiveSheet

you need to insert

workbooks.add

to make a new workbook.

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"Dave" wrote:

Marin,

This looks great. A few more questions:

I am new to VBA and Macros.

Is there a way to specify criteria using an array? (i.e. if row 10="one",
then only pull information from rows 1-9 and put that information in the new
workbook.) If this is too difficult it is not absolutely necessary, though
would be extremely helpful.

Where do I paste this code once it is complete?

Is there a way to create a button that when pressed will update this new
workbook?

I really do appreciate your help.

"Martin Fishlock" wrote:

Dave, the following will do as requested.

You may need to tweek the dir function or consider just a loop if you have a
fixed file name structure (ie week01.xls, week02.xls....).

Option Explicit

Sub getdata()
' directory where the data is with the trailing \
Const cszDir As String = "C:\data\"
' prefix for the file name
Const cszFilePrefix As String = "file"
' extension for the file name
Const cszFileExtension As String = ".xls"

Dim wsd As Worksheet
Dim wbc As Workbook
Dim lRowDst As Long
Dim szFileCur As String
Dim szDir As String

Set wsd = ActiveSheet
lRowDst = 2
szFileCur = Dir(cszDir & cszFilePrefix & _
"*" & cszFileExtension)
Do While szFileCur < ""
Set wbc = Workbooks.Open(szFileCur, , True)
'get data'
wsd.Cells(lRowDst, 1) = wbc.Worksheets(1).Range("A1")
wsd.Cells(lRowDst, 2) = wbc.Worksheets(1).Range("C1")
wsd.Cells(lRowDst, 3) = wbc.Worksheets(1).Range("D1")
'....
wbc.Close False
szFileCur = Dir
lRowDst = lRowDst + 1
Loop
End Sub

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"Dave" wrote:

I need to find a solution to the folowing problem:

There is a network folder with an Exel workbook for every week in the year.
Each Friday it is updated with one new sheet for that week. All the data
contained is in the same format. I need to create a single file that when
one presses a button, will go through all of the workbooks in the network
drive and pull specific information and populate it into the one file. Is
this possible? Any help would be appreciated.

Dave:

Q. Where do I instert the "IF" criteria statements in the code?

A. After the rem statement 'get data.

Q. Do I have to insert the name of every workbook in the folder into the
code?

If you set the specifications at the start of the macro it should work on
all files.

Const cszDir As String = "C:\data\" this is the directory
' prefix for the file name
Const cszFilePrefix As String = "" ' set this to "" as you have no prefix.
' extension for the file name
Const cszFileExtension As String = ".xls" ' this is the extension and you
say .xlc not .xls ????

It will give you a headache because the file names are in mm.dd.yy format
and not in yy.mm.dd which would order it correctly. So you many have to sort
the data afterwards.

The workbook names are actually dates (i.e. 12.1.06.xlc and so forth). Will
this code look through every book in the specified folder and return the
specified information regardless of it's name?

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"Dave" wrote:

Martin,

Thank you so much. This is starting to come together perfectly. The code
apears to work, but I think the tweaks I made are causing problems.

Where do I instert the "IF" criteria statements in the code?

Do I have to insert the name of every workbook in the folder into the code?
The workbook names are actually dates (i.e. 12.1.06.xlc and so forth). Will
this code look through every book in the specified folder and return the
specified information regardless of it's name?

I am sorry to keep bothering, but I am almost there. I have learned more
about VBA in the past ten minute than I have in the past three days of
working on this. Thanks.

"Martin Fishlock" wrote:

My posts seem to have got a little messed up....and there seems to bew two
answers to the original question, never mind they are both the same.

In answer to your questions

Q: Is there a way to specify criteria using an array? (i.e. if row 10="one",
then only pull information from rows 1-9 and put that information in the new
workbook.) If this is too difficult it is not absolutely necessary, though
would be extremely helpful.

A: Yes it is. You don't need to use arrays because it then gets difficult to
control the output but you would deal with the different treatments in this
type of manner:

if wbc.Worksheets(1).Range("A1") = "Widgets" then
wsd.Cells(lRowDst, 1) = wbc.Worksheets(1).Range("A1")
elseif wbc.Worksheets(1).Range("A1") = "Acme" then
wsd.Cells(lRowDst, 2) = wbc.Worksheets(1).Range("A1")
elseif wbc.Worksheets(1).Range("B1") = "Gold" then
wsd.Cells(lRowDst, 2) = wbc.Worksheets(1).Range("A1")
'.....
endif

ie testing the conditions and then dealing with the specific data.

Where do I paste this code once it is complete?

You paste the code into a module in the visual basic editior.

1. Tools|Macros|Visual Basic Editor or Alt+F11
2. In the edotor insert a module file.
you needt to ensure that the project explorer window is displayed
(View|Project Explorer) and then select the workbook that you are using.
3.Then insert a module file (Insert|Module)
4. Paste the code in the module window.
5. Tweek the code as required
6. Check it (Debug|Compile VBA Project)
7. Go back to excel workbook
8. Run the macro (Tools|Macros) and select the macro and run it.
9. Hopefully it should work.

Is there a way to create a button that when pressed will update this new
workbook?

It is possible to create a button and one way to do this is with the forms
toolbar.

With the 'button' button on the forms toolbar select it and then draw the
button on the worksheet and them assign the macro to it.

Note in this situation you need to modify the code and before

Set wsd = ActiveSheet

you need to insert

workbooks.add

to make a new workbook.

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"Dave" wrote:

Marin,

This looks great. A few more questions:

I am new to VBA and Macros.

Is there a way to specify criteria using an array? (i.e. if row 10="one",
then only pull information from rows 1-9 and put that information in the new
workbook.) If this is too difficult it is not absolutely necessary, though
would be extremely helpful.

Where do I paste this code once it is complete?

Is there a way to create a button that when pressed will update this new
workbook?

I really do appreciate your help.

"Martin Fishlock" wrote:

Dave, the following will do as requested.

You may need to tweek the dir function or consider just a loop if you have a
fixed file name structure (ie week01.xls, week02.xls....).

Option Explicit

Sub getdata()
' directory where the data is with the trailing \
Const cszDir As String = "C:\data\"
' prefix for the file name
Const cszFilePrefix As String = "file"
' extension for the file name
Const cszFileExtension As String = ".xls"

Dim wsd As Worksheet
Dim wbc As Workbook
Dim lRowDst As Long
Dim szFileCur As String
Dim szDir As String

Set wsd = ActiveSheet
lRowDst = 2
szFileCur = Dir(cszDir & cszFilePrefix & _
"*" & cszFileExtension)
Do While szFileCur < ""
Set wbc = Workbooks.Open(szFileCur, , True)
'get data'
wsd.Cells(lRowDst, 1) = wbc.Worksheets(1).Range("A1")
wsd.Cells(lRowDst, 2) = wbc.Worksheets(1).Range("C1")
wsd.Cells(lRowDst, 3) = wbc.Worksheets(1).Range("D1")
'....
wbc.Close False
szFileCur = Dir
lRowDst = lRowDst + 1
Loop
End Sub

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"Dave" wrote:

I need to find a solution to the folowing problem:

There is a network folder with an Exel workbook for every week in the year.
Each Friday it is updated with one new sheet for that week. All the data
contained is in the same format. I need to create a single file that when
one presses a button, will go through all of the workbooks in the network
drive and pull specific information and populate it into the one file. Is
this possible? Any help would be appreciated.

Martin,

The code runs without any errors but isn't returning any data. The screen
flashes, but the sheet I want to the data is blank. Here is the code: (I am
not worrying about the IF statements at the moment. My first priority is to
be able to consolidate all of the information other workbooks into on
spreadsheet. I don't want to take up any more of your time worrying about IF
statements.)

Sub getdata()
' directory where the data is with the trailing \
Const cszDir As String = "C:\Documents and Settings\David Morris\Desktop\Test"
' prefix for the file name
Const cszFilePrefix As String = ""
' extension for the file name
Const cszFileExtension As String = ".xls"

Dim wsd As Worksheet
Dim wbc As Workbook
Dim lRowDst As Long
Dim szFileCur As String
Dim szDir As String


Set wsd = ActiveSheet
lRowDst = 2
szFileCur = Dir(cszDir & cszFilePrefix & _
"*" & cszFileExtension)
Do While szFileCur < ""
Set wbc = Workbooks.Open(szFileCur, , True)
'get data
wsd.Cells(lRowDst, 1) = wbc.Worksheets(1).Range("1:1")
wsd.Cells(lRowDst, 2) = wbc.Worksheets(1).Range("2:2")
wsd.Cells(lRowDst, 3) = wbc.Worksheets(1).Range("3:3")
'....
wbc.Close False
szFileCur = Dir
lRowDst = lRowDst + 1
Loop
End Sub

"Martin Fishlock" wrote:

Dave:

Q. Where do I instert the "IF" criteria statements in the code?

A. After the rem statement 'get data.

Q. Do I have to insert the name of every workbook in the folder into the
code?

If you set the specifications at the start of the macro it should work on
all files.

Const cszDir As String = "C:\data\" this is the directory
' prefix for the file name
Const cszFilePrefix As String = "" ' set this to "" as you have no prefix.
' extension for the file name
Const cszFileExtension As String = ".xls" ' this is the extension and you
say .xlc not .xls ????

It will give you a headache because the file names are in mm.dd.yy format
and not in yy.mm.dd which would order it correctly. So you many have to sort
the data afterwards.

The workbook names are actually dates (i.e. 12.1.06.xlc and so forth). Will
this code look through every book in the specified folder and return the
specified information regardless of it's name?

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"Dave" wrote:

Martin,

Thank you so much. This is starting to come together perfectly. The code
apears to work, but I think the tweaks I made are causing problems.

Where do I instert the "IF" criteria statements in the code?

Do I have to insert the name of every workbook in the folder into the code?
The workbook names are actually dates (i.e. 12.1.06.xlc and so forth). Will
this code look through every book in the specified folder and return the
specified information regardless of it's name?

I am sorry to keep bothering, but I am almost there. I have learned more
about VBA in the past ten minute than I have in the past three days of
working on this. Thanks.

"Martin Fishlock" wrote:

My posts seem to have got a little messed up....and there seems to bew two
answers to the original question, never mind they are both the same.

In answer to your questions

Q: Is there a way to specify criteria using an array? (i.e. if row 10="one",
then only pull information from rows 1-9 and put that information in the new
workbook.) If this is too difficult it is not absolutely necessary, though
would be extremely helpful.

A: Yes it is. You don't need to use arrays because it then gets difficult to
control the output but you would deal with the different treatments in this
type of manner:

if wbc.Worksheets(1).Range("A1") = "Widgets" then
wsd.Cells(lRowDst, 1) = wbc.Worksheets(1).Range("A1")
elseif wbc.Worksheets(1).Range("A1") = "Acme" then
wsd.Cells(lRowDst, 2) = wbc.Worksheets(1).Range("A1")
elseif wbc.Worksheets(1).Range("B1") = "Gold" then
wsd.Cells(lRowDst, 2) = wbc.Worksheets(1).Range("A1")
'.....
endif

ie testing the conditions and then dealing with the specific data.

Where do I paste this code once it is complete?

You paste the code into a module in the visual basic editior.

1. Tools|Macros|Visual Basic Editor or Alt+F11
2. In the edotor insert a module file.
you needt to ensure that the project explorer window is displayed
(View|Project Explorer) and then select the workbook that you are using.
3.Then insert a module file (Insert|Module)
4. Paste the code in the module window.
5. Tweek the code as required
6. Check it (Debug|Compile VBA Project)
7. Go back to excel workbook
8. Run the macro (Tools|Macros) and select the macro and run it.
9. Hopefully it should work.

Is there a way to create a button that when pressed will update this new
workbook?

It is possible to create a button and one way to do this is with the forms
toolbar.

With the 'button' button on the forms toolbar select it and then draw the
button on the worksheet and them assign the macro to it.

Note in this situation you need to modify the code and before

Set wsd = ActiveSheet

you need to insert

workbooks.add

to make a new workbook.

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"Dave" wrote:

Marin,

This looks great. A few more questions:

I am new to VBA and Macros.

Is there a way to specify criteria using an array? (i.e. if row 10="one",
then only pull information from rows 1-9 and put that information in the new
workbook.) If this is too difficult it is not absolutely necessary, though
would be extremely helpful.

Where do I paste this code once it is complete?

Is there a way to create a button that when pressed will update this new
workbook?

I really do appreciate your help.

"Martin Fishlock" wrote:

Dave, the following will do as requested.

You may need to tweek the dir function or consider just a loop if you have a
fixed file name structure (ie week01.xls, week02.xls....).

Option Explicit

Sub getdata()
' directory where the data is with the trailing \
Const cszDir As String = "C:\data\"
' prefix for the file name
Const cszFilePrefix As String = "file"
' extension for the file name
Const cszFileExtension As String = ".xls"

Dim wsd As Worksheet
Dim wbc As Workbook
Dim lRowDst As Long
Dim szFileCur As String
Dim szDir As String

Set wsd = ActiveSheet
lRowDst = 2
szFileCur = Dir(cszDir & cszFilePrefix & _
"*" & cszFileExtension)
Do While szFileCur < ""
Set wbc = Workbooks.Open(szFileCur, , True)
'get data'
wsd.Cells(lRowDst, 1) = wbc.Worksheets(1).Range("A1")
wsd.Cells(lRowDst, 2) = wbc.Worksheets(1).Range("C1")
wsd.Cells(lRowDst, 3) = wbc.Worksheets(1).Range("D1")
'....
wbc.Close False
szFileCur = Dir
lRowDst = lRowDst + 1
Loop
End Sub

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"Dave" wrote:

I need to find a solution to the folowing problem:

There is a network folder with an Exel workbook for every week in the year.
Each Friday it is updated with one new sheet for that week. All the data
contained is in the same format. I need to create a single file that when
one presses a button, will go through all of the workbooks in the network
drive and pull specific information and populate it into the one file. Is
this possible? Any help would be appreciated.