We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now


Importing to Record from Excel

jforget1 asked
Medium Priority
Last Modified: 2013-12-18
I am trying to get some script together which will allow the data from a spreadsheet to be imported into a form. I will have a spreadsheet where the Office ID is in the first column, I will have a record open in notes which will have the Office in a field on the form. I want the system to look through each row of the spreadsheet and pull in the data if it matches the office ID on the open record. The code below is close but it forces me to separate the data for each office into its own tab. On the form I will have a series of fields in a table that the data will fall into. Below is just a basic layout where Import 1a..b..c would be the data that has matched this office ID from columns A,B,C in the spreadsheet. Hopefully what I am asking for amkes sense, appreciate any assistance.

Import1a  Import 1b  Import 1c
Import2a  Import 2b  Import 2c
Import3a  Import 3b  Import 3c
Import4a  Import 4b  Import 4c
Import5a  Import 5b  Import 5c

Sub Click(Source As Button)
      Dim session As New NotesSession
      Dim workspace As New NotesUIWorkspace
      Dim db As NotesDatabase
      Dim doc As NotesDocument
      Dim item As NotesItem
      Dim Filename As String
      Set db = session.CurrentDatabase
      Set uidoc = workspace.CurrentDocument
      Set doc = uidoc.Document
      Dim xlapp As Variant
      Dim xlsheet As Variant
      Dim x As Long
      Filename = "C:\dlm_refresh\dlm_refresh.xls"
'      Excel.Workbooks.Open Filename '// Open the Excel file
      Set xlapp = GetObject( Filename , "" )
      shtname= doc.office_id(0)   '  each sheet in excel is named with the agency 3-character id.  I pull this from the doc to locate the correct sheet.
      Set xlsheet = xlapp.WorkSheets( shtname )
      doc.userid1 = Clng(xlsheet.range("C2").value)
      doc.userid2 = Clng(xlsheet.range("C3").value)
      doc.userid3 = Clng(xlsheet.range("C4").value)
      doc.userid4 = Clng(xlsheet.range("C5").value)
      doc.userid5 = Clng(xlsheet.range("C6").value)
      doc.asset1 = Clng(xlsheet.range("B2").value)
      doc.asset2 = Clng(xlsheet.range("B3").value)
      doc.asset3 = Clng(xlsheet.range("B4").value)
      doc.asset4 = Clng(xlsheet.range("B5").value)
      doc.asset5 = Clng(xlsheet.range("B6").value)
      Call doc.save(True,True)
      Messagebox "Import done successfully."
      Set xls = Nothing
      Set sheet = Nothing
End Sub
Watch Question

Sjef BosmanGroupware Consultant

I tried to find out what your real question is, but I can't. I don't quite get it.

I assume you need to fetch the current document's office-id
    id= doc.OfficeID(0)
