Agent to detach and import xls files automatically

One method to automate import from Excel to Notes docs.

fter spending a great deal of time to import information from Excel over the
years, I wrote the following code to pull data from Excel files. The code
allows me to get the users data into a blank database where I can manipulate
it. The script assumes that the values in the first row are the names to be
used for the field names and that the data begins on the second row. It
continues to search for columns until it finds a blank cell in the first row.
After finding which columns to use it adds a document for each row in the Excel
file until it finds a blank row. Hope you find the following useful.

=============================
Jason Goodloe
jgoodloe@evolvetech.com

Code

'Re-formated Version 'Multi-platform:

Option Public
Option Explicit


'WIN32
Declare Function W32_NEMGetFile Lib "nnotesws" Alias "NEMGetFile" ( wUnk As
Integer, Byval szFileName As String, Byval szFilter As String, Byval szTitle As
String ) As Integer
'WIN16
Declare Function W16_NEMGetFile Lib "_nem" Alias "NEMGetFile" ( wUnk As
Integer, Byval szFileName As String, Byval szFilter As String, Byval szTitle As
String ) As Integer
'OS2
Declare Function OS2_NEMGetFile Lib "inotesws" Alias "NEMGetFile" ( wUnk As
Integer, Byval szFileName As String, Byval szFilter As String, Byval szTitle As
String ) As Integer
'use nnotesws for Win95 and WinNT, inotesws for OS/2, and _nem for Win16




Sub Initialize
%REM
******************************************************************************
OverView: This Function deletes all of the documents in the current
database and then re-populates the database based on data
contained in the excel file. The column heading row is used
for the field names in the notes doucments.
Input: User must supply the path and file name for the excel file
Results: Creates a Notes document for each row in the excel spreadsheet,
excluding the header row
Called By: This function is run from the agent list
Assumptions: 1. Data is on one worksheet only and that this worksheet is
the first worksheet in the workbook
2 Worksheet uses the first row as a column heading
3. The first blank column header is assumed to be the
last column to read data in the file
4. The first blank row is assumed to be the last row of the file
5. Field names are the first 20 spaces of the column heading
excluding spaces
******************************************************************************
M O D I F I C A T I O N H I S T O R Y
******************************************************************************
Date BY
Description
----------------- -------------------- ------------------------------
09/22/1999 J.Goodloe Initial Development 4.6.2
******************************************************************************
%END REM
Dim S As New NotesSession
Dim db As NotesDatabase
Dim Doc As NotesDocument
Dim item As NotesItem
Dim strCellRange$
Dim varCellValue As Variant
'File Dialog
Dim strFileName As String*256
Dim strTitle$
Dim strFilter$
'Excel Application
Dim varExcelApp As Variant
Dim varExcelWB As Variant
Dim varExcelSheet As Variant
'Valid columns list
Dim strExcelCodeList List As String
Dim FirstletterCode&
Dim LastletterCode&
'Iterate Rows
Dim intExcelRow%
Dim bIsRowBlank%
Dim bRowValueFound%

Set db = S.CurrentDatabase

'File Dialog
strFileName = Chr(0)
strTitle$ = s.Commonusername & ", select your database NOW"
strFilter$ = "MS Excel Files|*.xls|All Files|*.*|" 'Use
this format for ANY file type

If IsDefined ("WIN32") Then
If W32_NEMGetFile (0, strFileName, strFilter$, strTitle$) <> 0 Then
strFileName = strFileName & |"| 'We
need to do this because the return is a NULL terminated string.
End If
Elseif IsDefined ("WIN16") Then
If W16_NEMGetFile (0, strFileName, strFilter$, strTitle$) <> 0 Then
strFileName = strFileName & |"|
End If
Elseif IsDefined ("OS2") Then
If OS2_NEMGetFile (0, strFileName, strFilter$, strTitle$) <> 0 Then
strFileName = strFileName & |"|
End
DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.  
http://searchdomino.techtarget.com/tip/1,289483,sid4_gci489539,00.html
 
---------------------------------------
Import from Excel
Nadir Patir
13 Apr 2004
Rating: -3.71- (out of 5)

With this code, you can import data from an Excel sheet directly (without saving it first as a Lotus 1-2-3 file).
Note: This will only work when run on a Windows platform with MS Office installed.

 
Sub ImportXL

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

EXCEL SHEET FORMAT:

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.
%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, x(1) 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
 fileXL=v(0)
 cnt=0
 
 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
 
