Export from Oracle to excel using VB

[alexianet]

Hi. I would like to make an export from an Oracle database to excel but I do not know how. Could anyone please help me?
I have tried this:
[code=vb]
Public Function ExportOracleTab leToExcel(ByVal strTable As String, strSheetName As String, path_file_excel As String)

Dim SirSql As String
SirSql = ""
Dim prj_temp As String
prj_temp = ""
prj_temp = Trim(UCase(Form 1.txtPrjTempRap .Text))
Dim PrjTab As String ‘name of project and table
PrjTab = prj_temp & "." & strTable
Dim ConnOracleTemp As String
Dim ConnectOracleTe mp As New ADODB.Connectio n
Dim RS_Oracle_Temp As New ADODB.Recordset
ConnOracleTemp = "DSN=" & Form1.txtDSN.Te xt & "; Uid=" & Form1.txtUid.Te xt & "; Pwd=" & Form1.txtPwd.Te xt & ";"
ConnectOracleTe mp.CursorLocati on = adUseServer

ConnectOracleTe mp.Open (ConnOracleTemp )
SirSql = " SELECT * INTO " & path_file_excel & “ FROM " & PrjTab & " "

Debug.Print SirSql
ConnectOracleTe mp.Execute (SirSql)
ConnectOracleTe mp.Close

End Function
[/code]
but no luck. VB just crashes - VB has encountered a problem and needs to close.
Thank you!

[debasisdas]

The way you are trying is never going to work . Better try to use TOAD for the purpose.

[alexianet]

The way you are trying is never going to work . Better try to use TOAD for the purpose.

Hi. Thank you for your answer. What is TOAD?

[debasisdas]

Hi. Thank you for your answer. What is TOAD?

That is a third party tool for oracle .

[alexianet]

That is a third party tool for oracle .

Hi. Are you saying that it is not possible to export a table from oracle into excel using VB6? I can export it by putting all information into a mshflexgrid and then take each cell and put it into excel but I want something faster. Like for Access for example. You can put the entire table from an Access database into an excel file. It should be possible from oracle too ... I think. Thank you!

[debasisdas]

I did not say that it is impossible. What i said is the code you have tried is wrong . The sql you tried totally wrong . You can't directly select reford from a table to a file.

[alexianet]

I did not say that it is impossible. What i said is the code you have tried is wrong . The sql you tried totally wrong . You can't directly select reford from a table to a file.

Ok. Do you have nay other idea that you could share? Thank you!

[debasisdas]

You can read data from database and write the same to the excel file record by record in a loop..

[alexianet]

You can read data from database and write the same to the excel file record by record in a loop..

Hi. I have thought about this too, but I wanted to export all at once. using the recordset works quite fast. Thank you for your help!

Do Until RS_Oracle_Temp. EOF 'parcurgere inregistrari
'inserare valori in excel
For col_xls = 0 To Val(RS_Oracle_T emp.Fields.Coun t - 1)
ExcelSheet.Cell s(lin_xls, col_xls + 1).Value = RS_Oracle_Temp. Fields(col_xls)
Next
RS_Oracle_Temp. MoveNext
lin_xls = lin_xls + 1
Loop