Right,further to previous posts, I want
-
user presses a button;
-
It provides the export dialogue from a view;
-
User can select various options;
-
Whatever the user selects is exported to .csv (or .xls is that possible with @Command)
-
Some formatting of the Excel occours to make it look nicer.
OK so far?
So I created some code.
Two different scenarios
- Run code. When excel opens, click back on notes.
Result=OK. Excel is formatted just like I wanted. Not great for user, as has to click notes then excel;
- Run code. Do nothing.
Error 208 Cannot create automation object.
I even checked the registry, and added a .11 as per other posts, and have had a lot of help from forum so far…
But, I need this to work better, user shouldn’t need to suddenly click back on notes for no reason!! and no ‘error 208’ should occour! (and I shouldn’t need to code a Sleep statement!!)
Heres’ the code:
Sub Initialize
’ FormatMacro Macro
’ Macro recorded 22/02/2007 by 3M
’
’
Dim Excel As Variant
Dim Exceldoc As Variant
On Error Goto processError
'Activateapp "CRM Change Management V7 - 9.LOV's Report - Lotus Notes"
'Dim ws As New NotesUIWorkspace
'Dim uidb As NotesUIDatabase
'Set uidb = ws.CurrentDatabase
'Call uidb.OpenView("LOVRep", , True)
Sleep 5
'Activateapp "Microsoft Excel"
'Set Excel = CreateObject("Excel.Application") 'Create Excel object
Set Excel = GetObject(,"Excel.Application.11")
If Excel Is Nothing Then
Msgbox "Error!!! Agent was unable to open Microsoft Excel."
Exit Sub
End If
Set Exceldoc = Excel.ActiveWorkbook 'Get a handle for the active document
Const xlrange =2
Const xlColumns=2
'Const Columns=2
Const xlGeneral=1
Const xlBottom=-4107
Const xlHorizontal=-4128
Dim Range As Variant
Dim Columns As Variant
Columns = xlColumns
'Excel.Application.Columns("M:M").Select
' FormatMacro Macro
’ Macro recorded 22/02/2007 by 3M
’
’
Selection=Excel.Application.Range("M2").Select
With Excel.Selection
Excel.selection.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
'Excel.selection.ReadingOrder = xlContext
.MergeCells = False
End With
Selection=Excel.Application.Range("M3").Select
'Range("M3").Select
Exceldoc.ActiveSheet.Columns("M:M").EntireColumn.AutoFit
'Columns("M:M").EntireColumn.AutoFit
Excel.Application.ActiveWindow.SmallScroll ToRight=6
Exceldoc.ActiveSheet.Columns("M:M").Select
With Excel.Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
'.ReadingOrder = xlContext
.MergeCells = False
End With
Excel.Application.ActiveWindow.SmallScroll ToRight=0
Selection=Excel.Application.Range("M3").Select
'Exceldoc.ActiveSheet.Columns("M:M").EntireColumn.AutoFit
Exceldoc.ActiveSheet.Columns("M:M").EntireColumn.AutoFit
processError:
Messagebox "Error " & Err() & ": " & Error()
Exit Sub
'Agent.Run("CommandAgent")
End Sub
'***this is running from a button in a view, the code is the following:
@Command([FileExport]; “Comma Separated Value”;
“C:\Temp\LOVexport.csv”);
@UpdateFormulaContext;
@Command( [Execute] ;
“C:\Srccode\wins\O2K3\FILES\PFILES\MSOFFICE\OFFICE11\EXCEL.EXE” ;
“C:\Temp\LOVexport.csv” );
@UpdateFormulaContext;
@PostedCommand([ToolsRunMacro];“FExcel”)