Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not a programmer...am I in over my head??
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not a programmer...am I in over my head??
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? -- __________________________________ HTH Bob "Christina" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not a programmer...am I in over my head??
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... -- HTH... Jim Thomlinson "Christina" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not a programmer...am I in over my head??
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. "Bob Phillips" wrote: 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? -- __________________________________ HTH Bob "Christina" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not a programmer...am I in over my head??
OK, I can do that. I will have to do it tomorrow morning -- THANK YOU!!!
"Jim Thomlinson" wrote: 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... -- HTH... Jim Thomlinson "Christina" wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not a programmer...am I in over my head??
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").Activa te 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" wrote: 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? -- __________________________________ HTH Bob "Christina" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not a programmer...am I in over my head??
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").Activa te 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 "Jim Thomlinson" wrote: 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... -- HTH... Jim Thomlinson "Christina" wrote: 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not a programmer...am I in over my head??
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? -- __________________________________ HTH Bob "Christina" wrote in message ... 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").Activa te 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" wrote: 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? -- __________________________________ HTH Bob "Christina" wrote in message ... 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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not a programmer...am I in over my head??
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. "Bob Phillips" wrote: 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? -- __________________________________ HTH Bob "Christina" wrote in message ... 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").Activa te 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" wrote: 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? -- __________________________________ HTH Bob "Christina" wrote in message ... 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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not a programmer...am I in over my head??
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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Christina" wrote: 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").Activa te 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 "Jim Thomlinson" wrote: 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... -- HTH... Jim Thomlinson "Christina" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ot - Wanted: Excel Programmer Writers in RedmondWanted: Excel Programmer Writers in Redmond | Excel Programming | |||
Please Help, Newbie VB programmer | Excel Programming | |||
Programmer for hire | Excel Programming | |||
VBA programmer feedback | Excel Programming | |||
Non programmer needs calculation help!!!!! | Excel Programming |