Importing to Record from Excel

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
    Loop

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.

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.

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 !

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 #
OS
Harddrive
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....

Regards!

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.

simple

ub ImportXL_Reg
      
%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.

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
      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
      
      calcf=      "No"
      
'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

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

OK
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 !

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"

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.