Export to Excel - Save to File

Posted on 2006-05-04
Last Modified: 2013-12-18
How can the below script be modified?  I want it to automatically name the excel file to SoExport.xls and save it to the f:\cabinets directory overwriting any previous copies.

'Export so to Excel:

'Export to Excel|expexcel:

'Export to Excel v2:

Option Public
This Script has been created by D. Hasa, Yel GmbH, Switzerland  in April 2001

It may be distributed and modified freely, as long as this header is kept intact.

Please report any bugs, fixes or enhancements to

This script exports a UIView 'As-Is' from Notes 5 to Excel 2000
It has been tested with Notes 5.03/5.05/5.08 into Excel97 & 2000

--> every column (include headers) is a column in Excel
     and every value displayed of a document is a row in Excel
Every Value will be inserted as Text into Excel
You can now export also only selected documents, but the script gets thru all documents in a view, because the the property doc.ColumnValues(n) only returns a value if it has been fetched from a view (selected documents get fetched by a NotesDocumentCollection).
Excel-Object Problems
Added another ExcelApp-Constant (Excel.Application.8)
Visualised Progress
This script is From
Thanks to Les Szklanny
--> I cannot give you any  guaranty of proper functionality you can turn it on or of --> const visualproc
It is only a script without any Dialog-Boxes by exception --> Distribution and Implementation is very easy

Simply copy this whole file into an Agent:
Name:    Export to Excel
Run:     Manually from Actions Menu
act on:       Selected documents in View
Run:     Lotus Script

--> Export works in any View/Folder of that database

Const visualproc = True 'Display VisualProgress true = yes /false = no
Const AppConst = "Excel.Application"
Const AppConst2 = "Excel.Application.8"
Const NPB_TWOLINE% = 1 '1 is for the big "in its window" progress bar and 32 is for the small blue line at the bottom of the screen

' Procedures in nnotesws.dll (undocumented!!).

Dim db As NotesDatabase
Dim view As NotesView
Dim doc As NotesDocument
Dim nc, nl, nmore
Dim selList(0 To 20) As String
Dim vcol List As String
Dim excelAppObject As Variant
Declare Function NEMProgressBegin Lib "nnotesws.dll" ( Byval wFlags As Integer ) As Long
Declare Sub NEMProgressEnd Lib "nnotesws.dll" ( Byval hwnd As Long )
Declare Sub NEMProgressSetBarPos Lib "nnotesws.dll" ( Byval hwnd As Long, Byval dwPos As Long)
Declare Sub NEMProgressSetBarRange Lib "nnotesws.dll" ( Byval hwnd As Long, Byval dwMax As Long )
Declare Sub NEMProgressSetText Lib "nnotesws.dll" ( Byval hwnd As Long, Byval pcszLine1 As String, _
Byval pcszLine2 As String )
Class ProgressBar
' Objects
      Private hwnd As Long
' Constructor.
      Sub New (BarRange As Long)
            On Error Goto ErrorHandler
' Create the progress bar.
            Me.hwnd = NEMProgressBegin (NPB_TWOLINE)
' Set the bar range.
            Call NEMProgressSetBarRange (Me.hwnd, BarRange)
            Exit Sub
            Dim TheError As String
            TheError = "Constructor: Error " + Str(Err) + ": " + Error$
            Messagebox TheError, 0 + 48, "Progress Bar Error"
      End Sub
' Destructor.
      Sub Delete
' Destroy the progress bar.
            Call NEMProgressEnd (Me.hwnd)
      End Sub
      Public Sub UpdatePosition (BarPos As Long)
' Update the bar position.
            Call NEMProgressSetBarPos (Me.hwnd, BarPos)
      End Sub
      Public Sub UpdateProgressText (BarMsg As String, UpdateMsg As String)
' Update progress text.
            Call NEMProgressSetText (Me.hwnd, BarMsg, UpdateMsg)
      End Sub
End Class

Sub Initialize
      On Error Goto ExitExcel
'Main Code
      Dim session As New NotesSession
      Dim workspace As New NotesUIWorkspace
      Dim UIview As NotesUIView
      Dim collection As NotesDocumentCollection      
      Dim coldoc As NotesDocument
      Dim BarMsg As String, UpdateMsg As String
      Dim countall As Long, countthis As Long, countallsel As Long, countthissel As Long
      Dim NChar As String
      Set UIview = workspace.CurrentView
      Set db = session.CurrentDatabase
      UIViewname = UIView.ViewName
      UIViewAlias = UIView.Viewalias
      Set view = db.GetView( UIViewName )
      Set collection = db.UnprocessedDocuments
      gowithselection = False
      goonall = True
      Set collection = Nothing
      BarMsg = "Exporting documents ..."
      Call SetSelList()
      SelForm = "Simple"
      TitleBar = "0"
      If Titlebar > 90 Then
            TitleBar = 90
      Elseif TitleBar < -90 Then
            TitleBar = -90
      End If
      SelAutoForm = getAutoForm( selForm )
