Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How Do I Show SharePoint List Data in Excel 2007?

Posted on 2011-03-18
16
Medium Priority
?
468 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 3
  • 3
  • +1
16 Comments
 
LVL 2

Expert Comment

by:typerracer
ID: 35168254
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
ID: 35168340
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
ID: 35168380
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 2

Author Comment

by:cefranklin
ID: 35168667
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
ID: 35168672
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
ID: 35168767
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
ID: 35168778
Yeah, I just want a report about tickets lol, that's it.
0
 
LVL 14

Expert Comment

by:GeorgeGergues
ID: 35168806
why can't you just create a view ?

you can also use the Content Query Web part .
0
 
LVL 2

Author Comment

by:cefranklin
ID: 35168961
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
ID: 35171268
>>  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
ID: 35175702
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
ID: 35180420
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:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 1500 total points
ID: 35183900
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
ID: 35191324
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
ID: 35193309
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
ID: 35193355
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When using a search centre, I'm going to show you how to configure Sharepoint's search to only return results from the current site collection. Very useful when using Office 365 with multiple site collections.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

722 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