Solved

How to copy RS data into excel sheet in VB?

Posted on 2004-08-18
10
296 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now