Solved

How to copy RS data into excel sheet in VB?

Posted on 2004-08-18
10
316 Views
Last Modified: 2013-12-25
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
0
Comment
Question by:dhamijap
  • 5
  • 3
  • 2
10 Comments
 
LVL 29

Assisted Solution

by:leonstryker
leonstryker earned 50 total points
ID: 11830047
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
 

Author Comment

by:dhamijap
ID: 11830103
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
 
LVL 29

Expert Comment

by:leonstryker
ID: 11830535
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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
LVL 45

Expert Comment

by:aikimark
ID: 11862418
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
 

Author Comment

by:dhamijap
ID: 11869867
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
 
LVL 45

Accepted Solution

by:
aikimark earned 50 total points
ID: 11870474
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
 

Author Comment

by:dhamijap
ID: 11870881
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
 

Author Comment

by:dhamijap
ID: 11870900
oops I did it wrong. Accepted answer and assisted answer got switched. Sorry about that.
Dhamijap
0
 
LVL 45

Expert Comment

by:aikimark
ID: 11871402
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
 

Author Comment

by:dhamijap
ID: 11871609
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

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question