Datagrid Value to Excel

[__wired__]

this is my last problem in my program... hehehehe.. can someone help me on this. thanks

[__wired__]

BTW.. the data's in datagrid is not all from database/recordset.. i have 2 columns coming from a textbox.. is it possible to move all the data's in my datagrid to an excel sheet?

[wysiwyg327]

Try to use this as a reference :

Private Sub TransFer_Xls()

Dim xlApp As Excel.Application
Dim xlsource As Excel.Workbook
Dim wkSheetSrc As Excel.Worksheet
Dim cellCtr As Long
Dim rst As ADODB.Recordset
Dim FileXls As String
Set rst = New ADODB.Recordset

rst.CursorLocation = adUseClient


If Not xlApp Is Nothing Then Set xlApp = Nothing

Set xlApp = New Excel.Application
Set xlsource = xlApp.Workbooks.Open(App.Path & "\excel_file.xls")
Set wkSheetSrc = xlsource.Worksheets(1)


'use this if you want to save the xls to another filename
FileXls = App.Path & "\file.xls"
xlApp.ActiveWorkbook.SaveAs FileName
'end
xlApp.DisplayAlerts = False
xlApp.Visible = True

'sql query
strSql = "Select * From......"

rst.Open strSql, cnn, 3, 3

On Error Resume Next
If Not rst.EOF Then
    Do While Not rst.EOF
        With wkSheetSrc
            .Cells(cellCtr, 1).Value = rst!FieldTest 'sample field
        End With
        rst.MoveNext
        cellCtr = cellCtr + 1
    DoEvents
    Loop
End If

xlApp.ActiveWorkbook.Save
xlApp.ActiveWorkbook.Close
xlApp.Quit
Set xlApp = Nothing

End Sub

[__wired__]

thanks for the reply.. i found a code for mshflex then i apply in datagrid.. i got 1 error.. hope someone can figure it out...

Dim xlObject    As Excel.Application
 
      Dim xlWB        As Excel.Workbook
 
          Set xlObject = New Excel.Application
 
          'This Adds a new woorkbook, you could open the workbook from file also
 
          Set xlWB = xlObject.Workbooks.Add
 
          Clipboard.Clear 'Clear the Clipboard
 
          With DataGrid1
 
              'Select Full Contents (You could also select partial content)
 
              .SelStartCol = 0
              .SelEndCol = 4
          '==================== i got error on this =================='
              Clipboard.SetText .SelText  'Send to Clipboard          '
          '==================== i got error on this =================='
          End With
 
          With xlObject.ActiveWorkbook.ActiveSheet
 
              .Range("A1").Select 'Select Cell A1 (will paste from here, to different cells)
            
              .Paste              'Paste clipboard contents
 
          End With
  
          ' This makes Excel visible
 
          xlObject.Visible = True

original code is >>> Clipboard.SetText .Clip

[deathfxu]

I think it is throwing up the error because it needs to be saved into the clipboard as an object, not as text. Replace the error line with:

SendKeys "^c"

Then see if it works.

[__wired__]

i replace the error line with your code.. i got error "Paste method of Worksheet class failed"

here's the error code:

.Paste             'Paste clipboard contents

[__wired__]

i replace the this error line:

.Paste             'Paste clipboard contents

with this one:

SendKeys "^v"

i got empty excel sheet..