473,320 Members | 2,104 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Export from Oracle to excel using VB

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:
Expand|Select|Wrap|Line Numbers
  1. Public Function ExportOracleTableToExcel(ByVal strTable As String, strSheetName As String, path_file_excel As String)
  2.  
  3.         Dim SirSql As String
  4.             SirSql = ""
  5.         Dim prj_temp As String
  6.             prj_temp = ""
  7.             prj_temp = Trim(UCase(Form1.txtPrjTempRap.Text))
  8.         Dim PrjTab As String         ‘name of project and table
  9.             PrjTab = prj_temp & "." & strTable
  10.         Dim ConnOracleTemp As String
  11.         Dim ConnectOracleTemp As New ADODB.Connection
  12.         Dim RS_Oracle_Temp As New ADODB.Recordset
  13.         ConnOracleTemp = "DSN=" & Form1.txtDSN.Text & "; Uid=" & Form1.txtUid.Text & "; Pwd=" & Form1.txtPwd.Text & ";"
  14.         ConnectOracleTemp.CursorLocation = adUseServer
  15.  
  16. ConnectOracleTemp.Open (ConnOracleTemp)
  17. SirSql = " SELECT * INTO " & path_file_excel & “ FROM " & PrjTab & " "
  18.  
  19. Debug.Print SirSql
  20. ConnectOracleTemp.Execute (SirSql)
  21. ConnectOracleTemp.Close
  22.  
  23. End Function
  24.  
but no luck. VB just crashes - VB has encountered a problem and needs to close.
Thank you!
Mar 12 '08 #1
8 4586
debasisdas
8,127 Expert 4TB
The way you are trying is never going to work . Better try to use TOAD for the purpose.
Mar 12 '08 #2
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?
Mar 12 '08 #3
debasisdas
8,127 Expert 4TB
Hi. Thank you for your answer. What is TOAD?
That is a third party tool for oracle .
Mar 12 '08 #4
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!
Mar 12 '08 #5
debasisdas
8,127 Expert 4TB
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.
Mar 12 '08 #6
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!
Mar 12 '08 #7
debasisdas
8,127 Expert 4TB
You can read data from database and write the same to the excel file record by record in a loop..
Mar 12 '08 #8
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_Temp.Fields.Count - 1)
ExcelSheet.Cells(lin_xls, col_xls + 1).Value = RS_Oracle_Temp.Fields(col_xls)
Next
RS_Oracle_Temp.MoveNext
lin_xls = lin_xls + 1
Loop
Mar 13 '08 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Matt | last post by:
I have an ASP page that calls ASP routines that I created that execute a database query and return the results to a recordset. I then iterate through the recordset and display the data in a table....
13
by: BigDaDDY | last post by:
Um yeah....In case you haven't figured it out, Microsoft sucks. I'm going to be kicked back in my chair eating popcorn and watching football 10 years from now, while all you clowns are scrambling...
14
by: bonehead | last post by:
Greetings, I'm using the DoCmd.TransferText method to export the results of a MS Access query to a csv file. The csv will then be used to load an Oracle table. In other systems such as TOAD...
3
by: jelinjose | last post by:
Hi, I have created a table with table name T1 with a particular schema. I have entered the data in an excel sheet in the particular schema above and saved as T2.dmp file. I would like to know...
5
by: harljef | last post by:
I need help with a PHP script code that could export to excel from a Oracle database I am using PHP4
1
by: smaczylo | last post by:
Hello, I've recently been asked to work with Microsoft Access, and while I feel quite comfortable with Excel, I'm at a complete loss with databases. If someone could help me with this issue I'm...
1
by: CoolFactor | last post by:
MY CODE IS NEAR THE BOTTOM I want to export this Access query into Excel using a command button on an Access form in the following way I describe below. Below you will find the simple query I am...
2
hemantbasva
by: hemantbasva | last post by:
Note We need to have a template on server for generating report in multiple sheet as we do not had msoffice on server moreover this require a batch job to delete excel file created by the...
5
by: sumanta123 | last post by:
Dear Sir, How to export/import the excel file in oracle database using sql promt. Please guide me the command(export/import) for the neddful. Thanks in Adavance. Regards Sumanta Panda
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.