'Read field names
 cols=1
 For k=1 To 255
  title(k)=Wsheet.Cells(1, k).Value
  If Trim(title(k))="" Then
   cols=k-1
   Exit For
  End If
 Next
 
'Import documents
 row=2
 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
  Next
 
'Calculate
  If calcf="Yes" Then
   Call doc.ComputeWithForm(False,False)
  End If
 
'Save
  Call doc.Save(True,True)
  cnt=cnt+1
  Print cnt
 
  row=row+1
  form=Trim(Cstr(Wsheet.Cells(row, 1).Value))
 
 Loop
 
fin:
 Messagebox Cstr(cnt) + " documents imported"
 App.Application.Quit
 Set App = Nothing
 Set Wbook = Nothing
 Set Wsheet = Nothing
 
End Sub
 

---------------------------------------

Actually 2 different methods.

The second one, slightly modified is what I use generally.

I hope this helps !

SysExpert,
 
When you have a moment, could you check out this thread: https://www.experts-exchange.com/questions/21712095/Domain-field-in-public-address-book-Internal-Notes-Addressing.html

I think you could help us clear up many questions.. thanx
m

Hi SysExpert,

thanks for the code. I notice this requires the user to specify where the excel sheet is. Is there a way the path can be put in the code, so that no user interaction is required?

Also, when you say the code looks for the first blank cell in a row before it stops importing rows, does this mean for example it will stop importing if any cell on a row is blank? Can this be adapted to all cells on a row?
Some of the records I am importing will have blank cells which I want to allow.

Thanks for your help!

in this section after the comment  , remove the first 2 lines and just hard code the last one as shown below

' Choose Excel file
 v=ws.OpenFileDialog(False,"Please Select Excel File", "*.xls", "")
 If Isempty(v) Then Exit Sub
 fileXL="yourfilename.xls"

Also the second input program will work until the first empty cell in the First column ( where the Form name is )

I hope this helps !

Ok thanks, I have the path hard coded in now.
Which part of the code deals with loop until all rows are copied exactly?

At the moment, my agent requires user to type the number of rows as below...

'Call Terminate
      Dim xlFilename As String
      ExcelPath1=Inputbox$("Enter no.of rows to export","Importing no.of Rows","2000")
 '// This is the name of the Excel file that will be imported
      xlFilename= "C:\testodbc.xls"

How can this be changed to loop until all rows are imported?

See the section that starts with

form=Trim(Cstr(Wsheet.Cells(row, 1).Value))
 
 Do While form<>""

This will loop until the first time it finds a blank value in the first column

I hope this helps !

Is that the only two lines I need? My agent so far is as follows:

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
      Set db = session.currentdatabase
      Set view = db.getview("edv")
      If Not view Is Nothing Then
            Set vColl = view.allentries
            If vColl.count>0 Then
                  Call vcoll.removeAll(True)
            End If
            If Not vColl Is Nothing Then Set vColl = Nothing
      End If
      
'Call Terminate
      Dim xlFilename As String
      ExcelPath1=Inputbox$("Enter no.of rows to export","Importing no.of Rows","2000")
 '// This is the name of the Excel file that will be imported
      xlFilename= "C:\testodbc.xls"
      
      Dim doc As NotesDocument
      Dim row As Integer
      Dim written As Integer
      Set db = session.CurrentDatabase
      Set doc = New NotesDocument(db)
      
 '// 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" )
      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 = 1 '// These integers intialize to zero anyway
      written =0
      
      Print "Starting import from Excel file..."
      
      Do While True '// We use a test a few lines down to exit the loop
Finish:
            With xlSheet
                  row = row + 1
                  
                  Set view= db.GetView("edv")
                  Set doc = db.CreateDocument '// Create a new doc
                  doc.Form = "EmpDetails" 'Form name
               'Update to Fields
                  doc.employee_number = .Cells(row, 1).Value
                  doc.employee_lastname = .Cells( row, 2).Value
                  doc.employee_firstname = .Cells( row, 3).Value
                  doc.employee_middlename = .Cells(row, 4).Value
                  
                  Call doc.Save( True, True ) '// Save the new doc
                  written = written + 1
                  k=k+1
'Below line is cool, will print the doc being imported'
                  Print"Document Created:"+Cstr(k)
                  
                  If ExcelPath1= written Then  
                        Goto Done
                  Else
                        Goto Finish
                  End If
                  
          '     If written = 1000 Then
          '          Print written
          '          Print written & " Documents Imported"
          '          Goto Done
          '     Else
          '          Goto Finish
          '     End If
                  
            End With
            
            Print written & " records imported...", doc.assetsjobno_x(0)
            
            
      Loop
      Return
