• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

Automatic Export to Excel to a network drive

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
padillrr
Asked:
padillrr
  • 11
  • 6
  • 2
1 Solution
 
p_parthaCommented:
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
 
padillrrProject CoordinatorAuthor Commented:
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
 
p_parthaCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
padillrrProject CoordinatorAuthor Commented:
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
 
madheeswarCommented:
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
 
padillrrProject CoordinatorAuthor Commented:
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
 
madheeswarCommented:
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
 
padillrrProject CoordinatorAuthor Commented:
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
 
padillrrProject CoordinatorAuthor Commented:
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
 
madheeswarCommented:
dim view as notesview
dim db as notesdatabase
set ss as new notessession
set db=ss.currentdatabase
set view=db.getview("yourViewName")


0
 
padillrrProject CoordinatorAuthor Commented:
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
 
madheeswarCommented:
it should be dim ss as new notessession

sorry for the typo.
0
 
padillrrProject CoordinatorAuthor Commented:
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
 
madheeswarCommented:
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
 
padillrrProject CoordinatorAuthor Commented:
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
 
padillrrProject CoordinatorAuthor Commented:
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
 
padillrrProject CoordinatorAuthor Commented:
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
 
madheeswarCommented:
The last few lines in my code should do the trick of checking Duplicate file names and save with other name.
0
 
padillrrProject CoordinatorAuthor Commented:
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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 11
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now