locked
Export Modified Date to Excel?

    Pergunta

  • Is there a way to export the Modified Time and Date to Excel from a SharePoint Survey Response List?

    SharePoint / Excel 2003  only.

    Also have Microsoft Access 2003, but have not used it yet. 
    terça-feira, 5 de agosto de 2008 15:54

Respostas

  • Hi:
    I saw a thread you posted here for your reference:

    http://forums.technet.microsoft.com/en-US/sharepointadmin/thread/bc6de61d-8c29-4d84-b890-56bf8c1bcaa9/

     

     

    Because SharePoint use Microsoft Office Excel Web Query File (.iqy) to maintain the connection between SharePoint List and Excel. You can modify the connection properties to display different views of a list and export the data you want.

     

    Here are the detailed steps about your issue:

    1.     Add a web part which refers to your survey.

    2.     Modify the web part, and change the Selected View to All Responses.

    3.     Click Edit the current view, check the column you want to export.

    4.     In the URL box of IE, you will find the View GUID in the URL.

     

          in my test, it looks like:

    http://yoursite/_layouts/viewedit.aspx?List=%7B6B5B2D7B%2DD5C0%2D4CFF%2DAB8D%2D506EAC3CE85E%7D&View=%7B95A38984%2D1F14%2D4DA0%2DA282%2DEF6556E0F091%7D&Source=http%3A%2F%2F146189m1%2Fsurvey%2Fdefault%2Easpx%3FPageView%3DShared

     

    In this sample, the View GUID is {95A38984-1F14-4DA0-A282-EF6556E0F091}.

    (The relationships between symbols and their meanings: %7B = {; %7D = }; %2D = - )

     

    5.     Click the Export to Spreadsheet in Action menu of the survey web part and open the ovssrv.iqy in the Excel.

    6.     In the Definition tab of Connection properties, replace the VIEWGUID with the GUID you got from Step 4. Save and exit.

     

    7.     If you don't know how to operate with Excel GUI, you can click the Export to Spreadsheet in Action menu of the survey web part and save the ovssrv.iqy to the local drive.

    8.     Open the owssrv.iqy with Notepad and replace the GUID with the GUID you got in Step 4. In my test, it looks like below:

     

    WEB  
    1  
    http://site/_vti_bin/owssvr.dll?XMLDATA=1&List={6B5B2D7B-D5C0-4CFF-AB8D-506EAC3CE85E}&View={95A38984-1F14-4DA0-A282-EF6556E0F091}&RowLimit=0&RootFolder=%2fsurvey%2fLists%2f3630115Survey  
     
    Selection={6B5B2D7B-D5C0-4CFF-AB8D-506EAC3CE85E}-{95A38984-1F14-4DA0-A282-EF6556E0F091}EditWebPage=  
    Formatting=None  
    PreFormattedTextToColumns=True 
    ConsecutiveDelimitersAsOne=True 
    SingleBlockTextImport=False 
    DisableDateRecognition=False 
    DisableRedirections=False 
    SharePointApplication=http://site/_vti_bin  
    SharePointListView={95A38984-1F14-4DA0-A282-EF6556E0F091}  
    SharePointListName={6B5B2D7B-D5C0-4CFF-AB8D-506EAC3CE85E}  
    RootFolder=/survey/Lists/3630115Survey  
     

     

     

    Hope the information can be helpful.

    -lambert

    quinta-feira, 7 de agosto de 2008 07:29

Todas as Respostas

  • Hi:
    I saw a thread you posted here for your reference:

    http://forums.technet.microsoft.com/en-US/sharepointadmin/thread/bc6de61d-8c29-4d84-b890-56bf8c1bcaa9/

     

     

    Because SharePoint use Microsoft Office Excel Web Query File (.iqy) to maintain the connection between SharePoint List and Excel. You can modify the connection properties to display different views of a list and export the data you want.

     

    Here are the detailed steps about your issue:

    1.     Add a web part which refers to your survey.

    2.     Modify the web part, and change the Selected View to All Responses.

    3.     Click Edit the current view, check the column you want to export.

    4.     In the URL box of IE, you will find the View GUID in the URL.

     

          in my test, it looks like:

    http://yoursite/_layouts/viewedit.aspx?List=%7B6B5B2D7B%2DD5C0%2D4CFF%2DAB8D%2D506EAC3CE85E%7D&View=%7B95A38984%2D1F14%2D4DA0%2DA282%2DEF6556E0F091%7D&Source=http%3A%2F%2F146189m1%2Fsurvey%2Fdefault%2Easpx%3FPageView%3DShared

     

    In this sample, the View GUID is {95A38984-1F14-4DA0-A282-EF6556E0F091}.

    (The relationships between symbols and their meanings: %7B = {; %7D = }; %2D = - )

     

    5.     Click the Export to Spreadsheet in Action menu of the survey web part and open the ovssrv.iqy in the Excel.

    6.     In the Definition tab of Connection properties, replace the VIEWGUID with the GUID you got from Step 4. Save and exit.

     

    7.     If you don't know how to operate with Excel GUI, you can click the Export to Spreadsheet in Action menu of the survey web part and save the ovssrv.iqy to the local drive.

    8.     Open the owssrv.iqy with Notepad and replace the GUID with the GUID you got in Step 4. In my test, it looks like below:

     

    WEB  
    1  
    http://site/_vti_bin/owssvr.dll?XMLDATA=1&List={6B5B2D7B-D5C0-4CFF-AB8D-506EAC3CE85E}&View={95A38984-1F14-4DA0-A282-EF6556E0F091}&RowLimit=0&RootFolder=%2fsurvey%2fLists%2f3630115Survey  
     
    Selection={6B5B2D7B-D5C0-4CFF-AB8D-506EAC3CE85E}-{95A38984-1F14-4DA0-A282-EF6556E0F091}EditWebPage=  
    Formatting=None  
    PreFormattedTextToColumns=True 
    ConsecutiveDelimitersAsOne=True 
    SingleBlockTextImport=False 
    DisableDateRecognition=False 
    DisableRedirections=False 
    SharePointApplication=http://site/_vti_bin  
    SharePointListView={95A38984-1F14-4DA0-A282-EF6556E0F091}  
    SharePointListName={6B5B2D7B-D5C0-4CFF-AB8D-506EAC3CE85E}  
    RootFolder=/survey/Lists/3630115Survey  
     

     

     

    Hope the information can be helpful.

    -lambert

    quinta-feira, 7 de agosto de 2008 07:29
  • Im thinking that this is the answer...

    EDIT: Deleted my stupidity. Working on  the .iqy file right now....

    Thanks.
    quinta-feira, 7 de agosto de 2008 16:30
  • Sweet it worked! Thanks a ton. I would have never been able to figure this out on my own.
    quinta-feira, 7 de agosto de 2008 16:45