SharePoint Designer (2007) - Run reports for all sites programmatically - SharePoint Development & Administration + InfoPath

Monday, May 4, 2009

SharePoint Designer (2007) - Run reports for all sites programmatically

Ever needed to generate reports using SharePoint designer for all sites in a site collection? The following code takes a pre-pared list of sites from your site collection and programmatically runs the specified report on each.

The example used below automatically creates a ‘Customized Pages” report for every site, which is saved into a document library in the site collection. This is achieved by using Visual Basic (VB) code inside a SharePoint Designer module (Macro), to load each site and save the required reports.

The following is a full list of reports supported by SharePoint Designer. To run a different report, change the highlighted text to the type required for the report. If you require multiple types reports to be run, add a new line to save the report for each required type. To avoid report types for a single site or sub-site overwriting each other is saving multiple, I would suggest adding the name of the report type to the beginning of the filename. For more information, or specific details regarding the various reports available in SharePoint Designer 2007, please see Microsoft’s Use reports to measure site performance and usage page.

Report Types:









































1: Generate a list of all sites in a site collection

  • Start at the root/top level site of your site collection

  • Go to Site Settings – Site Actions (menu) à Site Settings à Modify all site settings

  • Under the Site Collection Administration – Select “Site Hierarchy” ( http://siteURL/_layouts/vsubwebs.aspx )

  • Copy the list of urls for each subsite

  • Paste into Excel (or similar)

  • Remove unwanted columns (only one column containing site URLs should remain)

  • Copy and paste / Save as a text document (each URL on a new line)

Note: To view a list of all sites, you must be logged in as a Site Collection Administrator.

2: Set up and run the SharePoint Designer macro

  • Copy the following code into a SharePoint Designer module (Macro)

  • Update the “filePath” variable to point to your sites list text file

  • Update the “saveLocation” variable to point to the directory to save the report


Sub CustomizedPagesReport()

'set the path to the sites list text file - (See above for instructions on creating list of all sites in a site collection)

filePath = "D:\Sites_List.txt"
saveLocation = "http://siteCollection/Documents/"

Dim sitesList() As String
intCount = 0

'Open the Sites List text file if file exists
Set filesys = CreateObject("Scripting.FileSystemObject")

If (filesys.FileExists(filePath)) Then

'Get Site names from file
Set siteListFile = filesys.OpenTextFile(filePath)

'Loop through Sites List text file
Do While Not siteListFile.AtEndOfStream

'Declare the multi-dimensional array to prepare for a new element (previous elements are preserved)
ReDim Preserve sitesList(intCount)

'Add the current url to the Sites List Array
sitesList(intCount) = siteListFile.ReadLine

intCount = intCount + 1


End If

'Loop through each site url in the Sites List array
For Each Url In sitesList

'Open the current site
Application.Webs.Open Url, , , WebOpenNoWindow

'Save the required report(s) for the current site - Currently creating "Customized Pages" reports for each site
Application.ActiveWebWindow.SaveReport WebViewGhostedPages, saveLocation & "CustomPages--" & Replace(Mid(Url, 8), "/", "-")
& ".htm", True

'Close the current Site


End Sub

Share this article:
Stumble This Delicious
submit to reddit


Unknown said...

This is cool, but I just tried it and got a syntax error at the second If...


DanielS said...

Hi jwb3 thanks for your comment, the second "IF" statement should actually be the closing if statement for the first "If".

I have edited the code in the post so it should paste into a text editor properly.

To fix yourself,

Change the line 34, 35 from:

Line 34: End
Line 35: If


Line 34: End If



Unknown said...

That worked! Thanks, Daniel!

LisaJane said...

You are a lifesaver! This is exactly what I was looking for...

Thanks for sharing!

Manjeet Singh said...

Does running these customized reports have a performance impact on the sites.

DanielS said...

Hi Manjeet,

Running any report using SPD, or any browser request for a page on a SharePoint site for that matter will have an impact on the server.

In general, using SharePoint Designer to make changes to pages on a site or running reports has minimal impact on the server. If SharePoint Designer wan't so slow when processing requests, this may be a different story.

Running the reports on all sites won't affect site performance directly, but will use overall server resources, which in turn may slow load times for any sites/pages hosted by the server.

Performing actions such as this should always be first tested in a development or test environment prior to running in a production environment, especially if you are concerned about performance impacts or a more serious error occurring.

If in a production environment, process the reports at a time when the server is under no pressure. You can monitor the server's performance while running the reports regardless. Always ensure that the entire environment is backed up before executing any major operation, although running reports using SharePoint Designer is relatively harmless.


Unknown said...

This only works for me when I choose WebViewAllFiles but I get an error on Ln43 whenever I substitute WebViewUsageSummary or WebViewMonthlyPageHits. Do you have any idea what's causing this?

Adam said...

New to all of this, but I received the following pop-up: "Compile Error: Argument not optional"

Not sure what I did wrong. :(

Unknown said...

Daniel, this report would save me a ton of time but I am receiving the following error message:

Run-time error '429':
ActiveX component can't create object

on this line:

Set filesys = CreateObject("Scripting.FileSystemObject")

Any ideas why I am receiving this error message?

Julie said...

Hi All, I am newbie to Sharepoint designer coding, can anyone suggest where to write this code in sharepoint designer? what are the exact steps?


DanielS said...

@Julie, This is done using a Macro running from SharePoint Designer, so you would need to create a new macro and past the code from the example above into the macro.

It is essential automating the process of running a separate instance of a report available from SharePoint Designer on various sites.

Please also check that you are able to successfully run the reports manually before executing from a macro to help prevent errors.

This article was originally written for SharePoint Designer 2007 and MOSS, which I haven't tried using SPD 2010.

Has anyone got this working in 2010?

DanielS said...

Steps to create a macro in SharePoint Designer:

You should test the report you wish to automate manually first by running on one or more sites ("Site" menu -> Reports).

1. Open SharePoint Designer 2007 and a SharePoint site (eg. the top level site).

2. From the "Tools" menu, select "Macros", then "Visual Basic Editor"

3. Paste the code from above into the Module that loads when the Visual Basic Editor opens.

4. Complete Step 2 from this post to configure the macro.

5. Run the macro. You can do this from the Tools -> Macros dialog, or directly from the Visual Basic Editor under the "Run" menu, which I would recommend. Using the Debug -> "Step Into" / (F8) option will allow you to step through the code line by line which may also help.


Post a Comment