I have a record show in the datagrid, how can I save it to the excel files?Thank you.
Printable View
I have a record show in the datagrid, how can I save it to the excel files?Thank you.
I can think of 2 ways using ADO..
1. Automate excel objects and then use the CopyFromRecordset method.
Code:Dim xlsApp As Excel.Application
Dim xlsWbk As Excel.Workbook
Dim xlsWsh As Excel.Worksheet
Set xlsApp = New Excel.Application
xlsApp.Visible = True
xlsApp.UserControl = False
Set xlsWbk = xlsApp.Workbooks.Add("<template xls file>")
Set xlsWsh = xlsWbk.Worksheets(1)
xlsWsh.Range("A1").CopyFromRecordset <datagrid's recordset object>
Set xlsWsh = Nothing
On Local Error Resume Next
xlsWbk.SaveAs "<output xls file>"
On Local Error GoTo 0
xlsWbk.Close False
Set xlsWbk = Nothing
xlsApp.Quit
Set xlsApp = Nothing
2. Create an excel using ADO (Jet OLEDB) and then transfer the data by records.
Code:Dim cn As ADODB.Connection
Dim rs as adodb.recordset
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=<output xls file>; " & _
"Extended Properties=""Excel 8.0; HDR=Yes;"""
cn.Execute "<create table sql statement with the same structure as the datagrid's recordset>", ,
adCmdText + adExecuteNoRecords
'create table sql sample
'CREATE TABLE Sheet1 (field1 text, field2 text,.. fieldN text)
cn.BeginTrans
set rs = <datagrid's recordset>
rs.movefirst
do until rs.eof
cn.execute "insert into [sheet1$] values ('" & rs.collect("field1") & "', '" & _
rs.collect("field2") & "', '" & _
rs.collect("fieldN") & "')" _
, , adCmdText + adExecuteNoRecords
loop
cn.commitrans
cn.close
set cn = nothing
i never tested the code as i just created it here though but i hope it will help you.
thank you very much, let me try these
Could any one tell me what should i write above syntax. I want to do same thing but i have no any idea what should to write.Quote:
Originally Posted by Thread1
Please help
Regards
no help ?
The normal way is to open excel as an excel document and the transfering dat by writing them into the actual worksheet. If you need excel examples use excel as a keeyword and you'll find some examples in my former posts. IMHO Excel is no database, so I would not access it with normal ADODB commands. So maybe use his solution no 1Quote:
Originally Posted by jdavide
Adsditional here are some links. Some of them have zip files with small examples which I did in this former posts. Maybe they help you to start
http://www.codeguru.com/forum/showth...ighlight=Excel
http://www.codeguru.com/forum/showth...ighlight=Excel
http://www.codeguru.com/forum/showth...ighlight=Excel
http://www.codeguru.com/forum/showth...ighlight=Excel
Jonny Poet
This may also be useful
MSFLEXGRID REPORTING USING EXCEL
You need to create a REFERENCE to Excel in the Project
(You will need Excel on the Generating Machine - Excel or Excel Reader on the viewing machines)
Code:Public Sub FlexGrid_To_Excel(TheFlexgrid As MSFlexGrid, _
TheRows As Integer, TheCols As Integer, _
Optional GridStyle As Integer = 1, Optional WorkSheetName _
As String)
Dim objXL As New Excel.Application
Dim wbXL As New Excel.Workbook
Dim wsXL As New Excel.Worksheet
Dim intRow As Integer ' counter
Dim intCol As Integer ' counter
If Not IsObject(objXL) Then
MsgBox "You need Microsoft Excel to use this function", _
vbExclamation, "Print to Excel"
Exit Sub
End If
'On Error Resume Next is necessary because
'someone may pass more rows
'or columns than the flexgrid has
'you can instead check for this,
'or rewrite the function so that
'it exports all non-fixed cells
'to Excel
On Error Resume Next
' open Excel
objXL.Visible = True
Set wbXL = objXL.Workbooks.Add
Set wsXL = objXL.ActiveSheet
' name the worksheet
With wsXL
If Not WorkSheetName = "" Then
.Name = WorkSheetName
End If
End With
' fill worksheet
For intRow = 1 To TheRows
For intCol = 1 To TheCols
With TheFlexgrid
wsXL.Cells(intRow, intCol).Value = _
.TextMatrix(intRow - 1, intCol - 1) & " "
End With
Next
Next
' format the look
For intCol = 1 To TheCols
wsXL.Columns(intCol).AutoFit
'wsXL.Columns(intCol).AutoFormat (1)
wsXL.Range("a1", Right(wsXL.Columns(TheCols).AddressLocal, _
1) & TheRows).AutoFormat GridStyle
Next
End Sub
JonnyPoet and George i really appreciate your reply, but it seems these are not solve my problem.
I mean all i want is Click button and then save all datagrid into excel file thats all. Any other idea ?
Regards
As easy your idea seems to be, there is nothing which does it on one shot, so you need to study how to open excel and doing a loop to write all datainto a excel sheets rows / cols What we have given you very well solves your problem, but we are not a free of costs ordering office, whre you will ask and get the full solution done. we are showing you a way - you have to go it - not we, definitly you. !!! :DQuote:
Originally Posted by jdavide
If problems are coming up with your code then we will show you how to fix, but the thing you want to do is per sure not a single line like a magic spell, its work to do and it needs to be coded by hand. So reread what is gven to you and try the examples. Then sit down and do it !!
If questios or problems with your code feel free to ask. Also if there are questions with the givn examples.
---- edited ---
I just looked at George's applicationpart Is at least 80 % of your code you need, if not nearly all. Maybe it only needs to be adapted to your needs. So what are you moaning about in this case? :(
are you using ado control?Quote:
Originally Posted by jdavide
if yes
Quote:
dim rs as new adodb.recorset
set rs= adodc1
then you can use the code on post 2 copy recordset
Here you go - all coding complete
Thats simple the same recordset with which you fill your datagrid. So if your source is an ADODC element then this is the same source If you are using DE ( dataenviromentobjects its also the same but then you have to decide which table of your DE you are using. So this needs to see your code to decide what to be used here. So if you cannot decide yourself then show up your code. But the standard way to use Excel is Excel.Application object as George1111 shows in his example. You can do formatting and all whats needed in this case. Using ADO in my for working with Excel in my eyes is 'walking on crutches'. But maybe this is only my personal opinion about that. As I'm not using a shoehorn to stir the soup even in german both are 'Spoons' ( german word for shoehorn translated word by word is shoe-spoon ) I would also not use ADO for handling Excel even I know it is possible. ( Its also possible to use a new shoehorn for stiring soup :lol: ) Excel is not a database it includes lots of classes which have properties and methods easy to handle using the correct tool - excel application objects.Quote:
Originally Posted by jdavide
Using ADO especially for mathematical data which need some special formatting maybe to correctly adding them, can easily lead to a disaster. I've just read another post which was comimg up with the ADO issue in the beginning and the next question was how to set currency format using ADO -- ?? IMHO Thats all Excel stuff. Not ADO
Greetings from Vienna :wave:
Dear All
Finally i solved my problem by myself. And now it is working pretty cool. Here is the code.
Thanks indeedly for helpingCode:Private Sub MnSave_Click()
Call SaveDataGrdIntoExcel(Rs)
End Sub
Public Sub SaveDataGrdIntoExcel(ByRef Rs As ADODB.Recordset)
Dim Cn As ADODB.Connection
Dim CertainDate As String
Dim Day, Month, Year As String
Dim DataGrd As DataGrid
Day = Mid(Date, 1, 2)
Month = Mid(Date, 4, 2)
Year = Mid(Date, 7)
CertainDate = "" & Day & "_" & Month & "_" & Year & ""
Set Cn = New ADODB.Connection
Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & "Data Source=" & CStr(CeratinDate) & "; " & _
"Extended Properties=""Excel 8.0; HDR=Yes;"""
Cn.Execute "CREATE TABLE " & CStr(CertainDate) & " (Name text, LDate text, Prize text, Quantity text, Memo text)", , adCmdText
Cn.BeginTrans
Rs.MoveFirst
Do Until Rs.EOF
Cn.Execute "insert into " & CStr(CeratinDate) & " values ('" & Rs.Collect("Name") & "', '" & Rs.Collect("LDate") & "', '" & Rs.Collect("Prize") & "', '" & Rs.Collect("Quantity") & "', '" & Rs.Collect("Memo") & "')", , adCmdText + adExecuteNoRecords
Rs.MoveNext
Loop
Set Cn = Nothing
End Sub
Well done Jdavide !!
Thanks for sharing your solution with forum - there's always another way to do something which can be appreciated by others !