how to save the data in datagrid into excel file

[lwong]

I have a record show in the datagrid, how can I save it to the excel files?Thank you.

[Thread1]

I can think of 2 ways using ADO..

1. Automate excel objects and then use the CopyFromRecordset method.

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.

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.

[lwong]

thank you very much, let me try these

[jdavide]

  set rs = <datagrid's recordset>

i never tested the code as i just created it here though but i hope it will help you.

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.
Please help

Regards

[jdavide]

no help ?

[JonnyPoet]

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 1

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

[George1111]

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)

 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

[jdavide]

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

[JonnyPoet]

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. !!!
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?

[mikelangelotriplex]

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.
Please help

Regards

are you using ado control?
if yes

dim rs as new adodb.recorset
set rs= adodc1

then you can use the code on post 2 copy recordset

[George1111]

Here you go - all coding complete

[JonnyPoet]

set rs = <datagrid's recordset>

Could any one tell me what should i write above syntax...

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 ) 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.
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

[jdavide]

Dear All
Finally i solved my problem by myself. And now it is working pretty cool. Here is the code.

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

Thanks indeedly for helping

[George1111]

Well done Jdavide !!

Thanks for sharing your solution with forum - there's always another way to do something which can be appreciated by others !