Excel Vacabulary

Thousands of line of VBA code in my Resource Workbooks

Here is some vocabulary you might want to adopt so that others can understand you and so you can understand the programmers and discover that they are just ordinary people doing a different job.

Procedure: A set of instruction that you want the computer to execute. In the old days when the computers were small and slow and the languages were not well developed, they were called macros

Objects: They are the building blocks of your procedures (workbooks, worksheets, charts, drawings, controls (buttons, text boxes, list boxes, etc.)), VBA modules, procedures, etc.

Properties: Think of the verb to be. Objects have properties that you can change.

Methods: Think of the verb to do. You might want an object to close, to open, etc.

Events: For a procedure to be executed, an event must happen. One event that everybody knows is the click of the button. Other events are the opening a workbook, the activating a sheet. So, you have to tell the computer when to execute a procedure.

Example:

Sub Command125_Click ()
Range("A2").Value= 2
Application.Close
End Sub

The "Sub" is the procedure; "Command125_Click" is the event; "Range ("A2")" and "Application" are objects; "Value" is a property" and "Close" is a method.

This procedure says that when the user clicks on the command button 125, the cell A2 takes a value of 2 and Excel is closed.

As you will discover in this Web site, you don't have to write much code, the Macro Recorder does the work for you.

http://codeprogrammer.blogspot.com

Excel Intruduction

Introduction

VBA in Excel was first used to automate repetitive tasks, VBA served to order the computer to do tasks that the users usually did. Because VBA did that more rapidly and more efficiently than the best user, it was possible to develop applications to complete tasks that even a team of well trained users could not perform within the deadlines imposed by the decision making process. These orders were MACROS. Since 1997, VBA is a full fledge programming language that allows a developer to create applications (programs within Excel), the MACROS are now PROCEDURES.

If you don't master thoroughly the functions and functionalities of Excel, you will never become a good VBA programmer, you will be re-inventing the wheel while cars will be flying.

If you don't listen to the user who has the knowledge of the data, you will never be a good VBA programmer and if you don't listen to the client and don't try to understand her/his needs, you will never be a good VBA programer.

Programing will never constitute more than 25% of the work, the other 75% consisting of understanding the needs of the client and designing the workbook within which the programming will be developed.

So to begin with, I offer you some vocabulary of the trade and then, the tools I use in my daily work as a Consultant in Excel-VBA-Access.

First, a section of General Rules for VBA and the Visual Basic Editor (VBE) to simplify the work of the developer and of the person who will have to maintain the applications or modify them in the absence of the original designer.

Secondly, the most important section: Moving Around in Excel. This section shos you how to move between cell, between sheet, between workbooks. It shows you how to select cells and ranges.

Then a section on VB itself VB-Statements and a section on functions of both VB and VBA VB-VBA-Functions. A section covers the controls and forms VBA-Controls and Forms and another one adresses the procedure's triggers, VBA-Events. Finally three pages on VBA-Objects, VBA-Properties and, VBA-Methods.

Finally, join the major leagues, discover the VBA array variables. Start working in the virtual world.

VBA Q&A's

What is VBA?

VBA is "Visual Basic for application". It is a programming language that allows users to program complex tasks within an application. There is VBA for Excel, VBA for Word, VBA for Project, VBA for Access, etc.

Where does VBA come from?

In the ྂ a new computer language appeared, it was called Basic. I used it on advanced calculators and on my COCO64 from Radio-Shack. At the time, RAM (memory) and CPU's (the brain of the computer) being very very small, you were limited in what you could do with the language. With today's computer, came Visual Basic. It is still Basic, but a lot of elements are pre-programmed making the user's task much simpler. Microsoft adopted the language and introduced it as a component of all its applications thus the VBA that stands for Visual Basic for Application. So now you can program in Visual Basic (VB) to create applications or you can program within an application (Excel, Access, Word, Project, etc.) with Visual Basic for Application (VBA). This section of my Website is about VBA for Excel. I also maintain a Web site on VBA for Access.

Should I learn VBA or VB?

Let's say that VB is for programmers and VBA is for users (or developers). Personally, I took hundreds of hours of training in VB but I do all my work in VBA (Excel and Access). You don't need to learn VB to be good at VBA. I adopted VBA because most of the functions I need to organize and analyze data are pre-programmed in Excel and Access. It costs a lot less to develop financial and management applications in Excel and Access than to re-invent the wheel and do everything in VB.

What is the difference between VB, VBA for Excel, VBA for Access, VBA for Word, VBA for Project, etc?

To answer this question, I will ask you to imagine a meeting of five English speaking persons, a lawyer, an accountant, a physician, a chemist and a psychologist. They all speak English but when it is time to talk about their work, none of them totally understand the other. It is the same with each VBA, the objects, properties, methods and events vary from a language to the other.



MACROS or Procedures

You will notice that within my HelpSite on VBA-Excel, I use the term "procedure" rather than "Macro". I consider VBA-Excel as a Development Environment hence, the "procedure". I would hope that my colleagues Developers start using this word so that VBA-Excel earns a status among Developers and Users in the field of Database Interface, Datamarting, Datamining and Financial and Strategic Information Analysis Systems.





The tips offered below are:

Indispensable
Very useful
Interesting



All through this web site, you will encounter the following icon:


Click on it and a demo will appear to illustrate the topic. The cursor moves, explanations are supplied in balloons, it happens in front of your very eyes, on line.

And you can get all the viewlets for free, click here.



General Rules and VBE

Note: Your best VBA coach is the Macro Recorder (Tools/Macro/New Macro). Start it, do what you want your code to do and then go to the Visual Basic Editor (VBE) and cut and paste these segments of code. Personally, I use the Register a lot to avoid writing and spelling errors. I adapt the code that is offered by VBE.

Note: Quotes within quotes must be doubled. Hence,
Selection.Formula= "=Range ("A1").Value & "normal"" must be written
Selection.Formula = "=Range(""A1"").Value & ""normal"""

When I work with the Visual Basic Editor (VBE) I use three windows, the Project window, the properties window and the code window.

You can test a procedure line by line by clicking within the procedure in the Code Window of the VBE and using the F8 key. After a line of code containing a variable has been executed, you can check the value of this variable by moving the cursor on top of the name of the variable.

You can see your VBA code at work by opening both Excel and the Visual Basic Editor and then by right clicking on the task bar and splitting the screen.

Declare all your variables (Dim) at the beginning of the procedure, it will simplify the testing of your code.

To test part of a VBA procedure, start the execution with the F8 key. Then, click on the yellow arrow in the margin of the code window and drag it down to the line you want to test. Use the F8 key from there.

To test part of a procedure, you can also press F8 then right click in the margin of the code window in front of the line that you want to test and select "Set next statement". From then on, you use F8.


To create code within an event related to the sheet, right click on the sheet's tab and select "View Code". Then select the event from the event window and write your code.

You can carry the value of a variable from one procedure to the other by stocking this value in any cell in the workbook. ex:
in the first procedure:
Range("A3456").Value=Variable1
in the second procedure:
Variable2=Range("A3456").Value

Name ALL the cells and ranges that you use in your code so that if their address changes (adding or deleting columns or rows, changing the name of sheets, etc..), your code is not invalidated.
Selection.Name = "nfaBalance"

Note: If you want to name the fields that you use in array formulas, Index/Match formulas and VBA procedures in Excel, you select the pertinent cell or field and then you go to the Name Zone and type a name. Then, CLICK ENTER. Use prefixes (nf, general named fields; prf, fields used in procedures; aff, array formula fields; etc..)

Write your code in lower case letters. If the spelling is right, VBE will capitalize the necessary letters. If it doesn't, check your spelling.

