Import from excel

xlFilePath = workspace.OpenFileDialog( False, "Please select the excel file ","*.xls", "d:")

If Isempty(xlFilePath) Then

Exit Sub

End If
Set varExcel = CreateObject( "Excel.Application" )

varExcel.Visible = False ' Making the selected Excel invisible

varExcel.Workbooks.Open xlFilePath '// Open the Excel file

Set xlWorkbook = varExcel.ActiveWorkbook

Set xlSheet = xlWorkbook.ActiveSheet

intExcelRow= 2 ' The excel file content starts from 2 always
'Processing the Excel row and sending an email

Print "Upload process started......."

While (xlSheet.cells(intExcelRow, 1).value <> "" )

With xlSheet

strExcelRepID = Cstr (.Cells( intExcelRow, 1).Value) '

strExcelFilename = Cstr (.Cells( intExcelRow, 2).Value)

End With

intExcelRow = intExcelRow +1

Print "Row Number -- " + Cstr (intExcelRow)

Wend

Probelm after installing SP3 for Excel 2003.

After installing SP3 for Excel 2003, I'm not able to open Lotus 1-2-3 files. When I try to open/save a .wk3 or .wk4 file we get the following error."You are attempting to open a file type that is blocked by your registry policy setting".When I googled for the above problem, I landed in Microsoft site. The below extract is from Microsoft site.

