Opening a macro on another excel spreedsheet.

The straight answer is YES it can be done. But we need to if possible you post the sample workbook that have the macros to see how it is linked.
gowflow

I uploaded the 2 macros but here are the 2 excel sheets. Test is the first file that I work with and it makes the second file which I also attached. Thank you.
Test.xlsm
101412B.xlsm

ok I need you to post a sample of MonthEnd.xlsm

Also something I don't understand the macro Copy_Patient_Data that is in the second workbook (the one created does not exist in Test !!! did it landed manually ? you copied it ?)
gowlfow

It is only need in the second macro not in Test. Here is the MonthEnd file

It is only need in the second macro not in Test.

What do you mean ??? ma the second file is created by the first one test right ??? or I am completely off ???!!!! ???

then you forgot to attach Monthend file
gowlfow

Yes the second file is created by the first file. It copies the data from Test to template and renames that template to the clients number which gives us the second file. In that second file is the macro (export data) which I need to trigger. I will upload the monthend files as well. Thank you.
MonthEnd.xlsm

Sorry but we are chasing our tails here !!!!

the macro: export data does not exist in the files you posted !!! so please check your data when you have the correct file that contains this macro then post it and then I will look into it.

I believe it is in the file Med-A-Therapy.xlsm that you will need to post.

You cannot simply take a sheet and plug it in a workbook and make up a story and expect to get the result that you desire ! It has to be same as the reality you have. Don't try to makeup files with test etc ... simply take you REAL files and make sure all the sensitive personal confidential info is changed or removed and then post it and we can then give you what you want.

Anything else would simply result in a waste of time to both of us.
gowlfow

