1

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?

3

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.

1

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.

  • 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
1

You must add your column to the view but this cannot be done using the browser as the view will be hidden and does not allow modifications.

  1. Open the site with SharePoint Designer.
  2. Click on All files -> Lists -> [LIST NAME] -> [Excel view]
  3. Add the field references manually (using internal names)
  4. Save the view
  5. Refresh the data connection in Excel.
0

I found a solution for excel files created by clicking on "Export to Excel", which does not require the use of SharePointDesigner:

  1. Open the sharepoint list and select the view which contains all data you need in the connected excel file
  2. From the address bar of your browser, you need to copy all the part contained within brackets, something like {5E8ABE86-857E-43C7-A58D-AEEA85E01C5E}

[I copy-pasted the following part from F106Dart's answer]

  1. Open the Excel file and 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.

[now the different part, to be used for excel files created from the sharepoint]

  1. Within the "Command Text" box, you will see a section between <VIEWGUID> and </VIEWGUID> containing a part between brackets, similar but not equal to the one you copy-pasted from your browser's address bar. Just select it and replace it with the part you copied.
  2. A warning will inform you that there will be an incoherence with the external connection file. Accept the change and the file will be updated with the additional column you included in your sharepoint view.
0

I recommend doing this with a backup excel file. I think is easier than the above suggestions which are also valuable. Thanks.

Create a new iqy by exporting your list from your browser. Go to Data -> Connections Connection properties -> Definition In Connection File, select your new iqy file

For some reason after refreshin the data, I had to select the iqy file again. Hope it helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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