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 (889591)

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:

Open the Web site in FrontPage 2003.
Create a new blank page.
On the Insert menu, point to Database, and then click Data View.
Click the arrow next to your survey that is listed on the right, and then click Show Data.
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.
Save the page.
On the File menu, click Preview in Browser.
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$("Please enter the number of Rating scale ranges in your survey"))

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("Sheet1").Cells(myrow, 1).Value

'Skip rows at the begin of the survey that do not have any answers
While InStr(a$, "#") = 0 And InStr(Worksheets("Sheet1").Cells(myrow, 2).Value, "#") = 0 And myrow < 10000
    myrow = myrow + 1
    a$ = Worksheets("Sheet1").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("Sheet1").Cells(myrow, surveytable).Value
    
        While a$ <> ""
            firstDelim = InStr(a$, "#")
            SecondDelim = InStr(firstDelim + 2, a$, "#")
            
            head = Left(a$, firstDelim - 1)
            myval = Mid(a$, firstDelim + 2, SecondDelim - firstDelim - 2)
            
            mycol = FindSurveyAnswerInFirstRow(surveytable, head)
            Worksheets("Sheet" & CStr(surveytable + 1)).Cells(curSurvey, mycol).Value = myval
            
            a$ = Mid(a$, SecondDelim + 1)
        Wend
    Next

    myrow = myrow + 1
    a$ = Worksheets("Sheet1").Cells(myrow, 1).Value
Wend
End Sub
 
Function FindSurveyAnswerInFirstRow(surveytable, Surveyanswer) As Integer
i = 1
While 1 = 1
    If Worksheets("Sheet" & CStr(surveytable + 1)).Cells(1, i) = Surveyanswer Then
        FindSurveyAnswerInFirstRow = i
        Exit Function
    ElseIf Worksheets("Sheet" & CStr(surveytable + 1)).Cells(1, i) = "" Then
        Worksheets("Sheet" & 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 "Sheet" in the code example must be translated to the word for "Sheet" in that language.

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 (889591)

SYMPTOMS

CAUSE

WORKAROUND

MORE INFORMATION