Hi, I will see if we can get this right. The first file we use is the test file (I have changed the name to test as this is normal the name of the patient). we click on the export data button and all the data from D10 to AH37 is copied to Med-A-Therapy.xlsm but the file is renamed and saved as the patients account number (the second file this is what it will look like 1212121B Med A OC.xlsm) I will upload the Med-A-Therapy.xlsm file so you can see that file as well. We then open the second file and click on the Export Data button in this sheet (this is the macro that I would like to first Command Button to open ( I think the macro is called Copy_Patient_Data) this then copies the data to MonthEnd.xlsm. Sorry about the run around but I am hoping this can be done. Thank you for you time on this.
Med-A-Therapy.xlsm

Exactly as I thought you were missing to post Med-A-therapy.xlsm as now that you posted it has the Copy_Patient_Data Macro that before I was not seeing how the hell you got there.

Now let me recap:
You are running the test file that will produce a new file per patient and at the end you want to update month end file with the data of every patient and all this automatically without having to open each file and running the macro in it

Is my understanding correct ?

If yes then are you open to changing the logic ? and by logic I mean that we will save the patient file as a non macro file (.xlsx instead of .xlsm and it will not contain any macro but the macro will reside in Test file and will be run from there to update Month end

Is this fine with you ?

gowflow

That sounds good to me if it works. Again thank you for your time on this. It will safe me a lot of time at the end of every month.
Thank you.

if it works ???? are you kidding !!! ??

No problem I have better news for you, you will only have 1 file and only 1 that is test.xlsm that will generate the patient's files and will update the Month End file

Is that fine ?
gowflow

Yes that will work fine. Thank you.

ok 1 more question before I develop all this:

I see in your test file that you have data for 1 patient so you run this each and every time you have a patient or you have multiple patients ?

I need a sample data the way you have it as you mentioned having 160 clients to have to do this every month I need to know how is the data laid out are they all following each others or ... ? I mean in the file test.xlsm this is not clear to me.

Prefer to ask question but get it right from the first time.
gowflow

Yes we run one for each patient. We have 160 of these Test excel file each month and there are all are laid out the same, we just change the patients name and details in the excel sheet. I will up load the test with some data as we have it at the end of the month. The only 3 parts that are important are Patients Number, Date of service and the data from D10 to AH37. We only copy the Numbers not the initials. Hope this helps

sorry forgot to upload file
Test.xlsm

Wait wait wait,

You mean to tell me you have 160 versions of the test file ?????????????????????????????????

So if we change the way test is working then you will need to change the 160 !!! do you realize this ???

I think what I proposed is not workable unless if you are willing to change these 160 to be conform to the new way if not then I can recommend a different approach. Let me know.

gowflow

Yes I will change all 160. I have to make many changes when we set this up first.
Thank you.

What about I give you an other solution as imagine each time you need to change something in your code you need to go thru all this trouble !!!

I can have you all the code sitting in 1 workbook that will then access the files and create them and update month end and no need to go thru the hustle of having code in each and every customer file.

What do you think ?
gowflow

Yes sound great. Thank you.

ok great but for this we need to set things up. I think after well thinking this will take some more item than originally thought. If you are interested in such a solution once we close this question you can ask a new series where we will modify the present structure to work as proposed.

I am reading your post on ID: 40814986 and let me get this correctly please bear with me as want to help you but still things not clear. I will try to give you my understanding and if correct then maybe there is a way and we can do this right now instead of waiting:

here it is the way I see it:

You have a lot of patient's files (you call them clients) and these files are in the name of the patient and they are like the Test.xlsm format and they all reside in this folder: (please correct me if I am wrong)
T:\Occupational Therapy\

I presume you do the following at month end:
You take the first patient file you open it and after having entered the data in it during the month you click on Export button and it will create for this patient a new file under this format (PatientNumber & " Med A OC" & ".xlsm") and put this file in this folder:
T:\Occupational Therapy\Export Files\

You then process the second client file and press export and then the third client file and press export till you finish all client files.

Then you go to this folder
T:\Occupational Therapy\Export Files\
and start opening the new created files and also press Export so it update month End and this is the purpose of your question to avoid this part of opening the second time the new file created and if it was possible to have the macro run remotely in the existing files created.

Did I explain the situation correctly ? Kindly advise if not please put the correction so I tell you exactly how I view it and what I would need from you so I can test it to make sure everything works correctly.

gowflow

That is what we do. Yes you have correct. We just to cut out the second opening of the 160 patients files. Thank you.

ok then here is how I would see it:

You would have a workbook that contain all the macros and say it is called:
TherapyEngine.xlsm

So you would have this file sitting in this folder together with all the patient files.
T:\Occupational Therapy\

at month end you do the following:
Open TherapyEngine.xlsm
and in there you would have 2 buttons
Update Month End
Reset Patient files

Once Update Month End is Activated it will do the following:
1) Go thru all the files (.xlsx type) in the folder T:\Occupational Therapy\
2) For each file it would create the Patient file in the format: PatientNumber & " Med A OC" & ".xlsx" and save it in the folder T:\Occupational Therapy\Export Files\
3) It would also for the file created update the MonthEnd.xlsm file
4) Go to next Patient till end of patient files.

Once Reset Patient files is activated it will do the following:
1) Go thru all the files (.xlsx type) in the folder T:\Occupational Therapy\
2) Reset the data in the Patient file
3) Go to next Patient till end of patient files.


Please advise if this is what you want.
In this case I would need the following to make some tests
I would need you to post 3 or 4 sample of patient files (you can change the name but make sure inside the info as to customer code and data is different so we can make tests)

Once done I will build the whole thing and will revert.

Please note for this version you will need to do some manual changes:
1) You will need to go thru each and every patient file and remove all the code that is there and also the buttons and save the files as .xlsx and not .xlsm
2) Also if you have some produced files patientnumber & " Med A OC" & ".xlsm also remove the code in those and the buttons and save them as .xlsx and not xlsm.

In this version you will simply have 2 macro files:
TherapyEngine.xlsm
MonthEnd.xlsm
and they can both sit in the folder: T:\Occupational Therapy\

Please let me know your comments and advise if your willing to venture in this major change.
gowlfow

