ATTENTION: You are viewing a page formatted for mobile devices; to view the full web page, click HERE.

Main Area and Open Discussion > Living Room

How to automatically generate reports based on xls files?

<< < (2/3) > >>


this should be relatively easy in VBA, but I guess it depends on how you want to do it

Do you want to do this from somewhere in office, ie word or excel, and if not then where from (and maybe why)

Do you want to open the source file(s) or just poll them remotely

do you want to do this on a schedule, or ad-hoc


I want to learn.
-kalos (February 18, 2019, 02:30 PM)
--- End quote ---
I was aiming for 'learning by example', but ok:

You should probably use the mail merge feature of Word, it involves getting the data from Excel sheet(s). There are plenty manuals/guides available on the internet, you can use google to find one.
You might want to add some calculated fields in your Excel sheet that hold the counts/calculations you requested, and you already seem to know the formulas involved.

So far I plan to write a macro in excel that will create a new sheet and populate some cells with the formulas that calculate the numbers I want.
Then, I suppose I can use Mail Merge to grab the numbers from these cells and populate the report.

Would that be the best approach?

Or is it possible to do easily everything with Powershell or Word or Excel only?

I cannot find how to do this:

Range("A1").Formula = "This is some text ",TEXT((COUNTIFS(UDE!$AC:$AC,"Low",UDE!$G:$G,"High")+COUNTIFS(UDE!$AC:$AC,"MLD",UDE!$G:$G,"High"))/COUNTIF(UDE!$G:$G,"High"),"00%"))"
Range("A1").Formula = "=CONCATENATE("This is some text ",TEXT((COUNTIFS(UDE!$AC:$AC,"Low",UDE!$G:$G,"High")+COUNTIFS(UDE!$AC:$AC,"MLD",UDE!$G:$G,"High"))/COUNTIF(UDE!$G:$G,"High"),"00%"))"

Neither works, any idea?


[0] Message Index

[#] Next page

[*] Previous page

Go to full version