Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to copy RS data into excel sheet in VB?

Posted on 2004-08-18
10
Medium Priority
?
352 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
10 Comments
 
LVL 29

Assisted Solution

by:leonstryker
leonstryker earned 200 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 46

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 46

Accepted Solution

by:
aikimark earned 200 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 46

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

730 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