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
No comments:
Post a Comment