Microsoft KB Archive/889591

= Survey results in SharePoint Portal Server 2003 and in Windows SharePoint Services are not exported to an Excel workbook if the answer to a question uses a rating scale =

Article ID: 889591

Article Last Modified on 2/6/2006

-

APPLIES TO


 * Microsoft Office SharePoint Portal Server 2003
 * Microsoft Windows SharePoint Services

-





SYMPTOMS
You create a survey in a Microsoft Office SharePoint Portal Server 2003 portal site or in a Microsoft Windows SharePoint Services Web site. The survey contains a question whose answer uses a rating scale. You click Export results to spreadsheet to export survey results to Microsoft Excel.

When you view the Excel workbook, survey results that correspond to the question are not displayed in the Excel workbook. Only the ID column and the Created By column are displayed in the Excel workbook.



CAUSE
This behavior occurs because exporting data that uses a matrix data type is not supported in SharePoint Portal Server 2003 or in Windows SharePoint Services. A rating scale uses a matrix data type. Therefore, you cannot export survey results for a question whose answer uses the Rating Scale (a matrix of choices) setting to an Excel workbook. By design, SharePoint Portal Server 2003 and Windows SharePoint Services work this way.



WORKAROUND
When you export a survey from Windows SharePoint Services to Excel, rating scale information is not copied to Excel. The following workaround explains how to get the data into Excel and how to format the data in a way where the data is easier to process. Ranged type surveys do not export their data to Excel because ranged type surveys are a three-dimensional matrix. Only the first two dimensions are exported to Excel. Therefore, the data will not export as expected without additional steps. The use of various data views may help get some of the data into Excel. Follow these steps:
 * 1) Open the Web site in FrontPage 2003.
 * 2) Create a new blank page.
 * 3) On the Insert menu, point to Database, and then click Data View.
 * 4) Click the arrow next to your survey that is listed on the right, and then click Show Data.
 * 5) Press and hold CTRL, and then click all the fields that you want in the data view, such as all the questions. Then, click Insert Data View.
 * 6) Save the page.
 * 7) On the File menu, click Preview in Browser.
 * 8) Select all the text on the page, and then paste the data to an Excel worksheet.

The following is Excel Visual Basic for Applications (VBA) code that can be used to split the survey data after it is copied into separate tables in the current Excel workbook. To use this code, copy the code into a VBA module in Excel. Then, run the code.

-- Begin code

Sub SplitSurveyData

Dim NumRatingScales, curSurvey As Integer NumRatingScales = CInt(InputBox$(&quot;Please enter the number of Rating scale ranges in your survey&quot;))

For i = Worksheets.Count To NumRatingScales Worksheets.Add Next i curSurvey = 0 Dim myrange As Range Dim myrow, mycol, firstDelim, SecondDelim As Integer Dim a$

myrow = 1 mycol = 1 curSurvey = 1 ratingscale = 1 a$ = Worksheets(&quot;Sheet1&quot;).Cells(myrow, 1).Value

'Skip rows at the begin of the survey that do not have any answers While InStr(a$, &quot;#&quot;) = 0 And InStr(Worksheets(&quot;Sheet1&quot;).Cells(myrow, 2).Value, &quot;#&quot;) = 0 And myrow < 10000 myrow = myrow + 1 a$ = Worksheets(&quot;Sheet1&quot;).Cells(myrow, 1).Value Wend While myrow < 10000 'Adjust this to the max number of rows in your table curSurvey = curSurvey + 1 For surveytable = 1 To NumRatingScales mycol = 1 a$ = Worksheets(&quot;Sheet1&quot;).Cells(myrow, surveytable).Value While a$ <> &quot;&quot; firstDelim = InStr(a$, &quot;#&quot;) SecondDelim = InStr(firstDelim + 2, a$, &quot;#&quot;) head = Left(a$, firstDelim - 1) myval = Mid(a$, firstDelim + 2, SecondDelim - firstDelim - 2) mycol = FindSurveyAnswerInFirstRow(surveytable, head) Worksheets(&quot;Sheet&quot; & CStr(surveytable + 1)).Cells(curSurvey, mycol).Value = myval a$ = Mid(a$, SecondDelim + 1) Wend Next

myrow = myrow + 1 a$ = Worksheets(&quot;Sheet1&quot;).Cells(myrow, 1).Value Wend End Sub Function FindSurveyAnswerInFirstRow(surveytable, Surveyanswer) As Integer i = 1 While 1 = 1 If Worksheets(&quot;Sheet&quot; & CStr(surveytable + 1)).Cells(1, i) = Surveyanswer Then FindSurveyAnswerInFirstRow = i       Exit Function ElseIf Worksheets(&quot;Sheet&quot; & CStr(surveytable + 1)).Cells(1, i) = &quot;&quot; Then Worksheets(&quot;Sheet&quot; & CStr(surveytable + 1)).Cells(1, i) = Surveyanswer FindSurveyAnswerInFirstRow = i       Exit Function Else i = i + 1 End If Wend FindSurveryAnswer = i End Function

-- End code --

In international versions of Excel, the word &quot;Sheet&quot; in the code example must be translated to the word for &quot;Sheet&quot; in that language.



MORE INFORMATION
For more information about surveys, see the &quot;About surveys&quot; topic in SharePoint Portal Server 2003 Help or in Windows SharePoint Services Help.

Keywords: kbtshoot kbbug kbnofix KB889591

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.