Done:
End Sub

I'd like to somehow incorporate the loop until all records are imported from your agent. At the moment mine will just keep looping and importing blank records.

Thanks for your help!

Delete the line
 ExcelPath1=Inputbox$("Enter no.of rows to export","Importing no.of Rows","2000")

change the line
Print written & " records imported...", doc.assetsjobno_x(0)  to
Print written & " records imported...", row
add
Dim LoopOk as Integer

LoopOK =1
Do While ( LoopOK =1)

'
Before the loop command add

 if ( Cells(row, 1).Value="") and ( Cells(row, 2).Value="") and ( Cells(row, 3).Value="") and ( Cells(row, 4).Value="") then
   LoopOk =0
  End if

I hope this helps !

Hi SysExpert,

thanks for your prompt reply!
I added the lines, but got a lot of script errors. I ran the debugger and tried to correct.
The agent runs without any user interacction, however it loops forever. This is probably my fault as I added loops at the end of the script as the debugger kept saying "Expected Loop" Can you see where this now needs adapting?

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
      Set db = session.currentdatabase
      Set view = db.getview("edv")
      If Not view Is Nothing Then
            Set vColl = view.allentries
            If vColl.count>0 Then
                  Call vcoll.removeAll(True)
            End If
            If Not vColl Is Nothing Then Set vColl = Nothing
      End If
      
'Call Terminate
      Dim xlFilename As String
 '// This is the name of the Excel file that will be imported
      xlFilename= "C:\testodbc.xls"
      
      Dim doc As NotesDocument
      Dim row As Integer
      Dim written As Integer
      Dim LoopOk As Integer
      Set db = session.CurrentDatabase
      Set doc = New NotesDocument(db)
      
 '// 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" )
      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 = 1 '// These integers intialize to zero anyway
      written =0
      
      Print "Starting import from Excel file..."
      
      Do While True '// We use a test a few lines down to exit the loop
Finish:
            With xlSheet
                  row = row + 1
                  
                  Set view= db.GetView("edv")
                  Set doc = db.CreateDocument '// Create a new doc
                  doc.Form = "EmpDetails" 'Form name
               'Update to Fields
                  doc.employee_number = .Cells(row, 1).Value
                  doc.employee_lastname = .Cells( row, 2).Value
                  doc.employee_firstname = .Cells( row, 3).Value
                  doc.employee_middlename = .Cells(row, 4).Value
                  
                  Call doc.Save( True, True ) '// Save the new doc
                  written = written + 1
                  k=k+1
                  
                  If ( .Cells(row, 1).Value="") And ( .Cells(row, 2).Value="") And ( .Cells(row, 3).Value="") And ( .Cells(row, 4).Value="") Then
                        LoopOk =0
                  End If
                  
                  LoopOK =1
                  Do While ( LoopOK =1)
'Below line is cool, will print the doc being imported'
                        Print"Document Created:"+Cstr(k)
                        
                        If ExcelPath1= written Then  
                              Goto Done
                        Else
                              Goto Finish
                        End If
                        
          '     If written = 1000 Then
          '          Print written
          '          Print written & " Documents Imported"
          '          Goto Done
          '     Else
          '          Goto Finish
          '     End If
                        
                        Print written & " records imported...", row
                  
                  Loop
                  Return
Done:
            End With                  
      Loop
End Sub

The two lines and  any others for these variables, and the Dim statements for them are not needed

 written = written + 1
               k=k+1

also set
option Explicit in the options section

I think it's still a mess :(
Getting loads of expected Loops and End Withs when I try and save the script.

OK, move the    


End With  

to just before the
Loop

There is only one loop statement, and only one

 Do While ( LoopOK =1)  

so if you delete your old code,and copy mine with the correction, it should be OK, or close to it.

Ok,

that's working quite well now...

Just a couple of minor things:

Before, the status bar counted up documents as it was importing the rows. Now it just displays "Importing from Excel..."
The agent creates 1 empty/blank document (not really too much of an issue just wondering why?)

Do I still need?:

>> also set
>> option Explicit in the options section

Thanks for your help, I'm so pleased it's working this well!

It is a good habit to get into. It prevent incorrect or missing variable declarations.

to see a doc count

add

print row

before the loop statement

