A big job.

[RET79]

Hi.

I am currently trying to do ambtitious things with some data I get from the IT department at work. I will try and explain the steps involved in what I am trying to do:

1. We get the data in text files. I have created this macro that will allow me to open the data in excel in the format I want, so no problems here.

2. The data consists of around 15 columns. I need to add in a few extra columns with formulas to create new data columns based on the original data. so I end up with say 20 columns overall - 15 original and 5 manufactured.

3. The next step involves summarising all columns of the data to get subtotals etc. So, there is a couple of pivot tables on other sheets doing this.

4. This stage involves reading off the data from the pivot tables onto the sheets where we will be presenting the data. We are only interested in extracting the summarised data from the pivot tables. This makes use of the GETPIVOTDATA function. The data should now be presented in the forms as required.

OK, so what am I asking? Well, ideally, I want all of this combined into a SINGLE macro. Crazy? Oh yes I am.

I wonder if anyone has had similar big jobs to do. For next year's data I hope to have a system set up where by I can just get the text file from IT dept, then click a macro and ALL of this is done in one go.

Ambitious really isn't it, but I am convinced it can be done. But for it to be effective it will take a lot of clever, conscise programming and setting up I think.
Any advice or comments appreciated.

RET79

[RET79]

So, it's effectively:

textfile -> into excel -> add calculated columns -> put into pivot tables to summarise -> getpivotdata into presentation s'sheet.


This is a BIG job, but a macro could do it in one I suspect. Any advice appreciated.

RET79

[Ricky Morris]

It can be done. You might want to continue by recording your actions with the macro recorder, cleaning up and modifying the code, then come back here and ask specific questions as they come up along the way.

[RET79]

Hi again.

Yes, this big job is getting near the finishing line.

I have a macro now which will, subject to inputs (which the user will enter on 2 special sheets), take the data from the text file, open it in the correct excel format, add calculated columns then throw the whole thing into pivot tables. Let's call this stage B.

Now, I have discovered that there are 4 text files which need to go through this process, each with a different set of inputs, each ending up with its own pivot table. Stage C will involve about 8 different spreadsheets (which can't be grouped together as one for various reasons) reading off the data from these 4 created pivot tables as required.

I want this whole job compressed to the minimum in terms of how many files required and how many steps required as I want to convince my boss that this route will be easier and less error prone than our current method (which is the laborious printing out of these huge text files and doing data entry with the final spreadsheets - how crap).

I am thinking that maybe I need a special folder, containing the 4 text files, then set up instructions to a macro to create pivot tables based on these 4 files based on 4 different set of inputs. I am thinking that this may required 4 different stageB files.

The main thing I am worried about is that the macro may be doing too many steps at once if I get it to do everything. The trouble at work is that some people think they know the lot about excel and will therefore find it difficult not to tamper with things like cells, renaming worksheets, renaming files etc. that will completely stop the macro from working. Would it perhaps be better to have a macro at each stage instead of an overall one? If anyone has any experience of doing this sort of thing any sort of advice would be appreciated.

Does anyone have any comments about this whole thing I am trying to do? If so it would be welcomed. I will receive a frosty reception if I claim that I can get a job done with a click of a button which used to take months of data entry, and I don't want my case to be a failure becuase of the fact that the macro was not prepared for tampering of the system in place by users.

Thanks.

RET79

[RET79]

How easy is it to set up a wizard thing on excel? I want to make this whole thing work as easy as possible for the user and give them as little freedom as they should have to mess about with things.
For instance, a wizard might guide them through every step of the way, asking them which text file they wish to use, allowing them to fill in the required inputs etc, before calculating anything. This would be more effective I feel than me writing out instructions on word as some people think they know the lot and do things without reading instructions, this could seriously mess up the whole process.

Thanks.

[ This Message was edited by: RET79 on 2002-04-20 07:04 ]

[RET79]

Or has anyone got any cool macros available that would ensure that only the inputted cells would be available to be tampered with by an user? (However, these inputs ranges have been defined as dynamic ranges by the macro more often than not)

I dont know much about addins either, could this be a way?

Essentially, I just want user to start a wizard type thing, select their text file, put their inputs in, then the wizard would do step B and churn out a pivot table somewhere which could be read by the spreadsheets automatically (the spreadsheets are already set up vastly with GETPIVOTDATA functions).

I would rather the input stage was done within a wizard thing if possible, how one sets this up I have no idea, but letting an user not familiar with how the macro works seems to be asking for trouble.

Gosh, I am starting to repeat myself, I do apologise.

RET79