?
Solved

Automatic Export to Excel to a network drive

Posted on 2005-03-14
19
Medium Priority
?
258 Views
Last Modified: 2013-12-18
I am using the export to excel agent available on lotus sandbox to export some views,this works nicely, select the documents and it does the rest, now I need to automate this process and have the view exported daily at 5am how can I make this agent do this or is there a better way and will it work to a network drive? The agent now opens excel and lets me then save the file, I need it to just save the data to the network drive so it is available to other programs.....
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
  • 11
  • 6
  • 2
19 Comments
 
LVL 14

Expert Comment

by:p_partha
ID: 13537452
You have to point it to a view, instaed of unprocesseddocuments... so ideally it should be like this:

set doccoll = db.unprocesseddocuments

change it to
set doc = view.getfirstdocument


point your view to the place where the documents are, It will be easy to cahnge if you can paste the code

Partha
0
 

Author Comment

by:padillrr
ID: 13538223
I'm a little lost, I change the set collection to set doc but how do I set it up to save it to a network drive on a daily basis and where do I name the file?
0
 
LVL 14

Expert Comment

by:p_partha
ID: 13538244
You have to write to a file, you can use open statement , from the ex:

Open "c:\123w\work\thenames.txt" For Input As fileNum%
Do While Not EOF(fileNum%)
   ' Read each line of the file.
   Line Input #fileNum%, txt$
   ' Increment the line count.
   counter% = counter% + 1
Loop


Something in this lines

Partha
0
Industry Leaders: 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!

 

Author Comment

by:padillrr
ID: 13538259
Here's the code under initialize on the agent:  
Sub Initialize
      On Error Goto ExitExcel