To fix your issue with the blank line change the do while ...


  With xlSheet ' put before do while  
   do whike ( .Cells(row, 1).Value="") And ( .Cells(row, 2).Value="") And ( .Cells(row, 3).Value="") And ( .Cells(row, 4).Value="")  


 move
end with
 to after the loop statement instead of before.

Struggling again....Unexpected Loop....what have I done wrong?

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
      Set db = session.currentdatabase
      Set view = db.getview("edv")
      If Not view Is Nothing Then
            Set vColl = view.allentries
            If vColl.count>0 Then
                  Call vcoll.removeAll(True)
            End If
            If Not vColl Is Nothing Then Set vColl = Nothing
      End If
      
'Call Terminate
      Dim xlFilename As String
 '// This is the name of the Excel file that will be imported
      xlFilename= "C:\testodbc.xls"
      
      Dim doc As NotesDocument
      Dim row As Integer
      Dim written As Integer
      Dim LoopOk As Integer
      Set db = session.CurrentDatabase
      Set doc = New NotesDocument(db)
      
 '// 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" )
      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
      
Records:
      row = 1 '// These integers intialize to zero anyway
      written =0
      
      Print "Starting import from Excel file..."
      
      LoopOK =1
      Do While ( LoopOK =1)   '// We use a test a few lines down to exit the loop
            Do While ( .Cells(row, 1).Value="") And ( .Cells(row, 2).Value="") And ( .Cells(row, 3).Value="") And ( .Cells(row, 4).Value="")  
                  With xlSheet
                        row = row + 1
                        
                        Set view= db.GetView("edv")
                        Set doc = db.CreateDocument '// Create a new doc
                        doc.Form = "EmpDetails" 'Form name
               'Update Fields
                        doc.employee_number = .Cells(row, 1).Value
                        doc.employee_lastname = .Cells( row, 2).Value
                        doc.employee_firstname = .Cells( row, 3).Value
                        doc.employee_middlename = .Cells(row, 4).Value
                        
                        Call doc.Save( True, True ) '// Save the new doc
                        
                        If ( .Cells(row, 1).Value="") And ( .Cells(row, 2).Value="") And ( .Cells(row, 3).Value="") And ( .Cells(row, 4).Value="") Then
                              LoopOk =0
                        End If
                        Print row
                  Loop    
            End With    
            
'Below line is cool, will print the doc being imported'
      ' Print"Document Created:"+Cstr(k)
            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
            
            Print written & " records imported...", row
            
Done:
            
End Sub

Delete

    Do While ( LoopOK =1)   '// We use a test a few lines down to exit the loop

Ok done that. Now I get "named product object does not exist" when I try and run it...
Lotus Script debugger points at the line:

    Do While ( .Cells(row, 1).Value="") And ( .Cells(row, 2).Value="") And ( .Cells(row, 3).Value="") And ( .Cells(row, 4).Value="")  

With xlSheet must go before the Do while

*sigh
now unexpected end with line 70

sysExpert, it might help suboarder if you post the entire code with corrections..

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
      Set db = session.currentdatabase
      Set view = db.getview("edv")
      If Not view Is Nothing Then
            Set vColl = view.allentries
            If vColl.count>0 Then
                  Call vcoll.removeAll(True)
            End If
            If Not vColl Is Nothing Then Set vColl = Nothing
      End If
      
'Call Terminate
      Dim xlFilename As String
 '// This is the name of the Excel file that will be imported
      xlFilename= "C:\testodbc.xls"
      
      Dim doc As NotesDocument
      Dim row As Integer
      Dim written As Integer
      Dim LoopOk As Integer
      Set db = session.CurrentDatabase
      Set doc = New NotesDocument(db)
      
 '// 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" )
      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
      
Records:
      row = 1 '// These integers intialize to zero anyway
      written =0
      
      Print "Starting import from Excel file..."
      
      LoopOK =1
      With xlSheet
            
'            Do While ( LoopOK =1)   '// We use a test a few lines down to exit the loop
            Do While ( .Cells(row, 1).Value="") And ( .Cells(row, 2).Value="") And ( .Cells(row, 3).Value="") And ( .Cells(row, 4).Value="")  
                  
                  row = row + 1
                  
                  Set view= db.GetView("edv")
                  Set doc = db.CreateDocument '// Create a new doc
                  doc.Form = "EmpDetails" 'Form name
               'Update Fields
                  doc.employee_number = .Cells(row, 1).Value
                  doc.employee_lastname = .Cells( row, 2).Value
                  doc.employee_firstname = .Cells( row, 3).Value
                  doc.employee_middlename = .Cells(row, 4).Value
                  
                  Call doc.Save( True, True ) '// Save the new doc
                  
                  If ( .Cells(row, 1).Value="") And ( .Cells(row, 2).Value="") And ( .Cells(row, 3).Value="") And ( .Cells(row, 4).Value="") Then
                        LoopOk =0
                  End If
                  Print row
            Loop    
      End With    
      
