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
End Sub

Tuesday, November 6, 2012

Access denied. You do not have permission to perform this action or access this resource.

Access denied. You do not have permission to perform this action or access this resource.




We have created a Custom List and added the list as a Web Part in the site's home page, grouped by 'Status' column. When the user approves an item under a Status 'Pending Cost Approval', the designer workflow will update the Status column to 'Cost Approved'. The order of action is

1. Select Approve/Reject menu for an item from the Web Part 
2. Select Approve, and click OK 
3. After approval, the page is redirected to the home page.
The issue is, when there is only one item in grouped under 'Pending Cost Approval' and since the item is getting moved to 'Cost Approval' status after approval, we are getting "Access denied. You do not have permission to perform this action or access this resource." error. No issues when there are more items in 'Pending Cost Approval' status. Attached the screen shot. Is there any workaround for this?
Access Denied error

On different version of browser, we received 'Render failed' error.
Render failed error

I'm able to resolve this issue with the following workaround.
Set the Groupings to Expanded in the view and add the following script in a content editor web part in the same page.
<script src="/Documents/jquery-1.8.2.min.js" type= "text/javascript">
<script type="text/javascript">
function collapseGroups() {



A duplicate name "" was found

Issue: Received an error when creating a site from  a custom site template.

Error: A duplicate name "Responsible" was found.


The issue was with the column names in a list of the site we created a custom template from. I initially created a Text field column with the name 'Responsible', later we created another Responsible column with People and group type after renaming the existing column to 'Contributors'. This actually led to an error when we created a new site using this site template. The issue was identified by renaming the site template from sitename.stp to > extact the cab file > open the manifest file.

Noticed that the internal name of 'Contributor' column was 'Responsible' and the display name of 'Responsible' column was 'Responsible'.

Deleting the 'Contributor' column and recreated with different internal name resolved the issue.