Hi,Still trying to export to Excel.
I have tried the main example in the sandbox. It doesn’t work, can’t handle multi-value fields. The ‘fix’ as described in the sandbox just creates an error.
I’ve also tried other code from the forum, this just creates a mess. I’m sure they were great for what they are, but they are just not doing what I want.
Right, thats out the way.
My button works like this:
- Button is in a view:
@Command([FileExport]; “Comma Separated Value”;
“C:\Temp\LOVexport.csv”);
@PostedCommand([ToolsRunMacro];“FExcel”)
- This runs the following Agent:
Sub Initialize
’ FormatMacro Macro
’ Macro recorded 22/02/2007 by 3M
’
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)
Dim Excel As Variant
Dim Exceldoc As Variant
'Set Excel = CreateObject("Excel.Application") 'Create Excel object
'Excel.Visible = True
Dim result As Integer
result=Shell("C:\Srccode\wins\O2K3\FILES\PFILES\MSOFFICE\OFFICE11\EXCEL.EXE C:\Temp\LOVexport.csv", 1)
'Dim askme As Integer
'Dim workspace As New NotesUIWorkspace
'askme = workspace.Prompt(PROMPT_OK, _
'"Reminder", "Don't forget to run backup tonight.")
'Dim result3 As Integer
'result3=Shell("C:\Program Files\Lotus\Notes\notes.exe",1)
Sleep 5
'Excel = ""
Set Excel = GetObject(,"Excel.Application.11")
Excel.Visible = True
If Excel Is Nothing Then
Set Excel = GetObject(,"Excel.Application.11")
'Msgbox "Error!!! Agent was unable to open Microsoft Excel."
Exit Sub
End If
'Set Exceldoc = Excel.WorkBooks.Add()
Set Exceldoc = Excel.ActiveWorkbook 'Get a handle for the active document
'Dim result2 As Integer
'result2=Shell("C:\Program Files\Lotus\Notes\notes.exe",1)
Const xlrange =2
Const xlColumns=2
'Const Columns=2
Const xlGeneral=1
Const xlBottom=-4107
Const xlHorizontal=-4128
Const xlGuess=0
Const xlDescending=2
Const xlTopToBottom=1
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
Exceldoc.ActiveSheet.Columns("A:A").EntireColumn.AutoFit
Exceldoc.ActiveSheet.Columns("B:B").EntireColumn.AutoFit
Exceldoc.ActiveSheet.Columns("C:C").EntireColumn.AutoFit
Exceldoc.ActiveSheet.Columns("D:D").EntireColumn.AutoFit
Exceldoc.ActiveSheet.Columns("E:E").EntireColumn.AutoFit
Exceldoc.ActiveSheet.Columns("F:F").EntireColumn.AutoFit
Exceldoc.ActiveSheet.Columns("G:G").EntireColumn.AutoFit
Exceldoc.ActiveSheet.Columns("I:I").EntireColumn.AutoFit
Exceldoc.ActiveSheet.Columns("J:J").EntireColumn.AutoFit
Exceldoc.ActiveSheet.Columns("K:K").EntireColumn.AutoFit
'ActiveWindow.SmallScroll ToRight:=5
Exceldoc.ActiveSheet.Columns("O:O").EntireColumn.AutoFit
Exceldoc.ActiveSheet.Columns("N:N").EntireColumn.AutoFit
'Exceldoc.ActiveSheet.Columns("M:M").EntireColumn.AutoFit
Exceldoc.ActiveSheet.Columns("L:L").EntireColumn.AutoFit
Exceldoc.ActiveSheet.Range("E2").Select
Exceldoc.ActiveSheet.Rows("1:1").Select
Exceldoc.ActiveSheet.Rows("1:1").Font.Bold = True
Exceldoc.ActiveSheet.Cells.EntireColumn.Autofit
'Exceldoc.ActiveSheet.Range("E2").Select
'Exceldoc.ActiveSheet.Range("A1:P2").Sort Key1=Exceldoc.ActiveSheet.Range("E2"),
'Exceldoc.ActiveSheet.Order1=xlDescending
'Exceldoc.ActiveSheet.Header=xlGuess
'Exceldoc.ActivceSheet.OrderCustom=1
'Exceldoc.ActiceSheet.MatchCase=False
'Exceldoc.ActiveSheet.Orientation=xlTopToBottom
'Exceldoc.ActiveSheet.DataOption1=xlSortNormal
Set xlsheet = Excel.Workbooks(1).Worksheets(1)
xlsheet.cells.select
Excel.selection.sort xlsheet.range("E1") , 1 , xlsheet.range("H1") , , 1 , , , 1 , 1, False , 1, 0, 0, 0
'return to cell A1 for tidyness
Exceldoc.ActiveSheet.Range("A1").Select
Dim result4 As Integer
result4=Shell("C:\Program Files\Lotus\Notes\notes.exe",1)
ProcessError:
Resume Next
'Agent.Run("CommandAgent")
End Sub
- Everything is working OK, IF IF IF you run this, and immediately click back on notes.
But I don’t want the user to click back on notes.
I want this to run without the user having to click back on notes…
If you run it with the debugger on it works fine.
But, I don’t want the user to need to use the debugger…
How can I switch focus back to notes, without breaking the process.
No error is appearing (possibly due to error handling?)
Before, an error saying something like ‘cannot create automation object’ or similar was appearing…