Solved

Saaving through Export to Excel

Posted on 2011-03-01
5
1,006 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
  • 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Validating Email Address 13 277
Lotus Notes and SQL Server 2008, ODBC 22 1,275
Email Message Background Color 4 113
Lotus notes email code 6 104
For beginners of Lotus Notes user this is important to know about the types of files and their location supported by IBM Notes. Mostly users are unaware about how many file types are created and what their usages are. This Article is fully dedicated…
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.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

757 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

16 Experts available now in Live!

Get 1:1 Help Now