Solved

How do I publish an excel workbook to excel calculation services in sharepoint

Posted on 2008-09-30
24
784 Views
Last Modified: 2011-10-19
I have an excel workbook that is connected to a sql database. I've been trying to publish it to a trusted data connection library, but I can't seem to be able to navigate to it when I use publish>excel services in excel. This is my first attempt so I may be doing something wrong. So far I have enable excel calculation services at the sharepoint server and added the trusted data connection library in central administration also. Is there another step?
0
Comment
Question by:kenfitzgerald
  • 14
  • 10
24 Comments
 
LVL 9

Expert Comment

by:cmv131
ID: 22608852
I have never had much luck "navigating" to places when trying to publish anything to SP.

Make the filename http://server/sitename/trustedlocation/excelfile.xlsx and it should work.

If not, what errors are you receiving?

0
 

Author Comment

by:kenfitzgerald
ID: 22609005
It keeps saying the path does not exist. I went to the list settings to see the url and it read http://jason/Trusted Data Connection Library/Forms/AllItems.aspx so I tried typing http://jason/Trusted Data Connection Library/Forms/AllItems.aspx/leadsheet.xlsx Am I typing it wrong or did I make a mistake when I added the trusted connection?
0
 

Author Comment

by:kenfitzgerald
ID: 22609093
it looks like the last post cut out part of my url http://jason/Trusted Data Connection Library/Forms/AllItems.aspx/leadsheet.xlsx
0
 

Author Comment

by:kenfitzgerald
ID: 22609099
ok it has it it just stopped lookin like a url at the spaces
0
 

Author Comment

by:kenfitzgerald
ID: 22609309
I tried uploading the file from the website and it worked. when I connected an excel web access web part to it it returns with Excel Services cannot load the workbook that you requested
0
 
LVL 9

Expert Comment

by:cmv131
ID: 22613964
The URL you are loading it to should be

http://jason/Trusted Data Connection Library/leadsheet.xlsx

you don't need or want Forms/AllItems.aspx in the URL

0
 

Author Comment

by:kenfitzgerald
ID: 22614061
I tried using that and I still get the path does not exist. the name of my sharepoint server is jason. I tried deleting the trusted data connection library and creating a document library called excel services library. I added the url http://jason/excel services library to the trusted file locations in shared services in central administration. I also added http://jason and http://jason:37704 and had all trust the children. I tried the publish to excel services with the url http://jason/excel services library/leadsheet.xlsx and I still got the path does not exist when I try to save. What am I missing?
0
 
LVL 9

Expert Comment

by:cmv131
ID: 22614210
Try putting it in quotes "http://jason/excel services library/leadsheet.xlsx" or using %20 instead of spaces in the URL
0
 

Author Comment

by:kenfitzgerald
ID: 22615476
I tried http://jason/excel%20services%20library/teleleadsheet2 and "http://jason/excel%20services%20library/teleleadsheet2" and "http://jason/excel services library/teleleadsheet2" they get the same message path not found. Could I be typing something wrong in the trusted file location. would that be the message I would get if that library wasn't trusted? Is the trusted file location change take effect right away or do I need to resart anything like IIS?
0
 
LVL 9

Expert Comment

by:cmv131
ID: 22616488
Trusted file location should take effect right away.  Error message would be something like it is not a trusted location when you tried to publish.  It is like it isn't finding the website at all.

Try this...  Type in http://jason as the file name.  Hit tab to get out of the field.  It should refresh and give you a "Site Content" window that you can pick the library where you want to save the file in
0
 

Author Comment

by:kenfitzgerald
ID: 22617264
I tried that and nothing happened when I tabbed out. Here is a screenshot. Is there something in excel I need to do? Here is a screenshot of what it looks like after I click publish/excel services and then type http://jason and tab out
screenshot.doc
0
 
LVL 9

Expert Comment

by:cmv131
ID: 22617975
Mine looks a little different.  I went http://sharepoint and tabbed out and you can see what I ended up with

