Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I've been using Excel to process data from a Sharepoint list for a while now. However, I recently added a column to the Sharepoint table, and when I refresh the data in Excel, I don't get the new column.

I perform a lot of calculations based on this data, so creating a new worksheet with the "new" Sharepoint list, moving the calculations and the pivots to THAT sheet is more hassle than I'd like to face.

Is there a way to force Excel to display this new column that I've added? Maybe by modifying the connection string?

share|improve this question
add comment

2 Answers 2

Since you mentioned that you are 'refreshing' the data in Excel, it sounds as if you are using Excel's 'MSQuery' feature. Those can be modified and saved; here's how I'd do it in one of my workbooks in Excel 2007. Note that these steps apply to modifying a SQL server-based MSQuery. I've not done a SharePoint-based MSQuery, but hope it's similar:

  1. Click anywhere in the refreshable dataset. Then at the top of your workbook, select 'Data', then 'Properties', then the 'Connection Properties' icon next to the connection name.

  2. That should bring up a "Connection Properties" form, which should have a 'Usage' and 'Definition' tab. Select the 'Definition' tab, then at the lower left, click the 'Edit Query' button.

  3. That should bring up a 'Query Wizard' form. At this point you have 2 nice options:

    a. You ought to be able to bring in your new column from here, when you scroll to your source table in that Query Wizard.

    b. If you want some more flexibility, you can hit the 'Cancel' button on that 'Query Wizard' form, and it will ask you "Do you want to continue editing this query in Microsoft Query?" Select 'Yes'.

    This should bring up a 'Microsoft Query' editor. You should be able to drag-and-drop your new column into the query here. You also have the 'SQL' button at the top, and you can make direct edits to your SQL statement.

Whichever one you choose, when you are done, be sure to then select 'File', and 'Save'.

I hope this works for you; again, I'm not familiar how Sharepoint plays with MSQuery. Best of luck.

share|improve this answer
    
Ugh, can't edit the query. I created the table using the "Export to Spreadsheet" option in the Sharepoint List "Actions" menu. If there were another way to get the data, then maybe I could edit the process itself. –  Jono Jan 11 '11 at 7:42
add comment

The excel file that you exported is connected to a view, not to the list. Therefore, you need to add the new column to this view apart from adding it to the list. Afterward, refresh your excel data.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.