How to automatically generate reports based on xls files?

[kalos]

Hello!

I have an MS Word document that I want some fields to be pulled from an xls file stored in a server.

For example:

1) a field would be the total number of lines of the xls apart from the header
2) a field would be the number of lines that the field under a specific header is the same as one or two values
3) a field would be the percentage of the above number of lines to the total number of lines

Do you know how to implement these? Is there an easy way?
I can do it in other tools if more easy, but they have to be part of MS OS, like Powershell, MS Office, etc.

Thanks!

[kalos]

Ideally, I would like to make use of Excel Functions, e.g. use the COUNTIF, INDEX/MATCH etc to find the numbers I need.

[Ath]

Can you attach a real sample of the xls file here, so we can determine the structure, and you describe what exact elements you need the report to operate on?

[kalos]

I don't want you to do the job, I want to learn.

[ConstanceJill]

Hi there .o/

I suppose you could convert the .xls file to a .csv and then make use of a tool like textql, though it's not part of Windows.

[4wd]

http://lmgtfy.com/?q...+into+word+using+vba

[Target]

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

 

[Ath]

I want to learn.

-kalos (February 18, 2019, 02:30 PM)

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.

[kalos]

Thanks,
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?

[kalos]

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?

[Target]

without knowing the format of your spreadsheet it's difficult to provide accurate answers

that said, don't make things harder for yourself than they need to be, ie let excel do the calculations for you (thats what its for after all) by setting up a couple of fields in the source workbook

the counta formula counts the number of fields (cells) in a range that are not blank, eg =counta(A:A).  add '-1' to account for header rows

The countif/countifs function counts the number of occurrences of defined values within a range, eg =countifs(UDE!$AC:$AC,"Low",UDE!$AC:$AC,"MLD") + countif(UDE!$G:$G,"High")

percentages are calculated by dividing the results of your 2 counta formula's, eg =counta(B:B)/counta(A:A).  Note that the proper way to do this would be to substitute the relevant cell references for the counta functions 

can't give you any advice regarding the extraction of the results to another document without knowing more about where and how the source files are stored.  Mail merge may work, but I can't test it against your scenario