1

I am rebuilding a SharePoint site that has been in use for about three years. The main part of a site is a list that allows employees where I work to track their cases. We are considerably changing this list. There are 1700 active cases and around 10,000 resolved cases in the list. The list contains three content types, with a set of common columns between them and specific columns each, and three columns that look up names in the company's Outlook address book. The list also has a chronology column that uses version history in SharePoint to track changes in the cases.

Since the list is changing considerably I have had to make considerable changes to the data. I have done this in Excel using macros written in VBA. I downloaded the version history of the chronology column using a PowerShell script. I now have the data in an Excel workbook divided into four sheets, one for each content type, and one listing all the comments in the version history. Note that I am merging two lists (a main and an archive) together, and also replicating some entries that should have been multiple cases not single cases.

I have attempted to write a PowerShell script to upload this data. I have had two problems with PowerShell. First, how do I insert names in a column that looks up names from the corporate Outlook address book? Second, how do I upload the version history with the comments and the information about who modified the data and when? I can't seem to find how to do either of these things through Google or a book I have on PowerShell.

I am open to doing this upload in either PowerShell or C#. Note that I am using SharePoint 2010.

  • MS-Access instead of Excel can write data back to SharePoint, Excel is read-only. – Danny '365CSI' Engelman Nov 25 '16 at 21:13
0

Danny, this is 100% not true.

You can create a two-way bridge very easily in Excel with Sharepoint either via using ADO recordsets and SQL as you would in Access or by using ListObjects. I currently have 3 lists every day that change in Excel and I update in Sharepoint from there. I got this working in under 5 minutes.

Need to watch this video to find out how to get the http address and list names you will need: https://www.youtube.com/watch?v=KdMM0hgp4q4

Then watch the one below from creating a bridge with List Objects https://www.youtube.com/watch?v=nM-gq3N6f2E

Or this one for creating a bridge with ADO/SQL:

www.youtube.com/watch?v=1qu6u2x7Kms&t=30s

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.