Wednesday, November 7, 2012

Remove User ID of a People and Group column when exporting SharePoint list to Excel



This is a VBA code to remove the user IDs from the exported spreadsheet. Apply the following code and run the macro to remove all User IDs from the spreadsheet

Sub RemoveUserIDs()
           'run against rows 1 to 10, if you want more change the start and end below
           start_of_range = 2
           end_of_range = ActiveSheet.UsedRange.Rows.Count

           'currently changes cells in colum A if you want B change to 2, C to 3 etc etc
            colum_start = 1

For col = start_of_range To ActiveSheet.UsedRange.Columns.Count
    For t = start_of_range To end_of_range
    newstring = ""
          For i = 1 To Len(Cells(t, col))
               If Not IsNumeric(Mid(Cells(t, col), i, 1)) And Mid(Cells(t, col), i, 1) <> "#" Then
                    newstring = newstring & _
                    Mid(Cells(t, col), i, 1)
               End If
         Next i
     newstring = Replace(newstring, ";;", ", ")
     newstring = Replace(newstring, ";", "")
     Cells(t, col).Value = newstring
    Next t
Next col
    ActiveWorkbook.Save
End Sub

3 comments:

  1. Hi, thanks for this post. I am not sure if anyone is still following the blog, but how do I change this macro to end at the 7th column? It runs across all of my columns and deletes numbers in my date fields.

    Thanks!

    ReplyDelete
  2. One issue I found is an error in the line:
    For col = start_of_range To ActiveSheet.UsedRange.Columns.Count

    The looping through columns starts at the value set in start_of_range (which is the first ROW, I believe, in this case 2 so that it skips the header row) rather than column_start which is the variable defined for that purpose.

    So changing that line to
    For col = column_start To ActiveSheet.UsedRange.Columns.Count
    fixes it.

    In my case, I only wanted one column, so I actually just tweaked it to
    For col = column_start To column_start
    although with some small tweaking, you could remove the loop altogether. I opted to leave it in because it was a faster change and in case I want it over multiple columns in the future. Although I might set a column_end value instead of presuming it's supposed to go to the end of the spreadsheet.

    ReplyDelete