• There has been a recent cluster of spammers accessing BARFer accounts and posting spam. To safeguard your account, please consider changing your password. It would be even better to take the additional step of enabling 2 Factor Authentication (2FA) on your BARF account. Read more here.

Export from Sharepoint to Excel

F4iChic

F4iChic

Kiss My Arse
Joined
Jun 21, 2002
Location
Concord
Moto(s)
PONY LIVES w00t
Name
seraF-ina
BARF perks
AMA #: 873747
:mad

I used to be able to do it, now I can't :cry

I want to export my data from Sharepoint to Excel, but I *don't* want the export to be a dynamic link back to sharepoint when I open Excel. I just want a bloody one-time static download

HALP - how do I do this

fanks :toothless
 
Don't know shit about sharepoint, but I have heard that it can be a pain in the ass to export out of it.

How complex is the spreadsheet? It seems like there should be a way to "export" or "save a copy", barring that I think there are cut/paste special options that might allow you to preserve locality of function references (they point locally instead of back to the sharepoint version).
 
Spreadsheet is simple, a collection of "Projects" as it were with assigned to and due dates and whatnot

and there is functionality to export, using the Actions, Export to Spreadsheet options. It is exporting fine. I save it to desktop but every time I open it, it is dynamically linking back to Sharepoint to update the information and that is not what I want.

