?
Solved

Export Agent Not Working

Posted on 2005-03-28
32
Medium Priority
?
598 Views
Last Modified: 2013-12-18
Hello Everyone, I was helped in creating this agent for the purpose of exporting an excel spreadsheet of a particular view. The agent does almost everything I need it to do except that the agent will not run scheduled.....HELP

Sub Initialize
      
      Dim Session As New NotesSession
      Dim db As NotesDatabase
      Dim dataview As NotesView
      Dim datadoc As NotesDocument
      Dim maxcols As Integer
      
      Dim ViewString As String
      
      Kill "\\orlfs04\grp\TSD\ASSETDB\ELUDepts.xls"
      
      
      Set db = session.CurrentDatabase
      
      ViewString= "<deptnum>"
      
      Set dataview = db.getview("deptnum")
      
      Dim xlApp As Variant
      Dim xlsheet As Variant
      Dim rows As Integer
      Dim cols As Integer
      
      rows = 1
      cols = 1
      maxcols = dataview.columncount
      
      Set xlApp = CreateObject("Excel.Application")
      xlApp.StatusBar = "Creating WorkSheet. Please be patient..."
      xlApp.Visible = False
      xlApp.Workbooks.Add
      xlApp.ReferenceStyle = 2
      Set xlsheet = xlApp.Workbooks(1).Worksheets(1)
      xlsheet.Name = "Export From Notes"  'viewstring
      
      xlApp.StatusBar = "Creating Column Heading. Please be patient..."
      
      For x=1 To maxcols
            xlsheet.Cells(rows,cols).Value = dataview.columns(x-1).title
            cols = cols + 1
      Next
      
      Set datadoc = dataview.getfirstdocument
      Dim fitem As NotesItem
      cols=1
      rows=2
      Do While Not (datadoc Is Nothing)
            For x=1 To maxcols
                  With dataview.Columns(x-1)
                        If .isField Then
                              xlsheet.Cells(rows,cols).Value = datadoc.GetItemValue(.itemname)
                        Elseif .isFormula Then
                              If .formula="@IsExpandable" Then
                                    xlsheet.Cells(rows,cols).Value = ""
                              Else
                                    xlsheet.Cells(rows,cols).Value = Evaluate(.formula,datadoc)
                              End If
                        End If
                  End With
                  cols=cols+1
            Next
            xlApp.StatusBar = "Importing Notes Data - Document " 
            rows=rows+1
            cols=1
            Set datadoc = dataview.getnextdocument(datadoc)
      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
      With xlApp.Worksheets(1)
            .PageSetup.Orientation = 2
            .PageSetup.centerheader = "Report - Confidential"
            .Pagesetup.RightFooter = "Page &P" & Chr$(13) & "Date: &D"
            .Pagesetup.CenterFooter = ""
      End With
      xlApp.ReferenceStyle = 1
      xlApp.Range("A1").Select
      xlApp.StatusBar = "Importing Data from Lotus Notes Application was Completed."
      xlapp.activeworkbook.saveas "\\orlfs04\grp\TSD\ASSETDB\ELUDepts.xls"
      'xlapp.activeworkbook.saveas "c:\temp\eludepts.xls"
      xlapp.quit
      
'Send an email with attached xls file
      Dim MRKnotice As NotesDocument
      Dim RTItem As NotesRichTextItem
      Set MRKnotice = New NotesDocument(db)
      MRKnotice.Form = "Email Notification"
      MRKnotice.SendTo = "ray.padilla@cbs.fiserv.com"
'MRKnotice.CopyTo = "someone@somewhere"
'MRKnotice.BlindCopyTo = "someone@somewhere"
      MRKnotice.SentBy = "Lotus Notes ELU"
      MRKnotice.Subject = "ELU Export of Department Names"
      Set RTItem = MRKnotice.CreateRichTextItem("Body")
      Call RTItem.EmbedObject(Embed_Attachment, "", "\\orlfs04\grp\TSD\ASSETDB\ELUDepts.xls")
      'Call RTItem.EmbedObject(Embed_Attachment, "", "c:\temp\eludepts.xls")
      Call MRKnotice.Send(True)
      
'this copies, renames and deletes the newly created file so there will be no file contention on next running of agent
      
      Dim filePath As String
      Dim strFileDate As String
      'filePath = "c:\temp\eludepts.xls"
      filePath = "\\orlfs04\grp\TSD\ASSETDB\ELUDepts.xls"
      
      
'Our agent ran weekly or daily so this is enough to make the file name unique
      'strFileDate = Format$(Today,"mmddyyyy")
      'Filecopy filePath, "c:\temp\"&ViewString & strFileDate & ".xls"
      'Kill filePath      
