Solved

Having problems exporting data from Notes to an Excel Spreadsheet

Posted on 2002-04-23
11
240 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This is an old article, please see an updated version of this article, located here: http://www.experts-exchange.com/articles/23619/Notes-8-5x-Windows-7-Notes-info-and-tips.html
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…
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…
This video discusses moving either the default database or any database to a new volume.

743 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

13 Experts available now in Live!

Get 1:1 Help Now