Re: How to set logic for a macro tool that will import , operate and save the reports.

[VKR]

Hello All Experts,

I am doing a job where I have to prepare around 100 reports using five data files.
I want to prepare a Macro enabled excel tool that will prepare all reports.
What should be logic so that my team will use this macro file with minimum steps to do?
I have a simple logic given below but I want experts’ advice as I am new to VBA.

My Logic Steps-
1) Import all five data files in Excel macro tool
2) Perform operations on them, (Operations are kind to filter column and save them)
3) Tool will create new folders
4) Save prepared reports to allotted folders
5) Exit

Experts, I need your opinion on this or any new thought. Please help on this.
Note: All five files have around 50 MB data.

Please let me know if require more info from my side.

Thank you,
VKR

[AndyLitch]

Hi VKR
A few questions for you ...
1. What type of files are the data files ? .txt, .csv etc
2. Are the separate reports always based on the same criteria or will the user need to set them?
3. Are the data files 50Mb EACH or is that the total size?

[VKR]

Hey Andy,

Thanks for reply.

Ans:
1) all are excel files, .xlsx is file type.
2) All reports have fixed criteria. Nothing for user to enter. I also want to use fixed save location for creating folders.
3) All combine data files are 50 MB as A 20 MB , B 10 MB etc. Not individually.

[AndyLitch]

Hey VKR

The basic flow then would be to
1. Open each file in turn
2. Compile all the data into a single database
3. Extract the relevant data for each of the 100 reports - create a new file from it and save it to disk.

Given the file sizes you mention Excel 2003 may have a problem storing all the data on a single worksheet (or at least in a single range of columns) and this would significantly alter the programming in step 2.

Can you check the number of rows in each of your data input files - We need to find the Max possible we may encounter.

[VKR]

Few Queries
1. Open each file in turn.
Query - Yes It should be, but I want user will browse file (one by one) and data will collected to database. Is it possible?

2. Compile all the data into a single database
Query - Should I create separate excel file as database instead of Macro file?

3. Extract the relevant data for each of the 100 reports - create a new file from it and save it to disk.
Query - This is excellent thought. I have note it down.

I am using Excel 2010 so I think size is not a big issue.
Numbers of rows are not fixed. I generally use last row of data to select all as data has no missing entry.