Exporting a recordset to Excel

[Harry]

Is it possible to export the entire recordset (table) to an Excel Worksheet?? If anyone knows I would really appreciate the help. Please, provide some sample code. Thank-you very much!!

------------------

[bashfirst]

A very nice way to do this is to use the GetRows method to get an array of your records, then paste the array into a Range object in Excel. No fuss, no muss, and much faster than actually reading the items in...

You may want to look at VBPJ... there was an article about 'Faster Reporting using Excel' that talked about the various ways to get data into Excel. Don't have that issue handy to tell you which month it was.

Good luck.
Bash

[SmithVoice]

MS says it is not possible without automating either Excel of Access but the fact is that what you want is simple with one line of DAO or ADO SQL code.

And, it works for Jet, Excel, Lotus, HTML, CSVText, xBase, Paradox, and Outlook/Exchange ... even lets you convert from any of these to any other.

Take a look at:
http://www.smithvoice.com/vb5expt.htm

and note that it is for vb3-6, VC++ 4-6 and even Delphi via the Jet filter dlls.

You may never go back to WRITE# or any clipboard routines or automation.

-Smith

[LordCallubonn]

This is what i use to export, just when the user hits cancel on the saveas screen ti gets a 1004 error, but I'm looking into that..

Private Sub mnuexport_Click()

Dim d As Database
Dim r As Recordset
Dim q As QueryTable
Set d = OpenDatabase(App.Path & "\Peopledb.mdb")
Set r = d.OpenRecordset("SELECT * FROM Info")


Dim x As New Excel.Application
x.Application.DisplayAlerts = False
x.Visible = False

Dim w As Worksheet
x.Workbooks.Add
Set w = x.Worksheets(1)
Set q = w.QueryTables.Add(r, w.Range("A1"))
q.Refresh (True)

x.Workbooks.Application.SaveWorkspace "c:\Personal.xls"
MsgBox "Saved Production to EXCEL97.", vbInformation, "Personal.xls Saved!!"
'x.SaveChanges = False

x.Quit
Set d = Nothing
Set r = Nothing
On Error GoTo exit2

exit2:
Exit Sub
MsgBox "WE GOOFED UP!!!!", vbInformation, "Personal.xls Not-Saved!!"
End Sub


Hope this helps you, it does me..

------------------
~\*/~ Thank you....

[alvin777]

Hi, try catching the error when you cancel it in the save dialog box

'put this on the line before SaveWorkspace
On Error Goto Err

'put this after Exit2
Err:
Exit Sub

End Sub

[kirankumar]

you can also try this

rs.Open "select * from yourtable", cn, adOpenDynamic, adLockOptimistic
Open "d:\jkk.xls" For Output As #1
If Not rs.EOF Then
Do Until rs.EOF
str = rs(0) & vbTab & rs(1) & vbTab & rs(2)
Print #1, str
rs.MoveNext
Loop
rs.Close
End If
Close #1

[NateBrei]

Also, try saving your file as something other than 'Personal.xls'. That is an Excel reserved filename that is used to save macros (VBA code) that you want available to use on all Excel files. It is automatically generated if you record a macro & choose to save it in the "Personal Macro Workbook".

If you have a Personal.xls, it opens automatically everytime you start Excel. And, you can't have two files open with the same name. That may be the problem.

Nate

[alvin777]

Good morning, I used your code below. I used an Error trap for the bug when you cancel the dialog box but the Excel window is still there (the stripped down Excel window). Is there anyway Excel can be closed when cancelled?

Also why is it when it trasfers dates from the VB recrodset to the Excel cells, the time is included in the dates. Iwish it just transferred the dates without the time to Excel.

Hoping for your kind help,
Alvin


This is what i use to export, just when the user hits cancel on the saveas screen ti gets a 1004 error, but I'm looking into that..

Private Sub mnuexport_Click()

Dim d As Database
Dim r As Recordset
Dim q As QueryTable
Set d = OpenDatabase(App.Path & "\Peopledb.mdb")
Set r = d.OpenRecordset("SELECT * FROM Info")


Dim x As New Excel.Application
x.Application.DisplayAlerts = False
x.Visible = False

Dim w As Worksheet
x.Workbooks.Add
Set w = x.Worksheets(1)
Set q = w.QueryTables.Add(r, w.Range("A1"))
q.Refresh (True)

x.Workbooks.Application.SaveWorkspace "c:\Personal.xls"
MsgBox "Saved Production to EXCEL97.", vbInformation, "Personal.xls Saved!!"
'x.SaveChanges = False

x.Quit
Set d = Nothing
Set r = Nothing
On Error GoTo exit2

exit2:
Exit Sub
MsgBox "WE GOOFED UP!!!!", vbInformation, "Personal.xls Not-Saved!!"
End Sub