Auto Updating of 100+ Files from Data File on Monthly Basis

[ats1312]

Hi,

I have a mini project that I would love some assistance to ensure I am being as efficent as possible.

Essentially I have 100+ files (indiviadual projects) that require a manual update for revenue and cost figures on a monthly basis from a load file from our source system. Presently we are manually updating each sheet.

Goal - To open each project sheet, update for correct month, then update several cells in each project for current month revenue and cost then close and loop onto the next one.

Plan
My plan was to record a macro in each project to open the Data sheet and pull the info in based on the month then somehow have a macro to on a control sheet to perform the loop???

Any advice would be greatly appreciated.

[OllieB]

You could also create a single "console" workbook that controls the entire process by clicking on a button for example, i.e. opening the individual project workbooks and making all necessary changes per workbook. That way you only have one "automation solution" to content with as opposed to creating macros in each project workbook.

[ats1312]

Sounds like a good idea but i have no idea how to approach that....could you ellaborate using an example please?

[OllieB]

There are too many questions to provide an example. What does the format of the load file look like, where would it be stored, where are the project files stored, how are they identified, what information needs to be updated, etc etc

[ats1312]

[Ok, let me explain

1 - The csv file which contains all the raw data has Month, Project number, Cost, Revenue as headers. this is the pull from our source sytem that we use to update the individual files (see attached - load file may not be the correct term) ATTACH]269386[/ATTACH]
2 - I am planning on storing all files in a fixed folder on network and when updated, save them in a seperate monthly folder elsewhere, so they should be all in the same location permanently.
3 - Each Project is defined a 0001-1111 for example, all projects are defined in this manner
4 - Each project has a data tab and has months in Columns from start to finsh of project, which could be 5 years. I want to update the months depending on the month as per the Header which is updated from the source file. Attached is the Revenue piece, the costs are underneath and the suma are linked into a Summary tab where we have all sorts of calcs/ comments etc Project File.jpg
Hope that helps

[OllieB]

Hi ats,

what you are asking for is possible but (quite) a bit of work. I would at least require copies of he CSV file and at least one project workbook. They may contain dummy data as long as the format is valid. I would furthermore require a further clarification about point 2. The location cannot be fixed if the files are moved elsewhere once they are updated. Lastly, I would require a full description of what needs to be updated in the project workbooks.

Without this information I would not be able to assist you

[ats1312]

Excellent, thank you. Im more than happy to do this myself with some assistance just to let you know.

Re: Point 2 - I was thinking for ease of code etc to keep them in one location for updating purposes. When updated then saving into the Shared folers etc, they would not be updated here.

Data File.xlsx - I dowload a report from our system and paste the results on the Data tab. I then Pivot these as we have several Departments for Reveneue and Costs but the Project book only requires us to enter Core & Other so I gropu the others into Other....I was thinking of using this table as the lookup.
I manipulated the Date on the File to read "Aug13" in cell A1 on Summary tab to input into Project Sheet.

0004-0009.xlxs - This is one of the 100+ Project Files. For this month, I want to update cell E2 from the Date in the Data File (Summary Tab A1)
Based on this month, lookup the value for this project and paste into "Aug13" cell which are B39 & C39 for Revenue & B79 & C79 for cost. This need to be dynmaic based on the month. I need the ability to run Jan13 again for example if we have any changes without distubing the other cells. So whatever date is in E2, they are the only cellls to be updated.

Thanks again


0004-0009.xlsx
Data File.xlsx

[OllieB]

I will have a look at this tomorrow

[ats1312]

Thank you very much!

[OllieB]

@ats,

Ok, I have created a basic solution for your request. See the attached workbook. There is a routine called "updateMonthlyProjectFigures" which will do what you ask. You will first need to specify the full name of the path where your project workbooks reside (including the trailing \). It is not very fancy but I have added a fair bit of documentation so you will be able to expand on the solution if you want. Note that the solution I created uses the raw data on the Data worksheet and does not rely on the pivot table you have created.

[ats1312]

Hi OllieB,

Thank you so much for all the effort you put in here. Unfortunately I tried to run the routine but it keeps looping an error meassage (attached)

The only thing I updated was the path...perhaps I am doping this incorrectly???
Const pvt_cstr_ProjectWorkbookPath As String = "K:\Department\planning\2013\Project\PP Update Processing\"

Capture.JPG

[OllieB]

I have tested the version before I posted it with the worksheets you provided. I do not get any error messages. There seems to be an issue with the opening/updating/closing of the project workbooks. Are any of them open on your PC or locked by other users? Can you try with copying just a few workbooks to a local PC folder? I am afraid you will need to do some investigating yourself as I am unable to replicate

[ats1312]

Hmmm, well the folder I created only has 4 test subject projects in there...does the name of those files need to be a certain format...like the 0001-0049.xls I sent? Alos, is the path I inserted in the correct format?

