Import excel -----Need help

HCL Domino Domino Forum
1

Hi All,

I have an excel file and my first column is First Names and 2nd column is Last Name,

I need to create a document that has Name in the form as Abbreviated Name (How can I convert FirstName and lastName as 2 separate columns into an Abbreviated name …)

I guess I will have to do a lookup to the Address Book…

Please help , how to do a look up to the address book and get the abbreviated Name

here is my part of the code

Dim session As New NotesSession

Dim workspace As New NotesUIWorkspace

Dim db As NotesDatabase

Dim Doc As NotesDocument

Dim file As String



Dim username As NotesName

Dim xlsheet As Variant

Dim date1 As NotesDateTime



Set db = Session.currentdatabase

date1.LSLocalTime = Now



Set xlApp = CreateObject("Excel.application")

xlApp.Visible = False

file=Inputbox("Please enter path of the spreadsheet like" & Chr(10) &_

" C:\spreadsheet.xls", "File Path Inquiry Box", " enter path here ....")

xlApp.Workbooks.Open file

ARangeValue = xlApp.Range("A5").Value

Print " Please Wait Importing Data ....."

Dim toff, ton , vac As String

Set username =  New NotesName(session.Username)

i = 4

Do Until ARangeValue = ""

	i = i + 1

	

	ARangeValue = xlApp.Range("A" & Trim(Str(i))).Value

	Set doc =  db.CreateDocument

	doc.Form = "Appointment"

	doc.Name_1 = xlApp.Range("B" & Trim(Str(i))).Value + " " + xlApp.Range("A" & Trim(Str(i))).Value

	doc.CreateDate = date1.DateOnly

	doc.Status = "Approved"

		

	Call doc.save(True, False)

	

Loop

Call workspace.ViewRefresh

Set xlApp = Nothing

end Sub

How to dod the lookup and get the abbreviated name?

Also In my excel sheet after the 2nd column , I have Month Date Calendar and I need to bring every cell for the each month and data in those cells For eg Cell Range AX to BN is for the Month of April and AX value is 14 and cell BN value is 30

(14 and 30 are the dates for the Month of April 2008)

How can I programmatically get these values …

and besides every Firstname and Lastname I have for eg : ac ac cell AX is filled as vacation to AZ as Vacation

For next row Name: XYZ : cell BA is Training to BB as training and so on …

How can I get it programmatically every month range into the document

My form has Start Date and End Date (The Form Start Date should have if the person’s scheduled for a vacation or training or etc and end date should be the date when it is ended.

Please help,

Thanks in advance

ac ac

2

Subject: Import excel -----Need help

To get an abbreviated name, you could just create a new NotesName using the string from the sheet. Something like:

tempName = xlApp.Range(“B” & Trim(Str(i))).Value + " " + xlApp.Range(“A” & Trim(Str(i))).Value

Set myName = session.CreateName( tempName )

abbrName = myName.Abbreviated

Of course this just puts it in the abbreviated format, I don’t think it ensures that the name exists in the nab.

You could do a lookup to the names.nsf database using notesView.GetDocumentByKey. I think I have used the ($VIMPeople) view to do lookups in the past.