Saaving through Export to Excel

Posted on 2011-03-01
Last Modified: 2013-11-17
I am using a button "export to excel" to run on selected documents of the view and export them to excel and then save it to Local disk, by giving path. The problem I am facing is that the code is running fine but it si not saving the excel and instead at last a pop box from Microsoft Excel Application comes which asks do you want to save file by giving the file name as book3, whereas I have given different file name.
 I am using following code on the action button:
Mar 2, 2011
10:42:57 AM: Sathish Puvvada: Sub Click(Source As Button)
      On Error Resume Next  ' < don't like this
      Dim s As New notessession
      Dim db As notesdatabase
      Set db= s.currentdatabase
      Dim uiw As New NotesUIWorkspace
      Dim otherdoc As NotesDocument
      Dim otherview As NotesView
      Dim othercol As NotesDocumentCollection
      Dim flocation As Variant

      Dim tempdoc As notesdocument

'Work out the current view's name
      Dim uiv As notesuiview
      Set uiv = uiw.currentview

'if it is R4 then viewalias doesn't work so use 'environment variable stashed in the post open event

      If Instr(s.Notesversion, "Release 4") Then
            currentviewname = s.getenvironmentstring("CurrentView")  
            If currentviewname="" Then
                  Msgbox "Notes R4, code is not set up properly. Contact developer."
            End If
            Call s.setenvironmentvar("CurrentView","")
      Elseif uiv.viewalias <> "" Then 'use alias if it isn't blank
            currentviewname = uiv.viewalias
      Else ' use name
            currentviewname = uiv.viewname
      End If

 'Get the view
      Set otherview = db.GetView(currentviewname)
      If otherview Is Nothing Then
            Messagebox "Could not open the view. """ & currentviewname & """"
            Exit Sub
      End If

 'Check if it is for all documents or only selected
      Set othercol = db.unprocesseddocuments
      If othercol.count >1 Then 'if more than one doc selected then confirm
            resp = Messagebox("Do you want to export only the " & _
            "selected " & othercol.count & " documents?", 36, "Selected only?" )

            Messagebox "Exporting all rows. (To export only selected " & _
            "rows tick those required in the left margin first.)"
      End If  '6= yes

      'code for Disc by chaitanya

      'Call CreateExcelObject

      Dim object As NotesEmbeddedObject
      Dim xlApp As Variant
      Dim oWorkbook As Variant  

      flocation=uiw.SaveFileDialog(False, "Choose New File", "Microsoft Excel|*.xls", "c:\windows\desktop")

      Set xlApp = CreateObject("Excel.Application")
      'xlApp.Visible = True 'set to visible, this can be moved to the end if you wish

      Set oworkbook = xlApp.Workbooks 'handle to Workbook

'Stick out the column headers
      Forall c In otherview.Columns
            xlApp.cells(1,hcolmn) = c.title
      End Forall  


      If resp=6 Then 'selected documents
            Dim seldoc As notesdocument
            Set seldoc = othercol.GetFirstDocument  
            While Not seldoc Is Nothing
                  If resp=6 Then
                        Set otherdoc = otherview.getnextdocument(seldoc)
                        If otherdoc Is Nothing Then
                              Set otherdoc = otherview.getprevdocument(seldoc)
                              If otherdoc Is Nothing Then
                                    Print " >1 doc should be selected"
                                    Set otherdoc = otherview.getnextdocument(otherdoc)
                              End If
                        Else 'got next doc
                              Set otherdoc = otherview.getprevdocument(otherdoc)
                        End If        
                  End If
                  For colmn = 0 To Ubound(otherview.Columns)
                        xlApp.cells(row,colmn+1) = otherdoc.columnvalues(colmn)
                  Set seldoc = othercol.GetNextDocument(seldoc)      
      Else  ' all documents
            Set otherdoc = otherview.GetFirstDocument  
            While Not otherdoc Is Nothing  
                  For colmn = 0 To Ubound(otherview.Columns)
                        xlApp.cells(row,colmn+1) = otherdoc.columnvalues(colmn)
                  Set otherdoc = otherview.GetNextDocument(otherdoc)      
      End If

'this highlights the headings
      With xlApp.application.Selection.Font
            .bold = True
            .ColorIndex = 48
            .Name = "Arial"
            .Size = 12
      End With

'this freezes the panes
      xlApp.application.ActiveWindow.FreezePanes = True

'this autofits the columns
      Msgbox flocation(0)
End Sub
Question by:Glactus
  • 4
LVL 22

Expert Comment

ID: 35015802
Is this happening because of the word "oworkbook"?
LVL 22

Expert Comment

ID: 35015837
Isn't that supposed to go something like this:

xlApp.ActiveWorkbook.SaveAs( fileName )

See here:

Author Comment

ID: 35015875
but    Set oworkbook = xlApp.Workbooks has been declared earlier, So dont you think it is correct.
LVL 22

Expert Comment

ID: 35016239
Then not xlApp.oworkbook, but just oworkbook.
LVL 22

Accepted Solution

mbonaci earned 500 total points
ID: 35016312
xlApp.Workbooks returns Workbooks collection, not one Workbook.

So no, it's not correct.

You should use:
Set oworkbook = xlApp.Workbooks.Add
to get newly created workbook object.

See here how to use Workbooks object:

to get specific Workbook:

Here's a simple class I use as the starting point, when exporting to Excel (I added the comment for you about how to obtain reference to a newly created workbook object):
Class ExcelReport
	Private xlApp As Variant
	Private xlSheet1 As Variant
	Private xlSheet2 As Variant
	Private xlSheet3 As Variant
	Private strFilePath As String

	Sub new( xlFilename , isVisable )
		Set xlApp = CreateObject("Excel.application")
		xlApp.Workbooks.Open xlFilename

		%REM For Glactus, to add new workbook:
		Set oworkbook = xlApp.Workbooks.Add

		xlApp.Visible = isVisable
		strFilePath = xlFilename
	End Sub

	Function saveFile
		xlApp.ActiveWorkbook.SaveAs( strFilePath )
	End Function

	Function insertData( intSheet As Integer , row As Integer , column As Integer , value As String )
		xlApp.Workbooks(1).Worksheets( intSheet).Cells( row , column ).Value = value
	End Function

	Function getData( intSheet As Integer , row As Integer , column As Integer ) As String
		On Error Goto err_hdl
		getData = xlApp.Workbooks(1).Worksheets( intSheet ).Cells( row , column).Value
		Exit Function
		Print Error$ + "in cls: ExcelReport , method: getData , at line" + Cstr( Erl )
		getData = ""
		Exit Function
	End Function

	Function doQuit
		Set xlApp = Nothing
	End Function

	Function makeVisable
		xlApp.Visible = True
	End Function

End Class

'Example usage:
Sub Initialize
	Dim report as ExcelReport
	Dim iCol As Integer
	Dim iRow as Integer
	Dim strData as String
	Set report = New ExcelReport( "C:\mysheet.xls", True )
	For iRow = 1 To 10
		strData = report.getData( 1, iRow, 1 )
		If strData = "" Then
 			report.insertData( 1, iRow, 2, "Null" )
 			report.insertData( 1, iRow, 2, "Not Null" )
 		End If
 	Next iRow
 End Sub

Open in new window


Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You’ve got a lotus Domino web server, and you have been told that “leverage browser caching” is a must do. This means that we have to tell the browser everywhere in the web to use cache. In other words, we set (and send) an expiration date in the HT…
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.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

813 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

11 Experts available now in Live!

Get 1:1 Help Now