View member feedback to this tip.

I found this to be the fastest and best way to export a view's data to Excel. It keeps the formatting specified in the view as well as columns such as total and categories. The best part is that it is super fast.
Put this code into a agent to be run manually from action menu. You can then copy it into any database from which you want to export a view.

 'Admin - New Export To Excel Script: 
 Dim db    As NotesDatabase
 Dim view   As NotesView
 Dim doc    As NotesDocument
 Dim tmpCount  As Integer
 Dim nmore, nc, nchar 
 Dim session   As New NotesSession
 Dim workspace  As New NotesUIWorkspace
 Dim strURL   As String
 
 Set UIview   = workspace.CurrentView
 Set db    = session.CurrentDatabase
 UIViewname   = UIView.ViewName
 UIViewAlias   = UIView.Viewalias
 Set view    = db.GetView( UIViewName )
 filePat    = ReplaceSubstring(db.FilePath, "", "/")
 
 'Get the common database name
 Dim nm As notesname
 Set nm = New notesname(db.server)
 
 'Get the view aliases
 If Not Isempty(view.Aliases) Then
  Forall aliass In view.Aliases
   strViewAlias = aliass
   Exit Forall
  End Forall
 Else
  Msgbox "No view aliases specified"
  Exit Function
 End If

 
 'Build the URL string
 strURL = "URL;http://" & nm.common 
& "/" & filepat & "/"
 & strViewAlias
 & "?openView"
 strURL1 = strViewAlias & "?openView"
 
     'Launch Excel and open it in the UI
 Set excelAppObject   = 
CreateObject("Excel.Application")

 excelAppObject.Visible  = True
 Call excelAppObject.Workbooks.Add
 Set excelWorksheetObject = 
excelAppObject.ActiveSheet
 
' With ActiveSheet.QueryTables.Add
(Connection:= "URL;http://lon-db-
03/epsreq.nsf/1.%20Authorisation%20
Views%5Cc)%20All%20by%20Status?
Open View" , Destination:=Range("A1"))
 With excelWorksheetObject.
QueryTables.Add( strURL , 
excelWorksheetObject.Range("A1"))
  .Name =    strURL1  '"1.%20Authorisation%
20Views%5Cc)%20All%20by%20Status?OpenView"
  .FieldNames = True
  .RowNumbers = False
  .FillAdjacentFormulas = False
  .PreserveFormatting = False
  .RefreshOnFileOpen = False
  .BackgroundQuery = True
  .RefreshStyle = xlInsertDeleteCells
  .SavePassword = False
  .SaveData = True
  .AdjustColumnWidth = True
  .RefreshPeriod = 0
  .WebSelectionType = xlSpecifiedTables
  .WebFormatting = xlWebFormattingAll
  .WebTables = "2"
  .WebPreFormattedTextToColumns = True
  .WebConsecutiveDelimitersAsOne = True
  .WebSingleBlockTextImport = False
  .WebDisableDateRecognition = False
  .WebDisableRedirections = False
  .Refresh 'BackgroundQuery:=False
 End With
 
 excelAppObject.Visible = False
 Messagebox "Export to Excel finished!", 64, db.title
 excelAppObject.Visible = True
 Exit Function
 
 
End Function

The replacesubstring function was copied from Ultimate Replacesubstring Function on SearchDomino.com. Thanks to its developer, Scott Bockelman.

Function ReplaceSubstring (fullString 
As String, oldString As String, 
newString As String) As String
 lenOldString = Len(oldString) 
 position = Instr (fullString, oldString) 
 Do While position > 0 And 
oldString <> ""
  fullString = Left(fullString, position -1) 
& newString & Mid (fullString, 
position + lenOldString) 
  position = Instr (position + Len(newString), 
fullString, oldString) 
 Loop
 ReplaceSubstring = fullString
End Function

MEMBER FEEDBACK TO THIS TIP

This agent does not work as advertised. When run from the Web, it does nothing. When run from the Notes client, it creates a spreadsheet, but the spreadsheet only includes the view column headings. It does not include the view data.

-- Tony G.

********************************************************

It has potential to be a great tip, but unfortunately has these problems:

1. Says "via Web" but appears to be a LotusScript agent to be run from Notes client (am I missing something)?
2. Does not work as-is: for example, it has "Exit Function" statement without a beginning Function statement.
3. Generates error "WEBREDIRECT not valid" and I'm using Excel 2000. Excel version (required, or at least what the author uses) should be indicated.

-- Samir

********************************************************

Unless I'm missing something, I don't understand how this agent can work via the Web. The UI classes are not supported there, not to mention that there is no view context when running an agent from the Web. It does look like it would work fine run from the Notes client. Perhaps the title of the article is just incorrect.

-- Scott D.

********************************************************

Looking at my tip again, I found out that I forgot to mention that the database needs to be on server that has an HTTP task running on it. Also, "via the Web" in the name doesn't mean it is a Web agent -- sorry for the confusion! It is a Notes-based agent that uses an HTTP task to export data to Excel.

Sorry for the trouble.

-- Amit Kamat

********************************************************

Not as useful as the Export to Excel code I found elsewhere. (Most likely the Sandbox), which can export any view to any Excel spreadsheet three different ways. All by view, selected in the view and custom by fields on the form. I put this code into every custom database I create because it never fails, the user wants to sort it their own way.

Program was developed by Greg Vandeligt. -- Leslie M.

Do you have comments of your own? Let us know.

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.