Solved

import document

Posted on 2004-08-30
5
377 Views
Last Modified: 2013-12-18
Hi,
I have an Excel file with 3 columns: Code, Name, Age. I want to import the data in this file to Notes by Notes script ('cause I want to check something before import). Please help. Is there any other way?
PN.
0
Comment
Question by:phuongnguyen
[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
  • 2
  • 2
5 Comments
 
LVL 19

Accepted Solution

by:
madheeswar earned 70 total points
ID: 11938812
Sub Initialize
'========================================================
      
      'Main Functionality: Import rows from Excel sheet and store in Notes documents, while user input file path.
      'Limitation: If there are consecutive 2 empty rows, then end the code
'========================================================
      Dim FileNum As Integer
      Dim xlFilename As String
      On Error Goto Errorhandler
      Filenum% = Freefile()
      xlFileName$ = Inputbox("What file name and path? example:C:\June282001.xls")
      
      If xlFileName$="" Then
            Exit Sub
      End If
      
      Dim session As New NotesSession
      Dim db As NotesDatabase
      Dim view As NotesView
      Dim doc As NotesDocument
      Set db = session.CurrentDatabase
      Set doc = New NotesDocument(db)
      Dim One As String
      
      Dim row As Integer
      Dim written As Integer
      
 '// Next we connect to Excel and open the file. Then start pulling over the records.
      Dim Excel As Variant
      Dim xlWorkbook As Variant
      Dim xlSheet As Variant
      Print "Connecting to Excel..."
'      Set Excel = CreateObject( "Excel.Application.8" )
      Set Excel = CreateObject( "Excel.Application" )
      Excel.Visible = False '// Don't display the Excel window
      Print "Opening " & xlFilename & "..."
      Excel.Workbooks.Open xlFilename '// Open the Excel file
      Set xlWorkbook = Excel.ActiveWorkbook
      Set xlSheet = xlWorkbook.ActiveSheet
      
 '// Cycle through the rows of the Excel file, pulling the data over to Notes
      Goto Records
      Print "Disconnecting from Excel..."
      xlWorkbook.Close False '// Close the Excel file without saving (we made no changes)
      Excel.Quit '// Close Excel
      Set Excel = Nothing '// Free the memory that we'd used
      Print " " '// Clear the status line
      
      
Records:
      row = 0 '// These integers intialize to zero anyway
      written = 0
      tmpnullrows=0
      
      Print "Starting import from Excel file..."
      
      Do While True
Finish:
            With xlSheet
                  row = row + 1
                  
                  If .Cells (row, 1).Value = "" And .Cells(row,2).Value = "" _
                  And .Cells (row, 3).Value = "" And .Cells(row,4).Value = "" And .Cells (row, 5).Value = "" _
                  And .Cells(row,6).Value = "" And .Cells (row, 7).Value= "" _
                  And .Cells(row,8).Value = "" And .Cells (row, 9).Value = "" And .Cells (row, 10).Value = "" And .Cells (row, 11).Value = "" And .Cells (row, 12).Value = "" _
                  And .Cells (row, 13).Value = "" And .Cells (row, 14).Value = "" And .Cells (row, 15).Value = "" And .Cells(row,16).Value = "" Then
                        tmpnullrows=      tmpnullrows+1
                        If tmpnullrows=2 Then
                              Goto Done
                        End If
                        Goto Finish
                        
                  End If
                  
                  If .Cells (row, 1).Value = "Planning Year" And .Cells(row,2).Value= "ID" _
                  And .Cells (row, 3).Value = "Entered By" And .Cells(row,4).Value= "CRB Category" _
                  And .Cells (row, 5).Value = "FinSys Group" And .Cells(row,6).Value= "Finsys Owner" _
                  And .Cells (row, 7).Value = "Office Requested" And .Cells(row,8).Value= "Requestor Name" _
                  And .Cells (row, 9).Value = "Office Priority" And .Cells(row,10).Value = "Product" And .Cells(row,11).Value = "Prospective Project Name" _
                  And .Cells(row,12).Value = "Prospective Project Description" And .Cells(row,13).Value = "Application(s) Impacted" _
                  And .Cells(row,14).Value = "Requested Implementation Date" And .Cells(row,15).Value = "Funding Available from Office" _
                  And .Cells(row,16).Value = "Comments" Then
                        Goto Finish
                  End If
                  
                  'Set view = db.GetView("Main View")
                  Set doc = db.CreateDocument '// Create a new doc
                  doc.Form = "FinSysWP" '//FormName
                  doc.PlanYear = .Cells( row, 1 ).Value
                  doc.ID= .Cells(row, 2 ).Value 'Should be numeric
                  doc.EnteredBy = .Cells(row, 3).Value 'Should be Common user Name
                  doc.CRBCategory = .Cells( row, 4 ).Value
                  doc.FinSysGroup = .Cells(row, 5).Value
                  doc.FinSysOwner = .Cells( row, 6).Value
                  doc.OfficeRequested= .Cells(row, 7).Value
                  doc.Requestor = .Cells( row, 8).Value
                  doc.OfficePriority= .Cells(row, 9).Value
                  doc.Product = .Cells(row, 10).Value
                  doc.ProjectName = .Cells(row, 11).Value
                  doc.ProjDescription = .Cells(row, 12).Value
                  doc.ApplcnImpact = .Cells(row, 13).Value
                  doc.ImplDate = .Cells(row, 14).Value
                  doc.Funding = .Cells(row, 15).Value
                  doc.Comments = .Cells(row, 16).Value
                  tmpnullrows=0
                  Call doc.Save( True, True ) '// Save the new doc
                  written = written + 1
                  Print written
                  
                  Goto Finish
                  
            End With
      Loop
      Return
Errorhandler:
      Print "Error" & Str(Err) & ": " & Error$
      Resume Next
Done:
      Print "Finished importing"
End Sub
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 11939362
So EE indeed IS a rent-a-coder site! You already solved the problems the questioner was still unaware of.

Sjef ;)
0
 
LVL 13

Expert Comment

by:CRAK
ID: 11939701
I bet madheeswar had hat code ready in a personal library somewhere!
Or he's got nothing better to do and desterate for points......
;-))
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 11940372
CRAK/SJEF,
Last week I wrote the above code for one application. So, placed it.

ofcourse I am desparate for points.

Hope you guys are doing well.

-Thanks
madheeswar
0
 
LVL 13

Expert Comment

by:CRAK
ID: 11940457
All well here, thanks.
Don't worry about the code.... I think any other expert would have done the same thing!
;-))
0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

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

For users on the Lotus Notes 8 Standard client, this article provides information on checking the Java Heap size and adjusting it to half of your system RAM in attempt to get the Lotus Notes 8.x Standard client to run faster.  I've had to exercise t…
Notes Document Link used by IBM Notes is a link file which aids in the sharing of links to documents in email and webpages. The posts describe the importance and steps to create a Lotus Notes NDL file in brief.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

705 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