Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 473
  • Last Modified:

How Do I Show SharePoint List Data in Excel 2007?

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
cefranklin
Asked:
cefranklin
  • 9
  • 3
  • 3
  • +1
1 Solution
 
typerracerCommented:
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
 
cefranklinAuthor Commented:
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
 
GeorgeGerguesCommented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
cefranklinAuthor Commented:
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
 
cefranklinAuthor Commented:
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
 
GeorgeGerguesCommented:
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
 
cefranklinAuthor Commented:
Yeah, I just want a report about tickets lol, that's it.
0
 
GeorgeGerguesCommented:
why can't you just create a view ?

you can also use the Content Query Web part .
0
 
cefranklinAuthor Commented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
>>  I was hoping to get some cool pie charts

Think again. Save the Pies for desert. Pie charts are rarely a good choice.
0
 
cefranklinAuthor Commented:
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
 
cefranklinAuthor Commented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
cefranklinAuthor Commented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
cefranklinAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 9
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now