Always activate the "Option Explicit". You are forced to declare variables but there are many advantages. If you have the wrong spelling for your variable, VBE will tell you. You are always sure that your variables are considered by VBE. You can use Shift/Space to call your variables in a contextual menu and double click them rather then key them in.

http://codeprogrammer.blogspot.com

Excel As Database

Excel As Database ?
On this page, I present the limits of Excel as a database and it's functionalities in that field. There are sections on general tips, on filters, and on formulas.

I also illustrate an Data Management System that includes Excel and that can be a excellent solution if you have less than 60,0000 employees, less than 60,000 sales per year, less than 60,000 employees, less than 60,000 items for sale and less than 60,000 accounts in your accounting system. From this database, you can design an accounting system, an inventory system, a billing system, etc. You will be able to maintain such a system internally and adapt it to the changes in your organization.

Excel is not a database application but it is the best reporting tool on the market. You can import data from any database or datamart and create reports and analysis to satisfy your needs for a small fraction of the cost that you would incur if you would use the reporting applications of the database. You will save a lot of time and be able to train many users becoming less dependant on the limited resources of your IS department.
If you want to develop a database or a datamart in Access,
visit my page on the subject.
Even if many users can open the same workbook at the same time (in read only mode), the limit of Excel as a database resides in the fact that only one user can enter data in a workbook at one time. If two to 15 users must enter data simultaneously, you may want to move to Access as a database. If more than 20 users must enter data simultaneously, you will choose one of the big databases (SQL Server, Sybase, Oracle, dB, etc).



An Excel workbook may comprise 256 sheets with 256 columns and 65,000 rows each which amount to 4,294,967,296 cells. Each cell can hold 32,767 characters each or a formula 1,024 characters long. With a 500 mhz CPU and 128 Mg of RAM, Excel can be used as a database. The great power of Excel is its conviviality to design reports, analysis and charts.


Fundamental DATABASE Notion

A DATABASE is a set of data
which reading is convivial
for a MACHINE.

A REPORT (or form) is a set of data
which reading is convivial
for a HUMAN.

Very rarely will you be able to organize data so that reading them is convivial to both machines and humans.

To become an expert in database a good background in computer science is essential. To become an expert in reporting you need a good background in accounting and management.

Excel Database

Note: Select a different format for the title cells as oppose to the other cells of the table so that Excel understands that it is working with a database and not just a table with no title cells. Excel will consider your table as a Database if in the first cell of each column the font is bold and the inferior border is also bold. Once these conditions are met, all items of the "Data" sub-menu become accessible (Filters, Sort, Form, Sub-Total, etc...).

You know how to use the AUTOFILTER, you place the cursor within the database and go to the menu "Data/Filter/AutoFilter" and little arrows appear in the title cell of every field. If you want these dropdown menus to appear only for certain fields, select the desired column or columns before you go to the menu bar to use "Data/Filter/AutoFilter". (Thanks Darrel Steen)


Advanced Filters

Advanced filters are one of the most important tool to be used with databases. Here are a few tips on these filters. Let's say we have a database with the following fields:

First Name
Name
Age
City
Sex
Weight


The criteria range to filter members by the name of Thomas would be:
Name

Thomas


The criteria range to filter the women of Montreal would be:
Sex
City

W
Montreal

The criteria range to filter the women of Montreal and of Toronto would be:
Sex
City

W
Montreal

W
Toronto

The criteria range to filter men between ages of 50 and 59 would be:
Sex
Age
Age

M
>=50
<60>=50
<60

W

Other Formulas Useful with Excel as a Database

SUBTOTAL
The function SUBTOTAL allows (among other operations) to count, to sum or to calculate the average of non-filtered elements of a database. The function requires two arguments, the first is a number between "1" and "11" that specifies the operation to be performed (for ex. "1" is for average, "2" is for count and "9" is for sum) and, the second is the range covered by the function.
=SUBTOTAL(9,B2:B45)
This formula will sum the values in range (B2:B45) for records that are not filtered when a filter is applied on the database.

A Data Management System with Excel and Access

I have designed this system for a division of the Ministry of Supplies and Services (Government of Canada).

In this system, I don't use Microsoft Query and the data are LIVE. Many users can view the different reports at the same time (invoice, contract, statistics, report1) but only one user can enter data at one time.

The workbooks with a single sheet contain the data. They are the tables of this Data Management System. They can host as much as 60,000 records each (lines of data).

The different reports (unlimited numbers) are linked to the "tables" with array formulas and Index/Match formulas. When a report is opened, a dialog window allows the user to refresh the data. On of the great advantage of Excel Reports is that they can be printed and, unlike other reports designed within database, they can be viewed on line. These reports are invoices, contracts, analysis, summary and detailed tables and charts.

There is no convivial or ergonomic form to enter data in "Tables" in Excel. Even the automatic "Form" in Excel is rather limited. So I have created an Access application so that I could design convivial and ergonomic forms to enter the data in the "tables". The Excel workbooks with a single sheet are connected to the Access application through the "Link Tables" function.

This Data Management System is easy and inexpensive to build, easy to maintain and very very friendly. Training on Access is minimal and Excel is a development environment within which your users are probably already familiar. You might want to hire a Data Analyst to make sure that the Excel workbooks with a single worksheet (your tables) are well designed as far as what they contain and how the information is organized.

Excel As Database

Create Your Own Hit Counter

<%
Application.Lock
Application("Count") = Application("Count") + 1
Application.Unlock
x=application("Count")
%>

<%=x%>

Search Engine Script

<html>

<title>CodeAve.com(JavaScript: Search Many Sites)</title>

<body bgcolor="#FFFFFF">



<script language="JavaScript">

<!--

function search_site()

{

// Build the destination url by placing the selection option first

var url =
document.search_form.site.options[document.search_form.site.selectedIndex].value


// Append the text box input to the search url for the selected site

url = url + document.search_form.u_search.value;

// Change the the browser location to the new url

window.location.href = url;

}

-->

</script>



<center>



<form name="search_form">

<input tpye="text" name="u_search">

<select name="site" size="1">

<option value="http://search.yahoo.com/bin/search?p=">Yahoo</option>

<option value="http://www.go.com/Titles?qt=">Go.com</option>

<option value="http://www.goto.com/d/search/?Keywords=">GoTo.com</option>

<option value="http://search.excite.com/search.gw?search=">Excite</option>

<option value="http://www.lycos.com/srch/?query=">Lycos</option>

<option value="http://hotbot.lycos.com/?MT=">HotBot</option>

<option value="http://www.altavista.com/cgi-bin/query?&q=">Alta Vista</option>

<option value="http://www.webcrawler.com/cgi-bin/WebQuery?searchText=">WebCrawler</option>

<option value="http://www.mamma.com/Mamma?&query=">Mamma.com</option>

<option value="http://search.aol.com/dirsearch.adp?query=">AOL.com</option>

<option value="http://search.msn.com/results.asp?q=">MSN.com</option>

</select>

<input type="button" value="Search" onClick="JavaScript:search_site()">

</form>



</center>

</body>

</html>

 

http://codeprogrammer.blogspot.com

Formatting Numbers : Currency

The function you need to format currency is FormatCurrency. It is very easy to use and has been outlined and explained below:

