Solved

Saaving through Export to Excel

Posted on 2011-03-01
5
1,012 Views
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
            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?" )
      Else

            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
      oworkbook.Add

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

      row=2

      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"
                                    End
                              Else
                                    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)
                  Next  
                  row=row+1    
                  Set seldoc = othercol.GetNextDocument(seldoc)      
            Wend
      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)
                  Next  
                  row=row+1  
                  Set otherdoc = otherview.GetNextDocument(otherdoc)      
            Wend
      End If

'this highlights the headings
      xlApp.application.Rows("1:1").Select
      With xlApp.application.Selection.Font
            .bold = True
            .ColorIndex = 48
            .Name = "Arial"
            .Size = 12
      End With

'this freezes the panes
      xlApp.application.Rows("2:2").Select
      xlApp.application.ActiveWindow.FreezePanes = True

'this autofits the columns
      xlApp.cells.select
      xlApp.selection.Columns.AutoFit
      xlApp.application.rows("1:1").Select
      Msgbox flocation(0)
      xlApp.oworkbook.save(flocation(0))
      xlapp.quit
End Sub
 
0
Comment
Question by:Glactus
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 
LVL 22

Expert Comment

by:mbonaci
ID: 35015802
Is this happening because of the word "oworkbook"?

xlApp.oworkbook.save(flocation(0))
0
 
LVL 22

Expert Comment

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

xlApp.ActiveWorkbook.SaveAs( fileName )

See here:
http://www.mrexcel.com/forum/showthread.php?t=417770
0
 

Author Comment

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

Expert Comment

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

Accepted Solution

by:
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:
http://msdn.microsoft.com/en-us/library/aa224505(v=office.11).aspx

to get specific Workbook:
http://msdn.microsoft.com/en-us/library/aa224504(v=office.11).aspx

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
		%ENDREM

		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
	err_hdl:
		Print Error$ + "in cls: ExcelReport , method: getData , at line" + Cstr( Erl )
		getData = ""
		Exit Function
	End Function

	Function doQuit
		xlApp.Quit
		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" )
 		Else
 			report.insertData( 1, iRow, 2, "Not Null" )
 		End If
 	Next iRow
 	report.saveFile
 	report.doQuit
 End Sub

Open in new window

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Lotus Notes Views display column from text field in computed subform. 4 338
Add a cc field to email code 3 123
IBM Lotus notes 9 102
@Mailsend 3 77
For Desktop Techs: How to retain a user's Notes configuration data when swapping out the end user's computer. (Assuming that you are not upgrading to a completely different version of Notes client) All you need to do is: 1) install Notes o…
I thought it will be a good idea to make a post as it will help in case someone else faces these issues. I trust this gives an idea how each entry in Notes.ini can mean a lot for the Domino Server to be functioning properly. This article discusses t…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

740 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