End Sub

0
Comment
Question by:padillrr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 11
  • 4
  • +1
32 Comments
 
LVL 15

Accepted Solution

by:
Bozzie4 earned 1000 total points
ID: 13644044
You need excel installed on your server to make this run, and you must have 'unrestricted' rights in the agent, and on the server to be able to create files on the server.

cheers,

Tom
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13644393
It's the Kill that kills you... Go Unrestricted, as Bozzie says.
0
 

Author Comment

by:padillrr
ID: 13644429
Please explain the kill?  This is the only way I can get the agent to run without the error that there is a file already by that name.....I kill hte previous file the agent finishes and writes the excel spreadsheet with the same name which is something that needs to happen.....can the code be modified to do this?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13644579
As Tom says, to create or delete files on the filesystem, you agent needs unrestricted rights. It's an option that must be set in the Server document of the server by your Administrator.
0
 

Author Comment

by:padillrr
ID: 13645456
OK I have given myself unrestricted rights on my dev server I have installed excel and still nothing!!!!
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13645787
Nothing? That's not very much. Is there an error message in the server's log.nsf?  If you need more information, then add some error trapping functionality. Just before the first statement:

    On Error Goto onerror

... and just before the End Sub:

exitsub:
    Exit Sub
onerror:
    Messagebox "Error " & Error$ & " on line " & Erl
    Resume exitsub

The Messagebox will write its output to the log.nsf, in the Miscellaneous section.
0
 

Author Comment

by:padillrr
ID: 13647072
the server is giving me the error that the agent is disallowed in this session on line 31, I'm getting that from the console. When I let the agent run on the server as scheduled, absolutely nothing happens, I get no error messages, even after having added the error trap you provided me.  I can easily run it from the designer and it does exactly what it is supposed to. I even changed to location for it to save the file to the actual server itself just in case it was an issue with permissions on the network drive. When it runs as scheduled the log says that it ran on a number files. But when I go look for the file where it's supposed to be the time and date on the file is as of the last time I manually ran it......DRIVING ME NUTS!!!!
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13650477
Line 31 is about the place where you intend to start an Excel-session. The following comes to mind:
A. re-install Excel on the server, to make sure that registry etc are okay
B. forget about running an Excel-application on the server; instead, you could produce just a standard file that is suitable for Excel to read, like a comma-separated file.

Can you see that the agent did run scheduled? In the Designer, Agents, right-click on the agent and click on Log.
0
 

Author Comment

by:padillrr
ID: 13652644
A.  I will upgrade the current version of excel to the latest, but I see no problem with it since I can open the application and veiw and edit spreadsheets.

B.  It really needs to be excel since this is being run for how can I put it nicely (Management)

The agent ran as scheduled without errors
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13653271
So it used to work, right? Were there any changes in the server lately?

Lateral approach: can you create an empty test database, copy/paste the code over and test it like that? All sorts of funny things can happen in a database, although they are very rare.
0
 

Author Comment

by:padillrr
ID: 13653315
no it has never really worked scheduled, it will run from the designer but that's as far as it will work. The log says it ran as scheduled but there is no email and when I check the drive the file has not been updated.....
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13653514
Try a simple example, that does only local activities, e.g. save a file locally on the server, make a copy of the template locally, then open an Excel-object on the server. Maybe the network-path is a problem for the server?? Your guess is as good as mine...

I found some documents that might help you to generate Excel without having Excel. What to do with the template then I don't know.

http://www.advancode.com/articles.php?page=excel
http://www.freevbcode.com/ShowCode.Asp?ID=6856
0
 

Author Comment

by:padillrr
ID: 13826256
None of this has worked....I am still where I started from, I can run the agent manually and it works fine, scheduled the log says it ran but the excel document has not been changed.....
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13826458
It's a Winxxx server isn't it? Under who's authority is the Domino Server running? Is it a Service?? The "owner" of the Server process needs sufficient rights to write in the directory over the network. Try first on a local (server) directory if it works.
0
 

Author Comment

by:padillrr
ID: 13826484
tried to write the file to the local  c: drive and it does the same thing, The agent will run scheduled but will not write the file or update the file on any machine, the scheduled agent will not save the file to my local drive, however I can run it manually from my client and it will write it wherever I want it to....
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13831591
Did you try a different server? Did you try to create and run the simplest of all agents that creates a file in that directory? What does it say in the agent log, richt-click on the agent in the Designer, then Log? Are you sure the agent is scheduled to run on your server, or is it set to run on any server? Silly question probably: is the Agent Manager (AMGR) running on the server? I can't believe the agent leaves no message whatsoever in the log if it starts correctly. Maybe add a single line at the top somewhere, like
      Msgbox "Agent xxx started at " & Now