FormatCurrency(NumToBeFormatted[, NumOfDigitsAfterDecimal, IncludeLeadingDigit, UseParensForNegativeNumbers])
- returns a formatted currency from the number specified with the appropriate number of digits specified after the decimal
- the first parameter is the only one that is required, although I recommend that you use at least include the second parameter.
- the last two parentheses are tristate values which means you need to specify a -1 for true, 0 for false, or -2 to use the computers regional settings.
- the IncludeLeadingDigit parameter is used to specify whether or not you want a leading zero to be displayed when the number is a fractional value.
- the UseParensForNegativeNumbers parameter is pretty self explanatory, if set to true it will put parentheses around negative numbers.
- this function also adds a '$' sign at the beginning of the output for you.

Example:

var1 = 5443.354
var2 = -877743.2345
Response.Write("var1 formatted = " & FormatCurrency(var1, 2, -1, -1) & "
")
Response.Write("var2 formatted = " & FormatCurrency(var2, 1, -1, -1))

Result:

var1 formatted = $5,443.35
var2 formatted = ($877,743.2)

http://yudhisuhendro.blogspot.com

Date and Time Functions and Formulas

Note: When your are working on formulas in the control's property dialog window or in query pertinent fields, right click and choose "Zoom", a special enlarged window will open to facilitate your work.

Note: Click on the Wizard's stick to open the Expression Generator. In the left bottom window of the dialog box, you can click on "Functions" to select any of the functions offered by Access.

==========
Here are the functions that I use and some examples:

Date, Between, And, Or, Day, Month, Year, Now, WeekDay, SerialDate, Left, Right, &, +, -, /, *, Len, IIF, Nz, Sum

Nz
The Nz function allows you to make calculations even if some of the controls are empty, or have a "Null" value". If you don't use this functions, you cannot sum a series of text boxes if even one as a Null value.
=NZ(text1,0)+NZ(text2,0)....

Using a format like "Fixed 2 decimals" in a text box, doesn't round up the result of a calculation to 2 decimals, it just shows the result with 2 decimals but calculates with all the decimals. Use the "Round" function even with "+" and "-", it rounds up your result to the number of decimals defined in the format. For some unknown reasons, I ended up with 0.999952 as the result of "1.3-0.3"
=round([textBox1]/textBox2

===================
Date and Time Functions and Formulas

Today
Date()

Yesterday
Date()-1

Do not use Now() because it includes date and time

Last 7 days (do not confuse with last week)
Between Date()-1 and Date()-7

Current week
Between Date()-(WeekDay(Date())-1) AND Date()+(7-WeekDay(Date()))


Last week
Between Date()-6-WeekDay(Date()) AND Date()-WeekDay(Date())

Week before last week
Between Date()-13-WeekDay(Date()) AND Date()-WeekDay(Date())-7

Current month:
Month([yourDateField])=Month(Now())

Current year:
Year([yourDateField])=Year(Now())

Last year (do not confuse with year ending today):
Year([yourDateField])=Year(Now())-1

Year ending today:
Between Date() And Date()-365
or more precisely:
Between Date() and SerialDate(Year(Now())-1;Month(Now());Day(Now()))+1

http://codeprogramer.blogspot.com

Table Tips

Do not use lists in the fields of the tables, you will be stuck with them along the way. Create List Boxes or Combo Boxes in forms and reports when you need them.

A double Primary Key means that you can have two records "Leclerc" but not two records "Pierre"/"Leclerc". Remember though that to be able to enter data through a query linked to such a table, both fields will have to be included in the query.

A numerical field is a field comprising numbers on which you will want to do mathematical operations. So, a telephone number, a postal code or an invoice number are text fields.

You can put numbers in both numerical and text fields of an Access table but the sorting results will be different:
In numerical fields: 1 ,2 ,3 ,4 ,5 ,6.......10.....20....30
In text fields: 1, 10, 11, 12....,2, 20, 21....3, 30, 31

Linking tables can present some limits. If you have a query within another query and both need to look at a linked table at the same time, ODBC cannot handle it. Sometimes, imported table are the solution especially in a datamart that is not used to enter data in the main database but to generate reports.

Query Tips

The query is where users and developers meet, it is the most vital part of the database.

Once the data have been queried, they can be organized and analyzed by the user on forms and reports to achieve the ultimate goal of a database: to inform.



Notes

If your query or your table feeds a form, there is no need to filter and sort whithin the query. You can manage these aspects in the "Filter/Sort" query of the form that you can access opening the form and going to the menu bar "Record/Filter/Advanced Filter/Sort". So, a query or a table can feed many forms.

There is no "If" in Access, there is "Iif". So, in the criteria zone or in expressions, use "Iif" instead of "If". This rule doesn't apply in VBA coding except when you are setting an object formula through coding.

For each report or form that you develop, always create a query. You never know when you will need a field from another table in your report or your from.

When your Information Management System includes reports in Excel, create a query in Access and use Microsoft Query only to get the results of the query created in Access. Access is superior to Microsoft Query to create queries.

Right click in the criteria or the field box and select "Zoom" when you are working with long criteria or formulas in calculated fields.

To replace a word in a long formula, double-click on it and enter the new value. It is much simpler than clicking at the beginning of the word and dragging the pointer over the word.

Text File To Web Page Automatically

Transform plain text files into Web pages automatically with this PHP script
By Contributor Melonfire, TechRepublic | 2007/01/08 11:26:02

Print this | E-mail this | Leave a comment | digg this | del.ici.ous

Change the text size: a | A

Recently, an old friend of mine rang me up to ask for help. He'd been working as a journalist for many years, and had recently received reprint rights to a number of his earlier columns.

He was eager to publish his past work on the Web; however, his columns were all saved as plain-text files and he had neither the time nor the inclination to learn HTML and convert them to Web pages. Since I was the only geek in his phone book, he'd called me to see if I could help him.

"Let me take care of it", I said. "Call me back in an hour", I said. And sure enough, when he called back a couple of hours later, I had a solution waiting for him. It involved a little bit of PHP, and it earned me his eternal thanks and a crate of wine.

So what did I do in that hour? That's where this article comes in. I'm going to show you how you can use PHP to quickly transform plain ASCII text into perfectly readable HTML markup.

To begin, let's look at an example of one of the raw text files my friend wanted to convert:

Green for Mars!
John R. Doe

The idea of little green men from Mars, long a staple of science fiction, may soon
turn out to be less fantasy and more fact.

Recent samples sent by the latest Mars exploration team indicate a high presence
of chlorophyll in the atmosphere. Chlorophyll, you will recall, is what makes
plants green. It's quite likely, therefore, that organisms on Mars will have,
through continued exposure to the green stuff, developed a greenish tinge on their
outer exoskeleton.

An interview with Dr. Rushel Bunter, the head of ASDA's Mars Colonization Project
blah blah...

What does this mean for you? Well, it means blah blahblah...

Track follow-ups to this story online at http://www.mars-connect.dom/.

To see pictures of the latest samples, log on to http://www.asdamcp.dom/galleries/220/

Fairly standard text: it has a title (or "slug"), a byline, and many paragraphs of text. All that's really needed to transform this document into HTML is to use HTML line and paragraph break markers to preserve the original layout on a Web page. Special punctuation characters need to be converted into their HTML equivalents, and hyperlinks need to be made clickable.

Here's the PHP code (Listing A) to accomplish all of the above:

<?php
// set source file name and path
$source = "toi200686.txt";

// read raw text as array
$raw = file($source) or die("Cannot read file");

// retrieve first and second lines (title and author)
$slug = array_shift($raw);
$byline = array_shift($raw);

// join remaining data into string
$data = join('', $raw);

// replace special characters with HTML entities
// replace line breaks with <br />
$html = nl2br(htmlspecialchars($data));

// replace multiple spaces with single spaces
$html = preg_replace('/\s\s+/', ' ', $html);

// replace URLs with <a href...> elements
$html = preg_replace('/\s(\w+:\/\/)(\S+)/', ' <a href="\\1\\2" target="_blank">\\1\\2</a>', $html);

// start building output page
// add page header
$output =<<< HEADER
<html>
<head>
<style>
.slug {font-size: 15pt; font-weight: bold}
.byline { font-style: italic }
</style>
</head>
<body>
HEADER;

// add page content
$output .= "<div class='slug'>$slug</div>";
$output .= "<div class='byline'>By $byline</div><p />";
$output .= "<div>$html</div>";

// add page footer
$output .=<<< FOOTER
</body>
</html>
FOOTER;

// display in browser
echo $output;

// AND/OR

// write output to a new .html file
file_put_contents(basename($source, substr($source, strpos($source, '.'))) . ".html", $output) or die("Cannot write file");
?>

Let's see how this works:

1. The first step is to read the raw ASCII file into a PHP array. This is easily accomplished with the file() function, which turns every line of the file into an element of a numerically-indexed array.
2. Next, the title and author lines (I assume these are the first two lines of the file) are extracted from the array into separate variables using the array_shift() function. The remaining members of the array are then concatenated into a single string. This string will now contain the entire body of the article.
3. Special characters like ', < and > within the body are converted into their HTML equivalents using the htmlspecialchars() function. To preserve the original formatting of the article, line and paragraph breaks are converted into HTML
elements with the nl2br() function. Multiple spaces within the article body are compressed into a single space using simple string replacement.
4. URLs within the body are detected using regular expressions, and are surrounded by elements. This turns the URLs into clickable hyperlinks when the page is viewed in a Web browser.
5. The output HTML page is then constructed using standard HTML rules. The article title, author and body are formatted using CSS style rules. Although this script doesn't do it, this is the point at which you would customize the appearance of the final page, perhaps by adding graphical elements, colors or other whiz-bangs to the template.
6. Once the HTML page has been constructed, it can be sent to the browser or saved to a static file with file_put_contents(). Note that when saving, the original file name is decomposed and a new file (named filename.html) is created for the newly-minted Web page. You can then publish this Web page to a Web server, save it to a CD-ROM or edit it further.

Note: When using this script to create and save HTML files to disk, ensure that the script has write privileges on the directory to which the files are being saved.

As you can see, assuming you have ASCII plain-text data files in a standard format, you can convert them fairly quickly into usable Web pages with PHP. And if you have an existing Web site into which you plan to inject your new Web pages, it's also quite easy to tweak the template used by the page generator to match the look and feel of your existing Web site. So go on, try it out for yourself!

http://www.builderau.com.au/program/php/soa/Transform_plain_text_files_into_Web_pages_automatically_with_this_PHP_script/0,339028448,339272897,00.htm

Rosolution Adjust In Delphi

Another to change resolution in delphi, I used to apply is the following

function NewRes(XRes, YRes: DWORD): Integer;
var
DevMode: TDeviceMode;
begin
EnumDisplaySettings(nil, 0, DevMode);
DevMode.dmFields := DM_PELSWIDTH or DM_PELSHEIGHT or
DM_DISPLAYFREQUENCY;
DevMode.dmPelsWidth := XRes;
DevMode.dmPelsHeight := YRes;
//DevMode.dmDisplayFrequency := Frequency;
Result := ChangeDisplaySettings(DevMode, 0);
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
w:=Screen.Width;h:=Screen.Height;
if NewRes(800, 600) = DISP_CHANGE_SUCCESSFUL
then
ShowMessage('Resolution changed!');
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
if NewRes(w, h) = DISP_CHANGE_SUCCESSFUL
then
ShowMessage('Resolution changed Back!');
end;

Change Resolution In Delphi

How to change the screen resolution in delphi? Here is the function maybe usefull

function NewRes(XRes, YRes: DWORD): Integer;
var
DevMode: TDeviceMode;
begin
EnumDisplaySettings(nil, 0, DevMode);
DevMode.dmFields := DM_PELSWIDTH or DM_PELSHEIGHT or
DM_DISPLAYFREQUENCY;
DevMode.dmPelsWidth := XRes;
DevMode.dmPelsHeight := YRes;
//DevMode.dmDisplayFrequency := Frequency;
Result := ChangeDisplaySettings(DevMode, 0);
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
w:=Screen.Width;h:=Screen.Height;
if NewRes(800, 600) = DISP_CHANGE_SUCCESSFUL
then
ShowMessage('Resolution changed!');
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
if NewRes(w, h) = DISP_CHANGE_SUCCESSFUL
then
ShowMessage('Resolution changed Back!');
end;

Store Report Templates In Blob

How to store report templates in blob fields in database.
Answer:

Sometimes it is useful to keep report templates in blob fields in database. If you use EK RTF report component for Delphi you may use blob field to store report template in it.

Procedures below are using TBlobStream for input/output operations.

Store template in database:

procedure StoreTemplate(Fname:string);
var BS:TBlobStream;
begin
ReadTempFile(FName);
Table1.insert;
BS:=TBlobStream.create(Table1.FieldByName('Field1')as TBlobField,bmWrite);
BS.Write((PFile)^,flent);
Table1.Post;
Bs.Free;
end;

procedure ReadTempFile(FInFile:string);
var ActualRead : cardinal;
FileHandle: Integer;
SearchRec : TsearchRec;
size:integer;
flent:longint;
begin
flent:=-1;
if FindFirst(FInFile, faAnyFile, SearchRec)=0 then
begin
size:=SearchRec.Size;
GetMem(PFile,size+2);
FileHandle := FileOpen(FInFile, fmOpenRead);
ActualRead :=FileRead(FileHandle, PFile^, size);
FileClose(FileHandle);
flent:=ActualRead;
end;
if flent=0 then
begin showmessage('Can''t read file'+FInFile); end;
FindClose(SearchRec);
end;

Read template from database:

procedure ReadTemplate;
var BS:TBlobStream;
buffer:pointer;
size:longint;
begin
BS:=TBlobStream.create(Table1Field1,bmRead);
size:=BS.Seek(0,soFromEnd);
BS.Seek(0,soFromBeginning);
GetMem(Buffer,size);
BS.Seek(0,0);
BS.Read((Buffer)^,size);
BS.Free;
//Use method SetTemplateBuffer to assign template for EK RTF component
EKRTF1.SetTemplateBuffer(Buffer, Size);
end;

How To Use BLOB Type in Delphi

Many RDBMS usually complated with BLOB datatype. As far as I know there are many database supported blob already including the free version, which is firebird, mysql, postgresql, oracle and mssqlserver.

Blob type can be used to restore file such as image, e-book, exe file etc. And according to me, it can be used to make an update software version.

In delphi to save and load blob type is as the following:

//Save to blobstream and load
// To save a file to BLOB:
procedure TForm1.Button1Click(Sender: TObject);
var
//blob: TBlobStream;
blob, fs: TStream;
begin
blob := yourDataset.CreateBlobStream(yourDataset.FieldByName('YOUR_BLOB'), bmWrite);
try
blob.Seek(0, soFromBeginning);
fs := TFileStream.Create('c:\your_name.doc', fmOpenRead or
fmShareDenyWrite);
try
blob.CopyFrom(fs, fs.Size)
finally
fs.Free
end;
finally
blob.Free
end;
end;

// To load from BLOB:

procedure TForm1.Button1Click(Sender: TObject);
var
//blob: TBlobStream;
blob: TStream;
begin
blob := yourDataset.CreateBlobStream(yourDataset.FieldByName('YOUR_BLOB'), bmRead);
try
blob.Seek(0, soFromBeginning);

with TFileStream.Create('c:\your_name.doc', fmCreate) do
try
CopyFrom(blob, blob.Size)
finally
Free
end;
finally
blob.Free
end;
end;

Clone Record (Delphi)

procedure CloneRecord(Dataset: TDataSet);
var
aField : Variant;
i : Integer;
begin

// Create a variant Array
aField := VarArrayCreate([0,DataSet.Fieldcount-1],VarVariant);

// read values into the array
for i := 0 to (DataSet.Fieldcount-1) do
aField[i] := DataSet.fields[i].Value ;

DataSet.Append ;

// Put array values into new the record
for i := 0 to (DataSet.Fieldcount-1) do
DataSet.fields[i].Value := aField[i] ;

end;

MySQLDump

How to duplicate/backup data between database both in one server or other server ?
Answer:
- Make sure that you have mysqladmin and mysqldump
- Do the Following script:
#Create the database on the destination server
mysqladmin -u YourUserDBDest -h YourDestServer --password=YourPasswDBDest create YourDBDest
#The Real Job Goes Here
mysqldump -u YourUserDBSource -h YourSourceServer --password=YourPasswDBsource YourSourceDB| mysql -u YourUserDBDest -h YourDestServer --password=YourPassDBDest YourDBDest


Here is an example (I use EasyPHP 2.0b1 for win) that I bundled in batch file:

cd\Program Files\EasyPHP 2.0b1\mysql\bin\
mysqladmin -u root -h Server2 --password=secret2 create db2
mysqldump -u root -h Server1 --password=secret1 db1 | mysql -u root -h Server2 --password=secret2 db2

HTTP Post

Webbase application is good for any operation system,however, it's not simple to make it easy for bulk data manipulation. While, desktop application has it, easy for bulk data manipulation, user friendly and etc.
Why? Because desktop application, delphi for example, supported event driven, while webbase is not.
Now many webbase language, like php, combined with ajax can handle this, however, it's need huge of memory resources.

In order to solve this obstacle, delphi has already prepared. With IdHTTP (Indy component), we can manipulate web application through dekstop application.

The following is an example:

procedure TFHttpUpload.Upload;
var
Stream : TIdMultipartFormDataStream;
begin
Stream := TIdMultipartFormDataStream.Create;
try
StatusBar1.SimpleText:='Upload............';
Stream.AddFormField('ffield1','one');
Stream.AddFormField('ffield2','dua');
Stream.AddFile( 'fFile', fname, 'text/plain' );
str :=IdHTTP1.Post('http://www.myweb.com/upload.php', Stream );
ShowMessage('Upload Sucess :)');
except
ShowMessage('Upload Fail :(');
end;
Stream.Free;
end;

Query PHP Class

I have a php class 'Query' that I made for delphi programmer :).
The background of creating this class is to make easier and faster coding
for programmer especially for delphi programmer like me :).
This class is using mysql for the database, that we all knew already that
mysql and php are closer family and together with apache so called 'the
three masketeer' :))

