put files together

  • Thread starter Thread starter Jack Sons
  • Start date Start date
J

Jack Sons

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,

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.
 
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
 
Back
Top