There were issues on R4/R5 with agents that had a references to any of the UI objects of Notes. So if you have a scriptlibrary with a function that uses e.g. NotesUIWorkspace, loading the agent will fail. What Domino version is your server?

It's no use to send me a test-db, for I don't have access to a Winxxx server with Domino. And sorry if I asked or suggested something twice, I didn't re-read the whole question :$
0
 

Author Comment

by:padillrr
ID: 13835284
Let me see if I can answer all the questions:
1. Yes tried several servers
2. No haven't tried that as yet, but the agent is not that complicated and as I mentioned it runs manually and will place the file on any drive, local or network.
3. The log says the agent ran, a number of records were updated, but when I look on the drive the file has not been updated
4. I have tried running the agent on the server scheduled as well as any server scheduled, the log file reports running the agent I have set it to run every 10 minutes and it will reflect in the log accordingly but the files is not updated on the drive.
5. The agent manager is running

I am running a Windows 2003 Server with Domino 6.5.1

I appreciate you taking a look at this, the code posted is what I am using and it works fine as long as I sit on the designer and click and run the agent manually, something that is not difficult but does take up sometime and I know I will forget to run it the one time it is truly needed........
0
 

Author Comment

by:padillrr
ID: 13835316
Actually here is the code I'm using:

Sub Initialize
      
      Dim Session As New NotesSession
      Dim db As NotesDatabase
      Dim dataview As NotesView
      Dim datadoc As NotesDocument
      Dim maxcols As Integer
      
      Dim ViewString As String
      
      Kill "\\orlfs04\grp\TSD\ASSETDB\ELUDepts.xls"
      
      
      Set db = session.CurrentDatabase
      
      ViewString= "<deptnum>"
      
      Set dataview = db.getview("deptnum")
      
      Dim xlApp As Variant
      Dim xlsheet As Variant
      Dim rows As Integer
      Dim cols As Integer
      
      rows = 1
      cols = 1
      maxcols = dataview.columncount
      
      Set xlApp = CreateObject("Excel.Application")
      xlApp.StatusBar = "Creating WorkSheet. Please be patient..."
      xlApp.Visible = False
      xlApp.Workbooks.Add
      xlApp.ReferenceStyle = 2
      Set xlsheet = xlApp.Workbooks(1).Worksheets(1)
      xlsheet.Name = "Export From Notes"  'viewstring
      
      xlApp.StatusBar = "Creating Column Heading. Please be patient..."
      
      For x=1 To maxcols
            xlsheet.Cells(rows,cols).Value = dataview.columns(x-1).title
            cols = cols + 1
      Next
      
      Set datadoc = dataview.getfirstdocument
      Dim fitem As NotesItem
      cols=1
      rows=2
      Do While Not (datadoc Is Nothing)
            For x=1 To maxcols
                  With dataview.Columns(x-1)
                        If .isField Then
                              xlsheet.Cells(rows,cols).Value = datadoc.GetItemValue(.itemname)
                        Elseif .isFormula Then
                              If .formula="@IsExpandable" Then
                                    xlsheet.Cells(rows,cols).Value = ""
                              Else
                                    xlsheet.Cells(rows,cols).Value = Evaluate(.formula,datadoc)
                              End If
                        End If
                  End With
                  cols=cols+1
            Next
            xlApp.StatusBar = "Importing Notes Data - Document " 
            rows=rows+1
            cols=1
            Set datadoc = dataview.getnextdocument(datadoc)
      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
      With xlApp.Worksheets(1)
            .PageSetup.Orientation = 2
            .PageSetup.centerheader = "Report - Confidential"
            .Pagesetup.RightFooter = "Page &P" & Chr$(13) & "Date: &D"
            .Pagesetup.CenterFooter = ""
      End With
      xlApp.ReferenceStyle = 1
      xlApp.Range("A1").Select
      xlApp.StatusBar = "Importing Data from Lotus Notes Application was Completed."
      xlapp.activeworkbook.saveas "\\orlfs04\grp\TSD\ASSETDB\ELUDepts.xls"
      'xlapp.activeworkbook.saveas "c:\temp\eludepts.xls"
      xlapp.quit
      
'Send an email with attached xls file
      Dim MRKnotice As NotesDocument
      Dim RTItem As NotesRichTextItem
      Set MRKnotice = New NotesDocument(db)
      MRKnotice.Form = "Email Notification"
      MRKnotice.SendTo = "ray.padilla@cbs.fiserv.com"
