Community
Showing results for 
Search instead for 
Do you mean 
Reply

Excel reports on ACT lookups.

Accepted Solution Solved
Copper Elite Contributor
Posts: 227
Country: New_Zealand
Accepted Solution

Excel reports on ACT lookups.

I have to present some management reports on Excel 2010 spreadsheets.

I would typically need to show Company, Contact, Activity type, Date cleared, Result and Details in rows.

I can reasonably easily do this as an Act report from a look-up, but the result is a pdf, and they want an Excel sheet to merge with others.

Any tricks or 3rd party apps? (Act 2012, Office 2010, Win 7).

(I don't have/can't afford Adobe pdf writer to rip the pdf report apart with, even if it could that, which I am not sure it can).

 

Thanks, Graham.


Accepted Solutions
Solution
Accepted by topic author GrahamS
‎09-25-2015 03:20 AM
Bronze Super Contributor
Posts: 1,679
Country: Australia

Re: Excel reports on ACT lookups.

ah, what you want is this KB: http://kb.sagesoftwareonline.com/app/answers/detail/a_id/14690/

Titled: How to Add Microsoft Word, Microsoft Excel (and more) as an output option for Reports.

 

Don't know why they aren't there by default, but anyhow, this will let you run a report and spit it out as .xls.

 

Ben.

View solution in original post


All Replies
Solution
Accepted by topic author GrahamS
‎09-25-2015 03:20 AM
Bronze Super Contributor
Posts: 1,679
Country: Australia

Re: Excel reports on ACT lookups.

ah, what you want is this KB: http://kb.sagesoftwareonline.com/app/answers/detail/a_id/14690/

Titled: How to Add Microsoft Word, Microsoft Excel (and more) as an output option for Reports.

 

Don't know why they aren't there by default, but anyhow, this will let you run a report and spit it out as .xls.

 

Ben.

Copper Elite Contributor
Posts: 227
Country: New_Zealand

Re: Excel reports on ACT lookups.

Thanks for the quick reply Ben, I'll check it out.

I'm currently floundering around in OLE DB Provider 2.0, not understanding much of it, hoping it's the wrong approach!!

Copper Elite Contributor
Posts: 219
Country: Canada

Re: Excel reports on ACT lookups.

Can you get what you want by simply customizing your columns in List view and exporting that to Excel?

Mark Rogers
Act! v16 HF5, Office 365 SBP
Copper Elite Contributor
Posts: 227
Country: New_Zealand

Re: Excel reports on ACT lookups.

Thanks Mark. It's not a contact list that's needed, its latest activity and history reports,by contact. Easy enough to produce the report I need in Act, just want to save it in Excel format. I'm working on Ben's suggestion, will report on how it goes. All I need is a report in columns: Contact/Company/Date (filtered)/Activity (just calls and meetings)/Regarding/ Result/Details.

 

Cheers,

Graham.

Copper Elite Contributor
Posts: 227
Country: New_Zealand

Re: Excel reports on ACT lookups.

Thanks Ben. Ran the fix quite painlessly, tried it out, and lo - a report on a spreadsheet!!

So far, so good, now I just have to create the layout for the custom report that I want to export.

Thanks again.

Graham Sharp

Copper Elite Contributor
Posts: 227
Country: New_Zealand

Re: Excel reports on ACT lookups.

Hi Ben,

I have come up with what I think is a very neat quick way of creating the Excel spreadsheets I want with any Act data arranged in columns.

All I wanted on my spreadsheet was a list showing

Company/ Contact/ History Date/ Result/ Regarding and Details

for the last week.

1.  I installed the patch in KB: http://kb.sagesoftwareonline.com/app/answers/detai​l/a_id/14690/

Titled: How to Add Microsoft Word, Microsoft Excel (and more) as an output option for Reports.

 

2. Create a new report template with the fields you want arranged in a row across the Detail section

- Do not include labels.

- Ensure that each field is touching its adjacent fields.

- Align the tops of the fields with the top edge of the Detail section.

- Slide the bottom line of the Detail section up to the bottoms of the fields.

 

3. Leave the Report, Page and Section headers blank.

- Close the Page and Section headers up so they show no space.

 

4. Use the Group By to set the sequence.  Basically turn everything else of or collapse if blank.

 

5. Run the report for your look-up, on the General tab, Send the report to MS Excel.

 

6. On my report I had trouble with the History Range filter, so I filtered the look-up on the Last Reach fields.

 

7. You should end up with a spreadsheet with columns of data with no blank columns or rows, no merged cells etc. It can then be pasted into a spreadsheet with the headings and formatting you want. (I do this report weekly, so I have a blank master which I copy then paste the new data into).

 

Now I have set it up I can do my activity report for my sales meeting in a couple of minutes, and it can be added to other reports from sales people who aren't using a Act.

Bronze Super Contributor
Posts: 1,679
Country: Australia

Re: Excel reports on ACT lookups.

Glad to hear you've put it to good use. It's always good to know that we've changed the world for someone Smiley Happy

New Member
Posts: 5
Country: United_Kingdom

Re: Excel reports on ACT lookups.

Hi, I have just done something similar but I cannot get the details field to show on my excel report (details within history record type).  It works when I change the rich text to NO but then it pulls through a lot of other information that I don't really want.  Does anybody have any ideas what I need to do?

Copper Elite Contributor
Posts: 227
Country: New_Zealand

Re: Excel reports on ACT lookups.

Hi New Member,

I'll look into this for you, because printing the Details from the History tab was the whole point for me, so I will check exactly what I did again.

 

Cheers, Graham.