Sharepoint Forum

Ask Question   UnAnswered
Home » Forum » Sharepoint       RSS Feeds

Connecting a list to excel

  Asked By: Jaqueline    Date: Feb 18    Category: Sharepoint    Views: 7908

There must be an easy way to do this, I just don't know what it is.

I have an excel document that I need to load with list data. The users have been
loading a list with data for a while and they want to keep doing it that way,
however, they want to have graphs and pivot tables to manipulate the data.

I would like to create an excel doc in a reports library that will automatically
get the data from the list via a data connection. Is there a way to do this
without doing an export to excel in the list actions? If I can get the data to
pull into excel automatically, then I don't have to though and create the graphs
all the time.

So bottom line, I have an excel file that I need to map the fields in to the
fields in a WSS list in real time.



9 Answers Found

Answer #1    Answered By: Michelle Lewis     Answered On: Feb 18

Sounds like something you should be able to do with Excel Services.

Answer #2    Answered By: Cierra Navarro     Answered On: Feb 18

There is a way to pull data  from a SharePoint list  into a templated excel  file?
I am hoping so, but have not been able to figure out how to do it.

Answer #3    Answered By: Utsav Shah     Answered On: Feb 18

Just noticed you said WSS. If this is truly WSS and not MOSS, you won't
have Excel Services available.

Answer #4    Answered By: Breann Beach     Answered On: Feb 18

Sorry, I mislead you, it is MOSS and we do have excel  services. I have been
working on trying to get the data  to auto populate the excel template and create
the graphs  that I need, however, it seems that I will have to export  the list  as
excel every time, and then manually create  the graphs every time. Is there no
way to sync up an excel doc  to pull the data from the list and have it create
the graphs every time? There must be a way to do this via a data connection  or

Answer #5    Answered By: Lyndsay Montoya     Answered On: Feb 18

We do this ALL THE TIME and control the producing of the graphs  with VBA in
Excel 2007. The process will approximately be:
1) user opens Excel in a SharePoint DocLib via the drop-down (not dbl
2) user punches whatever keys you've assigned to your VBA (we often use
CTRL+SHT+R) the R=refresh.
That's it, your VBA code does the rest.

I DO NOT recommend using Excel Services - it SUCKS. You'd think it would be
great from all MS marketing stuff. But the list  of "can't do's" is very long.
Don't get me on this soap box. Have been very disappointed in these

Answer #6    Answered By: Darien Good     Answered On: Feb 18

Could you post some of the VBA code you use (the SharePoint
connect-and-download in particular)? I'd like to get some idea of what's

Answer #7    Answered By: Pauline Kent     Answered On: Feb 18

I exported the list  to excel, put it up in a library, showed my
customer how to create  some graphs  and refresh the data, they loved it. 100%
better than excel  services, you should blog this!

Answer #8    Answered By: Margarita Harrington     Answered On: Feb 18

I just have one more question about this. Every time  I open the excel  doc, I
have go though the process of enabling trust so that the doc  will sync with the
list, is there a way to change it so that trust is always enabled, or is that
another shortcut you create  using VBA?

Answer #9    Answered By: Lara Simon     Answered On: Feb 18

I too have that problem when opening an Excel 2007 WB. I put up with it.

BUT, I am almost certain that under file  > options > trust center there are
setting that could eliminate this problem. (I think that's the right clicking

Didn't find what you were looking for? Find more on Connecting a list to excel Or get search suggestion and latest updates.