'Main Code
      Dim session As New NotesSession
      Dim workspace As New NotesUIWorkspace
      Dim UIview As NotesUIView
      Dim collection As NotesDocumentCollection      
      Dim coldoc As NotesDocument
      Dim BarMsg As String, UpdateMsg As String
      Dim countall As Long, countthis As Long, countallsel As Long, countthissel As Long
      Dim NChar As String
      
      Set UIview = workspace.CurrentView
      Set db = session.CurrentDatabase
      UIViewname = UIView.dept#
      UIViewAlias = UIView.Viewalias
      Set view = db.GetView( dept# )
      
      Set doc = view.getfirstdocument
      gowithselection = False
      goonall = True
      
      'Determine if it is a collection
      countallsel = collection.count
      If countallsel >1 Then
            gowithselection = workspace.Prompt(PROMPT_YESNO, "Selection found", "Export only selected documents?")
            Set doc=collection.getfirstdocument
            'Check if there is really a doc selected
            If (doc Is Nothing) And (goonwithselection) Then
                  Msgbox "Invalid selection"
                  Exit Sub
            End If
            Set doc = Nothing
            BarMsg = "Exporting selected documents ..."
      Else
            goonall = workspace.Prompt(PROMPT_YESNO, "No Selection found", "Export all documents?" + Chr$(13) + "Info: If you want to export only selected documents," + Chr$(13) + "please select these documents before running this script.")
            If goonall=False Then
                  Print "Exiting..."
                  Exit Sub
            End If
            Set collection = Nothing
            BarMsg = "Exporting documents ..."
      End If
      
      doformat = Messagebox("Format the Excel-Sheet?", 36)
      If doFormat = 6 Then
            'SET THE AUTOFORMAT
            Call SetSelList()
            SelForm = workspace.Prompt(PROMPT_OKCANCELLIST, "AutoFormat-Form","Select the Autoformat-Form", "Simple" , SelList)
            TitleBar = Cint(Inputbox ( "How many degrees shall the Title-Line be turned", "Title-Turn", "0"))
            If Titlebar > 90 Then
                  TitleBar = 90
            Elseif TitleBar < -90 Then
                  TitleBar = -90
            End If
      End If
      
      SelAutoForm = getAutoForm( selForm )
      
'Launch Excel and open it in the UI
      Set excelAppObject = CreateObject( AppConst )
      'Try other AppConst
      If excelAppObject Is Nothing Then
            Set excelAppObject = CreateObject( AppConst2 )
            If excelAppObject Is Nothing Then
                  Msgbox "Could not create an Excel Object"
                  Exit Sub
            End If
      End If
      excelAppObject.Visible = False
      Call excelAppObject.Workbooks.Add
      Set excelWorksheetObject = excelAppObject.ActiveSheet
      
'Add the table labels
      nc=64
      nmore=0
      Forall c In view.Columns
            'do not export hidden columns or those with fixed vals (not displayed as doc.columnvalues!!!!)
            If Not c.ishidden And Not c.IsIcon And Not (c.IsFormula And Not Instr(1, c.Formula, "@") And Not Instr(1, c.Formula, "+")) Then
                  nchar = countcol(nChar)
                  excelWorksheetObject.Range( nchar + "1").Value = c.Title
            End If
      End Forall
      
      m_let = nchar
      nl=1
      
      'Export Documents
      Set doc = view.GetFirstDocument
      If gowithselection Then countall = countallsel Else countall = view.AllEntries.Count
      countthis = 0
      countthissel = 0
      If visualProc Then Dim RefreshProgress As New ProgressBar (countall) 'display the ProcessWindow/Bar
      
      exitnow=False
      While Not ( doc Is Nothing Or exitnow)
            countthis = countthis + 1
            If gowithselection Then
                  Set coldoc = Nothing
                  Set coldoc = collection.GetDocument(doc)
                  If Not coldoc Is Nothing Then 'Exports only if doc is part of collection
                        Call ExportDoc(excelWorksheetObject)
                        countthissel = countthissel + 1
                  End If
                  If visualproc Then
                        UpdateMsg = "Exporting document " + Cstr(countthissel) + " of " + Cstr(countall) + Chr$(13) + "Processing Doc in View: " + Cstr(countthis)                        
                        Call RefreshProgress.UpdatePosition (countthissel)
                  Else
                        Print "Exporting document " + Cstr(countthissel) + " of " + Cstr(countall) + " / " + "Processing Doc in View: " + Cstr(countthis)
                  End If
            Else
                  Call ExportDoc(excelWorksheetObject)
                  UpdateMsg = "Exporting document " + Cstr(countthis) + " of " + Cstr(countall)
                  If visualproc Then
                        Call RefreshProgress.UpdatePosition (countthis)
                  Else
                        Print UpdateMsg
                  End If
            End If
            If visualproc Then Call RefreshProgress.UpdateProgressText (BarMsg, UpdateMsg)
            If countall = countthissel Then exitnow = True  'Exit routine
            Set doc = view.GetNextDocument(doc)
      Wend
      
      
 'formating the Worksheet
      If doformat = 6 Then
            BarMsg = "One moment please..."
            UpdateMsg = "Formating the document..."
            If visualproc Then Call RefreshProgress.UpdateProgressText (BarMsg, UpdateMsg) Else Print Updatemsg
            excelWorksheetObject.Range("A2:" + m_let + Cstr(nl) ).Select
            excelAppObject.Selection.Columns.AutoFit
            excelWorksheetObject.Range("A1:" + m_let + Cstr(nl)).Select
            With excelAppObject.Selection
                  .AutoFormat SelAutoForm, False, True, False, True, True, False
                  .VerticalAlignment = -4160
            End With
            
            excelWorksheetObject.Rows("1:1").Select
            With excelAppObject.Selection
                  .VerticalAlignment = -4107
                  .HorizontalAlignment = -4108
                  .WrapText = True
                  .Orientation = Cint(titlebar)
                  .ShrinkToFit = False
                  .MergeCells = False
                  RowHeight = 215
            End With
            
            excelWorksheetObject.Range("A:" + m_let).Select
            With excelAppObject.Selection.Font
                  .Name = "Arial"
                  .Size = 10
            End With
            
            excelAppObject.Selection.Columns.Autofit
            excelWorksheetObject.Range("A1").Select
            
            With excelAppObject.Windows(1)    
                  .SplitRow=1
                  .FreezePanes=True
            End With  
            
            With excelWorksheetObject.PageSetup
                  .Orientation = 2
                  .LeftHeader = "&""Arial,Bold""&18"+db.Title+" - "+ UIViewAlias
                  .CenterHeader = ""
                  .RightHeader = "Datum: &D"
                  .LeftFooter = ""
                  .CenterFooter = ""
                  .RightFooter = "Seite &P"
                  .PrintArea = ("A1:"+ m_let + Cstr(nl))              
                  .PaperSize = 9
                  .CenterHorizontally = True          
                  .FitToPagesTall =False
                  .zoom = False
                  .FitToPagesWide=1
                  .PrintTitleRows=excelWorksheetObject.Rows("1:1").Address
            End With          
      End If
      excelAppObject.Visible = True
      Exit Sub
      
ExitExcel:
      Print "Error in Line " + Cstr(Erl) + " : " + Cstr(Error)
      excelAppObject.DisplayAlerts = False
      excelAppObject.Quit
      Exit Sub
End Sub

I need to be able to save the file not open excel and I need it to export a specific view did I do this right?
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 13542130
Sub Initialize
On Error Goto ExitExcel
'Main Code
Dim session As New NotesSession
Dim workspace As New NotesUIWorkspace
Dim UIview As NotesUIView
Dim collection As NotesDocumentCollection
Dim coldoc As NotesDocument
Dim BarMsg As String, UpdateMsg As String
Dim countall As Long, countthis As Long, countallsel As Long, countthissel As Long
Dim NChar As String

Set UIview = workspace.CurrentView
Set db = session.CurrentDatabase
UIViewname = UIView.dept#
UIViewAlias = UIView.Viewalias
Set view = db.GetView( dept# )

Set doc = view.getfirstdocument
gowithselection = False
goonall = True

'Determine if it is a collection
countallsel = collection.count
If countallsel >1 Then
gowithselection = workspace.Prompt(PROMPT_YESNO, "Selection found", "Export only selected documents?")
Set doc=collection.getfirstdocument
'Check if there is really a doc selected
If (doc Is Nothing) And (goonwithselection) Then
Msgbox "Invalid selection"
Exit Sub
End If
Set doc = Nothing
BarMsg = "Exporting selected documents ..."
Else
goonall = workspace.Prompt(PROMPT_YESNO, "No Selection found", "Export all documents?" + Chr$(13) + "Info: If you want to export only selected documents," + Chr$(13) + "please select these documents before running this script.")
If goonall=False Then
Print "Exiting..."
Exit Sub
End If
Set collection = Nothing
BarMsg = "Exporting documents ..."
End If

doformat = Messagebox("Format the Excel-Sheet?", 36)
If doFormat = 6 Then
'SET THE AUTOFORMAT
Call SetSelList()
SelForm = workspace.Prompt(PROMPT_OKCANCELLIST, "AutoFormat-Form","Select the Autoformat-Form", "Simple" , SelList)
TitleBar = Cint(Inputbox ( "How many degrees shall the Title-Line be turned", "Title-Turn", "0"))
If Titlebar > 90 Then
TitleBar = 90
Elseif TitleBar < -90 Then
TitleBar = -90
End If
End If

SelAutoForm = getAutoForm( selForm )

'Launch Excel and open it in the UI
Set excelAppObject = CreateObject( AppConst )
'Try other AppConst
If excelAppObject Is Nothing Then
Set excelAppObject = CreateObject( AppConst2 )
If excelAppObject Is Nothing Then
Msgbox "Could not create an Excel Object"
Exit Sub
End If
End If
excelAppObject.Visible = False
Call excelAppObject.Workbooks.Add
Set excelWorksheetObject = excelAppObject.ActiveSheet

'Add the table labels
nc=64
nmore=0
Forall c In view.Columns
'do not export hidden columns or those with fixed vals (not displayed as doc.columnvalues!!!!)
If Not c.ishidden And Not c.IsIcon And Not (c.IsFormula And Not Instr(1, c.Formula, "@") And Not Instr(1, c.Formula, "+")) Then
nchar = countcol(nChar)
excelWorksheetObject.Range( nchar + "1").Value = c.Title
End If
End Forall

m_let = nchar
nl=1

'Export Documents
Set doc = view.GetFirstDocument
If gowithselection Then countall = countallsel Else countall = view.AllEntries.Count
countthis = 0
countthissel = 0
If visualProc Then Dim RefreshProgress As New ProgressBar (countall) 'display the ProcessWindow/Bar

exitnow=False
While Not ( doc Is Nothing Or exitnow)
countthis = countthis + 1
If gowithselection Then
Set coldoc = Nothing
Set coldoc = collection.GetDocument(doc)
If Not coldoc Is Nothing Then 'Exports only if doc is part of collection
Call ExportDoc(excelWorksheetObject)
countthissel = countthissel + 1
End If
If visualproc Then
UpdateMsg = "Exporting document " + Cstr(countthissel) + " of " + Cstr(countall) + Chr$(13) + "Processing Doc in View: " + Cstr(countthis)
Call RefreshProgress.UpdatePosition (countthissel)
Else
Print "Exporting document " + Cstr(countthissel) + " of " + Cstr(countall) + " / " + "Processing Doc in View: " + Cstr(countthis)
End If
Else
Call ExportDoc(excelWorksheetObject)
UpdateMsg = "Exporting document " + Cstr(countthis) + " of " + Cstr(countall)
If visualproc Then
Call RefreshProgress.UpdatePosition (countthis)
Else
Print UpdateMsg
End If
End If
If visualproc Then Call RefreshProgress.UpdateProgressText (BarMsg, UpdateMsg)
If countall = countthissel Then exitnow = True 'Exit routine
Set doc = view.GetNextDocument(doc)
Wend


'formating the Worksheet
If doformat = 6 Then
BarMsg = "One moment please..."
UpdateMsg = "Formating the document..."
If visualproc Then Call RefreshProgress.UpdateProgressText (BarMsg, UpdateMsg) Else Print Updatemsg
excelWorksheetObject.Range("A2:" + m_let + Cstr(nl) ).Select
excelAppObject.Selection.Columns.AutoFit
excelWorksheetObject.Range("A1:" + m_let + Cstr(nl)).Select
With excelAppObject.Selection
.AutoFormat SelAutoForm, False, True, False, True, True, False
.VerticalAlignment = -4160
End With

excelWorksheetObject.Rows("1:1").Select
With excelAppObject.Selection
.VerticalAlignment = -4107
.HorizontalAlignment = -4108
.WrapText = True
.Orientation = Cint(titlebar)
.ShrinkToFit = False
.MergeCells = False
RowHeight = 215
End With

excelWorksheetObject.Range("A:" + m_let).Select
With excelAppObject.Selection.Font
.Name = "Arial"
.Size = 10
End With

excelAppObject.Selection.Columns.Autofit
excelWorksheetObject.Range("A1").Select

With excelAppObject.Windows(1)
.SplitRow=1
.FreezePanes=True
End With

With excelWorksheetObject.PageSetup
.Orientation = 2
.LeftHeader = "&""Arial,Bold""&18"+db.Title+" - "+ UIViewAlias
.CenterHeader = ""
.RightHeader = "Datum: &D"
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = "Seite &P"
.PrintArea = ("A1:"+ m_let + Cstr(nl))
.PaperSize = 9
.CenterHorizontally = True
.FitToPagesTall =False
.zoom = False
.FitToPagesWide=1
.PrintTitleRows=excelWorksheetObject.Rows("1:1").Address
End With
End If
'excelAppObject.Visible = False
excelAppObjec.activeworkbook.saveas "<Location and name of desired file>"
excelAppObjec.quit
Exit Sub

ExitExcel:
Print "Error in Line " + Cstr(Erl) + " : " + Cstr(Error)
excelAppObject.DisplayAlerts = False
excelAppObject.Quit
Exit Sub
End Sub


Additional info for you:
'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 = "<Location and name of desired file>"

'Our agent ran weekly or daily so this is enough to make the file name unique
strFileDate = Format$(Today, "MMDDYYYY")
Filecopy filePath, "c:\notes\"&ViewString & strFileDate & ".xls"
Kill filePath
0
 

Author Comment

by:padillrr
ID: 13545868
I copied your code and pasted got an error on  UIViewname = UIView.dept# I changed that to it's original format and now I get variant does not contain object when I try to run the agent manually.......the name of the file needs to remain the same, and it just needs to over write the previous version, but how am I telling it which view I want to export? does it make a difference that the location I want to save the file to is a network drive?
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 13553116
Turn On the debugger. And let us know in which line it is giving error.

NotesUIView will take care of which view you are triggering.

Since you want it to be a schedule, u can't use any of Notes UI classess. So, try to hard code the vuew name or make into a array of views and loop through them.

Saving to Network should not be a proble. First try to saave to local drive.
0
 

Author Comment

by:padillrr
ID: 13556366
This is where the debugger tells me I'm getting an error  excelAppObject.DisplayAlerts = False so I comment it out then I get this excelAppObject.Quit so I comment that out that will cause the agent to run but no file is saved to the drive.......
0
 

Author Comment

by:padillrr
ID: 13556760
tell me how to hard code the view please the code I'm using is on this page I've copied
Dim filePath As String
Dim strFileDate As String
filePath = "<Location and name of desired file>"

under the DIms on the code and added the path, I 've commented out the two lines previously mentioned as giving me errors, now however even though the lines are commented out I still get the same error at the same place?!?!? In may I will be going to a developers bookcamp which I am hoping will teach me some scripting so I don't have to bother you guys so much! Thanks....I promise once I get done with some real training I will contribute a lot more to this site.....
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 13562865
dim view as notesview
dim db as notesdatabase
set ss as new notessession
set db=ss.currentdatabase
set view=db.getview("yourViewName")


0
 

Author Comment

by:padillrr
ID: 13565426
here is my current code and the view name is dept# I am getting an error on this line set ss as new notessession

Sub Initialize
      On Error Goto ExitExcel
'Main Code
      Dim session As New NotesSession
      Dim workspace As New NotesUIWorkspace
      Dim UIview As NotesUIView
      Dim collection As NotesDocumentCollection
      Dim coldoc As NotesDocument
      Dim BarMsg As String, UpdateMsg As String
      Dim countall As Long, countthis As Long, countallsel As Long, countthissel As Long
      Dim NChar As String
      Dim filePath As String
      Dim strFileDate As String
      filePath = "K:\TSD\ASSETDB\elutest.xls"
      
      Dim view As notesview
      Dim db As notesdatabase
set ss as new notessession
      Set db=ss.currentdatabase
      Set view=db.getview("dept#")
      
      Set UIview = workspace.CurrentView
      Set db = session.CurrentDatabase
      UIViewname = UIView.viewname
      UIViewAlias = UIView.Viewalias
      Set view = db.GetView( dept# )
      
      Set doc = view.getfirstdocument
      gowithselection = False
      goonall = True
      
'Determine if it is a collection
      countallsel = collection.count
      If countallsel >1 Then
            gowithselection = workspace.Prompt(PROMPT_YESNO, "Selection found", "Export only selected documents?")
            Set doc=collection.getfirstdocument
'Check if there is really a doc selected
            If (doc Is Nothing) And (goonwithselection) Then
                  Msgbox "Invalid selection"
                  Exit Sub
            End If
            Set doc = Nothing
            BarMsg = "Exporting selected documents ..."
      Else
            goonall = workspace.Prompt(PROMPT_YESNO, "No Selection found", "Export all documents?" + Chr$(13) + "Info: If you want to export only selected documents," + Chr$(13) + "please select these documents before running this script.")
            If goonall=False Then
                  Print "Exiting..."
                  Exit Sub
            End If
            Set collection = Nothing
            BarMsg = "Exporting documents ..."
      End If
      
      doformat = Messagebox("Format the Excel-Sheet?", 36)
      If doFormat = 6 Then
'SET THE AUTOFORMAT
            Call SetSelList()
            SelForm = workspace.Prompt(PROMPT_OKCANCELLIST, "AutoFormat-Form","Select the Autoformat-Form", "Simple" , SelList)
            TitleBar = Cint(Inputbox ( "How many degrees shall the Title-Line be turned", "Title-Turn", "0"))
            If Titlebar > 90 Then
                  TitleBar = 90
            Elseif TitleBar < -90 Then
                  TitleBar = -90
            End If
      End If
      
      SelAutoForm = getAutoForm( selForm )
      
'Launch Excel and open it in the UI
      Set excelAppObject = CreateObject( AppConst )
'Try other AppConst
      If excelAppObject Is Nothing Then
            Set excelAppObject = CreateObject( AppConst2 )
            If excelAppObject Is Nothing Then
                  Msgbox "Could not create an Excel Object"
                  Exit Sub
            End If
      End If
      excelAppObject.Visible = False
      Call excelAppObject.Workbooks.Add
      Set excelWorksheetObject = excelAppObject.ActiveSheet
      
'Add the table labels
      nc=64
      nmore=0
      Forall c In view.Columns
'do not export hidden columns or those with fixed vals (not displayed as doc.columnvalues!!!!)
            If Not c.ishidden And Not c.IsIcon And Not (c.IsFormula And Not Instr(1, c.Formula, "@") And Not Instr(1, c.Formula, "+")) Then
                  nchar = countcol(nChar)
                  excelWorksheetObject.Range( nchar + "1").Value = c.Title
            End If
      End Forall
      
      m_let = nchar
      nl=1
      
'Export Documents
      Set doc = view.GetFirstDocument
      If gowithselection Then countall = countallsel Else countall = view.AllEntries.Count
      countthis = 0
      countthissel = 0
      If visualProc Then Dim RefreshProgress As New ProgressBar (countall) 'display the ProcessWindow/Bar
      
      exitnow=False
      While Not ( doc Is Nothing Or exitnow)
            countthis = countthis + 1
            If gowithselection Then
                  Set coldoc = Nothing
                  Set coldoc = collection.GetDocument(doc)
                  If Not coldoc Is Nothing Then 'Exports only if doc is part of collection
                        Call ExportDoc(excelWorksheetObject)
                        countthissel = countthissel + 1
                  End If
                  If visualproc Then
                        UpdateMsg = "Exporting document " + Cstr(countthissel) + " of " + Cstr(countall) + Chr$(13) + "Processing Doc in View: " + Cstr(countthis)
                        Call RefreshProgress.UpdatePosition (countthissel)
                  Else
                        Print "Exporting document " + Cstr(countthissel) + " of " + Cstr(countall) + " / " + "Processing Doc in View: " + Cstr(countthis)
                  End If
            Else
                  Call ExportDoc(excelWorksheetObject)
                  UpdateMsg = "Exporting document " + Cstr(countthis) + " of " + Cstr(countall)
                  If visualproc Then
                        Call RefreshProgress.UpdatePosition (countthis)
                  Else
                        Print UpdateMsg
                  End If
            End If
            If visualproc Then Call RefreshProgress.UpdateProgressText (BarMsg, UpdateMsg)
            If countall = countthissel Then exitnow = True 'Exit routine
            Set doc = view.GetNextDocument(doc)
      Wend
      
      
'formating the Worksheet
      If doformat = 6 Then
            BarMsg = "One moment please..."
            UpdateMsg = "Formating the document..."
            If visualproc Then Call RefreshProgress.UpdateProgressText (BarMsg, UpdateMsg) Else Print Updatemsg
            excelWorksheetObject.Range("A2:" + m_let + Cstr(nl) ).Select
            excelAppObject.Selection.Columns.AutoFit
            excelWorksheetObject.Range("A1:" + m_let + Cstr(nl)).Select
            With excelAppObject.Selection
                  .AutoFormat SelAutoForm, False, True, False, True, True, False
                  .VerticalAlignment = -4160
            End With
            
            excelWorksheetObject.Rows("1:1").Select
            With excelAppObject.Selection
                  .VerticalAlignment = -4107
                  .HorizontalAlignment = -4108
                  .WrapText = True
                  .Orientation = Cint(titlebar)
                  .ShrinkToFit = False
                  .MergeCells = False
                  RowHeight = 215
            End With
            
            excelWorksheetObject.Range("A:" + m_let).Select
            With excelAppObject.Selection.Font
                  .Name = "Arial"
                  .Size = 10
            End With
            
            excelAppObject.Selection.Columns.Autofit
            excelWorksheetObject.Range("A1").Select
            
            With excelAppObject.Windows(1)
                  .SplitRow=1
                  .FreezePanes=True
            End With
            
            With excelWorksheetObject.PageSetup
                  .Orientation = 2
                  .LeftHeader = "&""Arial,Bold""&18"+db.Title+" - "+ UIViewAlias
                  .CenterHeader = ""
                  .RightHeader = "Datum: &D"
                  .LeftFooter = ""
                  .CenterFooter = ""
                  .RightFooter = "Seite &P"
                  .PrintArea = ("A1:"+ m_let + Cstr(nl))
                  .PaperSize = 9
                  .CenterHorizontally = True
                  .FitToPagesTall =False
                  .zoom = False
                  .FitToPagesWide=1
                  .PrintTitleRows=excelWorksheetObject.Rows("1:1").Address
            End With
      End If
      excelAppObject.Visible = False
      excelAppObjec.activeworkbook.saveas "K:\TSD\ASSETDB\elutest.xls"
      excelAppObjec.quit
      Exit Sub
      
ExitExcel:
      Print "Error in Line " + Cstr(Erl) + " : " + Cstr(Error)
      excelAppObject.DisplayAlerts = False
      excelAppObject.Quit
      Exit Sub
End Sub
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 13572145
it should be dim ss as new notessession

sorry for the typo.
0
 

Author Comment

by:padillrr
ID: 13574790
I'm still getting the variant does not contain an object and when I run it through the debugger it's on this line  excelAppObject.DisplayAlerts = False

if I comment that line out then it goes to the next line, I've also noticed that once the debugger gets to
Set doc = view.getfirstdocument
the code skips down to the line that is causing the error........any ideas?
0
 
LVL 19

Accepted Solution

by:
madheeswar earned 2000 total points
ID: 13589768
Check this code for Schedule Export to Excel:
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



Set db = session.CurrentDatabase

ViewString= "<your View Name here>"

Set dataview = db.getview(ViewString)

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 = True
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 "<Location and name of desired file>"
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 = "someone@somewhere"
'MRKnotice.CopyTo = "someone@somewhere"
'MRKnotice.BlindCopyTo = "someone@somewhere"
MRKnotice.SentBy = "Whomever"
MRKnotice.Subject = "Whatever"
Set RTItem = MRKnotice.CreateRichTextItem("Body")
Call RTItem.EmbedObject(Embed_Attachment, "", "<Location and name of desired file>")
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 = "<Location and name of desired file>"

'Our agent ran weekly or daily so this is enough to make the file name unique
strFileDate = Format$(Today, "MMDDYYYY")
Filecopy filePath, "c:\notes\"&ViewString & strFileDate & ".xls"
Kill filePath
0
 

Author Comment

by:padillrr
ID: 13591286
Well it worked it opened excel and exported the data but it did not save it to where I wanted it to. Where do I put the code below in order to have th espreadsheet saved to the drive ? I don't need it renamed I just need it to overwrite the file there and keep the same name....

Dim filePath As String
Dim strFileDate As String
filePath = "<Location and name of desired file>"
0
 

Author Comment

by:padillrr
ID: 13591759
Now I'm getting an object variable not set on this line:
maxcols= dataview.columncount
.......just in case I'm running ND 6.5.1
It ran earlier but like I said it opened excel and exported the data, it did not save it to the folder, then it told me to insurethe folder existed, that the file name was not using improper characters and that the rights were set properly on the folder. I verfied all of it was good but now I get the object variable not set......
0
 

Author Comment

by:padillrr
ID: 13614177
hello all! Well I finally got it to work for me you'll see I had to comment out a line and add the view name to the viewstring.....this worked and now I am saving to the networkdrive as well only problem is that I cannot make it overwrite the existing file from the previous export.......any ideas......by the wya I will be awarding the points to madheeswar cause I used most of his code.....thanks
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 13619891
The last few lines in my code should do the trick of checking Duplicate file names and save with other name.
0
 

Author Comment

by:padillrr
ID: 13622012
I'm looking to save the file with the SAME name over itself. I don't want a duplicate I want it to either delete the file that's there with that name or just overwrite it.....
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

For users on the Lotus Notes 8 Standard client, this article provides information on checking the Java Heap size and adjusting it to half of your system RAM in attempt to get the Lotus Notes 8.x Standard client to run faster.  I've had to exercise t…
Problem "Can you help me recover my changes?  I double-clicked the attachment, made changes, and then hit Save before closing it.  But when I try to re-open it, my changes are missing!"    Solution This solution opens the Outlook Secure Temp Fold…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

765 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