Excel 2003
loadTOCNode(2, 'moreinformation');
Important This section, method, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. For more information about how to back up and restore the registry, click the following article number to view the article in the Microsoft Knowledge Base:
322756 (http://support.microsoft.com/kb/322756/) How to back up and restore the registry in WindowsAn administrator can restrict the types of files that you can open in Excel 2003. This ability was not provided in Microsoft Office 2003 when it was originally released. To restrict the types of files that can be opened in Excel 2003, install security update 933666.For more information about security update 933666, click the following article number to view the article in the Microsoft Knowledge Base:
933666 (http://support.microsoft.com/kb/933666/) Description of the security update for Excel 2003: May 8, 2007 To restrict the types of files that you can open in Excel 2003, an administrator can use one of the following methods:
•
Use the updated Office 2003 Administrative Templates to configure the registry on the client computer.
•
Modify the registry by adding a registy key setting on the client computer. To download the Office 2003 Service Pack 3 Administrative Template (ADM), OPAs, and Explain Text Update, visit the following Microsoft Web site:
http://www.microsoft.com/downloads/details.aspx?familyid=BA8BC720-EDC2-479B-B115-5ABB70B3F490&displaylang=en (http://www.microsoft.com/downloads/details.aspx?familyid=BA8BC720-EDC2-479B-B115-5ABB70B3F490&displaylang=en)The following table contains the DWORD names that an administrator can add to the registry to restrict certain file types by using the FileOpenBlock subkey. Additionally, the table contains the corresponding file types that are restricted.
DWORD name
File types that are restricted
BinaryFiles
.xls, .xla, .xlt, .xlm, .xlw, .xlb
HtmlandXmlssFiles
.mht, .mhtml, .htm, .html, .xml, .xmlss
XmlFiles
.xml
DifandSylkFiles
.dif, .slk
TextFiles
.txt, .csv, .prn
XllFiles
.xll
Converters
All file formats that are available by using a text converter (including the Office Compatibility Pack)
DatabaseandDatasourceFiles
.odc, .udl, .dsn, .dbc, .dqy, .iqy, .oqy, .rqy, .mde, .mdb, .dbf, .ade, .adp, .cub
LotusandQuattroFiles
.wk1, .wk4, .wj3, wk1 FMT, .wks, .wk3, wk3 FM3, .wj2, .wq1, .fm3, .wj1
LegacyBinaryFiles
Microsoft Excel 4.0 Charts (.xlc) files (Office 2003 SP3)
LegacyDatabaseAndDatasourceFiles
DBF 2 (dBASE II) (.dbf) files (Office 2003 SP3)To enable the FileOpenBlock subkey settings, follow these steps:
1.
Exit Excel.
2.
If you are running Excel 2003, install security update 933666 if it is not installed, and then go to step 3. If you are running Excel 2007, go to step 3.
3.
Click Start, click Run, type regedit in the Open box, and then click OK.
4.
Locate and then click to select one of the following registry subkeys: Excel 2007
HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Security\FileOpenBlock HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\12.0\Excel\Security\FileOpenBlockExcel 2003
HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Security\FileOpenBlock HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\11.0\Excel\Security\FileOpenBlockIf the FileOpenBlock subkey does not exist, follow these steps to create it:
a.
Click to select the Security subkey.
b.
On the Edit menu, point to New, and then click Key.
c.
Type FileOpenBlock, and then press ENTER.
5.
Point to New on the Edit menu, and then click DWORD Value.
6.
Type the DWORD name that you want to restrict, and then press ENTER. For example, type BinaryFiles.
7.
Right-click the DWORD name that you typed in step 6, and then click Modify.
8.
In the Value data box, type 1, and then click OK.
9.
On the File menu, click Exit to exit Registry Editor.For more information, click the following article number to view the article in the Microsoft Knowledge Base:
945797 (http://support.microsoft.com/kb/945797/) You receive an error message when you try to save a file type that was blocked by your registry policy settings in Excel 2007 or in Excel 2003

For full details check here http://support.microsoft.com/kb/922848/

CSS to HTML table

A good article about applying CSS to HTML tables.
http://veerle.duoh.com/index.php/blog/comments/a_css_styled_table/

NNOTESWS.DLL - Notes Progress Bar

Yet another API code. Through this code we can bring the native notes progress bar to our application.

Sub Click(Source As Button)
Dim pb As New LNProgressBar(False)
Dim i As Long


For i=1 To 1000
'we process the elements
Call pb.SetText(Cstr(i)+" Sample Progress Bar","")
Call pb.SetProgressPos(i)
Next
'Terminate the progress bar
Delete pb
End Sub
The below code in the decalration event:
Declare Function NEMProgressBegin Lib "nnotesws.dll" ( Byval wFlags As Integer ) As Long
Declare Sub NEMProgressDeltaPos Lib "nnotesws.dll" ( Byval hwnd As Long, Byval dwIncrement As Long )
Declare Sub NEMProgressEnd Lib "nnotesws.dll" ( Byval hwnd As Long )
Declare Sub NEMProgressSetBarPos Lib "nnotesws.dll" ( Byval hwnd As Long, Byval dwPos As Long)
Declare Sub NEMProgressSetBarRange Lib "nnotesws.dll" ( Byval hwnd As Long, Byval dwMax As Long )
Declare Sub NEMProgressSetText Lib "nnotesws.dll" ( Byval hwnd As Long, Byval pcszLine1 As String, Byval pcszLine2 As String )
Const NPB_TWOLINE = 3
Const NPB_ONELINE = 2
Class LNProgressbar
hwnd As Long
Sub New(SecondLineVisible As Integer)
'Set-up the progress bar on the screen
If SecondLineVisible Then
hwnd = NEMProgressBegin(NPB_TWOLINE)
Else
hwnd = NEMProgressBegin(NPB_ONELINE)
End If
End Sub
Sub SetText(FirstLineText As String,SecondLineText As String)
'Display the text in progress bar
NemProgressSetText hwnd, FirstLineTExt,SecondLineText
End Sub
Sub SetProgressPos(Progresspos As Long)
NEMProgressSetBarPos hwnd, ProgressPos
End Sub
Sub SetProgressRange(ProgressMaxElements As Long)
'Set-up the max elements in the progress bar, if you have
'a list with 230 elements then set the MAX to 230 elements.
'For every element you proceed increase the SetProgressPos
'by one to reached 230
NEMProgressSetBarRange hwnd, ProgressMaxElements
End Sub
Sub DeltaPos(DPos As Long)
' This function adds the number in DPOS to the current ProgressPos
NEMProgressDeltaPos hwnd, DPos
End Sub
Sub Delete
'Terminate the progress bar on the screen
NEMProgressEnd hwnd
End Sub
End Class

Rich Text attachment processing

Rich text validation:
If we want to process the attachments in a rich text field we normally use the following code

"Forall o In rtitem.EmbeddedObjects"

How ever, if there are no attachments in the field then we get type mismatch error. To avoid it we can use the following condition before the above code

"If Not Isempty(rtitem.EmbeddedObjects) Then

Forall o In rtitem.EmbeddedObjects

End forall

End If"

Technical Articles - 3: Web 2.0

Web2.0. We come across this word now and then in the Internet. When ever a CTO or some big head gives a talk, at least once he/she will use this word. What is Web 2.0?. Do we have any thing like Web1.0 and web 3.0?

The term Web2.0 became notable after the first O'Reilly Media Web 2.0 conference in 2004. According to them web2.0 stands for ,
"Web 2.0 is the business revolution in the computer industry caused by the move to the Internet as platform, and an attempt to understand the rules for success on that new platform".

Here is an another definition for web 2.0 from First Monday.
"Web 2.0 represents a blurring of the boundaries between Web users and producers, consumption and participation, authority and amateurism, play and work, data and the network, reality and virtuality. "

So, Web 2.0 is a not a technology but a way of designing applications. We cannot even call it as a way of programming.

Returning back to O'Reillys statement,
Mr. O’Reilly characterized Web 1.0 through a set of static, one–way browser–based applications like personal Web sites and the encyclopedia Britannica Online, publishing, content management systems, and taxonomies. Subsequently, he distinguished Web 2.0 by associating it with the “new participatory architectures of the Web” that allow for online services such as the photo sharing site Flickr, blogs, the peer–to–peer file sharing standard BitTorrent, Wikipedia, event sites like Upcoming.org, the file–sharing service Napster, wikis (collaborative Web sites that allow for real–time editing), folksonomies (user–generated taxonomies), and the aggregation of online content through Web feeds.

The definition morphed considerably over time. Late in 2005 O’Reilly wrote that:
"Web 2.0 is the network as platform, spanning all connected devices; Web 2.0 applications & [are] delivering software as a continually–updated service that gets better the more people use it, consuming and remixing data from multiple sources, including individual users, while providing their own data and services in a form that allows remixing by others, creating network effects through an ‘architecture of participation,’ and & deliver rich user experiences."

According to MichelZimmer's article in First Monday,
"Which technologies are commonly collected under the Web 2.0 umbrella? Evangelists of the phrase huddle technologies like the Web development technique Ajax , the Ruby programming language, CSS, RSS, OpenAPIs, wikis, blogs, mashups (digital media works that draw from already existing texts or audio), and podcasts (media files that are distributed over the Internet to be played back on portable media players) under its roof. They highlight user–friendly interfaces, activities like tagging, social networking and microformats as Web 2.0 descriptors"

To put it in my words, there is no restriction that only a particular set of technology can be used for web2.0.

(To be Continued)

Technical Articles - 2 :Coupling

Coupling:
Interfacing of 2 systems is called as coupling. Coupling is of 2 types
1 Tight Coupling
2 Loose Coupling

Tight Coupling:
In Tight Coupling, Computers are connected to each other through strictures of Custom made interface. In Distributed environment, it is not wrong. How ever, if you want to make any change in the interface it is either costly or too difficult.
Lets consider our previous case (Notes <-> LEI <-> DB2).

The user also has a SAP application. If he wants that integrate with the above, the he need to develop or procure some other interface which connects both DB2 and SAP with Lotus notes.

To put it in simple words, In Tight Coupling Change is expensive and time consuming.
In the below figure, we have interface (in dark blue color) which connects with all the 3 system. The interface converts each request in such a way that other systems can understand it.

Before talking about Loose Coupling, we can discuss why change is difficult in tight coupling.
Let’s take a real time example:
Currently I’m living at Bangalore, India. The official language here is Kannada, which I don’t know. If I go to a barbershop, I need to explain the barber about my hair style, so that he can continue in the same pattern.

I don’t know Kannada, and the barber knew only Kannada. He cannot understand what I try to convey. So I need to have a translator with me who can translate Tamil, the language that I speak, into Kannada, so that the barber can understand. Or I should learn to speak Kannada.

When I’m in Karnataka, I can use this translator. Consider if I have move to some other part where the people speak Marathi. The entire process repeats. Either I have to learn Marathi or I need to have a translator.

The same thing applies to the software too. Every OS/Software has its own set of standards, which need not to be compatible with other OS/Software. More clearly 2 different softwares cannot directly communicate with each other.

Learning a new language is some thing like developing a custom interface and having a translator is like buying some software from the market.



Loose Coupling:
In loose coupling, we can add any number of computers to the given network with minor/no modification.
As I said previously, each system has its own standard. But the problem with the tight coupling made the manufactures to think in a different way. Each system may have different standards but for communication all of the use the same standard.
Let’s have a real time example here for a better understanding:
My mother tongue is Tamil. I’m at barber shop. The barber’s mother tongue is Kannada. How can we communicate each other? Both of us know English. I would explain my requirements in English and as he too knows English he can understand what I want. So I no need to Learn Kannada or need not have Translator.
Following the same, all applications can do all their work in their own standard, but when they need to communicate with some other system, all the applications will/should follow a common standard/protocal.

Technical Articles - 1

I have been working in IT industry for almost 3 years. I did my graduation in Electrical and Electronics Engineering. Of the 45 + papers that I had in my under graduation, only 3 papers were related to IT. They are Programming in C & UNIX (No practical or lab classes. Only theory classes), Object Oriented Programming (No practical or lab classes. Only theory classes) and Software Engineering. And during my college days the only paper that I failed to pass at the first attempt is Object Oriented Programming. Failing in a paper doesn’t mean that I don’t have any knowledge about that paper. I have written the exam well and I don’t know why I couldn’t clear. After a short stint in construction industry I joined the IT industry.

I started my IT carrier with a startup organization. The advantage working in startup organization is we had the chance to work on several emerging technologies. Often my boss would insist us to learn/practice some new technology. At times, what he speaks would be like a Greek and Latin to me. And my boss, when talking to us, he often use the words SOAP, Ajax, Web services, SOA, XML, DXL XSLT, Web2.0 etc. In free time I will search in Google / wikipedia for the terms that my boss used. If I search for some terms, it will lead me to some other term. If I search for the other term it will lead to some other term. It will be an infinite loop. It took several days for me to understand what each term means. How can we use it in our day to day programming.

For guys like me, without the basic functional knowledge, I would like to share what I know. The basics of computer, Internet and some other programming practices. I cannot assure that what ever I write is correct. It’s just my understanding. Since I work mostly on collaboration and internet technologies, most of the articles will be related to that. In this post I will write some thing about Distributed Computing and Interfaces.

1. Distributed Computing:
A software/hardware architecture where more than 1 computer is involved in completion of a task.
Let’s have a simple example:
A food processing company has a DB2 database to store the stock details. And a Lotus notes application to handle the order. When there is new order, the lotus notes application needs to check the DB2 for the available stocks and process the order. So we have 2 different systems, interacting with each other and complete the task. This is called distributed computing

2. Interface :
Interface is hardware/software component which is used to connect to 2 or more computers.
Let’s consider the previous case.
Lotus notes and Db2 needs to work in sync for successful completion of the task. As Lotus notes and DB2 has different architecture they cannot be directly connected. Instead we need a middle man who takes the responsibility of connecting the different system. The middle man understands the query from the lotus notes application and translates it into such a manner that the DB2 can understand. And vice versa. The middle man is considered as the interface. In our case we used Lotus Enterprise Integrator (LEI) as the middle man (Interface)
I hope the above is clear.

Chaning the datatype

When we change the data type of a field, then the new data type will not reflected in old documents. To change the data type in the existing documents we have 2 options
1. Open and Save all the documents.
2. We can have background agent to do this.
Here is the logic,

• Create an item for the existing field
• Copy the items content
• Delete the item
• Create a new item for the same field (The new item will have the new data type)
• Set the old value in the new item
• Save the document.

Why I’m a better software developer than you

What makes one developer better than another? Shouldn’t we all be performing at the same level? Of course not, we’re not sewing buttons on an assembly line. We’re using every bit of our intelligence to create something that we can only begin to understand.

• I constantly look for better ways of doing things. I subscribe to a good number of development blogs. I alone cannot always come up with the best way to solve a problem, but somebody somewhere probably can.
• I don’t stop thinking about problems and how to solve them through automation. Sometimes I’ll wake up in the middle of the night, and I can’t get back to sleep until I write some code that I can’t get out of my head.

more........

NNOTESWS.DLL - File Open and File Save

Normally when we want to open/save a file through lotus script we will be using the open file dialog and save file dialog. Here is an API alternative for that.

NNOTESWS.DLL is part of the Lotus Notes software and handles the interface side of the Notes client like file open dialog, file save dialog, progress bar etc..

Please Note: I have tried the below code on Version 6.5.4. I haven't tried it on other versions.

Open File Dialog

'Declartion
Declare Function NEMGetFile Lib "nnotesws" ( wHandle As Integer, Byval szFileName As String, Byval szFilter As String, Byval szTitle As String ) As Integer
'On Click
'Declare variables...
Dim szFileName As String*256
Dim szTitle As String
Dim szFilter As String
Dim szSelectedFile As String

Set values...
szFilename = Chr(0)
szTitle = "Open File"
szFilter = "All Files *.* Word Document *.docText Files*.txt" ' a pipe symbol should come after every entry. (szFilter = "All Files pipe *.* pipe Word Document pipe *.doc pipe Text Filespipe .txt pipe"). The symbol gets missed when i publish.
If NEMGetFile( 0, szFileName, szFilter, szTitle) <> 0 Then
szSelectedFile = szFileName
End If

Save As Dialog:

'Declaration
Declare Function NEMPutFile Lib "nnotesws" ( wHandle As Integer, Byval szFileName As String, Byval szFilter As String, Byval szTitle As String ) As Integer
'On Click
'Declare variables...
Dim szFileName As String*256
Dim szTitle As String
Dim szFilter As String

Set values...
szFilename = Chr(0)
szTitle = "Save File"
szFilter = "All Files *.*Word Document *.docText Files*.txt" 'a pipe symbol should come after every entry. (szFilter = "All Files pipe *.* pipe Word Document pipe *.doc pipe Text Filespipe .txt pipe"). The symbol gets missed when i publish.
If NEMPutFile( 0, szFileName, szFilter, szTitle) <> 0 Then
szSaveFile = szFileName
End If

Pivot table

http://www.martinscott.com/designlibrary.nsf/scategory/672C7A8D4679A60685256C08001DF4A3?opendocument

Excel Export-1. OLE/COM tip

From LDD..
http://www-10.lotus.com/ldd/46dom.nsf/DateAllFlatweb/dc09168723293c9885256b9e004dd58b?OpenDocument

Here's an OLE/COM tip for you all: How to get around the ":=" that VB uses to set parameters in Lotus NotesPosted by Brandt S Fundak on 17.Apr.02 at 10:10 AM using a Web browserCategory: Domino Designer -- LotusScriptRelease: All ReleasesPlatform: All Platforms

There have been a few questions about how to get around parameters in Visual Basic methods and properties (set through recording macros in excel, word and powerpoint, among others) that are set with the ":=" operator in the past few weeks, so I thought I would post this tip on how to get around it. Maybe this will help someone when using the forum search.We've all heard that most of the stuff that you can do in Visual Basic we can do in LotusScript, but are often stymied by the ":=" operator, which LotusScript does not recognize. It is not that LotusScript can not use those parameters, but we must know HOW to tell LotusScript what the parameters are.Since LotusScript will not recognize the ":=" operator, we must use the only method of passing parameters that LotusScript knows: Parentheses "()"So let's say you have a method in Excel that has parameters. Let's say we use the Add method of the worksheets object in Excel. If we look at the help file on the Add method we find this syntax:expression.Add(Before, After, Count, Type)Of course, this is syntax is something that Notes can understand. However, when we go into the help example to see how we set our parameters, we are confronted with this:This example adds a new worksheet after the last worksheet in the active workbook.Worksheets.Add.Move after:=Worksheets(Worksheets.Count)Now we have a quandry. Let's say I want to follow the example and have the after parameter put my new worksheet after all existing sheets. LotusScript doesn't understand ":=" and if I follow the syntax as listed above, like so:expression.Add(After)it still drops the sheet BEFORE my existing sheets.We're really confronted by two problems here. First, we have to figure out how to set the After parameter. Since it is a variant object that is supposed to represent an existing sheet, we can easily set the after parameter the same way we set xlsheet objects in LotusScript:Dim after as variant...set after = xlapp.workbooks(1).worksheets("existingsheet")Now we have our after worksheet set, but when I use the line:xlapp.workbooks(1).worksheets.Add(after)it STILL puts the worksheet before the existing sheets. this is because the "before" parameter is not optional. Instead of the above you must instead use this:xlapp.workbooks(1).worksheets.Add(NULL,after)This line will correctly set the sheet. Passing NULL to the parameter will notify the program that there is no "Before" parameter. [Note: You do not have to name the parameters "before" or "after". They can be anything you want...they just have to be in the right location in the parameter listing.]The above translation of VB's ":=" operator works for any OLE method and property that has parameters. If you see ":=" in a recorded macro's code, find out what data type you are working with (the Object Browser help file is really helpful here) and then modify the code to set the variable. Unless the value is a constant (in which case you would have to pass the actual constant value, and not the constant) this will work.A good way to think of it is that when you see ":=" it's usually the equivalent of the LotusScript keyword "Set." So if you see the ":=" operator, just figure out how you can change the line to reflect the "Set" keyword instead, like in the example above.For reference, here are some of my other posts on the subject in the last couple of weeks (as well as one from about a year ago.)sample script for Excel:http://www.notes.net/46dom.nsf/55c38d716d632d9b8525689b005ba1c0/9dd101c8a69d20ec852569af0065ad88?OpenDocument&Highlight=0,excel,brandtan explanation of the above script (most of the information duplicated here):http://www.notes.net/46dom.nsf/ShowMyTopicsAllFlatweb/ffaaf962e0e171ba85256b9000665cec?OpenDocumenta discussion on how to use the above techniques in powerpoint:http://www.notes.net/46dom.nsf/ShowMyTopicsAllFlatweb/c2d7c421890934ea85256b9c00534e4e?OpenDocumentI hope someone finds this helpful.brandt