Friday, August 3, 2007

Sort by Colors

In excel, sorting by colors is not a difficult situation. Anyone with a little knowledge of VBA can write this simple code. Just rush to the following link to read more:

http://www.cpearson.com/excel/SortByColor.htm

Thanks,
Vikas

Getting values from Databases quickly.

When you request some values from databases in excel, it probably takes few secs. For example, when you get values from Microsoft query section, it takes lesser time as compared to the procedure when you create a connection and enters the values record by record. Its always a better option to fetch the complete table and insert the whole table in excel. If your database is huge, this technique works really well. See the following code:

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;DSN=Your Oracle DSN;DBQ=Your database Service;uid=your account;pwd=your password;DriverId=25;FIL=Oracle;MaxBufferSize=2048;PageTimeout=5;" _
)), Destination:=Range("$A$8")).QueryTable
.CommandText = qur <-----(write your query like select * from something)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With


The above code fetches the data from oracle and enters in the active excel sheet. It will return the table object based on the query you will give in the code, and insert the results in tabular format in no time. If you compare the process with the procedure of connecting and reading the rows one by one through recordset....its going to take 20 times longer than it should usually take!

Thanks,
Vikas