At the past, everytime I made a new php page along with mysql database, I
have to write the same code like the following :
$q=mysql_query("select * from table");
if (mysql_num_rows($q)>0){
$r = mysql_fetch_array ($q);
foreach ($r AS $key=>$val)}
{
.........
}

until I thought, how if I have a class like ZQuery or IBQuery in delphi. And
I tried to write a code like the following to overcome that burden :), the
code maybe is perfectless, however it's helpfull especially for me :)
Here is the code:


class YQuery {
Var $ArrRecord;
Var $SQLText;
Var $ArrField;
Var $Dataset;
Var $RecordCount;
Var $RecNo;
Var $FieldByName;
Var $Fields;
Var $FieldCount;
Var $FieldNameIndex;
Var $FieldArr;
Var $dsState;
Var $TableName;
Var $Active;

function Connect(){
$db_username = "root";
$db_hostname = "localhost";
$db_password = "";
$db_name = "your_database_name";
$con = mysql_connect($db_hostname, $db_username, $db_password);
$db = mysql_select_db($db_name, $con);
}

function YQuery(){
/*Create*/
}
function Close(){
/* Destroy/Free */
$this->Dataset->Close;
$this->FieldCount->Close;
$this->FieldNameIndex->Close;
$this->FieldArr->Close;
$this->Fields->Close;
mysql_free_result;
unset($ArrRecord,$SQLText,$ArrField,$Dataset,$RecordCount,$RecNo,
$FieldByName,$FieldNames,$FieldCount,$FieldNameIndex,$FieldArr,
$dsState,$TableName,$Active);
}

function GetSQLText(){
return $this->SQlText;
}
function SetSQLText($pSQl_Text){
$this->SQl_Text=$pSQL_Text;
}
function Open(){
$this->Connect();
$this->Dataset= mysql_query($this->SQLText) or die (mysql_error());
$this->FieldCount =mysql_num_fields($this->Dataset);

for ($i=0;$i<$this->FieldCount;$i++) {
$str=$str."&".mysql_field_name($this->Dataset, $i)."=".$i;
};

/*buang huruf "&" pertama */
$str=substr_replace($str,'',0,1);

$this->FieldNameIndex=$str;

/*Isi Array FieldArr[Field Ke-][baris Ke-]*/
$j=0;
$this->RecNo=0;
while ($this->Fields=mysql_fetch_row($this->Dataset)){
for ($i=0;$i<$this->FieldCount;$i++) {
$this->FieldArr[$i][$j]=$this->Fields[$i];
};
$j++;
};
if ($j>0) {
$this->RecNo=$j;
$this->RecordCount=$j;
};
$this->Active=1;
}

function GetFieldByName($pName){
parse_str($this->FieldNameIndex);
return $this->FieldArr[$$pName][$this->RecNo-1];
}
function SetFieldByName($pName,$Value){
parse_str($this->FieldNameIndex);
$this->TmpArr[$$pName]=$Value;
}

function Next(){
if ($this->RecNo<$this->RecordCount) {
$this->RecNo=$this->RecNo+1;
};
}
function First(){
if ($this->RecordCount>0) {
$this->RecNo=1;
};

}
function Previous(){
if ($this->RecNo>1) {
$this->RecNo=$this->RecNo-1;
};
}
function Last(){
if ($this->RecordCount>0) {
$this->RecNo=$this->RecordCount;
};
}
function GotoRow($aRow){
if ($Row<=$this->RecordCount-1) {
$this->RecNo=$aRow;
};
}
function Post(){
echo "Possss";
if ($this->dsState=='Insert'){
$str_ins="insert into ".$this->TableName."(";
for ($i=0;$i<$this->FieldCount;$i++) {
$str_field=$str_field.",".mysql_field_name($this->Dataset, $i);
};

/*buang huruf "," pertama*/
$str_field=substr_replace($str_field,'',0,1).")";

$str_field= $str_field." values(";
for ($i=0; $i < $this->FieldCount; $i++) {
parse_str($this->FieldNameIndex);
$fnm=mysql_field_name($this->Dataset, $i);
$type =mysql_field_type($this->Dataset, $i);

/*Siapkan Nilai dan format penulisannya*/
$str_value=$str_value.",".$this->Quoted($this->TmpArr[$$fnm],$type);
};

/*buang huruf "," pertama*/
$str_value=substr_replace($str_value,'',0,1).")";

$sqlstr=$str_ins.$str_field.$str_value;
echo $sqlstr;
} ;
if ($this->dsState=='Edit'){
$str_upd="update ".$this->TableName." set ";
for ($i=0; $i < $this->FieldCount; $i++) {
parse_str($this->FieldNameIndex);
$fnm=mysql_field_name($this->Dataset, $i);
$type =mysql_field_type($this->Dataset, $i);

/*Siapkan Nilai,Klausa Where dan format penulisannya*/
$str_value=$str_value.",".$fnm."=".$this->Quoted($this->TmpArr[$$fnm],$type);
$nilai=$this->Quoted($this->GetFieldByName($fnm),$type);
if ($nilai=='null'){
$str_whr=$str_whr." and ".$fnm." is ".$nilai;
} else {
$str_whr=$str_whr." and ".$fnm."=".$nilai;
};
};

/*buang huruf "," pertama*/
$str_value=substr_replace($str_value,'',0,1);

$sqlstr=$str_upd." ".$str_value." where 1=1 ".$str_whr;
} ;
//echo $sqlstr;
mysql_query($sqlstr);
$this->dsState='Browse';

/*Refresh*/;
$this->Open();
}
/* end Post-------*/

function Delete(){
if ($this->Active!=1) {
echo "Not Open Yet!
";
break;
};
$str_del="delete from ".$this->TableName;
for ($i=0; $i < $this->FieldCount; $i++) {
parse_str($this->FieldNameIndex);
$fnm=mysql_field_name($this->Dataset, $i);
$type =mysql_field_type($this->Dataset, $i);

/*Siapkan Klause Where dan format penulisannya*/
$nilai=$this->Quoted($this->GetFieldByName($fnm),$type);
if ($nilai=='null'){
$str_whr=$str_whr." and ".$fnm." is ".$nilai;
} else {
$str_whr=$str_whr." and ".$fnm."=".$nilai;
};
};

$sqlstr=$str_del." where 1=1 ".$str_whr;
//echo $sqlstr;
mysql_query($sqlstr);
$this->dsState='Browse';
}

function Edit(){
if ($this->Active!=1) {
echo "Not Open Yet!
";
break;
};
$this->dsState='Edit';
}

function Insert(){
if ($this->Active!=1) {
echo "Not Open Yet!
";
break;
};
$this->dsState='Insert';
}

/* fungsi penyiapan quote jika klausa where tipe datanya string,date,time
dll*/
function Quoted($str,$data_type) {
switch ($data_type) {
case "string":
if ($str=="") {
return "null";
} else {
return "'".$str."'";
};
break;
case "time":
if ($str=="") {
return "null";
} else {
return "'".$str."'";
};
break;
case "date":
if ($str=="") {
return "null";
} else {
//return
"'".Substr($str,6,4)."-".Substr($str,3,2)."-".Substr($str,0,2)."'";
return "'".$str."'";
};
break;
case "int":
if ($str=="") {
return "null";
} else {
return $str;
};
break;
case "real":
if ($str=="") {
return "null";
} else {
return $str;
};
break;
};
}
};