'MRKnotice.CopyTo = "someone@somewhere"
'MRKnotice.BlindCopyTo = "someone@somewhere"
      MRKnotice.SentBy = "Lotus Notes ELU"
      MRKnotice.Subject = "ELU Export of Department Names"
      Set RTItem = MRKnotice.CreateRichTextItem("Body")
      Call RTItem.EmbedObject(Embed_Attachment, "", "\\orlfs04\grp\TSD\ASSETDB\ELUDepts.xls")
      'Call RTItem.EmbedObject(Embed_Attachment, "", "c:\temp\eludepts.xls")
      Call MRKnotice.Send(True)
      
'this copies, renames and deletes the newly created file so there will be no file contention on next running of agent
      
      Dim filePath As String
      Dim strFileDate As String
      'filePath = "c:\temp\eludepts.xls"
      filePath = "\\orlfs04\grp\TSD\ASSETDB\ELUDepts.xls"
      
      
'Our agent ran weekly or daily so this is enough to make the file name unique
      'strFileDate = Format$(Today,"mmddyyyy")
      'Filecopy filePath, "c:\temp\"&ViewString & strFileDate & ".xls"
      'Kill filePath      
End Sub
0
 

Author Comment

by:padillrr
ID: 14222647
anyone still looking at this one? I am still not being able to export on a schedule, I have been doing this manually.......
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 14223156
> Did you try to create and run the simplest of all agents that creates a file in that directory?
Simplify, and again, and again, until something works. So please try this... You need Domino privileges to create files in a directory. You also need the user running the Domino server to have O/S privileges to create files on another server.

0
 

Author Comment

by:padillrr
ID: 14225041
You need Domino privileges to create files in a directory???? Can you explain please.......
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 14229034
In the Server document, under Security, there are three levels of privileges for agents that run on the server: there is Unrestricted, Restricted, and None (by default). AFAIK, to access the server's file system, you need to have the "unrestricted methods and operations". There would expect an error message in the Miscellaneous section of the server's log database...

Please, please, try the simplest agent that creates a file, and make it run scheduled on the server. See if it creates a file in a TEMP directory on the same server. If it does, change the directory name to the TEMP directory on the network server. If that also works, change the name to the directory you want.

May I suggest a very methodical way of looking for the problem, by Holmesian elimination? I'm sorry to say taht, until you do so, I see very little point in continuing.
0
 

Author Comment

by:padillrr
ID: 15291142
I am getting this error on the notes log:

Microsoft Excel: Unable to set the Orientation property of the PageSetup class

which is in this line of the code: .PageSetup.Orientation = 2

running it locally I am using excel 2003 on the server I'm using 2000 locally I get no errors when it runs scheduled it errors at the pagesetup.orientation line

any ideas?
0
 
LVL 46

Assisted Solution

by:Sjef Bosman
Sjef Bosman earned 1000 total points
ID: 15291977
Lucky-guessing here...

1) Does the default printer on the server support a different orientation?? Is there a printer driver installed on the server??
     See also http://www.kbalertz.com/kb_172320.aspx
2) Can you build some error catching around that line, so the work can continue (but you'll get the wrong output)?
     On Error Resume Next ' just continue if it fails
     .PageSetup.Orientation = 2
     On Error Goto 0
Look into the specifications of Excel2003, to find out why the message is given.
0
 
LVL 18

Expert Comment

by:marilyng
ID: 16161587
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
    Accept: Bozzie4 {http:#13644044} & sjef_bosman {http:#13650477} & sjef_bosman {http:#15291977}

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

marilyng
EE Cleanup Volunteer
0
 

Author Comment

by:padillrr
ID: 16165717
PLease do not close this question, I am still working on getting a resolution!
0
 
LVL 18

Expert Comment

by:marilyng
ID: 16165976
xlApp.Worksheets(1).PageSetup.Orientation = 2      

Excel 2000, Excel 2003, win2000 win2003

      
0
 
LVL 18

Expert Comment

by:marilyng
ID: 16165991
Oops, submitted before explanation... it fails if your default printer is a network printer.  Try error trapping, as sjef suggests, or configure a default printer on the server that the server can configure, i.e. print to file printer.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For beginners of Lotus Notes user this is important to know about the types of files and their location supported by IBM Notes. Mostly users are unaware about how many file types are created and what their usages are. This Article is fully dedicated…
Lack of Storage capacity is a common problem that exists in every field of life. Here we are taking the case of Lotus Notes Emails, as we all know that we are totally depend on e-communication i.e. Emails. This article is fully dedicated to resolvin…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question