Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How Do I Show SharePoint List Data in Excel 2007?

Posted on 2011-03-18
16
Medium Priority
?
470 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
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Technology Partners: 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

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

971 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