?>


and use this following code to test drive :)


//contoh Penggunaan:
include("YComponent.php");
$Query=new YQuery; //Create
$Query->SQLText='select * from tbl_menu order by kode'; //Masukkan query
$Query->TableName='menu'; //jangan lupa nama tabelnya berguna waktu
insert,update,delete;
$Query->Open(); //Open Query
$Query->First(); //Ke Baris Pertama

/*Demo Loop*/
for ($i=0;$i<=$Query->RecordCount-1;$i++){
echo
$Query->GetFieldByName("kode")."-".$Query->GetFieldByName("nama")."
";
$Query->Next(); //Ke Baris Berikutnya
};

$Query->GotoRow(3);
echo "
GotoRow(".$Query->RecNo.")
".$Query->GetFieldByName("kode")."-".$Query->GetFieldByName("nama")."
";

$Query->Previous();
echo "
Previous:".$Query->RecNo."
".$Query->GetFieldByName("kode")."-".$Query->GetFieldByName("nama")."
";

/*Demo Insert Data*/;
/*$Query->Insert(); //Persiapkan Flag Insert;
$Query->SetFieldByName("kode","kode baru"); //isi field kode='kode baru'
$Query->SetFieldByName("nama","nam baru"); //isi field nama='nama baru'
$Query->SetFieldByName("tingkat","0");
$Query->SetFieldByName("sts_dtl",1);
$Query->SetFieldByName("nama_file","nmfilleee");
$Query->Post();
*/