:(
 
F4iChic said:
Spreadsheet is simple, a collection of "Projects" as it were with assigned to and due dates and whatnot

and there is functionality to export, using the Actions, Export to Spreadsheet options. It is exporting fine. I save it to desktop but every time I open it, it is dynamically linking back to Sharepoint to update the information and that is not what I want.

:(
Click to expand...

in 2010, goto formulas and choose "show formulas" so you see the formula instead of the value for each cell. It might be as simple as tweaking a couple of formulas in a few different places.
 
I was going to ask which version of sharepoint 07 or 10?
 
Once you open it in Excel. Just do a "save as" to save it locally. If you just do a save then it's still linked to the MOSS copy.
 
scotinexcile said:
I was going to ask which version of sharepoint 07 or 10?
Click to expand...

:dunno


openhelix said:
Once you open it in Excel. Just do a "save as" to save it locally. If you just do a save then it's still linked to the MOSS copy.
Click to expand...

Doesn't work. Wehn I first say Export to Spreadsheet it asks Open or Save. If I choose Save the default Save As Type = Microsoft Office Excel Web Query File

If I say Open it opens Excel and says Opening Query - This file contains queries to external data

I say Open, and the data populates into Excel

I say Save As - Book1 - Microsoft Excel Workbook

When I re-open it, it links back to Sharepoint to get data dynamically
:cry
 
F4iChic said:
:dunno




Doesn't work. Wehn I first say Export to Spreadsheet it asks Open or Save. If I choose Save the default Save As Type = Microsoft Office Excel Web Query File

If I say Open it opens Excel and says Opening Query - This file contains queries to external data

I say Open, and the data populates into Excel

I say Save As - Book1 - Microsoft Excel Workbook

When I re-open it, it links back to Sharepoint to get data dynamically
:cry
Click to expand...

Have you tried changing the Save as Type from the default?
 
Ah, looks like someone, somewhere dumped in a query that references something else on SP or a macro that does. Maybe opening it, copying the content to a new worksheet, then reuploading to sharepoint?
 
scotinexcile said:
Have you tried changing the Save as Type from the default?
Click to expand...

um, the only other choice is All Files.............

openhelix said:
Ah, looks like someone, somewhere dumped in a query that references something else on SP or a macro that does. Maybe opening it, copying the content to a new worksheet, then reuploading to sharepoint?
Click to expand...

I tried copying the ouput and pasting to a new workbook but that doesn't work either, still bloody links back to the data source

(ps, I don't want to upload to Sharepoint, just download a static view of the data for a status report at date whatever)

W
 
if you just want to get a completely static copy, just copy each sheet then choose "paste special" instead of paste, and choose the option "values".

It will strip functions, and it should strip any web queries as well. There is no way to strip that web query unless you find the cell they assigned the query to, and remove the query. I've created a bunch of queries and I know its not always obvious which cell contains that web query. might want to sniff around a bit on the web and try to figure out how to locate it. Excel might store it as a macro, in which case it would be pretty easy to remove.
 
I have some vague memory that they stopped supporting the export of lists to spreadsheets and now require exports to be done to access databases.

It's been a long time since I worked with Sharepoint though (like 18 months). We had MOSS (sharepoint 2007) and our corporate standard was still office 2003. There were many "excel view" and "export to excel" bugs to which the official MS support recommendation was "upgrade to office 2007." When we upgraded to office 2007, the excel/lists integration had changed to access, so we had to license the versions of office that included access.

The access list integration worked better than the excel export ever did, but sadly it required access (and I'm not sure why anyone would actually buy access other than for that reason).

This is all fairly hazy memory though. So take it with a grain of salt. :)
 
So, it looks like I have to copy paste special, one time formats, then do it again with values, that seemed to work

goddamned software.

Radvas, that's the combo we have - MOSS and 2003 (well I am on office 2003)

If the managers would just log in and look at the views I created, instead of me having to export the crap to Excel, this wouldn't be an issue, but ohhhh noooooo :shame, they are Executive Directors, they have to be spoon fed :rolleyes

Let's not mention that they are supposedly IT people. Well, Enterprise Architects so I guess that explains it, they talk about pictures and artifacts and rhubarb like that, never actually *build* anything :x

Thanks guys :)
 
I use paste special all the time. In fact I replaced the normal copy, cut and paste buttons with paste value and paste format buttons up in the standard toolbar. I always use shortcuts for cut, copy and past but there isn't one for copy special, that I know of anyway.
 
F4iChic said:
Radvas, that's the combo we have - MOSS and 2003 (well I am on office 2003)
Click to expand...

Yeah... get ready if/when you upgrade to office 2007. The "Export to Excel" feature is just no longer there as I recall.

It was fairly buggy as it was, but at least the people fancied themselves as decision makers held similar delusions that they knew jack shit about Excel, so they were halfway happy about getting things in Excel.

Reminds me a of a simulation and modeling class I took a year or two ago. There was something of a recurring thread that Excel was almost never the best tool for the job, but that virtually every exec thought they knew a lot about excel, and if you wanted them to listen, you'd have to make your models and simulations run in Excel. A bit of a warm blanket to the analytically challenged I suppose. :laughing

Another favorite quote from one of those profs was something like "Plenty of people deeply understand the business aspects of running an internet business, and plenty of people deeply understand the technical and analytic aspects of running an internet business, but the 5 who deeply understand both have already retired to the Bahamas."
 
Granted I don't have all that much perspective using alternate tools, but I always thought excel was a fine tool for basic number crunching. The built functions are accessible to non-coders, and you can crunch quite a bit of math, perform basic statistical analyses, and extract summaries in fairly short order.

The problem with excel is that there are definite limitations and bugs in the product, and these basic analyses inevitably expand as people tack on more workbooks and become ever more creative with how the figures are tied together, you get these huge spreadsheet monstrosities.

But in my mind, thats more of a people problem than a technology problem.
 
F4iChic said:
:mad

I used to be able to do it, now I can't :cry

I want to export my data from Sharepoint to Excel, but I *don't* want the export to be a dynamic link back to sharepoint when I open Excel. I just want a bloody one-time static download

HALP - how do I do this

fanks :toothless
Click to expand...

When you perform the export, the data appears in Excel as a table. Convert the table to a range and that will break the link to SP.
 
tzrider said:
When you perform the export, the data appears in Excel as a table. Convert the table to a range and that will break the link to SP.
Click to expand...

please to be giving me the exact instructions to perform such jiggerypokery :toothless
 
You must log in or register to reply here.
Back
Top