Not a programmer...am I in over my head??

[Christina]

I just need someone to tell me if I'm crazy to even think about trying to
figure this out or if I should just try to get a "real" programmer to do it.
Here's the problem:

5 workbooks for 1 company (call the company P1).
300 companies (P1-P300, 1500 files total).
Files are consistantly named P1_this_data, P1_that_data then P2_this_data,
P2_that_data(output from STATA in xls or cvs format).
Created a "template" where data for P1 is pulled in.
Need to pull in data into the same template from P2, then P3, etc.
Right now I know I can open the "template", open the next company's files,
do a search and replace (P1 with P2, etc.) and then save as.
For this many files...that's a lot of manual work, no?
Would like to automate:
Opening the next set of files, doing the search and replace doing the save
as and renaming.
Sorry to be so ignorant...is this even possilbe?

Thanks for any help.

 

[Bob Phillips]

Simple enough, hardest part is identifying the 'getting' of the files.

Are they all in the same directory, and are they the only ones there?

Where will the data go in the template, all appended on the first sheet, a
sheet per workbook, or something else?

 

[Jim Thomlinson]

Yes that is very possible. In fact it is not too bad to do at all if done one
step at a time. What you need to do is to record a macro doing it on one file
and then post that code with a descripion of exactly what you are doing...

 

[Christina]

Thanks for your response! Yes, they are all in the same directory. Output
will be to seperate sheets in the same book. Another response says I should
do a macro with the code and post it...? I can do that.

 

[Christina]

OK, I can do that. I will have to do it tomorrow morning -- THANK YOU!!!

 

[Christina]

Bob,

Here's the code for what I want to do. I decided to save as to a new
workbook rather than keep adding sheets to the existing workbook. In my head
it seems so simple...just increment all those company references by one and
VIOLA!! But I am clueless. I am also posting this to the other person who
responded. I appreciate any help. _c

Sub FindAndReplace()
'
' FindAndReplace Macro
'

'
ChDir "C:\Documents and Settings\cmg4\Desktop\Copy of CMHTemplate"
Workbooks.Open Filename:= _
"C:\Documents and Settings\cmg4\Desktop\Copy of
CMHTemplate\p2_wealth_act_pred_table_P2.xls"
Workbooks.Open Filename:= _
"C:\Documents and Settings\cmg4\Desktop\Copy of
CMHTemplate\p2_comp_act_pred_table_P2.xls"
Workbooks.Open Filename:= _
"C:\Documents and Settings\cmg4\Desktop\Copy of
CMHTemplate\p2_mix_act_pred_table_P2.xls"
Workbooks.Open Filename:= _
"C:\Documents and Settings\cmg4\Desktop\Copy of
CMHTemplate\p2_other_factors_act_pred_table_P2.xls"
Workbooks.Open Filename:= _
"C:\Documents and Settings\cmg4\Desktop\Copy of
CMHTemplate\p2_pay_perf_act_pred_table_P2.xls"
Workbooks.Open Filename:= _
"C:\Documents and Settings\cmg4\Desktop\Copy of
CMHTemplate\p2_relative_pay_table_P2.xls"
Windows("christina_helped_me_do_this.xlsx").Activate
Cells.Replace What:="p1", Replacement:="p2", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\cmg4\Desktop\Copy of
CMHTemplate\P2_Summary.xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

 

[Christina]

Hi Jim,

I recorded this maco this morning for exactly what I want to...like I said
to Bob...just get it to increment my company names by one, right?!?! HA! I
have no idea! While I started with the file "Christina helped me do this" I
can actually start with any of the "summary" files open after I create them,
right? Whatever...like I said, I just don't know what to do next. Thanks
for any help!! Here's the code:

Sub FindAndReplace()
'
' FindAndReplace Macro
'

'
ChDir "C:\Documents and Settings\cmg4\Desktop\Copy of CMHTemplate"
Workbooks.Open Filename:= _
"C:\Documents and Settings\cmg4\Desktop\Copy of
CMHTemplate\p2_wealth_act_pred_table_P2.xls"
Workbooks.Open Filename:= _
"C:\Documents and Settings\cmg4\Desktop\Copy of
CMHTemplate\p2_comp_act_pred_table_P2.xls"
Workbooks.Open Filename:= _
"C:\Documents and Settings\cmg4\Desktop\Copy of
CMHTemplate\p2_mix_act_pred_table_P2.xls"
Workbooks.Open Filename:= _
"C:\Documents and Settings\cmg4\Desktop\Copy of
CMHTemplate\p2_other_factors_act_pred_table_P2.xls"
Workbooks.Open Filename:= _
"C:\Documents and Settings\cmg4\Desktop\Copy of
CMHTemplate\p2_pay_perf_act_pred_table_P2.xls"
Workbooks.Open Filename:= _
"C:\Documents and Settings\cmg4\Desktop\Copy of
CMHTemplate\p2_relative_pay_table_P2.xls"
Windows("christina_helped_me_do_this.xlsx").Activate
Cells.Replace What:="p1", Replacement:="p2", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\cmg4\Desktop\Copy of
CMHTemplate\P2_Summary.xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

 

[Bob Phillips]

Christina,

That doesn't seem to do anything other than open a lot of files.

If you process all files in tha directory you can use a simple loop, but
where is the code that copies them to another workbook or whatever. What is
"christina_helped_me_do_this.xlsx") and what is P2_Summary.xls?

 

[Christina]

Sorry, here's the clarification:

The "Christina helped me with this" file is the "template". That is, I
started with this file open. I then opened the next company's files (that's
the P2 files you see). I then did a search and replace finding all
occurrences of P1 in the template file, replacing them with P2. This in
effect replaced all company P1 data with company P2 data. After I replaced
the P1 data with the P2 data, I did a save as so that my original "template"
remains, and I have a new file with the P2 data in it. I would need to
repeat this process, either opening the original template and replacing P1
with P3 then saving then replacing with P4, saving, etc. or opening the
summary sheet for P2 and replacing P2 data with P3 data. Does this make
sense?

I just don't know how to tell it to open the right company files and I don't
know how to tell it to do the right search and replace. So, if I'm on
company P103, I don't know how to tell it open P103 files and then I don't
know how to tell it to replace P1 with P103. And the files are all going to
be in the same directory, and if you want to loop through and hit all the
companies in the directory, that would be great too.

 

[ryguy7272]

Maybe you should look at this:
http://www.rondebruin.nl/copy4.htm

Replace the red code block with your code; replace it with what you
(specifically) want to do.

Just so you know, this is a batch processing macro. The program takes a set
of data files as input, process the data, and produces a set of output data
files.

HTH,
Ryan---