/*Demo Update Data*/;
/*$Query->Last(); //Misal mo edit record Terakhir;
$Query->Edit(); //Persiapkan Flag Update;
$Query->SetFieldByName("kode","kode update"); //isi field kode='kode
update'
$Query->SetFieldByName("nama","nam update"); //isi field nama='nama
update'
$Query->Post();
*/

/*Demo Delete Data*/
/*$Query->Last(); //Misal mo delete record Terakhir;
$Query->Delete();
$Query->Post();
*/

$Query->Close(); //Bebaskan Memory;
?>

require vs include

php, to combine scripts from several files is by using statements include,
include_once,require and require_once.
So, what's the different between them ?

include vs include_once:
I will describe it by refering to the following example:

suppose we have a file one.php consist of code:
echo "Welcome to the jungle"

and we have a file two.php consist of code:
echo "By Tarzan"

then we like to combine one.php code into two.php
with code like this:

include("one.php");
include("one.php");
include("one.php");
echo "By Tarzan"

the result would be :

Welcome to the jungle
Welcome to the jungle
Welcome to the jungle
By Tarzan


if the we change the code like this:
include_once("one.php");
include_once("one.php");
include_once("one.php");
echo "By Tarzan"

the result would be :

Welcome to the jungle
By Tarzan

because 'include_once' would eliminate all of same values and would display
once, while 'include' would display all of them many times depend on how
many times one.php was repeated


require vs require_once:
the same as include vs include_once


include vs require:
viewed from the function of combining code, both of include and require is
same.
the different is when error occure, 'require' would not execute all of the
next codes , while 'include' would execute.


that's all :)

PHP Class

I found a great thing in PHP 5, which is the real class concept has already
adopted ( similar to other language like delphi). So that, OOP (Object
Oriented Programming) can be implemented very well.

In general, PHP class has structure like this

class aname() {
//definition of variable, in delphi called property
var ....

//constructor, in delphi would be Create
function __constructor(){
//initial value should be placed here
......
}
function a(){
...
}
function b(){
....
}
}


Ideally, a class should be has Get and Set method
Get is to retrieve data from a class
Set is to place data into a class

example:

class person() {
var $IdentityNo,
$Name;

function __construct(){
$this->$IdentityNo="";
$this->$Name="";
}

function GetIdentityNo(){
return $this->$IdentityNo;
}
function GetName(){
return $this->$Name;
}

function SetIdentityNo($AIdentityNo){
$this->$IdentityNo=$AIdentityNo;
}
function SetIdentityNo($AName){
$this->$Name=$AName;
}
}

usage:

$p=new person; //construc
$p->SetIdentityNo("001"); //Set Identity No
$p->SetName("Paijo"); //Set Name

echo $p->GetIdentityNo(); //print Identity No
echo $p->GetName(); //print Name


that's a simple, right :)

MySQL Boolean/Integer Result Field

In mysql if we make a query like this:

select 1<0 ->the result would be 0 (incorrect because 1 is not less then 0)
select 1>0 ->the result would be 1 (correct because 1 is not more then 0)

it's mean that : 0=False, 1=True

Form Inherited

Do you experience with delphi form identically many time? If you do, you need to make it simple with inherited.
Actually this method is not something new, because delphi is OOP (Object Oriented Programming) and form is class. If you understanding class very well, you will not find serious trouble with this methode.
However this method doesn't need understanding of class, because this is very simple.
OK, let starting with :
-new application at your delphi and you would have form1 for default
-place one button and save your new application as project1 and form1 as form1
-click File->New->Other->project1->form1
-notice what happen :)
-place another button on your form
-Now you have Form2 identically with Form1, but you added with one button

