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?

Improve company productivity with a Business Account.Sign Up

x
 
aikimarkConnect With a Mentor Commented:
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
 
leonstrykerConnect With a Mentor Commented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
 
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
 
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
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.

All Courses

From novice to tech pro — start learning today.