dhamijap
asked on
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Leon
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.
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.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
oops I did it wrong. Accepted answer and assisted answer got switched. Sorry about that.
Dhamijap
Dhamijap
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.
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.
ASKER
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:
https://www.experts-exchange.com/questions/21103847/Access-to-Sql-table-bulk-copy.html
dhamijap
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:
https://www.experts-exchange.com/questions/21103847/Access-to-Sql-table-bulk-copy.html
dhamijap
ASKER
Set oXlApp = CreateObject("Excel.Applic
oXlApp.SheetsInNewWorkbook
oXlApp.Workbooks.ADD
oXlApp.Application.Workboo
'excel_app.Visible = True
oXlApp.Workbooks.Open sErrorFile 'txtExcelFile.Text
Set oXlSht = oXlApp.ActiveSheet
' Use the Recordset to fill the table.
oXlSht.Cells.CopyFromRecor
oXlSht.Cells.Columns.AutoF
' Save the workbook.
oXlApp.ActiveWorkbook.Save