This sounds great. I will upload 3 or 4 patients files with data. Thank you.
Allen--Florence-06-30-1915.xlsm
Davis--Muriel-02-25-1927.xlsm
Jones--Raphael-08-14-1936.xlsm
Smith--Neil-05-27-1930-Second-Admit.xlsm

ok fine.

This is more like a project than a question. As it holds lots of changes and info and testing I propose the following is to break this project into 3 questions not for points as to for being able to keep the threads short and explicit and allow a step by step implementation. Please let me know what you think and feel free to express exactly how you feel about the whole issue at the end I am only concerned in giving you the best solution for your problem.

I would envision it as follows:

1) this question would treat the creation of the main workbook macro that would contain a sheet called Export (same sheet that you have in file Med-A-Therapy.xlsm. Also this workbook would contain all of the macros that you had scattered in both the patient file and the Med-A-Therapy.xlsm file they would all be grouped in 1 module called original macros. Also this workbook would contain a new module called NewMacroes in which we would implement all the new macros that will be developed for the need of the entire project. Also this workbook has 1 sheet that is called Main and basically it serves as a dashboard or Trace where you would have all the necessary buttons to drive this project from and also would have settings for your main folder and your export folder (as I do not believe in hardcoding folders in the code but to make them user set) this way you may change them easily for testing and then point them where you want any time. Also this sheet has a trace sheet where it will log all the file info that it will process so you can make sure that everything is performing correctly. At the end of the month you can print this sheet and you have a record of what has been done file by file.

2) Once the backbone is finished which is the previous point you then post a new question that would take care of the MonthEnd process (and similarly you can post in here a zip file containing of all your patients files) That I can modify into .xlsx and remove all code from as I did with the 4 files you already posted. This is very important and we need to get rid of all code inn the patients files as well as the Export patient files which is a tremendous job and very delicate.

3) Once previous questions is finished then we can tackle the Reset Month End option and by this question you would have your whole project completed and done.

BTW I already almost finished the first question.
Please advise your comments order go from there.
gowflow

I not sure I can give you all the patients files as the change every month also not sure for security reasons. I think I will have to find another way around this problem. Thank you.

ok I was trying to alleviate your work its not a problem as long as you do it yourself. Is it fine with you ?
gowflow

Thank you for you help with this.

ok here is what I got to. This is the Engine as I mentioned. You activate macros and you need to choose your 2 directories Main and Export. and if all is ok you can lookup the code I gathered all your code into the Original module and the new code in the newMacroes. At this point there is not much as all this took time to put together.

From the next question you will see results as the next would be to develop the Monthend which is basically the heart of the work.

I have also attached the converted 4 files you had sent into .xlsx so you can see what I mean by removing code and buttons.

If you want I can layout step by step on how to go about converting the xlsm into xlsx. This I would prefer to do in the next question together with the MonthEnd as this is where we will need these files.

Pls advise your comments.
gowflow
TherapyEngine.xlsm
Allen--Florence-06-30-1915.xlsx
Davis--Muriel-02-25-1927.xlsx
Jones--Raphael-08-14-1936.xlsx
Smith--Neil-05-27-1930-Second-Admit.xlsx

Ok this looks good. I will open the next question today. How do you want me to word the question? Thank you for you help with this. I am leaving this open until we start working on second question.
Thank you.

First it is a follow-up on this one so you can title it .. Monthend Part2 or whatever is related to this part with Part2 and you need to put a link to this question Then the body simply put a small para that would explain what you need to achieve ie read all patient's file and get the data for each file in the format of the sheet Export in TherapyEngine and to produce the patient file that is saved in the Export folder and then to update the monthEnd.

For sure you will need to attach TherapyEngine and the 4 patients file in this new question so all data is available.

Don't forget to post a link of the new question in here so I can follow-it up
gowflow

I have added the part 2 of the question. This is the link
https://www.experts-exchange.com/questions/28689576/Monthend-Part2.html
Thank you.

re-edit the question you forgot to put the link !!! Already started working on it and almost half way there.
gowflow

Done. Thank you

Thank you so much for all your help on part 1 of my question.