Excel Sheet Creation

HCL Domino Domino Forum
1

Hi,

I need to write a code that extracts the contents of a view to an Excel sheet, with the columns of the sheet being in bold text. Can anyone point me to a code that can be written for doing this?

Thanks

2

Subject: Excel Sheet Creation

Following should be a good starting point. Use from an action in the view.

Sub Click(Source As Button)

Dim ws As New NotesUIWorkspace

Dim UIView As NotesUIView

Dim currView As NotesView

Dim currDoc As NotesDocument

Dim xlApp As Variant, xlSheet As Variant

Dim rowcount As Integer



Set UIView=ws.CurrentView

Set currView=UIView.View

Set currDoc=currView.GetFirstDocument

If Not currDoc Is Nothing Then

'set application object

	Set xlApp = CreateObject( "Excel.Application" )

	xlApp.Visible = False

	xlApp.workbooks.add

	Set xlSheet = xlApp.workbooks(1).worksheets(1)

	rowcount=1

	Do While Not currDoc Is Nothing

		xlSheet.Cells(rowCount,1).Value=currDoc.Field1(0)

		xlSheet.Cells(rowCount,2).Value=currDoc.Field2(0)

'and so on for all the fields you want to export

		rowCount=rowCount+1

		Set currDoc=currView.GetNextDocument(currDoc)

	Loop

	xlApp.ActiveWorkbook.SaveAs "WHATEVER_YOU_WANT_TO_CALL_YOUR_FILE.xls"

	xlApp.ActiveWorkbook.Close

	xlApp.Quit

End If

End Sub

hth

Pete

3

Subject: RE: Excel Sheet Creation

Thanks for your inputs Pete. It worked wonderfully.

Just wanted to know if there is a way that I can define the particular cell in the Excel sheet to have Wrap Text enabled?

4

Subject: RE: Excel Sheet Creation

Hi. Sorry for delay. Have been busy with other things.

Try xlSheet.Cells(row, col).WrapText=True where row is the row number and col is the column number.

btw, an easy way to find the commands is to open Excel, start macro recording, do whatever it is you want to do via the menus then look at the code of the macro. Usually if you put the code thats been generated after the reference to xlSheet it will work. It’s not always perfect but usually gives you a good starting point.

Good luck

Pete