Domino Code Fragment

Code Name*
MS Excel Import, using OLE		Date*
04/28/2024		Source (or email address if you prefer)*
Rlatulippe@romac.com
IP address:.3.147.103.202
Description*
Here's an excel import, using OLE, that can be fairly easily modified to import anything that has OLE classes registered (it can be easily adapted to read every MS Project file in a directory, and pull out each task separately as a Notes document).		Type*
LotusScript		Categories*
(Misc)
Implementation:
Required Client:
Server:
Limitations:
Comments:
Files/Graphics attachments (if applicable): Code:

This one gets every workbook file in a directory, then every sheet in the workbook, then cycles thru a range of cells in the worksheet. It then creates a new Notes document - and importantly for those who don't like wee red boxes! - deletes the document from memory once saved

Sub Initialize
Dim session As New NotesSession
Dim db As NotesDatabase
Dim BinDoc As NotesDocument
Dim TempDate As New NotesDateTime ("")
Set db=session.CurrentDatabase

ExcelPath=Inputbox$("Enter Excel Path","Excel Import","c:\temp")
If ExcelPath="" Then Goto finishup
If Right(ExcelPath,1)<>"\" Then ExcelPath=ExcelPath+"\"
ExcelFiles=Inputbox$("Enter File Name","Excel Import","*.xls")
If ExcelFiles="" Then Goto finishup
ExcelFile=Dir$(ExcelPath+ExcelFiles)

While ExcelFile<>""
Set xls=GetObject(ExcelPath+ExcelFIle,"")
LastStreet$=""

Forall sheet In xls.Worksheets
Street$=ProperCase(Trim(sheet.name))
RoundName$=Trim(sheet.range("B5").value)
RoundDay$=ProperCase(sheet.range("B7").value)

' cycle thru a10..f47 range of bin details

For x%=10 To 47
HouseNum$=Trim(sheet.range("A"+Ltrim(Str$(x%))).value)
If HouseNum$<>"" Then
Comments$=Trim(sheet.range("F"+Ltrim(Str$(x%))).value)
Assist$=sheet.range("E"+Ltrim(Str$(x%))).value

Set BinDoc= New NotesDocument(db)
BinDoc.Form="Bin"
Set TempItem= New NotesItem( BinDoc, "BinStreet",Street$)
TempItem.IsSummary=True
Set TempItem= New NotesItem( BinDoc, "BinHouse",HouseNum$)
TempItem.IsSummary=True
Set TempItem= New NotesItem( BinDoc, "BinDay",RoundDay$)
TempItem.IsSummary=True
TempItem.IsSummary=True
Set TempItem= New NotesItem( BinDoc, "BinAssistance",Assist$)
TempItem.IsSummary=True
Set TempItem= New NotesItem( BinDoc, "BinComments",Comments$)
TempItem.IsSummary=True
Call BinDoc.Save (True,True)
Delete BinDoc
End If
Next
End Forall
ExcelFile=Dir$
Wend
FinishUp:
End Sub