How to copy RS data into excel sheet in VB?

Hi:
I am working with VB6 and have created an excel.application object. I also have RS from SQL. Now I want to copy the RS into my excell sheet and save. I also have the save part working. Just need the code to move the data from excel to SQL.
Dhamija
dhamijapAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

leonstrykerCommented:
As long as you have you are working with Excel version 2000 or higher, all you need to do is use the CopyFromRecordset method of a range object:


Set myWorkbook = xl.Workbooks.Add
Set mySheet = myWorkbook.Sheets(0)

mySheet.Range("A1").CopyFromRecordset rsRecSet

Leon
0
dhamijapAuthor Commented:
Ok, I got that how do I get colum headers also in it. Like the first row of excel sheet will be column names of rs? How ever with the msdn help I am using the following and it worked. Tell me if your way or this way is the same

       Set oXlApp = CreateObject("Excel.Application")
        oXlApp.SheetsInNewWorkbook = 1
        oXlApp.Workbooks.ADD
        oXlApp.Application.Workbooks(1).SaveAs FileName:=sErrorFile
       
        'excel_app.Visible = True
        oXlApp.Workbooks.Open sErrorFile   'txtExcelFile.Text        
        Set oXlSht = oXlApp.ActiveSheet
       
        ' Use the Recordset to fill the table.
        oXlSht.Cells.CopyFromRecordset oRS
        oXlSht.Cells.Columns.AutoFit
       
        ' Save the workbook.
        oXlApp.ActiveWorkbook.Save

0
leonstrykerCommented:
The two are eaxctly the same, with the difference is They are using the Cells range object and I am using a particular cell as teh top left point for your data.

Leon
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

aikimarkCommented:
Alternative1:
You might define the Excel worksheet as an ADO database table.  You could loop through the recordset appending to the Excel table.

Alternative 2:
If your recordset resides in either MSAccess or MSDE, then you can attach the Excel worksheet to the database and append the data with a single Insert Into... Select... statement.
0
dhamijapAuthor Commented:
aikimark:
I have already done this but if you give me sample code for you alternate 2 I will increase the points to 100 award you 50 as well as this would increase my knowledge. All you have to do is send me sample code which I can test it quickly.
dhamija
0
aikimarkCommented:
Alternative 2 example:
1. In MSAccess, do the following:
File | Get External Data | Link Table

2. In the subsequent dialog box select Excel as the file type and then select the workbook and worksheet you want to export to.

3. In MSAccess, create a query (using the query design wizard) that appends data from your MSAccess table to the attached (Excel) table.

4. In your code, point your command object to the new append query (3) and execute it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dhamijapAuthor Commented:
Points raised to 100
 aikimark:
That is good to know. But my code is all dynamic in VB and Access db can change and so is the excell file. But I think it is a good solution as well so I will accept your solution as alternative solution as well. thanks.
0
dhamijapAuthor Commented:
oops I did it wrong. Accepted answer and assisted answer got switched. Sorry about that.
Dhamijap
0
aikimarkCommented:
Thanks for the points.

1. It is possible to programmatically attach/detach an Excel spreadsheet to an MSAccess database.

2. It is possible to use the IN sub-clause in MSAccess/Jet SQL that will point to an external data source.  This IN sub-clause usually appears in the FROM clause.
0
dhamijapAuthor Commented:
aikimark:
ohh. That is interesting. Now I am looking at another problem which I have posted and I am sure you can help me. Hre is the link:
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/VB_Databases/Q_21103847.html
dhamijap
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.