Solved

How Do I Show SharePoint List Data in Excel 2007?

Posted on 2011-03-18
16
465 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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 500 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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

615 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