'Launch Excel and open it in the UI
      Set excelAppObject = CreateObject( AppConst )
      'Try other AppConst
      If excelAppObject Is Nothing Then
            Set excelAppObject = CreateObject( AppConst2 )
            If excelAppObject Is Nothing Then
                  Msgbox "Could not create an Excel Object"
                  Exit Sub
            End If
      End If
      excelAppObject.Visible = False
      Call excelAppObject.Workbooks.Add
      Set excelWorksheetObject = excelAppObject.ActiveSheet
'Add the table labels
      Forall c In view.Columns
            'do not export hidden columns or those with fixed vals (not displayed as doc.columnvalues!!!!)
            If Not c.ishidden And Not c.IsIcon And Not (c.IsFormula And Not Instr(1, c.Formula, "@") And Not Instr(1, c.Formula, "+")) Then
                  nchar = countcol(nChar)
                  excelWorksheetObject.Range( nchar + "1").Value = c.Title
            End If
      End Forall
      m_let = nchar
      'Export Documents
      Set doc = view.GetFirstDocument
      If gowithselection Then countall = countallsel Else countall = view.AllEntries.Count
      countthis = 0
      countthissel = 0
      If visualProc Then Dim RefreshProgress As New ProgressBar (countall) 'display the ProcessWindow/Bar
      While Not ( doc Is Nothing Or exitnow)
            countthis = countthis + 1
            If gowithselection Then
                  Set coldoc = Nothing
                  Set coldoc = collection.GetDocument(doc)
                  If Not coldoc Is Nothing Then 'Exports only if doc is part of collection
                        Call ExportDoc(excelWorksheetObject)
                        countthissel = countthissel + 1
                  End If
                  If visualproc Then
                        UpdateMsg = "Exporting document " + Cstr(countthissel) + " of " + Cstr(countall) + Chr$(13) + "Processing Doc in View: " + Cstr(countthis)                        
                        Call RefreshProgress.UpdatePosition (countthissel)
                        Print "Exporting document " + Cstr(countthissel) + " of " + Cstr(countall) + " / " + "Processing Doc in View: " + Cstr(countthis)
                  End If
                  Call ExportDoc(excelWorksheetObject)
                  UpdateMsg = "Exporting document " + Cstr(countthis) + " of " + Cstr(countall)
                  If visualproc Then
                        Call RefreshProgress.UpdatePosition (countthis)
                        Print UpdateMsg
                  End If
            End If
            If visualproc Then Call RefreshProgress.UpdateProgressText (BarMsg, UpdateMsg)
            If countall = countthissel Then exitnow = True  'Exit routine
            Set doc = view.GetNextDocument(doc)
 'formatting the Worksheet
      If doformat = 6 Then
            BarMsg = "One moment please..."
            UpdateMsg = "Formatting the document..."
            If visualproc Then Call RefreshProgress.UpdateProgressText (BarMsg, UpdateMsg) Else Print Updatemsg
            excelWorksheetObject.Range("A2:" + m_let + Cstr(nl) ).Select
            excelWorksheetObject.Range("A1:" + m_let + Cstr(nl)).Select
            With excelAppObject.Selection
                  .AutoFormat SelAutoForm, False, True, False, True, True, False
                  .VerticalAlignment = -4160
            End With
            With excelAppObject.Selection
                  .VerticalAlignment = -4107
                  .HorizontalAlignment = -4108
                  .WrapText = True
                  .Orientation = Cint(titlebar)
                  .ShrinkToFit = False
                  .MergeCells = False
                  RowHeight = 215
            End With
            excelWorksheetObject.Range("A:" + m_let).Select
            With excelAppObject.Selection.Font
                  .Name = "Arial"
                  .Size = 10
            End With
            With excelAppObject.Windows(1)    
            End With  
            With excelWorksheetObject.PageSetup
                  .Orientation = 2
                  .LeftHeader = "&""Arial,Bold""&18"+db.Title+" - "+ UIViewAlias
                  .CenterHeader = ""
                  .RightHeader = "Datum: &D"
                  .LeftFooter = ""
                  .CenterFooter = ""
                  .RightFooter = "Seite &P"
                  .PrintArea = ("A1:"+ m_let + Cstr(nl))              
                  .PaperSize = 9
                  .CenterHorizontally = True          
                  .FitToPagesTall =False
                  .zoom = False
            End With          
      End If
      excelAppObject.Visible = True
      Exit Sub
      Print "Error in Line " + Cstr(Erl) + " : " + Cstr(Error)
      excelAppObject.DisplayAlerts = False
      Exit Sub
End Sub
Function countcol( nChar As String)
      If nc=91 Then
            nmore = nmore+1 'PreChar = Axx (AC23)
            nc=65 'reset to A
      End If
      If nmore > 0 Then
            nchar = Cstr(Chr(nc))      
      End If
      countcol = nchar