it's simple, right?

In any complex form with code inside, this would help much without coding same things many times :)

Getting Up Level

Getting Up Level

Leveling Database structure, is maight be not awkward for many programmer. I got problem when getting the up/upper level of some code. At last I found this way :


Suppose you have a data structure like this:

code varchar(10)
name varchar(100)
parent varchar(10)
level integer

code name parent level
------------------------------------
A1 AOne 1
A10 AOnezero A1 2
A11 AOneOne A1 2
A110 AOneOnezero A11 3


what is the up level 1 code of A110?

answer:
make the sql like this:
select * from table where level=1 and 'A110' like concat(code,'%')

Differences between CHAR and VARCHAR

Many people believe that VARCHAR is better because it stores only actual data, while CHAR is stored in full length. It is not true. In fact, both CHAR and VARCHAR are stored in memory buffer in their full, declared length; when the row is stored on disk, then RLE compression algorithm is used to compress whole row, i.e. CHARs, VARCHARs, INTEGERs, DATEs, etc. all together. So if you want to save space, CHARs are slightly better than VARCHARs (the differenece is that VARCHAR stores string length in two bytes).
There is also a bug that causes that VARCHAR does not properly clean string tail if you assign shorter string, thus causing worse compression. (this problem is fixed in Firebird-0.9.4)

Many people also believe that VARCHAR sends over network only actual data, while CHAR is sent in full length. It is not true either. Communication between client and server is done via messages of fixed length. For this reason both CHAR and VARCHAR are sent in their full declared length. (this problem is fixed in IB-6.5)

So decision whether use CHAR or VARCHAR should be based solely on application's requirements. E.g. store fixed length codes in CHAR, store names in VARCHAR (to allow correct concatenating).

Date and Time Calculations (Firebird)

Date and Time Calculations
When doing arithmetic operations with TIMESTAMP values, Firebird works with them as decimal number, where

* the integral fraction is the number of days
* the decimal fraction is the part of a day

For example:

* Two days: 2.0
* One hour: 1.0/24.0
* One minute: 1.0/1440.0
* One second: 1.0/86400.0

Extracting the smaller units from a TIMESTAMP value:

* Number of seconds: VALUE*86400.0
* Number of minutes: VALUE*1440.0
* Number of hours: VALUE*24.0

Don't forget the decimal point in the numbers (e.g. 1.0), otherwise the result will be integer. I spent a lot time debugging a calculation that didn't work because I was dividing by an integer. Since that time, I remember.
Posted by Dan : 2:04 PM


---------------
firebird:
tgl jam server sekarang : current_timestamp
tgl : CAST ('today' AS TIMESTAMP)
jam : extract(hour from current_timestamp)||':'||extract(minute from current_timestamp)||':'||extract(second from current_timestamp)

Export StringGrid to An Excel File (way two)

#144: Fast data transfer to MS Excel

