Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Having problems exporting data from Notes to an Excel Spreadsheet

Posted on 2002-04-23
11
Medium Priority
?
266 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
[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
  • 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 400 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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…
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.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
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…

670 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