I found an Export to Excel application from the Sandbox that Ken Pespisa created and currently, it prompts me to save the excel document after running the agent. I would like to modify the code to open the excel document instead of asking me to save it. Since I’m somewhat new in LotusScript, I’m not sure how to modify the code to open the excel document. Below is the code that prompts me to save it and I assumed that is the code I should replace.
Set excelApp = CreateObject(“Excel.Application”)
excelApp.DisplayAlerts = False
Set excelNewWkbk = excelApp.Workbooks.Add
vtFileName = w.SaveFileDialog(False, “Choose New File”, “Microsoft Excel|*.xls”, “c:\windows\desktop”)
If Isempty(vtFileName) Then
excelApp.Quit
CreateExcelObject = False
Exit Function
End If
On Error ErrOLEException Goto PROMPTAGAIN
Call excelNewWkbk.SaveAs(vtFileName(0))
Set excelSheet = excelNewWkbk.ActiveSheet
With excelNewWkbk
.Title = "Notes Data Export from " & sSourceName
End With
Subject: Export to Excel Question
Set excelApp= createobject(“Excel.Application”)Set excelworkbook = excel.workbooks.add
Set excelsheet = excel.activesheet
----------export data ----------
set excel=nothing
excelsheet.application.Visible = True
it will open the excel document.
Subject: RE: Export to Excel Question
Thank you Usharani and Stan for your help. I replaced some of the code by using your suggestions and it worked as expected. Below is the modified code:
Set excelApp = CreateObject(“Excel.Application”)
If excelApp Is Nothing Then
sMessage = “Could not create spreadsheet.” & Chr$( 10 )& “Make sure Excel is installed on this computer.”
Msgbox sMessage, 16, “Creation of Spreadsheet Object Failed”
CreateExcelObject = False
Exit Function
End If
excelApp.DisplayAlerts = False
Set excelNewWkbk = excelApp.Workbooks.Add
Set excelSheet = excelNewWkbk.ActiveSheet
With excelNewWkbk
.Title = "Notes Data Export from " & sSourceName
End With
Print “Printing spreadsheet…”
excelApp.Visible = True
Subject: Export to Excel Question
The spreadsheet is already open – it’s just not visible. All you need to do is to remove the line that calls the SaveAs (and, if it follows later in the code, the line that says excelApp.Quit), and add a line that says:
excelApp.Visible = True
just before the sub ends. That will leave Excel open and under the user’s control.