Export recordset to Excel file 2

[JaxtheDog]

I have a VB6 app connected to an Oracle 9i database. I have several queries which return large recordsets which I would like to save as an .XLS file. Does anyone know of a utility or module I can use to do this? I load the recordset into a flexgrid; can it be saved fromthere. I have experimented with writing one myself and it's cumbersome. I'd appreciate any advice you all could offer

Thanks,
Jax

 

[Swi]

Why not just put the query results directly into the excel spreadsheet:

***** Note that this example uses Access

Private Sub Command1_Click()
Dim cnSrc As New ADODB.Connection
Dim num_copied As Long
Screen.MousePointer = vbHourglass
DoEvents
cnSrc.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "J:\Palletags\Palletags.mdb" & _
";"
cnSrc.Execute "SELECT * INTO [Excel 8.0;" & _
"Database=C:\Test.xls].[Sheet1] FROM " & _
"TableName", num_copied
cnSrc.Close
Set cnSrc = Nothing
Screen.MousePointer = vbDefault
MsgBox "Copied " & num_copied & " records."
End Sub

Swi

 

[JaxtheDog]

Swi,
Thank you for the reply, I'll try your suggestion
Jax

 

[snehakevin]

You can try this also.....if you want....
'Code starts

Dim a As Object
Dim w As Object
Dim WSN As Object
Dim q As Object
Set a = CreateObject("Excel.Application&quot
Set w = a.Workbooks.Add()
Set WSN = w.Worksheets(1)
WSN.Activate
WSN.Range("a1&quot.Select
WSN.Range("a1&quot.Font.Color = vbBlue
WSN.Range("a1&quot.Font.Size = 16
'below use your recordset from oracle
your_rs_from_oracle9i.MoveFirst

Set q = WSN.QueryTables.Add(your_rs_from_oracle9i,WSN.Range("a1&quot)
q.BackgroundQuery = False
Set q.Recordset = your_rs_from_oracle9i
q.Refresh
a.Visible = True
Set WSN = Nothing
Set w = Nothing
Set a = Nothing
'Code ends
Note if the record set contains more than 65366 rows then excel will give error....

 

[CCLINT]

>CreateObject("Excel.Application&quot

You need a licensed version of MS Excel on the client in order to use this.

 

[JaxtheDog]

Thanks for the replies. I cannot get Swi's to work with Oracle. I did not succeed with snehakevin's because I do not have the QueryTable function installed on my machine.

I show the code I am using below. Its a pretty blunt force way of doing it. I have one big problem with it (besides speed). The first time it runs, I can see the spreadsheet as it fills. Then next time, I get only the Excel top bar and cell editor. When it asks me to save on quit the second time, I cannot see what I am saving. I assume there is a memory problem, but I think I am unloading Excel, so I don't understand why the difference in runtime actions exists. Any suggestions?

Code:
Private Sub cmdExport_Click()
ml_cmd.CommandText = "select * from facility_profile"
Set mrsFacility = ml_cmd.Execute
mrsFacility.MoveFirst

Dim i As Integer
Dim j As Integer

Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object

On Error GoTo NeedToLoadExcel

Set xlApp = Excel.Application 'use Excel in memory if already loaded

GoTo ExcelAlreadyLoaded

NeedToLoadExcel:
Set xlApp = New Excel.Application 'use NEW keyword to load Excel if not loaded

ExcelAlreadyLoaded:

On Error GoTo DoneExcel

If xlApp.Visible = False Then xlApp.Visible = True
i = xlApp.Workbooks.Count
If i = 0 Then
Set xlBook = xlApp.Workbooks.Add
End If


'get rid of all but one worksheet
xlApp.DisplayAlerts = False
If xlApp.Visible = False Then xlApp.Visible = True
i = xlBook.Worksheets.Count
For i = xlBook.Worksheets.Count To 2 Step -1
xlBook.Worksheets(i).Delete
Next i
xlApp.DisplayAlerts = True

Set xlSheet = xlBook.Worksheets(1)
xlSheet.Name = "Facility Profile"

With xlSheet

For i = 1 To mrsFacility.Fields.Count
With .Cells(1, i)
.Formula = mrsFacility.Fields(i - 1).Name
.Font.Bold = True
End With
Next i

End With

i = 1
Do Until mrsFacility.EOF = True
i = i + 1
For j = 1 To mrsFacility.Fields.Count
xlSheet.Cells(i, j).Formula = mrsFacility.Fields(j - 1).Value
Next j
mrsFacility.MoveNext
Loop

DoneExcel:
xlApp.Quit
Set xlApp = Nothing
Set xlBook = Nothing
Set xlSheet = Nothing

End Sub

Thanks,
Jax

 

[Swi]

Try this link for connection string examples:

http://www.able-consulting.com/ADO_Conn.htm

Swi

 

[prachisharma]

hi,

The code that swi has written owrks but any idea how would be export the queries with some conditions.

thanks,

Prachi.

 

[Swi]

Hope this is what you mean.
Add a WHERE clause to the end of the SQL statement:

Ex.-

cnSrc.Execute "SELECT * INTO [Excel 8.0;" & _
"Database=C:\Test.xls].[Sheet1] FROM " & _
"TableName" & "WHERE id_number = '99999', num_copied

Swi

 

[JaxtheDog]

Swi,
I have tried this code with an access database, and it was very helpful. However, I cannot get it to work with Oracle. I have looked at the links suggested, but get syntax errors on the "select into". Do you know of any way to select from oracle and output into Excel? I'd appreciate any help you can offer. I have given you a star for the help so far since it is greatly appreciated!

Thanks,
Jax

 

[SBendBuckeye]

Something in the back of my mind says that Select Into is a Microsoft extension of SQL. Check your Oracle documentation to see if that is a valid SQL statement. Sorry if my memory is failing me!

Have a great day!

j2consulting@yahoo.com

 

[JaxtheDog]

SBendBuckeye,
You are correct, thanks for the tip. I'll look into other Oracle to Excel options!

Jax

 

[SBendBuckeye]

Worst case scenario you could possibly export to Access and then from Access to Excel. Good Luck!

Have a great day!

j2consulting@yahoo.com