How to merge columns from excel and how to set customize each columns

[lancelot]

Hi To All !

Good Day,


I have a code that will generate report from Notes to Excel but I'm
having a problem a problem in merging the column as well as the
customization of data that was being exported from excel.

here's my sample code :

Dim Session As New NotesSession
Dim db As NotesDatabase
Dim sourceview As NotesView
Dim sourcedoc As NotesDocument
Dim dataview As NotesView
Dim dc As NotesDocumentCollection
Dim datadoc As NotesDocument
Dim maxcols As Integer
Dim ws As New Notesuiworkspace
Dim UiView As notesuiview
Dim ViewString As String
Dim EnvProfile As String

Set UiView=WS.currentview
Set db = session.CurrentDatabase
ViewString=UiView.viewname

'Selection of Control No to print
Dim SelectedItem As Variant
Dim Sview As notesview
Dim SDoc As notesdocument
SelectedItem = ws.Pickliststrings(3,False,db.Server,db.FilePath,"Risk
Main Entry", "Printing Document...","Select Control No.",5)
'End Selection


If SelectedItem(0) <> "" Then
Set dc = db.unprocesseddocuments
Set dataview = db.getview(ViewString)
xCont = "yes"
Dim xlApp As Variant
Dim xlsheet As Variant
Dim rows As Integer
Dim cols As Integer
rows = 1
cols = 1
max1 = 20
max2=max1
maxcols=Cint(max2)

Set xlApp = CreateObject("Excel.Application")
xlApp.StatusBar = "Creating WorkSheet. Please be patient..."
xlApp.Visible = True
xlApp.Workbooks.Add
xlApp.ReferenceStyle = 2
Set xlsheet = xlApp.Workbooks(1).Worksheets(1)
xlsheet.Name = "For Printing Document"
xlApp.StatusBar = "Creating Column Heading. Please be patient..."
' for headings
cols = 1
rows = 1
xlsheet.Cells(rows,cols).Value = "Unit/Dept"
xlsheet.Cells(rows,cols+1).Value = "Asset Type"
xlsheet.Cells(rows,cols+2).Value = "Asset ID"
xlsheet.Cells(rows,cols+3).Value = "Asset Name"
xlsheet.Cells(rows,cols+4).Value = "Threat"
xlsheet.Cells(rows,cols+5).Value = "Vulnerability"
'end for headings
Set SView = db.GetView("Risk Item Embedded (Main Entry)")
Set datadoc = SView.getdocumentbykey(SelectedItem)
cols=1
rows=3
Do While Not (datadoc Is Nothing) And xCont = "yes"
xlsheet.Cells(rows,cols).Value =
datadoc.GetItemValue("UniDept")
xlsheet.Cells(rows,cols+1).Value =
datadoc.GetItemValue("AType")
xlsheet.Cells(rows,cols+2).Value =
datadoc.GetItemValue("AssID")
xlsheet.Cells(rows,cols+3).Value =
datadoc.GetItemValue("AssName")
xlsheet.Cells(rows,cols+4).Value =
datadoc.GetItemValue("Threat")
xlsheet.Cells(rows,cols+5).Value =
datadoc.GetItemValue("Vulnerability")
xlApp.StatusBar = "Importing Notes Data - Document " &
rows-1 & "."
rows=rows+1
cols=1
Set datadoc = SView.getnextdocument(datadoc)
If datadoc.Maindoc(0) <> SelectedItem(0) Then
xcont = "no"
End If
Loop

xlApp.Rows("1:1").Select
xlApp.Selection.Font.Bold = True
xlApp.Selection.Font.Underline = True
xlApp.Range(xlsheet.Cells(1,1),
xlsheet.Cells(rows,maxcols)).Select
xlApp.Selection.Font.Name = "Arial"
xlApp.Selection.Font.Size = 9
xlApp.Selection.Columns.AutoFit
xlApp.ReferenceStyle = 1
xlApp.Range("A1").Select
xlApp.StatusBar = "Importing Data from Lotus Notes Application
was Completed."
End If

Please help me on how to merge the excel columns as well as in
customizing the size to columns for the exported data becasue I will
use this as Printing of report.

Thank you in advance,