Re: Copying data from multiple files

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 ottokmnop@xxxxxxxxxxxx Remove the "nop" from this address. Otto
"Crownman" <crownman451@xxxxxxxxxxxxx> wrote in message
news:7f0a5e09-273a-45f6-ba9c-5690757fe52e@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Jan 11, 5:38 pm, "Otto Moehrbach" <ottokm...@xxxxxxxxxxx> wrote:
Code can be written to loop through all the folders. The list of folders
can be put in some *** 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"
<crownman...@xxxxxxxxxxxxx> wrote in message

news:f7266922-3436-4119-8677-80ba5e65ea82@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Jan 11, 2:38 pm, "Otto Moehrbach" <ottokm...@xxxxxxxxxxx> wrote:





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" <crownman...@xxxxxxxxxxxxx> wrote in message

news:a50227db-76c3-42f5-830c-67faca9acada@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

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- Hide quoted text -

- 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- 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 *** 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


.

Loading