Anyone who worked with OLE automation, know that OLE is very slowly. Especially if you work using late binding (which have a lot of other advantages which early binding haven't)

A reason of bad performance is the next: every command (method or property) which you access (no matter in read or write mode) will be interpretated (a-la script). I mean that this command must be found in table of available methods/properties by string name and only if found, a physical memory address for execution will be calculated.

So if your code contain a lot of access to methods/properties, your code will be slow.

For example, you need transfer some data from Delphi application into xls-spreadsheet.

You can solve a task in two different ways (now I describe only late binding for OLE automation and don't describe another methods):

* to navigate thru own data and export every data in required cell
* to prepare a variant array with copied data and apply this array with data into desired range of cells

I must say that second method will be faster than first because you'll call less commands from OLE object and main code will be executed without OLE automation.

Small sample: to export some StringGrid into xls-file.

var
xls, wb, Range: OLEVariant;
arrData: Variant;
begin
{create variant array where we'll copy our data}
arrData := VarArrayCreate([1, yourStringGrid.RowCount, 1, yourStringGrid.ColCount], varVariant);

{fill array}
for i := 1 to yourStringGrid.RowCount do
for j := 1 to yourStringGrid.ColCount do
arrData[i, j] := yourStringGrid.Cells[j-1, i-1];

{initialize an instance of Excel}
xls := CreateOLEObject('Excel.Application');

{create workbook}
wb := xls.Workbooks.Add;

{retrieve a range where data must be placed}
Range := wb.WorkSheets[1].Range[wb.WorkSheets[1].Cells[1, 1],
wb.WorkSheets[1].Cells[yourStringGrid.RowCount, yourStringGrid.ColCount]];

{copy data from allocated variant array}
Range.Value := arrData;

{show Excel with our data}
xls.Visible := True;
end;

Of course, you must understand that such method is not good for large data arrays because to allocate in memory large array is not easy task. You must find some optimal size for data transfer (for example, to copy every 10 rows) and as result you'll receive an optimal code both for memory use and performance.

Anyway more faster way to transfer data is not use OLE at all:-) You can use my TSMExportToXLS component from SMExport suite for this task. There is implemented a direct xls-file creation which doesn't require installed MS Excel at all..

Export StringGrid to An Excel File (way one)

{1. With OLE Automation }

uses
ComObj;

function RefToCell(ARow, ACol: Integer): string;
begin
Result := Chr(Ord('A') + ACol - 1) + IntToStr(ARow);
end;

function SaveAsExcelFile(AGrid: TStringGrid; ASheetName, AFileName: string): Boolean;
const
xlWBATWorksheet = -4167;
var
Row, Col: Integer;
GridPrevFile: string;
XLApp, Sheet, Data: OLEVariant;
i, j: Integer;
begin
// Prepare Data
Data := VarArrayCreate([1, AGrid.RowCount, 1, AGrid.ColCount], varVariant);
for i := 0 to AGrid.ColCount - 1 do
for j := 0 to AGrid.RowCount - 1 do
Data[j + 1, i + 1] := AGrid.Cells[i, j];
// Create Excel-OLE Object
Result := False;
XLApp := CreateOleObject('Excel.Application');
try
// Hide Excel
XLApp.Visible := False;
// Add new Workbook
XLApp.Workbooks.Add(xlWBatWorkSheet);
Sheet := XLApp.Workbooks[1].WorkSheets[1];
Sheet.Name := ASheetName;
// Fill up the sheet
Sheet.Range[RefToCell(1, 1), RefToCell(AGrid.RowCount,
AGrid.ColCount)].Value := Data;
// Save Excel Worksheet
try
XLApp.Workbooks[1].SaveAs(AFileName);
Result := True;
except
// Error ?
end;
finally
// Quit Excel
if not VarIsEmpty(XLApp) then
begin
XLApp.DisplayAlerts := False;
XLApp.Quit;
XLAPP := Unassigned;
Sheet := Unassigned;
end;
end;
end;

// Example:

procedure TForm1.Button1Click(Sender: TObject);
begin
if SaveAsExcelFile(stringGrid1, 'My Stringgrid Data', 'c:\MyExcelFile.xls') then
ShowMessage('StringGrid saved!');
end;


{**************************************************************}
{2. Without OLE }

procedure XlsWriteCellLabel(XlsStream: TStream; const ACol, ARow: Word;
const AValue: string);
var
L: Word;
const
{$J+}
CXlsLabel: array[0..5] of Word = ($204, 0, 0, 0, 0, 0);
{$J-}
begin
L := Length(AValue);
CXlsLabel[1] := 8 + L;
CXlsLabel[2] := ARow;
CXlsLabel[3] := ACol;
CXlsLabel[5] := L;
XlsStream.WriteBuffer(CXlsLabel, SizeOf(CXlsLabel));
XlsStream.WriteBuffer(Pointer(AValue)^, L);
end;


function SaveAsExcelFile(AGrid: TStringGrid; AFileName: string): Boolean;
const
{$J+} CXlsBof: array[0..5] of Word = ($809, 8, 00, $10, 0, 0); {$J-}
CXlsEof: array[0..1] of Word = ($0A, 00);
var
FStream: TFileStream;
I, J: Integer;
begin
Result := False;
FStream := TFileStream.Create(PChar(AFileName), fmCreate or fmOpenWrite);
try
CXlsBof[4] := 0;
FStream.WriteBuffer(CXlsBof, SizeOf(CXlsBof));
for i := 0 to AGrid.ColCount - 1 do
for j := 0 to AGrid.RowCount - 1 do
XlsWriteCellLabel(FStream, I, J, AGrid.cells[i, j]);
FStream.WriteBuffer(CXlsEof, SizeOf(CXlsEof));
Result := True;
finally
FStream.Free;
end;
end;

// Example:

procedure TForm1.Button2Click(Sender: TObject);
begin
if SaveAsExcelFile(StringGrid1, 'c:\MyExcelFile.xls') then
ShowMessage('StringGrid saved!');
end;

{**************************************************************}
{3. Code by Reinhard Schatzl }

uses
ComObj;

// Hilfsfunktion für StringGridToExcelSheet
// Helper function for StringGridToExcelSheet
function RefToCell(RowID, ColID: Integer): string;
var
ACount, APos: Integer;
begin
ACount := ColID div 26;
APos := ColID mod 26;
if APos = 0 then
begin
ACount := ACount - 1;
APos := 26;
end;

if ACount = 0 then
Result := Chr(Ord('A') + ColID - 1) + IntToStr(RowID);

if ACount = 1 then
Result := 'A' + Chr(Ord('A') + APos - 1) + IntToStr(RowID);

if ACount > 1 then
Result := Chr(Ord('A') + ACount - 1) + Chr(Ord('A') + APos - 1) + IntToStr(RowID);
end;

// StringGrid Inhalt in Excel exportieren
// Export StringGrid contents to Excel
function StringGridToExcelSheet(Grid: TStringGrid; SheetName, FileName: string;
ShowExcel: Boolean): Boolean;
const
xlWBATWorksheet = -4167;
var
SheetCount, SheetColCount, SheetRowCount, BookCount: Integer;
XLApp, Sheet, Data: OLEVariant;
I, J, N, M: Integer;
SaveFileName: string;
begin
//notwendige Sheetanzahl feststellen
SheetCount := (Grid.ColCount div 256) + 1;
if Grid.ColCount mod 256 = 0 then
SheetCount := SheetCount - 1;
//notwendige Bookanzahl feststellen
BookCount := (Grid.RowCount div 65536) + 1;
if Grid.RowCount mod 65536 = 0 then
BookCount := BookCount - 1;

//Create Excel-OLE Object
Result := False;
XLApp := CreateOleObject('Excel.Application');
try
//Excelsheet anzeigen
if ShowExcel = False then
XLApp.Visible := False
else
XLApp.Visible := True;
//Workbook hinzufügen
for M := 1 to BookCount do
begin
XLApp.Workbooks.Add(xlWBATWorksheet);
//Sheets anlegen
for N := 1 to SheetCount - 1 do
begin
XLApp.Worksheets.Add;
end;
end;
//Sheet ColAnzahl feststellen
if Grid.ColCount <= 256 then
SheetColCount := Grid.ColCount
else
SheetColCount := 256;
//Sheet RowAnzahl feststellen
if Grid.RowCount <= 65536 then
SheetRowCount := Grid.RowCount
else
SheetRowCount := 65536;

//Sheets befüllen
for M := 1 to BookCount do
begin
for N := 1 to SheetCount do
begin
//Daten aus Grid holen
Data := VarArrayCreate([1, Grid.RowCount, 1, SheetColCount], varVariant);
for I := 0 to SheetColCount - 1 do
for J := 0 to SheetRowCount - 1 do
if ((I + 256 * (N - 1)) <= Grid.ColCount) and
((J + 65536 * (M - 1)) <= Grid.RowCount) then
Data[J + 1, I + 1] := Grid.Cells[I + 256 * (N - 1), J + 65536 * (M - 1)];
//-------------------------
XLApp.Worksheets[N].Select;
XLApp.Workbooks[M].Worksheets[N].Name := SheetName + IntToStr(N);
//Zellen als String Formatieren
XLApp.Workbooks[M].Worksheets[N].Range[RefToCell(1, 1),
RefToCell(SheetRowCount, SheetColCount)].Select;
XLApp.Selection.NumberFormat := '@';
XLApp.Workbooks[M].Worksheets[N].Range['A1'].Select;
//Daten dem Excelsheet übergeben
Sheet := XLApp.Workbooks[M].WorkSheets[N];
Sheet.Range[RefToCell(1, 1), RefToCell(SheetRowCount, SheetColCount)].Value :=
Data;
end;
end;
//Save Excel Worksheet
try
for M := 1 to BookCount do
begin
SaveFileName := Copy(FileName, 1,Pos('.', FileName) - 1) + IntToStr(M) +
Copy(FileName, Pos('.', FileName),
Length(FileName) - Pos('.', FileName) + 1);
XLApp.Workbooks[M].SaveAs(SaveFileName);
end;
Result := True;
except
// Error ?
end;
finally
//Excel Beenden
if (not VarIsEmpty(XLApp)) and (ShowExcel = False) then
begin
XLApp.DisplayAlerts := False;
XLApp.Quit;
XLAPP := Unassigned;
Sheet := Unassigned;
end;
end;
end;

//Example
procedure TForm1.Button1Click(Sender: TObject);
begin
//StringGrid inhalt in Excel exportieren
//Grid : stringGrid, SheetName : stringgrid Print, Pfad : c:\Test\ExcelFile.xls, Excelsheet anzeigen
StringGridToExcelSheet(StringGrid, 'Stringgrid Print', 'c:\Test\ExcelFile.xls', True);
end;

10 of Most Delphi Used Function

Here is 10 functions mostly used

procedure TForm1.Button1Click(Sender: TObject);
var
S: String;
begin
{ 1 }
S := Copy('Delphi Rulezz', 5, 6); { Reads the text }
ShowMessage(S);
{ 2 }
S := Concat('1 ',' Two, ','3'); { Connects the text }
ShowMessage(S);
{ 3 }
ShowMessage('Length: '+IntToStr(Length('Good'))); { Shows the length of the
string in numbers [integer] }
{ 4 }
S := 'The Example'; {Deletes the text }
Delete(S, 8, 7);
ShowMessage(S);
{ 5 }
S := 'Ralph is a dog';
Insert('good ', S, 12); { Inserts the text }
ShowMessage(S);
{ 6 }
S := 'Delphi';
ShowMessage(UpperCase(S)); { Converts the text to upper case }
{ 7 }
S := 'Delphi';
ShowMessage(LowerCase(S)); { Converts the text to lower case }
{ 8 }
S := 'Delphi';
ShowMessage('''P'' in ''Delphi'' is:'+IntToStr(Pos('p',S))+'th'); { Gets
the position of the string in the string }
{ 9 }
S := 'a1, a2, a3, a4, a5.';
ShowMessage('a change to b: ' + StringReplace(S, 'a', 'b', [rfReplaceAll]));
{ Changes the text from one to another }
{ 10 }
S := 'This text:goes lower.';
S := WrapText(S, #13#10, [':'], 12);
ShowMessage(S); { Moves the text lower }
end;