End Function
Function getAutoForm( selForm) As Integer
      Select Case SelForm
      Case "Simple"
            SelAutoForm = -4154
      Case "Classic1"
            SelAutoForm =1
      Case "Classic2"
            SelAutoForm =2
      Case "Classic3"
            SelAutoForm =3
      Case "Accounting1"
            SelAutoForm =4
      Case "Accounting2"
            SelAutoForm =5
      Case "Accounting3"
            SelAutoForm =6
      Case "Color1"
            SelAutoForm =7
      Case "Color2"
            SelAutoForm =8
      Case "Color3"            
            SelAutoForm =9
      Case "List1"
            SelAutoForm =10
      Case "List2"
            SelAutoForm =11
      Case "List3"
            SelAutoForm =12
      Case "D3Effects1"
            SelAutoForm =13
      Case "D3Effects2"
            SelAutoForm =14
      Case "Format1"
            SelAutoForm =15
      Case "Format2"
            SelAutoForm =16
      Case "Accounting4"
            SelAutoForm =17
      Case "Format3"
            SelAutoForm =19
      Case "Format4"
            SelAutoForm =20
      Case Else
            SelAutoForm =-4142
      End Select
      GetAutoForm = SelAutoForm      
End Function
Sub SetSelList()
      SelList(0) = "Simple"
      SelList(1) = "Classic1"
      SelList(2) = "Classic2"      
      SelList(3) = "Classic3"      
      SelList(4) = "Accounting1"
      SelList(5) = "Accounting2"
      SelList(6) = "Accounting3"
      SelList(7) = "Accounting4"
      SelList(8) = "Color1"
      SelList(9) = "Color2"
      SelList(10) = "Color3"
      SelList(11) = "List1"
      SelList(12) = "List2"
      SelList(13) = "List3"
      SelList(14) = "D3Effects1"
      SelList(15) = "D3Effects2"
      SelList(16) = "Format1"
      SelList(17) = "Format2"
      SelList(18) = "Format3"
      SelList(19) = "Format4"
      SelList(20) = "None"      
End Sub
Sub ExportDoc(excelWorksheetObject)
      On Error Goto ErrorEntry
      Dim nChar As String
      nl= nl+1
      ocount = 0
      Forall c In view.Columns
            If Not c.ishidden And Not c.isicon Then 'do not export hidden columns!
                  nchar = countcol(nChar)
                  With excelWorksheetObject.Range(nchar + Cstr(nl))
                        .NumberFormat = "@"                  
                        .Value = doc.ColumnValues(ocount)
                  End With
            End If
      End Forall
      Exit Sub
      With excelWorksheetObject.Range(nchar + Cstr(nl))
            .NumberFormat = "@"                  
            .Value = "ERROR: WRONG VALUE"
      End With
      Resume Next
End Sub

Question by:schmad01
    LVL 63

    Accepted Solution

    This is more an Excel question since most of the code is Excel VBA via an Excel Object

    something on the order of

     ActiveWorkbook.SaveAs Filename:= _
            "f:\cabinets\soExport.XLS", FileFormat _
            :=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
            False, CreateBackup:=False

    should do it.

    I would post this in the MS Office TA if you can not get a full answer here.

    I hope this helps !
    LVL 63

    Expert Comment

    Place it in the Sub ExportDoc(excelWorksheetObject)
    Before the End SUb line

    With excelWorksheetObject.ActiveWorkbook

    .SaveAs Filename:= _
            "f:\cabinets\soExport.XLS", FileFormat _
            :=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
            False, CreateBackup:=False
    End With
    LVL 18

    Assisted Solution

    I'm thinking that that won't work because you would first have to delete or rename the existing file.  If this is a scheduled or automatic agent, then the first thing is to see if the file exists, and rename it so that you can export the current stuff to the same filename.

    Overwriting is never a good idea, because if there are problems, you can't debug them.  Also, there are going to be times when the server goes down, and you will skip days, overwrite, etc.

    So, at agent open, check to see if file exists.  If it does, then move it - or if you have another operation acting on the file once it's placed in the folder, when that operation is done, then move it.

    If the export function finds a file, then you know the previous operation didn't finish, and to abort the current one.

    Other than that,

    ActiveWorkbook.SaveCopyAs "C:\TEMP\XXXX.XLS"
    Workbook Object:
    expression.SaveAs(FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AddToMru, TextCodepage, TextVisualLayout, Local)

    Worksheet object:
    expression.SaveAs(FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local)


    NewBook.SaveAs Filename:=fName  (simple version of sysExpert's post)  :))

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    IBM Notes offer Encryption feature using which the user can secure its NSF emails or entire database easily. In this section we will discuss about the process to Encrypt Incoming and Outgoing Mails in depth.
    Notes Document Link used by IBM Notes is a link file which aids in the sharing of links to documents in email and webpages. The posts describe the importance and steps to create a Lotus Notes NDL file in brief.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now