put files together
 

Hi all,

I have in a directory "summary" some 800 files with a number for a name,
like 8, 23, 44, 124, 2045 up to somewhere in the 2500's. There is no list of
all those file names.

Each file is a workbook with only one sheet called "ABCD". I know that the
last cell used will be a cell in column H. I also know that the content of
that cell will be ==== (4 to 7 times =). Column H will contain text, blanks
(empty cells), numbers and other characters (like f.i. =). The number of
rows used will be at least 6 and could be as much as 200 or so (25 is
typical).

I need code to do the following.

I want to put al those files into one sheet of a new workbook (workbook name
XYZ, sheet name KLM). Therefore the files of the summary directory have to
be taken one after each other (I cannot call them by name because there is
no system in the way the number names follow each other, so it has to be
"first file", "next file" etc.) and have the content of their only sheet
copied (appended) to sheet KLM of workbook XYZ one after the other .

But first I want to put in cell H1 of each file of the summary directory the
entry "firstCNR" (without the ") followed by the number that is the name of
that file (so for instance firstCNR23 or firstCNR2045). After the last cell
in column H (which can only contain a number of = characters), so one cell
below the last cell used in column H, I want to put "lastCNR" (without the
") followed by the number that is name of that file.

The thus modified file has to be put back in the summary directory after it
is appended two rows below to what already consists on sheet KLM of the new
workbook XYZ.

If the operation is completed all "number files" are back in the "summary"
directory, but slightly modified with first CNR... and lastCNR.. In sheet
KLM of workbook XYZ colums A to H we have then the some 800 files (slightly
modified) of "summary" below one other with one empty row separating them.

Your help will be enormously appreciated.

Jack Sons
The Netherlands

Hi Jack

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

Or

http://www.rondebruin.nl/summary2.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Jack Sons" wrote in message ...
Hi all,

I have in a directory "summary" some 800 files with a number for a name, like 8, 23, 44, 124, 2045 up to somewhere in the 2500's.
There is no list of all those file names.

Each file is a workbook with only one sheet called "ABCD". I know that the last cell used will be a cell in column H. I also know
that the content of that cell will be ==== (4 to 7 times =). Column H will contain text, blanks (empty cells), numbers and other
characters (like f.i. =). The number of rows used will be at least 6 and could be as much as 200 or so (25 is typical).

I need code to do the following.

I want to put al those files into one sheet of a new workbook (workbook name XYZ, sheet name KLM). Therefore the files of the
summary directory have to be taken one after each other (I cannot call them by name because there is no system in the way the
number names follow each other, so it has to be "first file", "next file" etc.) and have the content of their only sheet copied
(appended) to sheet KLM of workbook XYZ one after the other .

But first I want to put in cell H1 of each file of the summary directory the entry "firstCNR" (without the ") followed by the
number that is the name of that file (so for instance firstCNR23 or firstCNR2045). After the last cell in column H (which can only
contain a number of = characters), so one cell below the last cell used in column H, I want to put "lastCNR" (without the ")
followed by the number that is name of that file.

The thus modified file has to be put back in the summary directory after it is appended two rows below to what already consists on
sheet KLM of the new workbook XYZ.

If the operation is completed all "number files" are back in the "summary" directory, but slightly modified with first CNR... and
lastCNR.. In sheet KLM of workbook XYZ colums A to H we have then the some 800 files (slightly modified) of "summary" below one
other with one empty row separating them.

Your help will be enormously appreciated.

Jack Sons
The Netherlands

Oops

That stupid keyboard, this must be the first link

http://www.rondebruin.nl/copy2.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Jack Sons" wrote in message ...
Hi all,

I have in a directory "summary" some 800 files with a number for a name, like 8, 23, 44, 124, 2045 up to somewhere in the 2500's.
There is no list of all those file names.

Each file is a workbook with only one sheet called "ABCD". I know that the last cell used will be a cell in column H. I also know
that the content of that cell will be ==== (4 to 7 times =). Column H will contain text, blanks (empty cells), numbers and other
characters (like f.i. =). The number of rows used will be at least 6 and could be as much as 200 or so (25 is typical).

I need code to do the following.

I want to put al those files into one sheet of a new workbook (workbook name XYZ, sheet name KLM). Therefore the files of the
summary directory have to be taken one after each other (I cannot call them by name because there is no system in the way the
number names follow each other, so it has to be "first file", "next file" etc.) and have the content of their only sheet copied
(appended) to sheet KLM of workbook XYZ one after the other .

But first I want to put in cell H1 of each file of the summary directory the entry "firstCNR" (without the ") followed by the
number that is the name of that file (so for instance firstCNR23 or firstCNR2045). After the last cell in column H (which can only
contain a number of = characters), so one cell below the last cell used in column H, I want to put "lastCNR" (without the ")
followed by the number that is name of that file.

The thus modified file has to be put back in the summary directory after it is appended two rows below to what already consists on
sheet KLM of the new workbook XYZ.

If the operation is completed all "number files" are back in the "summary" directory, but slightly modified with first CNR... and
lastCNR.. In sheet KLM of workbook XYZ colums A to H we have then the some 800 files (slightly modified) of "summary" below one
other with one empty row separating them.

Your help will be enormously appreciated.

Jack Sons
The Netherlands

Shoot me ( the other one is for worksheets)
http://www.rondebruin.nl/copy3.htm

Next time I post the link to my tips page <g


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ron de Bruin" wrote in message ...
Hi Jack

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

Or

http://www.rondebruin.nl/summary2.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Jack Sons" wrote in message ...
Hi all,

I have in a directory "summary" some 800 files with a number for a name, like 8, 23, 44, 124, 2045 up to somewhere in the 2500's.
There is no list of all those file names.

Each file is a workbook with only one sheet called "ABCD". I know that the last cell used will be a cell in column H. I also know
that the content of that cell will be ==== (4 to 7 times =). Column H will contain text, blanks (empty cells), numbers and other
characters (like f.i. =). The number of rows used will be at least 6 and could be as much as 200 or so (25 is typical).

I need code to do the following.

I want to put al those files into one sheet of a new workbook (workbook name XYZ, sheet name KLM). Therefore the files of the
summary directory have to be taken one after each other (I cannot call them by name because there is no system in the way the
number names follow each other, so it has to be "first file", "next file" etc.) and have the content of their only sheet copied
(appended) to sheet KLM of workbook XYZ one after the other .

But first I want to put in cell H1 of each file of the summary directory the entry "firstCNR" (without the ") followed by the
number that is the name of that file (so for instance firstCNR23 or firstCNR2045). After the last cell in column H (which can
only contain a number of = characters), so one cell below the last cell used in column H, I want to put "lastCNR" (without the
") followed by the number that is name of that file.

The thus modified file has to be put back in the summary directory after it is appended two rows below to what already consists
on sheet KLM of the new workbook XYZ.

If the operation is completed all "number files" are back in the "summary" directory, but slightly modified with first CNR... and
lastCNR.. In sheet KLM of workbook XYZ colums A to H we have then the some 800 files (slightly modified) of "summary" below one
other with one empty row separating them.

Your help will be enormously appreciated.

Jack Sons
The Netherlands

Ron,

Thanks for your answer, almost faster than lightning.

I appreciate al the used range stuff, but for me the first difficult thing
will be taking the files of the directory "summary" and putting them back
after modifying. Second difficult thing is appending their contents to what
already exist on the new sheet KLM of the new workbook XYZ.with one empty
row as separation.

I forgot to tell that CNR followed by the number (that is also the file
name) is already in Cells(3, 8).

I tried a lot of things which kept me busy for hours, but I succeeded only
in the modifying job and appending the content of a file below itself. I
used the code below, you will see it is rather crude, not very efficient,
let alone sofisticated. Looks rather stupid I am afraid.

So please help me with the code I need, can't do it on my own.

Jack.

Range(Cells(1, 8), Cells(1, 8)).Value = "first" & Range(Cells(3, 8),
Cells(3, 8)).Value

Range("H60000").Select
Cells.Find(What:="==", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious,
MatchCase:= _
False).Activate
rr = ActiveCell.Row
cc = ActiveCell.Column
aa = ActiveCell.Address

Range(Cells(rr + 1, 8), Cells(rr + 1, 8)).Value = "last" & Range(Cells(3,
8), Cells(3, 8)).Value

Range(Cells(1, 1), Cells(rr + 1, 8)).Select
Selection.Copy
Range(Cells(rr + 2, 1), Cells(rr + 2, 1)).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
ActiveSheet.Paste
End Sub


"Ron de Bruin" schreef in bericht
...
Oops

That stupid keyboard, this must be the first link

http://www.rondebruin.nl/copy2.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Jack Sons" wrote in message
...
Hi all,

I have in a directory "summary" some 800 files with a number for a name,
like 8, 23, 44, 124, 2045 up to somewhere in the 2500's. There is no list
of all those file names.

Each file is a workbook with only one sheet called "ABCD". I know that
the last cell used will be a cell in column H. I also know that the
content of that cell will be ==== (4 to 7 times =). Column H will contain
text, blanks (empty cells), numbers and other characters (like f.i. =).
The number of rows used will be at least 6 and could be as much as 200 or
so (25 is typical).

I need code to do the following.

I want to put al those files into one sheet of a new workbook (workbook
name XYZ, sheet name KLM). Therefore the files of the summary directory
have to be taken one after each other (I cannot call them by name because
there is no system in the way the number names follow each other, so it
has to be "first file", "next file" etc.) and have the content of their
only sheet copied (appended) to sheet KLM of workbook XYZ one after the
other .

But first I want to put in cell H1 of each file of the summary directory
the entry "firstCNR" (without the ") followed by the number that is the
name of that file (so for instance firstCNR23 or firstCNR2045). After the
last cell in column H (which can only contain a number of = characters),
so one cell below the last cell used in column H, I want to put
"lastCNR" (without the ") followed by the number that is name of that
file.

The thus modified file has to be put back in the summary directory after
it is appended two rows below to what already consists on sheet KLM of
the new workbook XYZ.

If the operation is completed all "number files" are back in the
"summary" directory, but slightly modified with first CNR... and
lastCNR.. In sheet KLM of workbook XYZ colums A to H we have then the
some 800 files (slightly modified) of "summary" below one other with one
empty row separating them.

Your help will be enormously appreciated.

Jack Sons
The Netherlands

But first I want to put in cell H1 of each file of the summary directory the

Start with this example then (use the one cell example)
http://www.rondebruin.nl/copy4.htm

Use this line

mybook.Worksheets(1).Range("H1").Value = "firstCNR" & mybook.name



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Jack Sons" wrote in message ...
Hi all,

I have in a directory "summary" some 800 files with a number for a name, like 8, 23, 44, 124, 2045 up to somewhere in the 2500's.
There is no list of all those file names.

Each file is a workbook with only one sheet called "ABCD". I know that the last cell used will be a cell in column H. I also know
that the content of that cell will be ==== (4 to 7 times =). Column H will contain text, blanks (empty cells), numbers and other
characters (like f.i. =). The number of rows used will be at least 6 and could be as much as 200 or so (25 is typical).

I need code to do the following.

I want to put al those files into one sheet of a new workbook (workbook name XYZ, sheet name KLM). Therefore the files of the
summary directory have to be taken one after each other (I cannot call them by name because there is no system in the way the
number names follow each other, so it has to be "first file", "next file" etc.) and have the content of their only sheet copied
(appended) to sheet KLM of workbook XYZ one after the other .

But first I want to put in cell H1 of each file of the summary directory the entry "firstCNR" (without the ") followed by the
number that is the name of that file (so for instance firstCNR23 or firstCNR2045). After the last cell in column H (which can only
contain a number of = characters), so one cell below the last cell used in column H, I want to put "lastCNR" (without the ")
followed by the number that is name of that file.

The thus modified file has to be put back in the summary directory after it is appended two rows below to what already consists on
sheet KLM of the new workbook XYZ.

If the operation is completed all "number files" are back in the "summary" directory, but slightly modified with first CNR... and
lastCNR.. In sheet KLM of workbook XYZ colums A to H we have then the some 800 files (slightly modified) of "summary" below one
other with one empty row separating them.

Your help will be enormously appreciated.

Jack Sons
The Netherlands

Ron,

I don't know wether I understand the code well enough, is Sub
Change_One_Cell_2() what I should use?

As I showed in my code the adding of "firstCNR" & mybook.name is not the
real problem (your code is infinitely nicer of course) but the catching of
the successive files from the directory (for which I saw a solution in your
code?) was a problem and especially pasting (appending) the content of each
file to what is already in the only sheet of the new workbook is still
something I can't accomplish.

Do you have a solution for that?

Jack.


"Ron de Bruin" schreef in bericht
...
But first I want to put in cell H1 of each file of the summary directory
the

Start with this example then (use the one cell example)
http://www.rondebruin.nl/copy4.htm

Use this line

mybook.Worksheets(1).Range("H1").Value = "firstCNR" & mybook.name



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Jack Sons" wrote in message
...
Hi all,

I have in a directory "summary" some 800 files with a number for a name,
like 8, 23, 44, 124, 2045 up to somewhere in the 2500's. There is no list
of all those file names.

Each file is a workbook with only one sheet called "ABCD". I know that
the last cell used will be a cell in column H. I also know that the
content of that cell will be ==== (4 to 7 times =). Column H will contain
text, blanks (empty cells), numbers and other characters (like f.i. =).
The number of rows used will be at least 6 and could be as much as 200 or
so (25 is typical).

I need code to do the following.

I want to put al those files into one sheet of a new workbook (workbook
name XYZ, sheet name KLM). Therefore the files of the summary directory
have to be taken one after each other (I cannot call them by name because
there is no system in the way the number names follow each other, so it
has to be "first file", "next file" etc.) and have the content of their
only sheet copied (appended) to sheet KLM of workbook XYZ one after the
other .

But first I want to put in cell H1 of each file of the summary directory
the entry "firstCNR" (without the ") followed by the number that is the
name of that file (so for instance firstCNR23 or firstCNR2045). After the
last cell in column H (which can only contain a number of = characters),
so one cell below the last cell used in column H, I want to put
"lastCNR" (without the ") followed by the number that is name of that
file.

The thus modified file has to be put back in the summary directory after
it is appended two rows below to what already consists on sheet KLM of
the new workbook XYZ.

If the operation is completed all "number files" are back in the
"summary" directory, but slightly modified with first CNR... and
lastCNR.. In sheet KLM of workbook XYZ colums A to H we have then the
some 800 files (slightly modified) of "summary" below one other with one
empty row separating them.

Your help will be enormously appreciated.

Jack Sons
The Netherlands

Hi Jack

I am confused

Send me a private mail in Dutch (I live there also) if you want and I help you tomorrow
Send also one of the files and explain exact what you want


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Jack Sons" wrote in message ...
Ron,

I don't know wether I understand the code well enough, is Sub Change_One_Cell_2() what I should use?

As I showed in my code the adding of "firstCNR" & mybook.name is not the real problem (your code is infinitely nicer of course)
but the catching of the successive files from the directory (for which I saw a solution in your code?) was a problem and
especially pasting (appending) the content of each file to what is already in the only sheet of the new workbook is still
something I can't accomplish.

Do you have a solution for that?

Jack.


"Ron de Bruin" schreef in bericht ...
But first I want to put in cell H1 of each file of the summary directory the

Start with this example then (use the one cell example)
http://www.rondebruin.nl/copy4.htm

Use this line

mybook.Worksheets(1).Range("H1").Value = "firstCNR" & mybook.name



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Jack Sons" wrote in message ...
Hi all,

I have in a directory "summary" some 800 files with a number for a name, like 8, 23, 44, 124, 2045 up to somewhere in the
2500's. There is no list of all those file names.

Each file is a workbook with only one sheet called "ABCD". I know that the last cell used will be a cell in column H. I also
know that the content of that cell will be ==== (4 to 7 times =). Column H will contain text, blanks (empty cells), numbers and
other characters (like f.i. =). The number of rows used will be at least 6 and could be as much as 200 or so (25 is typical).

I need code to do the following.

I want to put al those files into one sheet of a new workbook (workbook name XYZ, sheet name KLM). Therefore the files of the
summary directory have to be taken one after each other (I cannot call them by name because there is no system in the way the
number names follow each other, so it has to be "first file", "next file" etc.) and have the content of their only sheet copied
(appended) to sheet KLM of workbook XYZ one after the other .

But first I want to put in cell H1 of each file of the summary directory the entry "firstCNR" (without the ") followed by the
number that is the name of that file (so for instance firstCNR23 or firstCNR2045). After the last cell in column H (which can
only contain a number of = characters), so one cell below the last cell used in column H, I want to put "lastCNR" (without the
") followed by the number that is name of that file.

The thus modified file has to be put back in the summary directory after it is appended two rows below to what already consists
on sheet KLM of the new workbook XYZ.

If the operation is completed all "number files" are back in the "summary" directory, but slightly modified with first CNR...
and lastCNR.. In sheet KLM of workbook XYZ colums A to H we have then the some 800 files (slightly modified) of "summary" below
one other with one empty row separating them.

Your help will be enormously appreciated.

Jack Sons
The Netherlands