Manage Learn to apply best practices and optimize your operations.

Export to Excel via the Web

A fast way to export a view's data to Excel.

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.

This was last published in September 2003

Dig Deeper on LotusScript

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchWindowsServer

Search400

  • iSeries tutorials

    Search400.com's tutorials provide in-depth information on the iSeries. Our iSeries tutorials address areas you need to know about...

  • V6R1 upgrade planning checklist

    When upgrading to V6R1, make sure your software will be supported, your programs will function and the correct PTFs have been ...

  • Connecting multiple iSeries systems through DDM

    Working with databases over multiple iSeries systems can be simple when remotely connecting logical partitions with distributed ...

SearchDataCenter

SearchExchange

SearchContentManagement

Close