Copying data from multiple files

[Crownman]

I am trying to create a macro that will copy the values of several
different ranges from a series of identically formatted workbooks to a
single new file to create a database. So far I have the following
code that does what I need for a single file, but I need to have the
macro recognize each new file rather than being fixed on the first
file (TEST 1). TEST 1 is the active file when I start the macro.

Most of this was done via the macro recorder as my knowledge of VBA is
extremely limited.

Sub FOB_REVIEW()
'
' Range("B2").Select
Application.CutCopyMode = False
Selection.Copy
Windows("FOB REVIEW.xls").Activate
Range("A6").End(xlDown).Offset(1, 0).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("TEST-1.xls").Activate
Range("B6").Select
Application.CutCopyMode = False
Selection.Copy
Windows("FOB REVIEW.xls").Activate
Range("A6").End(xlDown).Offset(0, 1).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("TEST-1.xls").Activate
Range("B4").Select
Application.CutCopyMode = False
Selection.Copy
Windows("FOB REVIEW.xls").Activate
Range("A6").End(xlDown).Offset(0, 2).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("TEST-1.xls").Activate
Range("C27").Select
''ActiveWindow.SmallScroll ToRight:=-4
Range("E26:R26").Select
Application.CutCopyMode = False
Selection.Copy
Windows("FOB REVIEW.xls").Activate
Range("A6").End(xlDown).Offset(0, 3).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A7").Select
End Sub

I would appreciate any help anyone can give me on this. TIA

Crownman

 

[CLR]

It could be done a couple of ways, I guess. The macro could interrogate
every file in a given directory, or it could check off the filenames from a
given list........the code must have some way of knowing which file to get
next and from where.

Vaya con Dios,
Chuck, CABGx3

 

[Crownman]

It could be done a couple of ways, I guess.  The macro could interrogate
every file in a given directory, or it could check off the filenames from a
given list........the code must have some way of knowing which file to get
next and from where.

Vaya con Dios,
Chuck, CABGx3













- Show quoted text -

Chuck:

My intention is to have the destination file and all of the source
files open before starting the macro. Does that simplify a possible
solution?

Thanks

Crownman

 

[CLR]

I'm outta day here now, but if that is your intention, then you know all the
file/paths in advance.........just turn on the macro recorder and go through
the whole sequence once.....or at least for a few of them, and then edit and
maybe copy and paste sections of the macro for the rest........this is a
thing thats done regularly, so don't doubt that you will ever get
there......it's just that the Devil is in the details..........

Vaya con Dios,
Chuck, CABGx3

 

[Otto Moehrbach]

The following macro should be placed in the Database file and the Database
file must be the active file.
Note the "For Each....." line. That line lists all the other file's names
from which you want to copy. Post back if you need more. HTH Otto
Sub CopyFromWBs()
Dim WBName As Variant
For Each WBName In Array("One.xls", "Two.xls", "Three.xls")
With Workbooks(WBName)
.Range("B2").Copy
Range("A6").End(xlDown).Offset(1, 0).PasteSpecial
Paste:=xlPasteValues
.Range("B6").Copy
Range("A6").End(xlDown).Offset(0, 1).PasteSpecial
Paste:=xlPasteValues
.Range("B4").Copy
Range("A6").End(xlDown).Offset(0, 2).PasteSpecial
Paste:=xlPasteValues
End With
Next WBName
End Sub

 

[Crownman]

I'm outta day here now, but if that is your intention, then you know all the
file/paths in advance.........just turn on the macro recorder and go through
the whole sequence once.....or at least for a few of them, and then edit and
maybe copy and paste sections of the macro for the rest........this is a
thing thats done regularly, so don't doubt that you will ever get
there......it's just that the Devil is in the details..........

Vaya con Dios,
Chuck, CABGx3





- Show quoted text -

Chuck:

Thanks for the direction, but I don't think that approach is
practical. There are about 500 files spread over about 35
directories. In addition there is the probability that additonal
files will get added to some of the directories over time. I was look
for something that might allow me to click on the first source file,
run the macro, click on the scond source file, run the macro, etc.

Some kind of a loop approach where the mactro might somehow cycle
through all of the open source files might work even better, but that
is probably completely beyond what I can do. If you have any other
ideas I would like to hear from you again.

Thanks for your help

Crownman

 

[Crownman]

The following macro should be placed in the Database file and the Database
file must be the active file.
Note the "For Each....." line.  That line lists all the other file's names
from which you want to copy.  Post back if you need more.  HTH  Otto
Sub CopyFromWBs()
      Dim WBName As Variant
      For Each WBName In Array("One.xls", "Two.xls", "Three.xls")
            With Workbooks(WBName)
                  .Range("B2").Copy
                  Range("A6").End(xlDown).Offset(1, 0).PasteSpecial
Paste:=xlPasteValues
                  .Range("B6").Copy
                  Range("A6").End(xlDown).Offset(0, 1).PasteSpecial
Paste:=xlPasteValues
                  .Range("B4").Copy
                  Range("A6").End(xlDown).Offset(0, 2).PasteSpecial
Paste:=xlPasteValues
            End With
      Next WBName
End Sub














- Show quoted text -

Otto:

Thank you for your suggestion. I pasted the macro code into the
database file and changed the filenames to match my three test source
files. I got a syntax error on the first of the following lines:

Paste:=xlPasteValues

If I understand your macro, I don't think that it will be practical to
list all of the source filenames within the macro code since there are
about 500 source files spread over about 35 folders and there is a
probability that additional source files will be added over time.

