Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

Having problems exporting data from Notes to an Excel Spreadsheet

Hello All,

I am having a problem exporting data to an excel spreadsheet from notes.  In one of the columns (the platform colums) all the data is not transferring over.  This column can have multiple data in it...and when I RUN export to excel, it only brings in the first platform over...not the others.  For example, let's say the plat form column has the following data in it: "Lotus Notes, Netware Servers, Windows Servers".  The only data that would transfer over would be "Lotus Notes".  Any ideas on why this would happen?

Here is the code, any help would be appreciated(in the code below the plaform colum is doc.ColumnValues(4)):

Sub Click(Source As Button)
     Dim workspace As New NotesUIWorkspace    
     Dim session As New NotesSession
     Dim view As NotesView
     Dim db As NotesDatabase
     Dim doc As NotesDocument
     Dim xlApp As Variant
     Dim i As Integer
     Dim row As Integer
     Dim count
     
     
     
     Set db = session.CurrentDatabase
     Set view = db.GetView( "3. Misc\7. All By Number")
     Set doc = view.GetFirstDocument    
     Set xlApp = CreateObject("Excel.application")
     
     xlApp.Visible = True
     xlApp.Workbooks.Add
     'Format and populate first worksheet
     With xlApp.Worksheets(1)
          .Name = "Changes Broken Down by Platform"
          .Range("A1").Value = "Platform"
          .Range("B1").Value = "Date"
          .Range("C1").Value = "Status"    
          .Range("D1").Value = "Description"    
          .Range("A1:D1").Font.Bold = True
          .Cells.Font.Name = "Arial"          
          row = 2          
         
         
          Do While Not doc Is Nothing
          'count = 0
          'Do Until count = 16
               
                        .Range("A" & Trim(Str(row))).value = doc.ColumnValues(4)
               '.Range("A" & Trim(Str(row))).value = doc.platform              
               .Range("B" & Trim(Str(row))).value = doc.ColumnValues(3)
               .Range("C" & Trim(Str(row))).value = doc.ColumnValues(7)
               .Range("D" & Trim(Str(row))).value = doc.ColumnValues(8)
               Set doc = view.GetNextDocument(doc)
               row = row+1
               count = count + 1
               
          Loop
         
         
     End With
End Sub

Thanks...
0
fiqbal
Asked:
fiqbal
  • 6
  • 2
  • 2
  • +1
1 Solution
 
ArunkumarCommented:
Is your view showing multiple values ?
0
 
ArunkumarCommented:
Okay in the column value instead of just the fieldname have this formula,

@Implode(FIELDNAME ; ",")

Replace the field name with yours.  And the code should work fine.

Good Luck!
Arun.
0
 
HemanthaKumarCommented:
Replace the line
              '.Range("A" & Trim(Str(row))).value = doc.platform              
             
with
          Set item = doc.GetFirstItem("platform")
          Forall v In item.Values
               temp = temp + Chr$(10) + v
          End Forall
          .Range("A" & Trim(Str(row))).value = temp

This will set the cell with all available values.

There is similar question which required to put the multi values in seperate rows, check it out http://www.experts-exchange.com/jsp/qShow.jsp?ta=lotusnotes&qid=20267343

This question is big and I don't think it will allow me to paste whole history.

~Hemanth
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!

 
fiqbalAuthor Commented:
Yes, the view shows multiple values
0
 
zvonkoCommented:
Do it simply this way:
  .Range("A" & Trim(Str(row))).value = doc.GetFirstItem("platform").Text

You should also close this old question:
http://www.experts-exchange.com/jsp/qShow.jsp?ta=lotusnotes&qid=20187205

Good luck,
zvonko



0
 
fiqbalAuthor Commented:
Thanks Hemanth,

That LotusScript helped.  I get an error because I have blank data in the set.  When it comes to a line with blank data I get the "Object variable not set".  But the code you sent me helped in pulling in all the values I needed.  Thanks Again!!!
0
 
HemanthaKumarCommented:
Does the error occur in the modified block provided by me or somewhere else...!
0
 
fiqbalAuthor Commented:
Thank you all for your input...
0
 
fiqbalAuthor Commented:
Thank you...
0
 
fiqbalAuthor Commented:
What happens is that since there is blank data in the set...it tries to modify blank data...when it comes to a blank column...and that's when the error comes up..
0
 
fiqbalAuthor Commented:
What happens is that since there is blank data in the set...it tries to modify blank data...when it comes to a blank column...and that's when the error comes up..
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

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