Monday, December 28, 2009

Selecting Dates from a SharePoint Dropdown List

Assume the following Scenario:
You need a week list with the week's Monday date and you want it to be a validation (dropdown) list for a column in another list. SharePoint 2007 doesn't support dates in a dropdown selection list so dates won't appear as an option for validation (dropdown).

The work around is to create the column as a single line text. In order to show the 'dates' in the right order you need to keep an exact format like 2009/10/22. You can use Excel to easily prepare such a list and copy the data into the SharePoint list opened in a datasheet mode. However, Excel treat date cells like dates so extra steps are needed:
  • Type the first Monday's date of your choice. (Let's say in A1)
  • Enter in A2: =A1+7 to calculate the next Monday.
  • Drage down cell A2 as many cells as needed to get the full week list.
  • In B1 enter: =TEXT(A1,"YYYY/MM/DD"). This will transform the date into a string with the exact format we need.
  • Drag B1 down to the last filled in row. 
  • Copy the cells in B and use Paste Spacial to paste the values. This will get rid of the formulas and leave the values in column B.
  • Copy the range and paste it into the list in SharePint (opened in a datasheet mode)
Now you can lookup at this column as a dropdown (validation) in another list.

If you need, for example, to filter the list be the year, you can create a calculated column with the formula
=LEFT([The Week of:],4)  (assuming that the column mane is 'The Week of:" and it contains 4 characters).

Friday, December 25, 2009

Accessing a SharePoint Web Part page after editing it and receiving an error message

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:

Saturday, November 07, 2009

Creating a Photo Catalog from a Folder of Photos

I have come accorss an article on Excel Tips website explaining how to incorporate images in Excel documents. To view the original article which explains the code and the limitations involved you can  click here. I do recommend visiting that site where you can subscribe for daily or weekly tips. The artiicles are very clear and educational. If you would like to polish your excel skills they have cheap electronic books for sale.

I copies the VBA for my own future use. The code assumes that there is a folder with the thumbnails and a folder with the images, and loops through the the list to get the images.

Sub PhotoCatalog()

  Dim i As Double
  Dim xPhoto As String
  Dim sLocT As String
  Dim sLocP As String
  Dim sPattern As String
  sLocT = "c:\Photos\Thumbnails\"
  sLocP = "c:\Photos\"
  sPattern = sLocT & "*.jpg"
  Application.EnableEvents = False
  Application.ScreenUpdating = False
  ActiveCell.FormulaR1C1 = "Description"
  ActiveCell.FormulaR1C1 = "Thumbnail"
  ActiveCell.FormulaR1C1 = "Hyperlink"

  With Selection.Font
    .Name = "Arial"
    .FontStyle = "Bold"
    .Size = 12
    .ColorIndex = xlAutomatic
  End With

  With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
  End With

   i = 1
  On Error GoTo 0
  xPhoto = Dir(sPattern, vbNormal)
  Do While xPhoto <> ""
    i = i + 1
    Range("B" & i).Select
    ActiveSheet.Pictures.Insert(sLocT & xPhoto).Select
    With Selection.ShapeRange
      .LockAspectRatio = msoTrue
      .Height = 54#
      .PictureFormat.Brightness = 0.5
      .PictureFormat.Contrast = 0.5
      .PictureFormat.ColorType = msoPictureAutomatic
    End With

    Range("C" & i).Select
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
    Address:= sLocP & xPhoto, TextToDisplay:=xPhoto
    xPhoto = Dir

  Application.EnableEvents = True
  Application.ScreenUpdating = True

End Sub

Friday, October 30, 2009

Starting in V8, the PDF iFilter file is bundled with Adobe Reader. To get the iFilter, you need to install Adobe Reader (8.0 or higher) onto the WSS server that will be doing the indexing.

The procedure below wouldn’t work for 64 bit servers. You can find comments on this  in: WSS needs to know what extension to use, so you need to change a few registry entries.

In regedit:
  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\12.0\Search\Applications\{ANYGUID}\Gather\Search\Extensions\ExtensionList

    Find the highest number in the list and add the next value with PDF for the value.
  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\12.0\Search\Setup\ContentIndexCommon\Filters\Extension

    This is the list of file extensions with a class ID for the iFilter used to index the extension. If .pdf is not listed, add it. It should have a multi-string value in which you need to add the CLSID for the iFilter added by Adobe Reader.  In version 9, this file is called: "AcroRDIF.dll."

    CLSID for 9 = {E8978DA6-047F-4E3D-9C78-CDBE46041603}.
  • Add its path to the environment variables of the server: Start Menu / right click My Computer /select Properties /go to the Advanced tab /click on the Environmental Variables button and scroll down the Path variable, select it and click on the Edit button and add the path ";C:\Program Files\Adobe\Reader 9.0\Reader" then click OK to apply and close.
  • Reboot the server. It may take some time until the PDF documents are indexed.

    You can also run the search service: stsadm -o spsearch... for WSS and SSP Full Crawl for MOSS.

Wednesday, October 28, 2009

PDF search in SharePoint

There are 2 subjects related to PDF documents.

  1. Out of the box, you won't see the PDF icon in document libraries as you see MS Office icons next to office documents.
  2. The search will look at the PDF document's properties but not in its content.

PDF Icon:

  • Download the small icon file from adobe.
  • Save the icon in the c:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\IMAGES folder. You can rename it to match the standard naming convention.
  • Open (in notepad) the DOCICON.XML file located in the c:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\XML folder.
  • In ByExtension section add the tag >Mapping Key="pdf" Value="icpdf.gif" OpenControl=""/>
    -Change the direction of the first > (just left to Mapping) to make the markup work right.
  • Save.
  • Reset IIS
  • You should see now all PDF documents with the correct icon.

I will post the instructions for PDF content search soon.

Saturday, October 17, 2009

How can we explain SharePoint?

I posted this article in another blog but thought it might be of interest to Excel experts as well. There is another post in this blog explaining the SharePoint-Excel interaction.

I always sympathize with SharePoint professionals when reading articles about their difficulties in answering the question: “What do you do for a living”? After thinking it over and over, I decided to rephrase the question. I asked myself, “What will eventually be the TLR (Three Letter Acronym) for SharePoint-like applications?

I have never been satisfied with my own explanations of SharePoint, even with my latest strategy to ask the person about his or her IT knowledge before I formulate my response. I see people nodding their heads, but in most cases I feel I didn’t convey the message well enough.

Thinking about a TLR, the first thing that comes to mind is the equivalent ERP (Enterprise Resource Planning). The name is intuitively perceived as a package of related applications that support end-to-end the enterprises’ operations. But it took 10 to 15 years until this TLR became the standard everyone uses and understands. Previously, there had been MRP, Logistics, Shipping, Financial, HR and other disparate applications.

So what do we package here? SharePoint supports several relatively independent processes. It combines what was before Portal, EDM (Enterprise Document Management), ECM (Enterprise Content Management), WCM (Web Content Management), team collaboration, activity tracking and even connectivity extensions to backend systems. We will probably see more Social Network support coming soon. Since companies usually need more than one of these applications, why not use the same tool and save on maintenance?

What can be a reasonable common denominator for all these processes? I think that the phrase “Knowledge Sharing” is pretty close to what they do. Prefix it with the E for Enterprise which is important, accurate - and also sells, and you get EKS – Enterprise Knowledge Sharing.

Spread the word…

Thursday, September 24, 2009

More on Excel Cell Size Limitation

Excel 2003 can store up to 1024 characters in a cell. However, is shows only the first 256 characters so one may think the rest has been lost. If you look at the top input area you can see the entire string but not in the cell itself or upon printing. Excel 2007 doesn't have this issue and can correctly handle veryyyyyy long strings.

When you copy to other programs the target cell may have the same limitation and will cut the string, as explained in a previous article pertaining SharePoint Link type column.

Saturday, September 19, 2009

Copying long URLs from Excel to a SharePoint list

In another post I explained how to extract the URL string that has an associated friendly name from a link stored in a cell . The problem is that a simple Copy and Paste carries both the URL and the friendly name while Paste Special as Values carries the name only.

However, if you have long URLs you may have some issues. Although Excel cells can store very long strings, SharePoint Hyperlink or Picture column type can handle 256 characters so longer URLs will be cut when you copy from Excel to SharePoint.

To work around this limitation, you can copy from Excel to a Sharepoint Multiple lines of text column type with Rich text attribute. The link will have the full length and will also function as a link as one can click it to navigate to another page.

Since the URL is very long, it is not practical to use it without a friendly name. But unlike the Hyperlink column type where you could copy a URL with a friendly name from Excel to SharePoint, this won't work with the Multiple lines column type. You will have to work in a standard view rather than in a data sheet mode and type in friendly names one by one.

Friday, August 28, 2009

Excel and SharePoint Metadata Synchronization

When uploading Excel or other 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 Excel (2007).
  2. Select Prepare / Properties to show the doc's current properties.
  3. Click Document Properties dropdown on the top left and select 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.
  7. If the name matches a SharePoint Library column, the property will transfer when uploading or downloading the document.

Monday, August 24, 2009

Fixing static links upon migration from SharePoint 2003 to 2007

In many cases, Sharepoint sites contain hard coded links pointing to other pages in the site collection. Upon migration from WSS V2 to V3 (for example) you may have lists with many such links requiring changes, usually in their prefix.

Fortunately, Excel can help out with this task.

One way to make the changes would be:
  1. Export the list from SharePoint to Excel.
  2. Apply a formula that extracts the URL from its 'friendly name', which requires using a UDF (User Defined Function) macro.
  3. Apply the 'Copy' and 'Paste special' as values to replace the URL formulas with values. Use 'Replace all' to get the new URLs.
    **It is assumed that you know how to do that.
  4. Apply the 'HYPERLINK' formula to create the new links.
  5. Copy the new links back to the SharePoint list.
Export the list from SharePoint to Excel
  • Navigate to the list in SharePoint.
  • Change the list to 'Edit in a Datasheet' (in Actions)
  • Export it to Excel (in Actions)
Extract the original URLs
In VBA, add the following function:
Function MyLinkText(pRange As Range) As String
Dim String1 As String
Dim String2 As String
If pRange.Hyperlinks.Count = 0 Then
Exit Function
End If
String1 = pRange.Hyperlinks(1).Address
String2 = pRange.Hyperlinks(1).SubAddress
If String2 <> "" Then
String1 = "[" & String1 & "]" & String2
End If
MyLinkText = String1
End Function
Use the function we have just defined to extract the URL only. (let's say your link's column is C and we are in cell D2):
Change the prefixes to the new URLs
Convert the formulas in column D to values and replace their prefix to comply with the names in the new site collection. This is done by copy, paste special / values, and then replace all.
Create the new links
Now you have the friendly name in C and the correct URL in D. In E2 Apply the formula:
This will create the desired links in column E.
Copy back to SharePoint
Copy the values in E. (Make sure you have the same numbers of rows as in the SharePoint Datasheet and you didn't change the order).
Paste into the first row in the link's column in SharePoint.

Sunday, August 16, 2009

Count workdays by excluding Sundays from a date interval

In Excel 2007 you can calculate the workdays between start date and end date by using NETWORKDAY function. It even lets you point to a holiday array of your choice to exclude holidays.

If you need to exclude Sundays only because Saturday is a working day, you can achive this in several ways.

Here is a simple way to do so (as Miki Avidan suggested) :
* The dates shown here are in European DD/MM/YYYY format which doesn't affect the solution.

Start: B10
End: C10


<>1 represent the exclusion of Sundays in the SumProduct function.

Click the image to enlage

Wednesday, August 12, 2009

How to lookup at columns on the left

When you use the Lookup or Vlookup function you can find the value of the related cell in any column on the right of the lookup value. If you need to find a column on the left you can of course move the 'Matching' column position to the left .

However, if this is not possible, you can still achieve the same goal.

Let's say you have an array in A1:B30 named 'Target.'
In G1 you have the lookup value to lookup on column A and would like to show the exact value from column B if found.
This would be a simple vlookup: =Vlookup(G1,Target,2,FALSE)

If you want to lookup on B to get the value from A you can use the Index and Match functions:

Index finds the value of a cell in row n and column m within the array. The column in this case is static and Match provides the position of the matched row in a column array.

Saturday, August 01, 2009

SharePoint, Excel, and Backend Systems (like JD Edwards on AS400 / iSeries)

With BDC, it maybe easier to combine backend and SharePoint data than doing so in WSS.  Nevertheless it is still a pretty hard task to accomplish, especially when working in IBM environment or more generally, in non-SQL Server based systems.
SharePoint Designer (SPD) is as difficult when it comes to Unix or IBM based data sources.

I use Excel to do the magic and will demonstrate how to build an Excel model that combines iSeries data managed in JD Edwards with complementary data entered in a SharePoint list.

Assume the following scenario: production quantities are entered in JDE by machine and job. Weekly Goals are entered in a SharePoint list. The data matching is done by machine to get the weekly quantity aggregation .

One way to achieve this is:
  1. Export the appropriate SharePoint list's view to a sheet (1).
    This is a standard SharePoint feature available for every list.
  2. Import AS400 data into another sheet (2).
    It is possible to use Microsoft query to define the SQL statement as done in Access but when I filter by a specific year entered in Excel, I use VBA.
  3. Create a formula just after the last list's column in sheet 1 to lookup the quantity from sheet 2.
    There is a parameter letting you define that formulas will be spread out when the SharePoint linked list is refreshed, over the new set of rows returned from SharePoint.
  4. Name the extended list and create a pivot table pointing to that name.
    The range of this name changes dynamically with the list size upon each refresh.
Get data from SharePoint
This is the list entered in SharePoint:

Export to Spreadsheet (as explained in another post):

After choosing Open you click on Properties:

Check the 'Fill down formulas:'

Excel will bring the List View data into the selected sheet.
The VBA code to bring the iSeries (AS400) data:
You first need to ensure that you have the appropriate ODBC driver for the environment where your data resides. If it is missing, you will need to get it from the Internet and install.
In Windows XP:
Start / Control Panel / Performance and maintenance / Administrative Tools / Data Sources (ODBC)

Adjust the following VBA code:
Sub GetJDEData()

' Clean 5000 rows in target area to make ready for a new retrieve
' Leave title in first row

' Declare variables
Dim MyDatabase As ADODB.Connection
Dim MyCommand As ADODB.Command
Dim MyRecordset As ADODB.Recordset
Dim Column As Long

' Open database connection
Set MyDatabase = New ADODB.Connection
MyDatabase.CursorLocation = adUseClient
MyDatabase.Open "Provider=IBMDA400.DataSource.1;Data Source=[ReplaceWithAS400ServerURLorIPaddress];Persist Security Info=True;User ID=[ReplaceWithAS400User];Password=[ReplaceWithPassword];Initial Catalog=[e.g.: S1030182];;Force Translate=0;"
' Build a custom query using command object
' and filter only the selected year in the function: RequestedYear
' This function takes its value from cell C2 in Admin sheet (see at the bottom).
Set MyRecordset = New ADODB.Recordset
Set MyCommand = New ADODB.Command

With MyCommand
Set .ActiveConnection = MyDatabase
.CommandType = adCmdText
.CommandText = "SELECT * "
.CommandText = .CommandText & " From NECPRDDTA.F553104"
.CommandText = .CommandText & " WHERE (wYEAR = " & RequestedYear() & ") “
.CommandText = .CommandText & " ORDER BY wWeek DESC, wMachine"

End With

MyRecordset.Open MyCommand, , adOpenDynamic, adLockReadOnly

' Test for no records
If MyRecordset.BOF And MyRecordset.EOF Then
MsgBox "No records found"


If MyRecordset.RecordCount > 0 Then
Sheets("Data-Prod").[A2].CopyFromRecordset MyRecordset
MsgBox "Production Data have been Refreshed "


End If
End If

End Sub

Function RequestedYear()
RequestedYear = Range("Admin!C2").Value
End Function

After running the code, froma button for example, you have the other sheet with refreshed data.

Create the Pivot Table

I assume that you know how to apply formulas and use lookup as well as name a range and create a pivot table.

The end result may look like this:

Monday, July 27, 2009

Using Excel in Multi-User Applications - II

In a previous post I suggested the option of using Excel together with SharePoint. I will demonstrate this in a simple scenario, probably known to SharePoint users.
For this solution your organization should have the free WSS 3.0 version (as a part of Server 2003) or the non-free product MOSS (Microsoft Office SharePoint Server 2007).
Let's assume that you work in a multi-plant manufacturing environment. The plants assign people to track their daily waste data in a SharePoint list while you want to create a Pivot Table or chart to show patterns.
The list may have the following columns:
  • Plant (Lansing, Detroit)
  • Machine-Type (Printer-A, Printer-B)
  • Date
  • Waste-Lb
Any SharePoint List lets you export its data to Excel (2003 or later).
  1. Navigate to the list
  2. In Actions, select Export to Spreadsheet.
  3. In the Pop up window click Open.
    -For Excel 2007 click Enable in the Security Notice window.
    -You may need to enter your user and password which should have permissions to the list.
  4. The list will open in your default Excel program.
    -from here you can name the list and refer to it from a Pivot Table, Chart, or any other model.
  5. To refresh the list with the up-to-date data just select any List's cell and click the 'Refresh' icon in Excel 2007 or right-click the cell, select List / Discard changes and Refresh List.
Any time you refresh the list, the name you assigned will be correctly adjusted to the new range. Upon refreshing the a Pivot Table for example, you will view the new numbers.

Wednesday, July 22, 2009

New Lines within Excel Cells

In MS Office, (Word for example), you can use the Shift + Enter to create a new line without breaking the paragraph or a cell in a table. In Excel this doesn't work as it takes you to the next cell.

To Achieve this goal in Excel:
  1. Type something within a cell to the point where you need a line break.
  2. Without exiting the cell, type: Alt + Enter.
    This will position the mouse in the next line within the cell.

Tuesday, July 14, 2009

Update SharePoint Lists from Excel 2007

Unlike Excel 2003, the 2007 version doesn’t let you update a list in Excel and then synchronize it with its linked list in SharePoint.

Microsoft developed a work around which requires the installation of an Excel add-in.

Comments: The document should be saved as XLS file rather than in 2007 format: XLSX.
You can read the full document in MSDN:
  1. Download Excel 2007 Add-in: Synchronizing Tables with SharePoint Lists.
  2. In Excel, Click the Microsoft Office Button, click Excel Options, and then click the Add-Ins tab.
  3. Select Excel Add-Ins in the Manage drop-down list and click Go.
  4. In the Add-Ins dialogue box, click Browse, navigate to the Excel 2007 SharePoint List Synchronizing Add-ins, and then click OK.
  5. In the Add-Ins dialogue box, verify that the SynchronizeWSSandExcel option is selected and then click OK.

The Add-Ins dialogue with the Rwsplist option selected

Right click any item in a SharePoint list in Excel.You will notice that the Synchronization option is now available as in Excel 2003.

Sunday, July 12, 2009

Using Excel in Multi-User Applications (or Excel - SharePoint Collaboration)

Excel is a great tool when it comes to a one-man-band show. However, when you need to collaborate with others, especially when the source data is continuously changed or provided by others, there are versions and synchronization issues that require extra care.

Many Excel advanced users are not aware that one of the main objectives of SharePoint from Microsoft is to address exactly these issues and does it extremely well for Excel.

For example, you can post the latest version of an Excel document to a SharePoint library with an automated email sent to interested parties who will continue working on the document with 'Check-in, Check-out' capabilities and post it back. Thus you eliminate the 'last version' issue.

But there are even more powerful options. Let users enter data in a SharePoint web form and easily pull the data into your Excel as the basis for Pivot Tables, Charts, or models you developed.

SharePoint is pretty much known to most companies. Its base product, WSS 3.0, comes free with Windows 2003 Server or later. It is likely that your company already uses it and your IT team will be happy to create a sub-site for your needs.

In future posts, I will demonstrate how Excel works with SharePoint in a multi-user application.

Saturday, July 11, 2009

Excel Case Sensitive Lookup

In Excel, the various lookup functions are not case sensitive so you can't differentiate between Joe and joe. Once the lookup locates the first match it references to that occurrence. There is a work around to locate the exact case sensitive match.

Using a Conditional test and the Exact function can differentiate between “Joe” or “joe” as explained in Microsoft support case:

However, it will always test the first “Joe” available so you won’t be able to get to the next one.
Therefore, you will need to use the Match function and calculate a narrower range to search where the first insensitive case match has been excluded.

This solution applies to two identical insensitive rows, so if you expect more than that you will need to add more nested ifs to further narrow down the lookup ranges. You also need to specifically address the last row in the range or add a fictitious entry that has no match.

The solution has been tested on 2003 and 2007 versions.

In I1:J4 enter the lookup table

aB Abraham
Ab Aaron
Cd Daniel
Rs Kevin

In A1:C1 enter the titles:
Key Name Range
In A2 A6 enter the lookup values:
Enter the following formulas into B2:B6 and C2:C6
  • For clarity purposes, the Range Column C2:C6 shows the calculated range based on whether there is no match at all, there is a perfect match, or a narrower range is needed for an imperfect match. The formula is:
  • The Name column uses the Indirect function on the calculated range to lookup the correct range to get the name.

The result after applying the formulas:
Key Name Range
cd Missing I4:J$4
Ab Aaron I2:J$4
aB Abraham I1:J$3
Cd Daniel I3:J$3
Ed Missing Missing
Of course, you can get rid of column C and incorporate its formula within B.

Welcome Message

This blog deals with Excel and Excel collaboration with SharePoint. I post SharePoint dedicated topics on Raytech's blog: