Tuesday, December 07, 2010

The 2000 Items Limit in a SharePoint WSS 3.0 List

WSS 3.0 and probably MOSS as well lets you enter easily tenths of thousands of items in a list. However, if you want to create a view, according to Microsoft the view will be limited to 2000 items. The article that explains this and other limitation is here.

This makes sense in terms of response time. What if you want to Export the data into Excel? A good question. I have a process triggered by a scheduler that exports 14000 items into Excel (this is an Excel List Refresh process), Excel saves it as a CSV file, and another program copies the CSV file into an external system. It works beautifully. However, I have another one that does the same, but sometimes Export 2000, sometimes 4000 and sometimes takes all the ~5000 items. So why is the behavior different?

The former list has about ten columns, none of the is a multiline text field. The other one has over 100 columns with a multiline item that is heavily used. I suspect that the random number of items returned has to do with the system's response time. If the process runs fast it will end up as expected otherwise it will stop at 2000 or 4000 count. I can't prove it though.

So the recommendation is to try avoiding data export to Excel for views that exceed 2000 items.

Sunday, October 31, 2010

How to Find the Excel File Name using Excel Formulas

The formula =CELL("Filename") returns the full path of the file and includes also the file's and the sheet's name.

If you need the file name only use this formula:


=MID(CELL("filename"),FIND("[",CELL("filename"))+1,LEN(CELL("filename"))-FIND("[",CELL("filename"))-(LEN(CELL("filename"))-FIND("]",CELL("filename")))-1)
 
For example, the formula returns the file name: MyFileName.xls from the full path:
C:\My Documents\Mt Excel Samples\[MyFileName.xls]sheet1

Tuesday, October 12, 2010

Data Missing from a Required SharePoint List Field

The following issue occurrs in 2007 release but it may apply also to SP 2010.

A user that has contributor permissions to a list (A) but doesn't have read permissions to the list (B) of a lookup column of the list (A) will be able to see list A but without the value entered for that column. So far so good.

Consider a situation where the lookup field is mandatory and such a user receives email alerts from people posting items into the list. As expected, the email will not show the column's value. If our contributor needs to take care of the request and to update the item's status as completed than SharePint responds undesirably.

SharePoint let's the user update the list's item but wipes out the original value in that column which is a mandatory one.

If this happened to you now you now the reason. It took a while for me to get to source of the problem.

Updating a Sharepoint List from Excel

SharePoint releases later than 2003, namely 2007 and 2010' don't let Excel update OOTB a SharePoint list it is connected to. For 2007 there may have been suggestions of how to recover this functionality. I didn't research for such solutions regarding 2010 release. I came lately across a product that was developed just for that purpose. You can view its specs in the following links in case you need such functionality:

http://softfluent.wordpress.com/2010/09/24/2-way-sync-functionality-for-excel-back-thanks-to-softfluent/

The following article explains the issue in details:
http://www.softfluent.com/article/les-listes-synchronisees-sous-excel.aspx

It is said that the product is free for non-commercial use.

Friday, October 01, 2010

View of Employees Birthday in a SharePoint List

Suppose you have a corporate or divisional employee contact list containing birthdays and you need to show the employees whose birthday fall within the current month.

You have at your disposal the system variable [Today] but you can't apply a formula to this variable (such as =Month([Today])) to get the desired view with this month' birthdays.

What you can do is to add a calculated column of the date type. Then you can create a view filtered by this column. The idea is to fix the year to any year of your choice and compare dates accordingly.

Here is an example:

Let's say the column's name BirthDate and the year of choice is 2000, then the calculated date column would be:

=DATE("2000",MONTH(birthDate),DAY(birthDate))

Tuesday, September 28, 2010

Corrupted Excel 2003 Connection to a SharePoint list

I thought I would share my solution to the following issue:

You exported a SharePoint (WSS 3 or MOSS) list to Excel 2003 and sent it to your users or posted it to a document library for them, promising that they can refresh the list anytime. Very soon you get responses that they get an error message. Excel can't connect to SharePoint.

