locked
Export Modified Date to Excel?

    Soru

  • 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. 
    05 Ağustos 2008 Salı 15:54

Yanıtlar

  • 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

    07 Ağustos 2008 Perşembe 07:29

Tüm Yanıtlar

  • 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

    07 Ağustos 2008 Perşembe 07:29
  • Im thinking that this is the answer...

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

    Thanks.
    07 Ağustos 2008 Perşembe 16:30
  • Sweet it worked! Thanks a ton. I would have never been able to figure this out on my own.
    07 Ağustos 2008 Perşembe 16:45