How can I learn EXCEL Programming easily, quickly and effectively?

You can learn EXCEL Programming very easily!
Believe me it is a child play!!
Yet the result will be an Expert Solution!!!

If you know EXCEL operations very well, you can code for the same. For example, you may wish to subtotal the EMP records based on DEPT. (Refer my previous Blog Post for programmatically fetching data from ORACLE to EXCEL Worksheet).

You know using DATA / SUBTOTAL menu. If you want to do the same from a EXCEL VBA macro, then You need not learn EXCEL VBA language. Here is the shortcut.

Procedure
----------

• Start recording macro. (TOOLS / MACRO / RECORD NEW MACRO)
• Now you can start working as usual. In this case you find the subtotal of the EMP records based on DEPT.
• Stop recording macro (TOOLS / MACROS / STOP RECORDING)
• Now Start VBE (Visual Basic Editor) (TOOLS / MACROS / Visual Basic Editor)
• In the Project Explorer, Expand the Modules. You can see 'Module1'. Open the 'Module1' by double clicking it.
• VOILA!!! You can see the code for finding subtotal.
• COPY, PASTE and CUSTOMIZE

If you ask me what is EXCEL-VBA programming!!!

for a NOVICE Programmers

RECORD, COPY, PASTE and CUSTOMIZE

For Advanced Programmers

Will tell in the subsequent posts. Keep visiting my Blog.

How to display the EMP records from ORACLE?

A 100 million question among IT managers???

MS-EXCEL is one of the most useful business tool, but can I display the RECORDS from our company Database... say from ORACLE?
This will enable the executive to process the data as per their working style and make useful decision!!! This facility offers flexibility and fidelity.

Here is a simple solution from kg.

MS-EXCEL can access data from any ODBC compatible RDBMS. Being a popular RDBMS of many corporate choice, I am going to demonstrate how to access ORACLE data using MS-EXCEL.

Step by Step Procedure

• Create a Data Source Name in ODBC. Using
  Control panel
  Administrative Tools
  Data Sources (ODBC)
  Add an User DSN (You can get help from competitive person from your organization)
• Start MS-EXCEL
• Start Visual Basic Editor (Tools / Macro / Visual Basic Editor)
• In the VBE (Visual Basic Editor), view the Project Explorer
  (VIEW / Project Explorer) I use CTRL+R key.
• Double click on the ThisWorkBook. You can see the CODE WINDOW. In this select WorkBook in the Object List and select Open in the Event List.
  
• Type the following code in Workbook_Open procedure
  
  Private Sub Workbook_Open()
  Dim cnEmp As ADODB.Connection
  Dim rsEmp As ADODB.Recordset
  
  If cnEmp Is Nothing Then
  Set cnEmp = New ADODB.Connection
  With cnEmp
  .ConnectionString = "DRIVER={Microsoft ODBC for Oracle};UID=SCOTT;PWD=TIGER;"
  .Open
  End With
  End If
  
  If rsEmp Is Nothing Then
  Set rsEmp = New ADODB.Recordset
  rsEmp.Open "select * from emp", cnEmp, adOpenStatic, adLockBatchOptimistic
  End If
  
  'To display Column names
  For i = 1 To rsEmp.Fields.Count
  Sheets("Sheet1").Cells(3, i).Value = rsEmp.Fields(i - 1)
  End If
  Sheets("sheet1").Range("a4").CopyFromRecordset rsEmp
  End Sub
  
  Here I uploaded the excel workbook also.

I hope that this is a simple yet more useful solution. Once you tasted, I am sure you will not leave this elegant tool. It can be put into effective use by ORACLE DBAs.

Once again, You can email me your queries. I am ready to give my assistance.

What is Excel Programming?

MS-EXCEL can be programmed to meet our custom requirement effectively. An EXCEL prgrammer write code in VBA (Visual Basic for Applications). Hence it is also called as EXCEL-VBA Programming.

So any existing VB Programmer can do EXCEL prgramming.

Here is a simple solution to add two numbers. It is just to show how to write VBA Functions. So dont argue with me what is the use of this solution. I know that the simple and well known cell formula =A1+B1 will do this.

• Start Visual Basic Editor using TOOLS / MACRO / Visual Basic Editor. (I use ALT+F11 key combination!!!)
• Insert a module (Insert / Module)
• Type the following in the module

Public Function add(ByVal a As Integer, ByVal b As Integer) As Long

add = a + b

End Function

• Now in the worksheet type the following table

TRAVEL FOOD TOTAL

JAN-08 230 430 =add(a1,b1)

FEB-08 210 545 copy the above formula

• In the total column enter the following formula =add(a2,b2) and drag below.

I hope that this will give you a fundamental idea for novice user. For advanced use, they can write many sophisticated function in this way.

Feel free to contact me for any assistance (ofcourse I will assist you free of cost only). karthiganesh@yahoo.com

What can you do using MS-EXCEL?

Literally You can use MS-EXCEL for anything...

* Worksheet
* Word processing
* Presentation
* Front End Tool for popular RDBMS
....

and You too can add one more item to this list...

Here is the list for Advanced / Specialised applications

* Business Intelligence Tool
* What-If Analysis Tool
* Real Time Calculations for Production Specialists
* Statistical Analysis Tool
* Charting Tool

and more.