Importing to Record from Excel

Posted on 2006-03-28
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)
      Messagebox "Import done successfully."
      Set xls = Nothing
      Set sheet = Nothing
End Sub
Question by:jforget1
    LVL 46

    Expert Comment

    by:Sjef Bosman
    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?

    Author Comment

    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.

    LVL 46

    Expert Comment

    by:Sjef Bosman
    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.
    LVL 63

    Expert Comment

    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 !
    LVL 63

    Expert Comment

    sililar= similar

    Author Comment

    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.
    LVL 18

    Expert Comment

    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....


    Author Comment

    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.
    LVL 63

    Expert Comment


    ub ImportXL_Reg
    This subroutine imports data from an Excel Sheet.
    Created by Dr. Nadir Patir
    Istanbul Pazarlama A.S., Istanbul Turkey
    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

    %END REM
          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

    Author Comment

    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
    LVL 63

    Expert Comment

    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 !
    LVL 46

    Expert Comment

    by:Sjef Bosman
    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"
    LVL 18

    Accepted Solution

    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
    LVL 46

    Expert Comment

    by:Sjef Bosman
    Goodness gracious me... Can't (and on't want to) beat that :(
    LVL 18

    Expert Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    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

    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.
    Article by: Rob
    Notes 8.5 Archiving Steps and Tips This article covers setting up a Notes archive, and helps understand some of the menu choices making setting up and maintaining a Notes archive file easier.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    794 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

    15 Experts available now in Live!

    Get 1:1 Help Now