I would appreciate any other suggestions that you might have. I am
open to practically any approach to this problem.

Crownman

 

[Otto Moehrbach]

Code can be written to loop through all the folders. The list of folders
can be put in some sheet and referenced in the code. If different paths
exist for the folders (besides the folder name itself), then the list must
include these path differences. Then a loop inside of that loop can be
written to open each file, in turn, in the folder. The code would work with
that one file, open it, do whatever with it, close it, open the next file,
etc. Then it would go to the next folder and repeat the process.
The problem you had with the code I sent you is probably a result of the
line wrapping in the posting. The line you reference is your code direct.
It belongs at the end of the line above it. Post back if what I describe
above sounds like what you might be able to use. Otto

The following macro should be placed in the Database file and the Database
file must be the active file.
Note the "For Each....." line. That line lists all the other file's names
from which you want to copy. Post back if you need more. HTH Otto
Sub CopyFromWBs()
Dim WBName As Variant
For Each WBName In Array("One.xls", "Two.xls", "Three.xls")
With Workbooks(WBName)
.Range("B2").Copy
Range("A6").End(xlDown).Offset(1, 0).PasteSpecial
Paste:=xlPasteValues
.Range("B6").Copy
Range("A6").End(xlDown).Offset(0, 1).PasteSpecial
Paste:=xlPasteValues
.Range("B4").Copy
Range("A6").End(xlDown).Offset(0, 2).PasteSpecial
Paste:=xlPasteValues
End With
Next WBName
End Sub














- Show quoted text -

Otto:

Thank you for your suggestion. I pasted the macro code into the
database file and changed the filenames to match my three test source
files. I got a syntax error on the first of the following lines:

Paste:=xlPasteValues

If I understand your macro, I don't think that it will be practical to
list all of the source filenames within the macro code since there are
about 500 source files spread over about 35 folders and there is a
probability that additional source files will be added over time.

I would appreciate any other suggestions that you might have. I am
open to practically any approach to this problem.

Crownman

 

[Crownman]

Code can be written to loop through all the folders.  The list of folders
can be put in some sheet and referenced in the code.  If different paths
exist for the folders (besides the folder name itself), then the list must
include these path differences.  Then a loop inside of that loop can be
written to open each file, in turn, in the folder.  The code would work with
that one file, open it, do whatever with it, close it, open the next file,
etc.  Then it would go to the next folder and repeat the process.
The problem you had with the code I sent you is probably a result of the
line wrapping in the posting.  The line you reference is your code direct.
It belongs at the end of the line above it.  Post back if what I describe





Otto:

Thank you for your suggestion.  I pasted the macro code into the
database file and changed the filenames to match my three test source
files.  I got a syntax error on the first of the following lines:

Paste:=xlPasteValues

If I understand your macro, I don't think that it will be practical to
list all of the source filenames within the macro code since there are
about 500 source files spread over about 35 folders and there is a
probability that additional source files will be added over time.

I would appreciate any other suggestions that you might have. I am
open to practically any approach to this problem.

Crownman- Hide quoted text -

- Show quoted text -

Otto:

This sounds like this would be a much more efficient approach than
what I originally intended to do. I would presume that the list of
folders should be on a separate sheet within the workbook that
contains the database.

The database would reside in the same folder as al of the folders
containing the source files. There are some cases where the source
files are in a sub folder, but I think I can handle sorting all of
that out. Would each individual file within the various folders have
to be included on a list as well?

I would love to see the code behind your ideas and greatly appreciate
your help.

Crownman

 

[Otto Moehrbach]

A list of the individual files would not be necessary. The code will loop
through all the files in the folders. If you want to exclude specific files
within those folders, then the code would need to know the names of just
those files or some way to identify those files.
The list would have to be a list of all the full paths to every folder.
If you wish, email me your database file with this list. My email address
is (e-mail address removed). Remove the "nop" from this address. Otto

Code can be written to loop through all the folders. The list of folders
can be put in some sheet and referenced in the code. If different paths
exist for the folders (besides the folder name itself), then the list must
include these path differences. Then a loop inside of that loop can be
written to open each file, in turn, in the folder. The code would work
with
that one file, open it, do whatever with it, close it, open the next file,
etc. Then it would go to the next folder and repeat the process.
The problem you had with the code I sent you is probably a result of the
line wrapping in the posting. The line you reference is your code direct.
It belongs at the end of the line above it. Post back if what I describe
above sounds like what you might be able to use. Otto"Crownman"





Otto:

Thank you for your suggestion. I pasted the macro code into the
database file and changed the filenames to match my three test source
files. I got a syntax error on the first of the following lines:

Paste:=xlPasteValues

If I understand your macro, I don't think that it will be practical to
list all of the source filenames within the macro code since there are
about 500 source files spread over about 35 folders and there is a
probability that additional source files will be added over time.

I would appreciate any other suggestions that you might have. I am
open to practically any approach to this problem.

Crownman- Hide quoted text -

- Show quoted text -

Otto:

This sounds like this would be a much more efficient approach than
what I originally intended to do. I would presume that the list of
folders should be on a separate sheet within the workbook that
contains the database.

The database would reside in the same folder as al of the folders
containing the source files. There are some cases where the source
files are in a sub folder, but I think I can handle sorting all of
that out. Would each individual file within the various folders have
to be included on a list as well?

I would love to see the code behind your ideas and greatly appreciate
your help.

Crownman