Everything you are doing seems fine and you shouldh't have to do anything else.  I assume you are on Office 2007, as none of the other versions have an excel services publishing feature.  I would make sure that the SharePoint components of your Office are installed (Add / Remove Programs / Office 2007 Pro / Modify.)
screenshot.doc
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:kenfitzgerald
ID: 22618797
I have office professional plus and have installed all the options, but I did not see an option for sharepoint components. Where would I find that? I did see something about web and html and added that. Is that it?
0
 

Author Comment

by:kenfitzgerald
ID: 22619206
OK, I found a copy of office 2007 enterprise and loaded it on my local pc. I was running remote apps with office professional plus. The screens matched yours and I got my library when I tabbed out. I found where the library was and started the upload. during that process I got the attached error message. It looks like we're getting close, but what does this mean. Do I need to add it as a trusted data connection library, and if so is it possible to add the existing document library or do I need to create a data connection library and make it a trusted data connection library and a trusted file location. The spreadsheet I'm trying to add to excel services is connected to a SQL database.
screenshot2.doc
0
 
LVL 9

Expert Comment

by:cmv131
ID: 22619817
Take a look here.  It looks like what you are trying to do could be unsupported with Excel Services

http://office.microsoft.com/en-us/sharepointserver/HA101054571033.aspx
0
 

Author Comment

by:kenfitzgerald
ID: 22624055
It appears it may be. So let me ask one more question. I'm trying to automate some lists for our telemarketing. I am able to connect excel to the same database our people are generating lists from. I want to get the spreadsheet into sharepoint and connect a view to it, and filter the view to only show certain people and columns. Would it be easier to upload the spreadsheet to a document library instead of publishing to excel services and then what web part should I use to create the view on another page? Thank you for all your help I feel like I've come a long way already.
0
 
LVL 9

Expert Comment

by:cmv131
ID: 22624478
Maybe you want to instead link the spreadsheet to a SharePoint list?  You could then create a custom view on the list with the data you want, and sync it at any time via excel

This fuctionality was kind of removed from Excel 2007, but there is an add-in for it that allows it.

See this -- http://msdn.microsoft.com/en-us/library/bb462636(office.11).aspx
0
 

Author Comment

by:kenfitzgerald
ID: 22624589
OK, I'll try that, is there a webpart I could link to the list and create a custom view for end users.
0
 
LVL 9

Expert Comment

by:cmv131
ID: 22624840
Once you create the list and views within the list, you can add it as its own web part.  Each list and library within a SP site can be added as a web part, and you can select which view is displayed.
0
 

Author Comment

by:kenfitzgerald
ID: 22624863
When I went to publish to a list I got a runtime error. Here is the screenshot, but it did create the list, there is column headers with the correct names, but no data under them.
screenshot.doc
0
 

Author Comment

by:kenfitzgerald
ID: 22625017
Would it be easier to connect a list straight to a sql database. What I want to end up with is a view in sharepoint that will automatically update itself based on a filter while getting it's data from a sql database. The spreadsheet becomes very big after I connect it to my database. when I try to save it as a 97 - 03 it tells me not all of the cells will be visible because 03 doesn't support as many as 07.
0
 
LVL 9

Accepted Solution

by:
cmv131 earned 500 total points
ID: 22625105
That could be an issue with a part of your spreadsheet.  See here - http://social.technet.microsoft.com/Forums/en-US/sharepointgeneral/thread/7011d8f5-c6bd-47bd-bd11-7cfeb3238e63/
and here - http://doolbox.blogspot.com/2008/07/method-publish-of-object-listobject.html

Otherwise there isn't mich reference to that error.  It very well could be easier to use the DB directly.  You could use the BDC to accomplish it I think (though that is out of my realm of expertise)
0
 

Author Comment

by:kenfitzgerald
ID: 22625231
what is a bdc I've done the data view through sharepoint designer for things before. Is that the same thing?
0
 
LVL 9

Expert Comment

by:cmv131
ID: 22625309
As I said, I am not real familiar with it, but a starting point would be here

http://office.microsoft.com/en-us/sharepointserver/CH100948691033.aspx

It is the Business Data Catalog, and it is used to integrate your line of business applications into SharePoint
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now