and you need to loop through all lines in the spreadsheet
    i= 1
    Do While Not(IsEMpty(xlsheet.Cells(i,1).Value)
        ' add some code
        i= i + 1

But why do you have to visit each row, and what are you going to do with those rows?


This is a tough one to explain, I will try and get it a little clearer. The person using this DB has a large spreadsheet which consists of machine information for laptops. THese are for machine which will need to be replaced soon. Instead of him having to enter the data for each office. I am trying to make it so he can start a new record enter the Office ID which will be the reference item. He will then click the import button and the system will then look to the spreadsheet and go through row by row to see if there are any machine for that office. If there are it wil pull the data from the sheet, (asset tag, serial # etc.). I want to have a record for each office and will setup a table with 25 rows of fields for the machine information to drop into. There would never be an office that needs more than 25 rows.

I know this is a weird one and I hope this makes it a lil clearer. I am new with LS so writing something like this is a challenge.

Sjef BosmanGroupware Consultant

You want a record per office, and not per machine? Never more than 25 machines per office? Never say never...

You know that the approach you chose might make things more difficult for you. You have to build code that will move machines in the office-document when one machine is deleted. Or inserted. Or whatever. Also, machine-dependent views are not possible. On the other hand, if you want to compare the Excel file with the documents present regularly, it is somewhat easier to do that with only one document. Will the user continue to use the spreadsheet? Altogether, I'm not in favour of the approach.
Top Expert 2007

I would build it all in advance. Import the Excel info into a Notes DB using a form that is sililar to the column setup.
Then run an agent that build the per office info.

I hope this helps !
Top Expert 2007

sililar= similar


The only reason we want to do them all on one form is so the person we are sending to does not get a ton of  emails in their inbox. If I can get a way where I can wrap all this up into one note when sending, maybe I can do that. I have not done this particular thing before and am trying to think of the best way.

Hi jforget1,
Both SysExpert and sjef are right here... you have a large worksheet, and as usual, when people use Notes they try to make Notes into Excel.   Excel is a spreadsheet, Notes is a database.

One machine= one form

An office can have many machines, so the office is the category, and the form is the single machine.  The view will show all machines for an office, another view can show all machines by user, or by machine type or by OS.

The form is simply:

Office ID
Machine asset tag
Serial #
and so on.
And can include - replacement date and cross reference to what it was replaced by... so now you have a view of DONE, TODO
Your views show the accumulated data any way you want to present them.

If you need to send an email, print the view to a PDF, or do a newsletter email, or have a report form that rolls everything into a rich text field, or back out to excel....



I am up for using this method of doing one form for each machine. How do I import into a form if there are no records in advance. I have done imports where I reference a field, happened to be office and then imported the data based on that reference point. But how do you import where one row in the sheet is populated into a new record? Have noever done this.
Top Expert 2007


ub ImportXL_Reg
This subroutine imports data from an Excel Sheet.
Created by Dr. Nadir Patir
Istanbul Pazarlama A.S., Istanbul Turkey
E-Mail: nadir@istpaz.com.tr
This routine is used in TeamWork CRM software of author.


Row 1 of Excel Sheet must contain Field Names to be imported.
Column 1 of Excel Sheet must contain Form name.
(You can import data to different forms based on form name in column 1.)

Each row will be imported to a document.

MR 1/31/05 - adding merge options see Import_mrg_XL

      Dim ws As New NotesUIWorkspace
      Dim session As New NotesSession
      Dim db As NotesDatabase
      Dim doc As NotesDocument
      Dim item As NotesItem
      Dim App As Variant, Wbook As Variant, WSheet As Variant
      Dim v As Variant
      Dim row As Double
      Dim  form As String, fileXL As String, calcf As String,  t As String
      Dim title(255) As String
      Dim k As Integer, cols As Integer
      Dim cnt As Long
      Set db = session.CurrentDatabase
      Set App = CreateObject("Excel.Application")
      App.Visible = False
'Choose Excel file
      v=ws.OpenFileDialog(False,"Please Select Excel File", "*.xls", "")
      If Isempty(v) Then Exit Sub
      App.Workbooks.Open fileXL
      Set Wbook = App.ActiveWorkbook
      Set WSheet = Wbook.ActiveSheet
      If Wsheet.Cells(1, 1).Value<>"Form" Then
            Messagebox "First Column of Excel sheet must contain Form names"
            Goto fin
      End If
'Recalc question
'      x(0)="Yes"
'      x(1)="No"
'      calcf= ws.Prompt( PROMPT_OKCANCELLIST, "CALC", "Calculate fields on  form during document Import?","No", x )
''      If calcf="" Then Exit Sub
      calcf=      "No"
'Read field names
      For k=1 To 255
            title(k)=Wsheet.Cells(1, k).Value
            If Trim(title(k))="" Then
                  Exit For
            End If
'Import documents
      form=Trim(Cstr(Wsheet.Cells(row, 1).Value))
      Do While form<>""
            Set doc=db.CreateDocument
            doc.Form = form
            For k=2 To cols
                  t=Trim(Cstr(Wsheet.Cells(row, k).Value))
                  If t<>"" Then
                        Set item = doc.ReplaceItemValue( title(k),  t )
                  End If
            If calcf="Yes" Then
                  Call doc.ComputeWithForm(False,False)
            End If
            Call doc.Save(True,True)
            Print cnt
            form=Trim(Cstr(Wsheet.Cells(row, 1).Value))
      Messagebox Cstr(cnt) + " documents imported"
      Set App = Nothing
      Set Wbook = Nothing
      Set Wsheet = Nothing
End Sub


I have put this code into a view in the database and have formatted the excel sheet as advised and I have matched the header for each column to match the field names but I am getting 0 records imported. Here is a copy of the data in the spreadsheet. dlm_refresh_tracking is the name of the form I want to import into.

dlm_refresh_tracking      asset      userid      office_id
dlm_refresh_tracking      7726484      04e4551      04E
dlm_refresh_tracking      7727189      04e4571      04E
dlm_refresh_tracking      7726103      13a8701      13A
dlm_refresh_tracking      7726717      44j9591      44J
Top Expert 2007

1) The agent stops on the first row with no Form name.
2) The Excel sheet must be the first in a workbook.

