THAT Makes Sense – Lotusscript to Excel (a vent and some tips)

I was asked to take some data from a Notes application and make it into a pretty report in Excel. Okay, no problem. A little lotusscript here, a little lotusscript there, a dash of VB. And VOILA, lovely report in Excel with shaded cells, borders, date formatting. Except for one little problem. I couldn’t get the Page Setup.FitToPagesWide to work.

I checked and re-checked my code. Went home and came back this morning and checked it again. It was correct and yet it didn’t work. Huh? A quick Google search gave me this…

“In Microsoft Excel, you cannot use the FitToPagesWide or FitToPagesTall property in Microsoft Visual Basic for Applications code to change the Adjust To option in page setup.

To change the scaling from Adjust To to Fit To in page setup, set the Zoom property to false as in the following example:

With ActiveSheet.PageSetup
.Zoom = False
.FitToPagesTall = 1
.FitToPagesWide = 1
End With
”

SERIOUSLY?! THAT makes sense. Of course! Why didn’t I think of that? [end sarcasm] I have to explicitly turn off the zoom in order to set the Adjust To option, even though every other parameter that Excel spits out for PageSetup can be ignored.

Sigh. Deep breath.

Anyway, I know other blogs have covered this, but I thought I would share a bit of the code for creating a “pretty” Excel sheet from Lotus.

Use Lotusscript in the normal manner to get some data. When you’re ready…

‘We need to launch Excel from Lotus
Dim excelApp As Variant
Set excelApp = CreateObject ("Excel.Application")
‘We need to create a new Workbook
Dim excelWB As Variant
Set excelWB = excelApp.Workbooks.Add()
‘And of course, get a handle on a Worksheet
Dim excelS As Variant
Set excelS = excelWB.ActiveSheet

Now given the handle to the Excel Worksheet we can use a little VB to simply populate fields…

‘Either with direct text
excelS.Range("A1").Value = "Hello World"
‘Or with Lotusscript
excelS.Range("A1").Value = udoc.FieldGetText("greetingField")


We can have some fun with the VB, by inserting Lotusscript. I used the following to increment the row in the sheet each time I looped through a NotesViewEntryCollection.

excelS.Range("A"+Cstr(y)).Value = viewentry.ColumnValues(2)

Now let’s get our Format groove on. One thing I found is that code like this won’t work…

.Orientation = xlLandscape

You need the code for “xlLandscape”. Lucky for me, I found Joe Litton’s old post on a quick and easy way to get the code. I was already recording macros in Excel and poaching the VB to use in my Lotusscript, but he showed this gem…

MsgBox "The value of xlPaperLetter is <" & xlPaperLetter & ">"

Ahhh. A thing of beauty. By putting that in an Excel macro, I could get the code for anything I needed for my formatting. So this:

With excelS.Rows(“2:2”)
.Font.Bold = True
.WrapText = True
.HorizontalAlignment = xlRight
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeLeft).ColorIndex = 17
End With

Is now this…

With excelS.Rows("2:2")
.Font.Bold = True
.WrapText = True
.HorizontalAlignment = -4152
.Borders(7).LineStyle = 1
.Borders(7).Weight = 2
.Borders(7).ColorIndex = 17
End with

And it works! So pretty.

One last tip, it’s a good idea to make Excel visible at the beginning of your code while developing, so you can see what’s happening and what’s not happening and when. But when you are done, you should probably move “excelApp.Visible = True” down to the end of your code so users aren’t watching the screen make every little format change. While magical to the user, it is slower.