'Below line is cool, will print the doc being imported'
      ' Print"Document Created:"+Cstr(k)
      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
      
      Print written & " records imported...", row
      
Done:
      
      
      
      
End Sub

Thanks for posting the whole code.

It now lets me save the agent ok, but when run it just says 0 records imported in the bottom left hand corner

1) The excel sheet you are opening should have the data in the first TAB ( Shret ), and not contain any blank rows at the beginning.
Worst case, go back to the version from 2/2/06 which was working. But had a single blank document.

ALternatively, start adding debug print statements just before the loop starts.

Ok, I went back through this post. I got the working version again, and then I changed the do while as suggested.
It appears to be this that is stopping any records being imported. I'm sure this will only be a simple thing, and would really appreciate any help fixing this last little thing. Here is the code I am trying to use at the moment:

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
      Set db = session.currentdatabase
      Set view = db.getview("edv")
      If Not view Is Nothing Then
            Set vColl = view.allentries
            If vColl.count>0 Then
                  Call vcoll.removeAll(True)
            End If
            If Not vColl Is Nothing Then Set vColl = Nothing
      End If
      
'Call Terminate
      Dim xlFilename As String
 '// This is the name of the Excel file that will be imported
      xlFilename= "C:\payrollinput.xls"
      
      Dim doc As NotesDocument
      Dim row As Integer
      Dim written As Integer
      Dim LoopOk As Integer
      Set db = session.CurrentDatabase
      Set doc = New NotesDocument(db)
      
 '// 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" )
      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
      
      
Records:
      row = 1 '// These integers intialize to zero anyway
      written =0
      
      Print "Starting import from Excel file..."
      
      LoopOK =1
      
      With xlSheet
            Do While ( .Cells(row, 1).Value="") And ( .Cells(row, 2).Value="") And ( .Cells(row, 3).Value="") And ( .Cells(row, 4).Value="")  
                  row = row + 1
                  
                  Set view= db.GetView("edv")
                  Set doc = db.CreateDocument '// Create a new doc
                  doc.Form = "EmpDetails" 'Form name
               'Update to Fields
                  doc.employee_number = .Cells(row, 1).Value
                  doc.employee_lastname = .Cells( row, 2).Value
                  doc.employee_firstname = .Cells( row, 3).Value
                  doc.employee_middlename = .Cells(row, 4).Value
                  
                  Call doc.Save( True, True ) '// Save the new doc
                  If ( .Cells(row, 1).Value="") And ( .Cells(row, 2).Value="") And ( .Cells(row, 3).Value="") And ( .Cells(row, 4).Value="") Then
                        LoopOk =0
                  End If
                  
                  Print " Creating Records..."row
            Loop    
      End With  
      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
      
Done:
      
End Sub

There are no blank rows at the beginning of the Excel sheet and all data is in the first sheet.
The code above appears to still connect properly. It opens up the Excel sheet, but quickly starts disconnecting before importing any records.

Anything else you can think of? How do I add debug print statements?

The dowhile is checking for blank cells, so it's saying do while these cells are empty.
 
 Do While ( .Cells(row, 1).Value="") And ( .Cells(row, 2).Value="") And ( .Cells(row, 3).Value="") And ( .Cells(row, 4).Value="")  

Change the ="" to <>""
(don't give me points, just trying to be helpful)

Personally, I probably would have done:

Do
' blah.......
Loop until trim(.Cells(row, 1).Value + .Cells(row, 2).Value + .Cells(row, 3).Value + .Cells(row, 4).Value)=""

Marilyng.... thanks, but that doesn't appear to have made a difference
other than occassionally it will import two rows from Excel rather than nothing
Very strange...

Ah, was just guessing without testing.. and didn't want to impose on sysExpert's hard work here.  Ya'll let me know if you want me to test..

If it's not too much trouble marilyng, please do!

Marilyng, I love you!

Thank you so much, this is perfect!

Now I am stuggling as to what to do with points....

SysExpert has obviously posted a lot of responses here, but you hit the nail on the head straight away, and obviously tested before posting for which I am extremely grateful.

Again, I didn't jump in because I didn't want to impose... whatever you think is fair.