Solved

Having problems exporting data from Notes to an Excel Spreadsheet

Posted on 2002-04-23
11
243 Views
Last Modified: 2013-12-18
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
Comment
Question by:fiqbal
  • 6
  • 2
  • 2
  • +1
11 Comments
 
LVL 9

Expert Comment

by:Arunkumar
ID: 6963380
Is your view showing multiple values ?
0
 
LVL 9

Expert Comment

by:Arunkumar
ID: 6963441
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
 
LVL 24

Accepted Solution

by:
HemanthaKumar earned 100 total points
ID: 6963546
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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

Author Comment

by:fiqbal
ID: 6963621
Yes, the view shows multiple values
0
 
LVL 10

Expert Comment

by:zvonko
ID: 6963651
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
 

Author Comment

by:fiqbal
ID: 6963741
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
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 6963768
Does the error occur in the modified block provided by me or somewhere else...!
0
 

Author Comment

by:fiqbal
ID: 6963776
Thank you all for your input...
0
 

Author Comment

by:fiqbal
ID: 6963778
Thank you...
0
 

Author Comment

by:fiqbal
ID: 6963789
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
 

Author Comment

by:fiqbal
ID: 6963854
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

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…
IBM Notes offer Encryption feature using which the user can secure its NSF emails or entire database easily. In this section we will discuss about the process to Encrypt Incoming and Outgoing Mails in depth.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

813 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now