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
  • 4
  • 4
  • 4
  • +1
LVL 46

Expert Comment

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

ID: 16321706
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
ID: 16323290
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.
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

LVL 63

Expert Comment

ID: 16324109
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

ID: 16324112
sililar= similar

Author Comment

ID: 16324230
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

ID: 16329617
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

ID: 16332013
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

ID: 16337362

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

      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

ID: 16343133
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

ID: 16345886
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
ID: 16347060
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

marilyng earned 500 total points
ID: 16348111
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
ID: 16381930
Goodness gracious me... Can't (and on't want to) beat that :(
LVL 18

Expert Comment

ID: 16388138
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.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
IBM Enterprise Integrator - ZID File 1 175
SQL Server linked server to Lotus notes 3 504
Event in Notes Form after open and visible 10 222
Only send email if contains "abcd" 3 148
  In today’s Arena we can’t imagine our lives without Internet as we are highly used to of it. If we consider our life style just for only 2 min we found that face to face communication is swapped by e-communication.  Every Where from Works place to…
Lack of Storage capacity is a common problem that exists in every field of life. Here we are taking the case of Lotus Notes Emails, as we all know that we are totally depend on e-communication i.e. Emails. This article is fully dedicated to resolvin…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

821 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