So make sure you have no blank rows at the begnining.
Also . Open Office in Excel Format does not always work, use MS Excel.

I hope this helps !
Sjef BosmanGroupware Consultant

If you just want to put all rows in your Notes database, never to use the spreadsheet again, then import all data. Save the data from Excel as a Lotus 1-2-3 file, with extension .wk4, and use fieldnames as the header. It's very well described in the Help databases.

http:Q_21200019.html "import excel data into contacts"
http:Q_20723818.html "Is it possible to import and export to a Excel Spreadsheet"
Or you can paste this into an agent, shared, Target = None, security = 3

It will create a view to make sure you're not importing stuff twice, and will create a field for each title in the first row.  But, I did account for Asset, userid and office_id.  

Please make sure you read the comments and configure the CONST values.

'Import Excel:

Option Public
Option Declare

'Important set these values to your environment..........
Const str_VIEWNAME="Excel Import Check"
Const str_FORM = "Refresh Tracking"
Const str_IMPORTFILE="C:\assettracking.xls"
Const max_RECORDS=100                   'Set this to the maximum number of records you want to loop
Const max_SKIPROWS=10                    'Set this to the number of blank rows you want to skip before you assume
                                                                       'the rest of the worksheet is blank
'I am using these to concatentate three column values into a unique key to avoid importing duplicates
Const chk_COL1=2       'Asset
Const chk_COL2=3       'userid
Const chk_COL3=4       'office_id
Sub Initialize
          'Clear Lotus Notes View Of Employee Records    
      Dim session As New NotesSession
      Dim db As NotesDatabase
      Dim view As NotesView
      Dim vColl As NotesViewEntryCollection
      Dim max_Columns As Integer
      Set db = session.currentdatabase
      Set view = db.getview(str_VIEWNAME)
      If view Is Nothing Then
            Set view = CreateChkView(session)
            If view Is Nothing Then
                  Msgbox "Sorry, unable to create the check view to avoid importing duplicate documents"
                  Exit Sub
            End If
      End If
      Dim xlFilename As String
     'IMPORT FILENAME.............................................
      xlFilename= str_IMPORTFILE
      Dim doc As NotesDocument, titles As Variant
      Dim row As Integer, col As Integer, k As Integer
      Dim written As Integer            
      On Error Goto Handle_Error
     'First Connect to Excel and see if there are any records to import......................
      Dim Excel As Variant
      Dim xlWorkbook As Variant
      Dim xlSheet As Variant
      Print "Connecting to Excel..."
      Set Excel = CreateObject( "Excel.Application" )
      If Excel Is Nothing Then
            Print "Unable to open the excel object..."
            Exit Sub
      End If
      Excel.Visible = False '// Don't display the Excel window
      Excel.Workbooks.Open xlFilename '// Open the Excel file
      Print "Opening " & xlFilename & "..."
      Set xlWorkbook = Excel.Workbooks(1)
      Set xlSheet = Excel.Workbooks(1).Worksheets(1)
      Dim chk As String, skiprow As Integer
      Dim FieldNames(255) As String, tmpStr As String
      row = 1 '// These integers intialize to zero anyway
      col = 1
      written =0      
      Print "Starting import from Excel file..."      
      With xlSheet
            For k = 1 To 255
                  tmpStr =Format(.cells(row,k).value)
                  If Len(Trim(tmpStr))>0 Then
                        FieldNames(k) = tmpStr
                        Select Case K
                        Case 1
                              Msgbox "First Row and Column in the worksheet must contain the Field Titles",,"Column Title Missing"
                              Exit Sub
                        Case Else
                              max_COLUMNS = k-1
                              Exit For
                        End Select
                  End If
      End With
      Row = 2      
      Do While (row<=max_RECORDS)  
            With xlSheet                  
                  chk =Lcase(Trim(Format(.cells(row,chk_COL1).value) + _
                  Format(.cells(row,chk_COL2).value) + _
                  If Len(chk)<0 Then
                        skiprow = skiprow +1
                        Goto NextRow
                  End If                  
                  Select Case row
                  Case 1
                        'Header Row is Titles
                  Case Else
                        If Len(chk)>0 Then
                        'Check to see if the document has been created
                              Set doc = view.GetDocumentByKey(chk, True)
                              If Not doc Is Nothing Then Goto NextRow
                              Set doc = New NotesDocument(db)
                              doc.Form = str_FORM  'Form name
                              'Update to Fields
                              For k = 1 To max_COLUMNS                                          
                                    Call doc.ReplaceItemValue(FieldNames(k), Format(xlsheet.cells(row, k).value))                                                
                              doc.Save True, False, True
                              Print " Creating Record..." + Cstr(row)                  
                              written = written +1
                                     'If we find a record, then set the skiprow back to 0 - it was just a blank row
                              skiprow = 0
                              Set doc = Nothing
                        End If
                  End Select                  
            End With        
            row= row+1
            If skiprow =>max_SKIPROWS Then
                  Print Cstr(max_SKIPROWS) + " blank rows reached... assuming the rest of the worksheet is blank..."
                  Exit Do
            End If
      Print "Disconnecting from Excel..."
      Excel.Quit '// Close Excel
      Set Excel = Nothing '// Free the memory that we'd used
      Exit Sub
      If Err = 213 Then
            Print "Import File not found..."
            Err = 0
            Exit Sub
      End If
      On Error Goto 0
      If Not xlworkbook Is Nothing Then
            Excel.activeworkbook.close         '// Close the Excel file without saving (we made no changes)
            Excel.Quit '// Close Excel
            Set Excel = Nothing '// Free the memory that we'd used
      End If
      Exit Sub
End Sub
Sub Terminate
End Sub

Function CreateChkView(thisSession As NotesSession) As NotesView
      Dim tmpView As NotesView, columnCount As Integer      
      Dim strSelect As String, strCOL As String
      ColumnCount = 0
      strSelect = {SELECT ((Form = "} + str_FORM + {"));@All}
      strCOL={@LowerCase(@Text(Asset) + @Text(UserID) + @Text(OfficeID))}
      Set tmpView = thisSession.currentdatabase.CreateView(str_VIEWNAME, strSelect)
      If Not tmpView Is Nothing Then
            Dim col As NotesViewColumn, Col1 As NotesViewColumn, Col2 As NotesViewColumn, Col3 As NotesViewColumn            
            Set col = tmpView.CreateColumn(1, "KEY", strCOL)
            Col. IsSorted = True
            Set col1 = tmpView.CreateColumn(2, "Asset", "Asset")
            Set col2 = tmpView.CreateColumn(3, "UserID", "userid")
            Set col3 = tmpView.CreateColumn(4, "Office ID", "office_id")
            Set CreateChkView = tmpView            
      End If      
End Function

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Sjef BosmanGroupware Consultant

Goodness gracious me... Can't (and on't want to) beat that :(

Unfortunately, the 4.6 import code doesn't work well with newer versions of excel.  So, I just supplied one that I know works with Office 2003, and reworked it to use the data supplied.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.