My files are saved as "CustomerName 0001-0049.xls" I was hoping to keep the cust name on the file as the number code is a code, so unclear to some...if this cant be kept then its no problem.

Thanks.

[OllieB]

@ats,

the path specified is correct. The changed workbook name is unexpected! The current solution looks for files named '0001-0049.xlsx' as per the example provided by you.

How would the solution arrive at the desired file name, in other words how will the code know the customer name?

[ats1312]

ok thanks, its fine to leave the names as '0001-0049.xlxs', my mistake! Ill retest and let you know :-)

[ats1312]

Hi Ollie...

WOW It works :-) !!! Maybe as I only had 3 samples projects in folder and the test data file had different numbers it got confused????

One thing though (Sorry!!!), the reason i was using the Pivot is that I have 2 different Types of Revene and Costs that needs to be input seperately. You have rolled up all into one amount. I did mention this here but perhaps it was not clear enough??

Data File.xlsx - I dowload a report from our system and paste the results on the Data tab. I then Pivot these as we have several Departments for Reveneue and Costs but the Project book only requires us to enter Core & Other so I gropu the others into Other....I was thinking of using this table as the lookup.

I really hope you can do this as it is very nessesary. I am not is any great rush for this so whenever you can review I would be very grateful.

[OllieB]

I am not sure what you are trying to say.

The solution I submitted already distinguishes between CORE and OTHER for both revenues and cost. I have not rolled it up into one amount!

I use the department name in column H on the Data worksheet to determine whether the cost or revenue should be added to the core (department name = Core) or other category (any other department).

You can see the code for yourself

[ats1312]

Hey OllieB....Im very grateful for what you done, please dont get upset with me

Any error on the execution is my fault due to my ignorance as i had to diguise some things as they are confidential and I though i could fix....obviously not!

The issue is that the Depts I have as "Core" and "Other" are called different names in reality. The theory is the same but i could not publish the real names. My ignorance! I apologise.

Can you advise me how to change these names in the code???

[OllieB]

Ok, clear

the following bit of code determines which department names get added to the CORE counter and which to the OTHER counter

Please Login or Register  to view this content.

[ats1312]

Graet, so...if i want to change Core to 'Total Medical', do i change every word where Core is mentioned? ie

If UCase(.Cells(pvt_lng_RowNumber, "H").Value) = "Total Medical" Then
.TotalMedicalCost = .TotalMedicalCost + pvt_xls_Data.Cells(pvt_lng_RowNumber, "J").Value

Thanks.

[OllieB]

Nope, the code would need to look like

Please Login or Register  to view this content.

[ats1312]

Hi, Im sorry to say that I cannot get this to work for me .....Revenue and Costs are going into Column B only and not C????

I have attached the files for you look at, I have updated for the correct headers etc so there will be no translation issues next time. Thanks again for your patience!!!! I hope you can spare some time to review....

[OllieB]

I checked the code and found this to be incorrect

Please Login or Register  to view this content.

should be

Please Login or Register  to view this content.

same change as the previous CORE to TOTAL MEDICAL example!!

Cost are always retrieved from column J

Please Login or Register  to view this content.

Revenues are always retrieved from column I

Please Login or Register  to view this content.

and core revenue and cost to column B

Please Login or Register  to view this content.

and finally other revenue and cost to column C

Please Login or Register  to view this content.

so with the exception of the first mistake (upper case text) I cannot see any problems in the code. If you still cannot figure it out, I will have a look tomorrow

[ats1312]

No, still cant get Column C to populate

[OllieB]

I found the problem!

REPLACE

Please Login or Register  to view this content.

WITH

Please Login or Register  to view this content.

MEA CULPA

[ats1312]

WOW!!!!

This is superb, thank you so much!!!!

I presume this will work if I add new months as time goes on??? i tested it for 2012 data and it works perfect as well. Thanks again!

[OllieB]

It should, it has been written in a generic manner so I can't see why it should stop working. If you run into problems later on, please feel free to send me a direct message

[ats1312]

hi Ollie, i have been testing the months and the data flows in very well Im still amazed how this works!

I have a question on the date that is placed in the individual report - the attachment will explain. I tried May 2011 - the routine input the data correctly into May 2011 but the Cell E2 on the individual project says 11/05/2013. I would like this to say 'May11' and keep in thes ame format as the cells in Column A as per the individual project sheet.

Thanks Ollie.

[OllieB]

The date cell is updated with a text string using the below statement

Please Login or Register  to view this content.

When the cell format of E2 in the project workbook is a date, Excel will display the value as a date. To change this you would need to change the format of the cell E2 to text, that way it would display the contents of the variable as a string.

[ats1312]

can you tell me where to add this code? Does it replace anything?
thanks

[OllieB]

There is no code to change.

Open the project workbook(s), select cell E2, click Ctrl-1, and change the format to text, save & close.

Run the code again.

[ats1312]

You are a genius

Thanks again!