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):
=MyLinkText(C2)
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:
=HYPERLINK(MyLinkText(D2),C2)
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

=SUMPRODUCT(N(WEEKDAY(ROW(INDIRECT(B10&":"&C10)))<>1))

<>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(Target,MATCH(G1,B1:B18,0),1)

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
Sheets("Data-Prod").Select
Range("A2:W5000").Select
Selection.ClearContents
Range("A1").Select

' 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"

Else

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

Sheets("Admin").Select

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: