topbanner_forum
  *

avatar image

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
  • October 23, 2019, 11:17 PM
  • Proudly celebrating 13 years online.
  • Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.
  • donate

Author Topic: How to automatically generate reports based on xls files?  (Read 1581 times)

kalos

  • Member
  • Joined in 2006
  • **
  • default avatar
  • Posts: 1,729
    • View Profile
    • Donate to Member
How to automatically generate reports based on xls files?
« on: February 18, 2019, 10:17 AM »
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

  • Member
  • Joined in 2006
  • **
  • default avatar
  • Posts: 1,729
    • View Profile
    • Donate to Member
Re: How to automatically generate reports based on xls files?
« Reply #1 on: February 18, 2019, 10:39 AM »
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

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 3,366
    • View Profile
    • Donate to Member
Re: How to automatically generate reports based on xls files?
« Reply #2 on: February 18, 2019, 12:45 PM »
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

  • Member
  • Joined in 2006
  • **
  • default avatar
  • Posts: 1,729
    • View Profile
    • Donate to Member
Re: How to automatically generate reports based on xls files?
« Reply #3 on: February 18, 2019, 02:30 PM »
I don't want you to do the job, I want to learn.

ConstanceJill

  • Supporting Member
  • Joined in 2012
  • **
  • Posts: 180
    • View Profile
    • Donate to Member
Re: How to automatically generate reports based on xls files?
« Reply #4 on: February 18, 2019, 03:59 PM »
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

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 5,234
    • View Profile
    • Donate to Member

Target

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,730
    • View Profile
    • Donate to Member
Re: How to automatically generate reports based on xls files?
« Reply #6 on: February 18, 2019, 09:00 PM »
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

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 3,366
    • View Profile
    • Donate to Member
Re: How to automatically generate reports based on xls files?
« Reply #7 on: February 19, 2019, 03:08 AM »
I want to learn.
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

  • Member
  • Joined in 2006
  • **
  • default avatar
  • Posts: 1,729
    • View Profile
    • Donate to Member
Re: How to automatically generate reports based on xls files?
« Reply #8 on: February 19, 2019, 03:46 AM »
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

  • Member
  • Joined in 2006
  • **
  • default avatar
  • Posts: 1,729
    • View Profile
    • Donate to Member
Re: How to automatically generate reports based on xls files?
« Reply #9 on: February 19, 2019, 08:32 AM »
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?
« Last Edit: February 19, 2019, 09:06 AM by kalos »

Target

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,730
    • View Profile
    • Donate to Member
Re: How to automatically generate reports based on xls files?
« Reply #10 on: February 19, 2019, 05:58 PM »
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
« Last Edit: February 19, 2019, 06:38 PM by Target »