I can't pinpoint the reason for this issues which as if occurs randomly but I provide my users a work around which I either do myself and send another doc or teach them the following procedure:
  • Navigate to the site.
  • Open any list, (a small one can save time).
  • Export it to the same Excel document into a new tab.
  • Try refreshing the original list - it should work right.
  • Delete the newly created tab.
That's it. This sequence fixed the connection every time I followed it.

Sunday, August 22, 2010

Unsupported External List Refresh in Excel Services

Excel Services is a great tool allowing interactive data analysis process by predefined models or pivot tables created in Excel and posted to SharePoint MOSS or 2010.

However, when it comes to external lists it is not possible to refresh the external data source directly from the browser. The visitor needs to do it in Excel which makes the user experience much less attractive for most users.

For example, you can easily export a SharePoint list to Excel as a pivot table. When you refresh the pivot table in the web interface of Excel Services it shows the latest data within the table in Excel even though the list in SharePoint might have been changed since the last synchronization with Excel.

Since Excel Services strips VBA functionality from the Excel model, it is also impossible to run the refresh via a macro button.

One would suspect that this has been designed on purpose to boost the purchase of MS Office latest versions but this limitation is very annoying and doesn't help Microsoft with their attempt to increase the usage of its BI solutions.

As a result, we are planning on developing a solution that will run a background refresh defined in the Scheduler. This will let people get the up-to-date external data based on the time intervals of the auto-refresh process. We haven't decided yet if it would be free though.

Saturday, August 21, 2010

Save as Template doesn't work for large SharePoint Lists or Libraries

In MOSS, WSS 3.0 and SP 2010 there are limitations on the file size when it comes to saving the entity as a template including its data.

The work around is to use STSADM to extend this limitation. If you want to extend the size to 30MB for example, use the following command:

stsadm -o setproperty -propertyname max-template-document-size -propertyvalue 30000000

Monday, July 12, 2010

Export SharePoint 2007 List with Multi Selection Lookup Column into Excel

Whether you use MOSS or WSS 3.0 lists that include multi-selection items, you may find it hard to manipulate the column's items in Excel. For example, if you try filtering by a specific value, any cell with multi item values won't show because it is not identical to the filter value. Excel 2007 includes all the multi selection values in the same cell and treats all the text inside the cell as a single long string. Another issue is that the ;#n; (where n denotes the item-ID) shows next to the lookup.
There is a solution, though not a perfect one, for this issue. You can add to the right side of the Excel table as many columns as the # of values in the dropdown list, and apply formulas to strip one value at a time into a column. Then you can filter the table by the calculated columns as you do for any other table's columns.
This would nicely solve 10-30 list items but any time you have a new item in the drop down list you will have to add a column for that item.

Here is an example using Excel 2007 though it should work for 2003 as well:

The are two tables (lists) exported: the main one and the dropdown.
The exported list had the project column with multi-item selection. Each selection has a value and next to it its ID separated by a semicolon. One of the values is 'Estimating; #1.'
In a new column named Proj-1 you can see all rows that have the value 'Estimating.' You will have to create a separate column for each separate value as explained below.
 

The Project column is associated with the ProjectList dropdown list: (this is an excerpt from the entire list).











For each entry in the selection list (ProjectList) you prepare a column (proj-1, proj-2 etc.) and enter the formula below:







=IF(RIGHT(Source!$B2,3)=";#1",VLOOKUP(1,ProjectList!$A$1:$D$19,2,FALSE),IF(ISERROR(SEARCH(";#1;",Source!$B2)),"",VLOOKUP(1,ProjectList!$A$1:$D$19,2,FALSE)))

 The formula first tests whether that last item in the string is our item (which is ;#1 for Estimating). if not it searches for the string ;#1;. upon failure the cell will stay empty otherwise it will contain 'estimating.'

although estimating can be obtained directly from the cell or entered as a string, I found it easier to export the list and vlookup it.

Of course, for the next value you should test #2 and the lookup will be for 2. When testing for IDs greater than 9 you will need to use RIGHT(Source!$B2,4) as you need to test 4 characters.

To improve the process you can use the row() function to further automate the formula but I didn't want to add another complexity.

Saturday, July 03, 2010

Web Part Page Error prevents opening it for update

When an error occurs after editing a web part page, for example by adding a custom WP, sometimes you can't access the page anymore and therefore you can't to fix the problem by removing that WP.

Although not well documented, there is a solution to this issue. You just need to add the suffix ?contents=1 to the page's URL. This will present the maintenance area of that page. In the maintenance page you can exclude the WP and view the page again.

For example, if your URL is www.myDomain/mySubSite/myPage.aspx, it should look like:
www.myDomain/mySubSite/myPage.aspx?contents=1.

Access violation error messages on SharePoint Lists

You configure some Lists or Surveys whose Item-level Permissions of Read access setting is set to "Only their own." However, when you run a crawl for these Lists or Surveys, you receive access violation error messages.

Here you can find the hotfix:

WSS 3.0:
Description of the Windows SharePoint Services 3.0 Cumulative Update Server Hotfix Package (Sts-x-none.msp): October 27, 2009 http://support.microsoft.com/kb/975002/


MOSS:
Description of the SharePoint Server 2007 Cumulative Update Server Hotfix Package (Coreserver-x-none.msp): October 27, 2009 http://support.microsoft.com/kb/975731/

Workflows are carried over upon saving SharePoint lists as templates

If you save a list or library as a template, the attached workflows are carried over. If this is not what you need you should detach the workflow before saving it as a template and re-attach it afterwards.

You need to be aware that newly created lists from such a template may not work correctly if created in another subsite, especially when they deal with editing or looking up items in lists that are now in another subsite.

It is sometimes a pain in the neck to get rid of such a workflow after the fact so the safest way to work is to store the list as a clean template in the first place.

Office and SharePoint Metadata Synchronization (2007 and 2010)

This post is an extension to a previous one adding the sequence for Excel 2010.

When uploading Office documents to a SharePoint Library the document properties are mapped to the Library columns by name. Therefore, you can easily add columns to the Library as appropriate to handle the MS-Office document properties. However, if you need an extra property for the document to map from a SharePoint column it is a bit harder. In Office 2003 it was easier but in 2007 it is more confusing:
  1. Click the Office Button on the top left of the Office 2007 program (Excel, for example)
  2. Select Prepare / Properties to show the doc's current properties.
  3. Click Document Properties dropdown on the top left and Advanced Properties.
  4. Select the Custom tab.
  5. Here you can add a Name of a new property and enter a value for that property.
  6. Click Add and OK and you are all set.
If the name matches a SharePoint Library column the property will pass upon uploading or downloading the document.

In Excel 2010:
  1. Click File and select Info.
    You will (hardly) see the Properties section on the right.
  2. Click to open the Properties dropdown.
  3. Select Advanced Properties to open that window.
    This is the same window as in step 3 above.
  4. Follow steps 4-6 above.
As Niall commented, this works as expected only upon upload. Afterwards when you update the doc's properties you need to do it twice, for the doc's and for the server's property. Otherwise the values won't be identical.

Adding Mark's important tip from September 7th 2012:
One can sync a cell value with a SharePoint list column via an Named Range given to the cell. See Mark's comment below.

How to limit a SharePoint user to view his list items only.

This is a simple procedure unless one needs to allow certain users to view all items, not only their own.
  • To limit the view on a list click Settings / List Settings / Advanced Settings.
  • For Read access and Edit access select 'only their own' as appropriate.
If you need to let certain users view and edit all items you need to provide them with Designer or Owner permissions.
Comment: Document Libraries don't have this option so you may need to look for a related feature on CodePlex http://www.codeplex.com/ (http://sharepointworkflows.codeplex.com/)

How to Lookup two or more source columns in SharePoint

Let's say you have a contact list that contains the columns: Fname, Lname and Phone#. In another list you would like to lookup this list by the combination of Lname and Fname and a space between them to get the contact's Phone#.

Add to the source list a calculated column of type text with the formula:
=Fname&" "&Lname.
Now you can create the Lookup Column in the target list pointing to the newly created 'FullName' column.

Remove Disabled AD Accounts from MOSS

You would think that whenever an AD account's attributes are changed, the new info will be synchronized with MOSS profiles - but this is not the case. The following walkthrough explains how to remove the AD disabled accounts from MOSS.
The first step is to change the LDAP OOTB search filter.
To get this done:
  1. In Central Administration click on SharedServices1 on the left navigation bar.
  2. Click User Profiles and Properties (under User Profiles and My Sites section).
  3. Click View Import Connections.
  4. Hover over the only connection you have, click the down arrow and then click Edit.
  5. Scroll down to the Search Settings section. There is a box titled User Filter in that section. Paste in the following filter string and OK:
    Instead of:
    (&(objectCategory=person)(objectClass=user))
    Place this string:
    (&(objectCategory=person)(objectClass=user)( !(userAccountControl:1.2.840.113556.1.4.803:=2)))
  6. In your navigation breadcrumbs near the top of the page, click on User Profile and Properties to navigate back there.
  7. Near the bottom, click the bulleted link Start Full Import.
    This process will go through your existing profiles and mark any previous disabled accounts as inactive profiles that will no longer show or be accessible in SharePoint. Disabled accounts not previously imported by MOSS will be ignored and no profile will be created.

    The procedure above was taken from Mark Eichenberger's SharePoint Blog with minor editing:http://sharepoint.microsoft.com/blogs/MarkE/Lists/Posts/Post.aspx?List=fd220abd%2D850c%2D44a4%2D85d8%2D805efe79f85a&ID=10
Next, remove current extra accounts: In Shared Services1 / Profiles and Properties / View User Profiles select the Profiles Missing from Import and delete them.

Import the profiles again
Create index to the related Data Source (usually all items)
And lastly, update the Search Scopes.

You should now have  a clean SharePoint User Profile list synchronized with AD.

Cannot get the list schema column property from the SharePoint list

When you try to export a SharePoint list to Excel you may see this message:

This may happen because you have calculated columns in the selected view. I am not sure why, but some of the calculated columns can't be exported to a spreadsheet.
If you remove these columns from the view you will be able to successfully complete the export operation.

Permissions on a SharePoint List Column

SharePoint OOTB doesn't let you apply permissions on a column level. However, if you need to hide the column's content and it is a lookup field, you just remove the user's read permissions from the target lookup list. The user will see an empty column.
Although this is fairly simple and straight forward, we sometimes forget such simple tips,so I decided to post it.

Incorrect search results when using the This Site or This List options

There are several reasons for a search to bring wrong results in MOSS. Here is one incident we have experienced lately:
The Web application is named with the internal server name and so is the Search in Content Sources which is configured to use the address http://servername/.
There is a translation in the DNS server to http://www.myinternetaddress.com/ which lands in the same place. If you change the Content Sources definition to the DNS name you may affect the Search Scopes, which in turn will bring wrong results.
The remedy is to change the address back to the original one.

Calculate SharePoint Date Columns Excluding Weekends

Sometimes you may need to calculate a SharePoint date column by adding x work days to another date column.
Let's assume you would like to add 3 days since the list item was created to the target date column 'Due Date: Initial Review.'
SharePoint uses Excel type functions making it is easy to add 3 days to date columns but you also need 2 days to compensate for Thursday, Friday, or Saturday, and 1 day if the item was created on Sunday.

  • Created + 3 adds the 3 days to the value in 'Created' column
  • To test the weekday you use: WEEKDAY(Created)=4. In this case testing is on Thursday.
  • If it is Thursday add 2 otherwise add 0 days would look like: If(WEEKDAY(Created)=4,2,0).
  • The entire formula is therefore:
Created+3+IF(OR(WEEKDAY(Created)=4,WEEKDAY(Created)=5,WEEKDAY(Created)=6),2,0)+IF(WEEKDAY(Created)=7,1,0)
Of course, if you have to exceed a full week you will need to work harder to add more than 7 days as more than one weekend might be involved.
Add a Date Calculated Column:

Enter the Formula:


Show Last Month SharePoint List Items

This article has been lost and rewritten (but not tested)

Add these 4 columns: (the last one is needed only for current month calculations in case you need it.)

Date is defaulted to today.
Month =MONTH(Date)
Last Month – Start column  =DATE(YEAR(Date),(Month+1),1)
Last Month – End column  =DATE(YEAR(Date),Month+2,1)

This Month column =DATE(YEAR(Date),(Month)+1,1)





















Create the Last Month  view with the filter:






















Create This Month view with the filter:

Wrap the Title (name) of SharePoint List Columns

If you have a SharePoint list or Library with long column names the views might look awkward because the column name is spread out in one line while its content may be as a short as Yes/No.

To work around this issue, add a Content Editor Web Part to the list and type 3 lines of code into it. The result will look like this:








Here are the steps to achieve such results:
(the sequence is for 2007 release. 2010 is pretty similar)
  • Navigate to the list.
  • In Site Actions select Edit Page.
  • Click Add a Web Part and navigate to select the Content Editor Web Part.
  • Click the link inside the WP or click Edit to open the WP settings area on the right.
  • (see an image at the bottom)
  • Click the Source Editor... to open it.
  • Enter the following code:  (within the style tags)

    td.ms-vb {white-space: normal !important}
  • for 2010 release use: th.ms-vh2 {white-space: normal !important}


  • Okay the operation and Exit Edit Mode.
    Tip: Don't forget to hide the WP title by changing its Chrome Type in Appearance to none.
  • The titles should now be wrapped.
WP Setting area is on the right:




Sunday, May 23, 2010

Calculate the Next Fixed Weekday (Tuesday for example)

How would you calculate the next Tuesday regardless today's weekday?

The idea is to reduce today to get always the same weekday (Sunday or 'zero' day for example) and add the target week day. this requires adjustment in case you already passed the day for this week and need to add another 7 days.

So, if you expect to get the next Tuesday while today is Tuesday the formula can look like this:

=A1-WEEKDAY(A1)+3+7-IF(WEEKDAY(A1)<3,7,0)

A1 is of course the reference date to add to.

Wednesday, May 19, 2010

SharePoint 2010 and Excel - List Validation Supprot

In SharePoint 2007 you could create calculated columns with Excel type formulas. For example, if you had the Division Name in one column and Department Name in another, than you could created a calculated column:

=[Division Name]&[Department Name]  to get a concatenated column / key.

In SharePoint 2010 (SP2010) you can do the same to achieve validation on a list's columns as well as on the list itself.

Saturday, May 08, 2010

Sum on SharePoint Calculated Column

You were probably frustrated by the fact that when creating views on a WSS 3.0 or MOSS list you can't sum up or run another aggregation on calculated columns. SharePoint would let you do that on numeric columns but calculated columns won't show in the selection list.

If you create the list in a datasheet mode though, you will see the calculated columns on the list and will be able to present the total for the column.

When in a standard view, you can switch to a datasheet mode, you can define a sum cell for such a column. However, this won't be preserved in the system and will not show next time you access the view.

How to Lock Excel Worksheet Cells with Content

This tip is an excerpt from Excel Tips where you can find the comprehensive explanation on how to unlock empty cells (or lock filled-in cells) either manually or using a macro.

Sub UnlockEmptyCells()


   Dim myCell As Range
   Set myCell = Selection
   Cells.Select
   Selection.Locked = True
   myCell.Select
   Selection.SpecialCells(xlCellTypeBlanks).Select
   Selection.Locked = False
   ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
   myCell.Select

End Sub

Comments:
  • Running this macro will leave all cells inside the input area unlocked if they are empty. All cells outside the input area will be locked including the empty ones. For Example, if the most bottom right cell with input is G20 all the columns from H and the rows from 21 will be locked.
  • If you need a wider range just add spaces to the most bottom right cell first thing in the macro to let it be the endpoint cell.
  • The macro won't run twice in a row. You need to unprotect the sheet before you run it next time. You can add the appropriate 'unprotect' code to the macro to avoid manual coding or record such a mcro and use two buttons, one to protect and the other to unprotect.
  • This tip applies to 2003, 2007 and 2010.

Thursday, May 06, 2010

Error on Site Creation from Template with calculated columns

If you try creating a custom WSS 3.0 or MOSS site template that has lists with calculated columns you may receive an error message. Nonetheless the site has been created but some functionality like the tab in the top navigation bar may be missing. To fix this issue and have the site fully functional do the following:
  • Click on the link to go back to the parent site.
  • Click on 'View All Site Content' and page down to locate the newly created site.
  • Click to access the site.
  • Click on Site Actions / Site Settings and reactivate the top navigation bar inheritance from parent.
  • Cpoy the URL to get the site name and navigate back to the parent site.
  • In Site Actions / Site Settings / top navigation bar - add the child site. Remove the suffix and the prefix leaving only the site name between slashes /MySubSiteName/ and Okay the operation.
  • That's it. You have your site back in business.

Friday, April 16, 2010

Address Labels in Sharepoint and Excel

I recenty read Bill Jelen's article named 'Creating Address Labels from a Spreadsheet Programmatically' and thought that this ccould be utilized to improve the unfriedliness of SharePoint when it come to printing.

To view original post where the Excel VBA code is explained click here.

 
The problem solved in this article is a list of contacts in SharePoint exported into Excel and rearranged in a button click as Labels in 3 columns ready to be printed.
  1. Prepare the spreadsheet as instructed in th ementioned above link. The article doesn't explain how to connect the macro with a button so you should have some basic knowledge on macros.
  2. Prepare a contact list in SharePoint. 
  3. Since the referenced VBA code supports a certain structure of 4 columns, create calculated columns in the SP contact list that imitate the required structure. For example, concatenate the address separated by commas to get the address right.
  4. Export the contact list into the Address tab in the (already open) Excel spreadsheet.
  5. Run the macro to rearrange the addresses as label in the Label  tab.
  6. You should see your addresses aligned in 3 columns as labels.
I haven't tried it myself as I don't need such a solution yet but I am sure it would work.

Thursday, March 04, 2010

Inconsistent MOSS Search Results in Different Computers for Multi Language settings

If you ever noticed getting inconsistent search results when using different clients, the reason might have been your IE browser settings when using more than one language. The order of the languages in your IE settings derives the logic of the results. The language in the first line dictates the "language analysis rules" thus the results might differ. The following image explains where in IE you can control the settings:



















Lior Zarfati cotributed this article with a minor edition on my side.  You can view Lior's blog here:
http://liorzar.blogspot.com/

Saturday, February 27, 2010

Sort a list of addresses in a walking order

Suppose you need to walk in a street using a list showing the odd numbers ascending first and then the even ones descending, a kind of a postman's walk in a four lanes street.

One way to solve the problem is to reorder the odds or the evens by making them negatives. This requires an extra effort because Excel doesn't like that when sorting lists. A better approach is to subtruct the street numbers from a large enough constant. Let's say that our constant would be the sum of all the street numbers. Applying the formula and concatenating it with the street name will solve the problem.

Here is the original list in columns A:Bwith the formula applied in the extra column C:

=A2&IF(ODD(B2)=B2,B2,SUM(B:B)-B2)
























Here is the new order sorted by column C:




Monday, February 15, 2010

Speed up SharePoint spin-up and stsadm execution time in sites without access to the Internet

For my own records, I posted excerptions of an article I found. You can find the full article and references here. Jeroen Ritmeijer originally posted it here.

Explanation:

The problem is that when loading signed assemblies the .net Framework checks the Internet based certificate revocation list. As our servers have, like most secure environments, no outgoing connections to the public Internet the connection to crl.microsoft.com times out after what appears to be 30 seconds. It probably does this a couple of times in succession, causing a 2 minute wait when spinning up SharePoint.

After the timeout the assembly is still loaded and the software works as expected, though very slow every time a new signed assembly is loaded for the first time, which happens a lot.

Possible Solutions: (You can try one or more, as appropriate in your environment)

  1. Add crl.microsoft.com to your hosts file and point it to your local machine. Some people have reported success with this, but it didn't work for me.
  2. Allow your servers to directly connect to crl.microsoft.com. If your environment dictates the use of a proxy server, configure it using proxycfg.
  3. Disable the CRL check by modifying the registry for all user accounts that use STSADM and all service accounts used by SharePoint. Find yourself a group policy wizard to help you out or manually modify the registry:
    [HKEY_USERS\\Software\Microsoft\Windows\CurrentVersion\WinTrust\Trust Providers\Software Publishing]
    "State"=dword:00023e00
  4. Download the CRLs and add them to the server manually (I haven't tested this, but it may work):
    http://crl.microsoft.com/pki/crl/products/CodeSignPCA.crl
    http://crl.microsoft.com/pki/crl/products/CodeSignPCA2.crl
  5. Add them:
    certutil -addstore CA CodeSignPCA.crl
    certutil -addstore CA CodeSignPCA2.crl
VBScript to apply registry change: (Contributed by Nik Shaw)


The following script applies the registry change to all users on a server. This will solve the spin-up time for the service accounts, interactive users and new users.

const HKEY_USERS = &H80000003
strComputer = "."
Set objReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" _
& strComputer & "\root\default:StdRegProv")
strKeyPath = ""
objReg.EnumKey HKEY_USERS, strKeyPath, arrSubKeys
strKeyPath = "\Software\Microsoft\Windows\CurrentVersion\WinTrust\Trust Providers\Software Publishing"

For Each subkey In arrSubKeys
    objReg.SetDWORDValue HKEY_USERS, subkey & strKeyPath, "State", 146944
Next

Tuesday, February 09, 2010

Drag and Drop Outlook Email to SharePoint Library

If you need to manually store emails in a MOSS or WSS library, you can drag and Drop items from your MS Outlook. Just open the document library with Windows Explorer from the Actions tab. Once opened, you can drag and drop into it Windows items (files) as well as MS Outlook emails. The emails are stored as MSG type files.

If you try dragging Outlook Express items (EML type) you will get an error message.

An alternate way is to define the library to support imcoming mails (there are many articles on teh subject). This enable storing EML files but in order to open them you first need to save the file in your hard drive.

Saturday, February 06, 2010

Count Even or Odd numbers in Arrays

There are several ways to count even or odd numbers in Excel. The simplects one is probably if you are familiar with Array Formulas.

Let's say you have an array A1:A100 with integers.
Enter the folloring formulas into the result cells, say C1 and D1 :
(the curely braces are automatically entered by Excel afret you type into the formula bar the formula and press Shift+Ctrl+Enter. Do not enter them manually.)

Odds {=SUM((ODD(A1:A100)=A1:A100)*1)}


Evens {=SUM((EVEN(A1:A100)=A1:A100)*1)}

Explanation:
The Odd and Even function is compared to the original value and return True or False.
Then it is multiplied by 1 to transtale the True / False into 1 / 0 respectively.
The SUM function then sums the ones to get the expected result.

If you don't use array formulas just create the formulas in column B1:B100 and count the results using the COUNT function.

Wednesday, February 03, 2010

Hiding all SharePoint List items from the Seach Results page.

Sometimes it makes no sense to show a list's content in WSS or MOSS search results. To hide the list or library you access the Setting / Library or List settings / Advanced Settings page and change the Search attribute to No.

However, be aware that new items won't appear in the search but existing items will continue showing in the search results. To completely clean the list or library you need to run a full crawl. This is done in the central administration area.