Solved

How Do I Show SharePoint List Data in Excel 2007?

Posted on 2011-03-18
16
427 Views
Last Modified: 2012-08-13
Well, the answer may be out there but, I cannot find it.  So, here is what I am doing...

I have MOSS 2007 on Windows Server 2003 R2 Enterprise with all the updates.
On my workstation I have Office 2007.

I created a site for the IT department which we use to track tickets, KB articles and the like. This is located at http://it/help

Now, I wanted to utilize the Reports site that comes with MOSS (http://it/Reports) to create a dashboard and various reports.  I figured I'd start with something simple to get my feet wet by, using data from the help site.

For the life of me, I cannot figure out how to get a connection from my tickets list into an excel sheet that will be stored in reports for the dashboard.  More specifically, I am stuck at the first part, trying to get the data connection.

Any references or pointers would be appreciated.  This day of searching has given me a headache.  All I see is how to pull data from the excel file into the list, not the other way around.
0
Comment
Question by:cefranklin
  • 9
  • 3
  • 3
  • +1
16 Comments
 
LVL 2

Expert Comment

by:typerracer
Comment Utility
Assuming you have appropriate permissions, when you are viewing the list directly, Choose Export to Excel from the Actions menu.  This will dynamically build a query against that particular list and send it to your copy of Excel.  If you save the document, that should preserve the data connection for you.
0
 
LVL 2

Author Comment

by:cefranklin
Comment Utility
Tried that, I get this error when I drop it in reports:

The workbook that you selected cannot be loaded because it contains the following features that are not supported by Excel Services:

SharePoint lists

Contact the workbook author.

Learn more about what features are not supported by Excel Services...
0
 
LVL 14

Expert Comment

by:GeorgeGergues
Comment Utility
Open Your Excel and create a file.

Click on the Data Ribbon  ,
Select  - From Web.

that will open a web page browser,
Enter your list view URL , and that should show a table of the items you want to handle.

Select the table  ( that is a dark arrow in a yellow box) of values you want.

Do your Excel magic (I am not good at ).

Best of luck.

0
 
LVL 2

Author Comment

by:cefranklin
Comment Utility
The workbook that you selected cannot be loaded because it contains the following features that are not supported by Excel Services:

Web queries

Contact the workbook author.
0
 
LVL 2

Author Comment

by:cefranklin
Comment Utility
Is this just going to work?  I was hoping to have an excel in the reports library that would just pull data from another list and be able to display it.
0
 
LVL 14

Expert Comment

by:GeorgeGergues
Comment Utility
Oh ,
maybe I didn't understand the request.

I am sorry
to host the Excel sheet inside a library and be able to connect to other content is a bit tricky

you can use trusted file connections , yet you want to view the items on a excel web view .


I don't recommend going for that complex process to get this very simple data.

I do recommend SP Reporting over SQL Reports Integrated mode.

I am afraid I can't help beyond this point.

Best of Luck.
0
 
LVL 2

Author Comment

by:cefranklin
Comment Utility
Yeah, I just want a report about tickets lol, that's it.
0
 
LVL 14

Expert Comment

by:GeorgeGergues
Comment Utility
why can't you just create a view ?

you can also use the Content Query Web part .
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 2

Author Comment

by:cefranklin
Comment Utility
Oh, well, it's also a demonstration for my boss.  I was hoping to get some cool pie charts and KPIs and stuff like that in there.  Also, would be nice to get some kind of report showing who our "problem" users are.

If this pans out, I can connect more items to IT type reports such as our Samsung phone system, phone tickets, etc.
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
>>  I was hoping to get some cool pie charts

Think again. Save the Pies for desert. Pie charts are rarely a good choice.
0
 
LVL 2

Author Comment

by:cefranklin
Comment Utility
LOL, thanks for that but, I say "pie chart" to encompass all charting. Can just be numbers next to names but, I would like the ability.
0
 
LVL 2

Author Comment

by:cefranklin
Comment Utility
So, even if I wanted to make a report, it works.  If I just add some random data and make my pivot tables and charts, excel services works fine.  So, if I want to get some data from a SP list into excel, just as plain data, maybe with no connections, any idea how to do that?
0
 
LVL 50

Accepted Solution

by:
teylyn earned 500 total points
Comment Utility
Open the list and then select Export to Spreadsheet from the Actions. Once the data is in Excel, you can remove the data connection if you want.
0
 
LVL 2

Author Closing Comment

by:cefranklin
Comment Utility
This works but, I was hoping for a solution where the data would be updated as the list changed instead of having to manually update all the time.
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
In your last comment you say "I want to get some data from a SP list into excel, just as plain data, maybe with no connections, any idea how to do that?" That's what I've shown you how to achieve. Now you close the question with a B Grade, saying you want a dynamic solution. If you want that, don't remove the connection in Excel and you can refresh the Excel list at any time.

This question should have been closed with an A, not a B grade.

cheers, teylyn
0
 
LVL 2

Author Comment

by:cefranklin
Comment Utility
Actually, your solution didn't work either but, I dont know how to re-open the question.  There is still some hidden connection info in the sheet somewhere.

It was grade B because I had to settle for a second best solution from my original question, which it seemed no one could answer.  The solution you provided was the one I picked but, it wasn't the solution I wanted.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now