Copying data from a datagridivew into Excel

Question

Im using VB.net I am trying to code copying data from a datagridveiw and open a new excel workbook and pasting the data into the sheet starting at cell A1.

So far I have the code to copy the data from the datagridview, open excel but cannot find any way to automatically paste the data with the user pasting the data.

Can anyone help please!

Dim objExcel As Application = New Application
 Dim objWB As Excel.Workbook
 Try
     Me.DataGridView1.SelectAll()
     Clipboard.SetDataObject(Me.DataGridView1.GetClipboardContent())
            objExcel = CreateObject("Excel.Application")
            objExcel.Visible = True
            objWB = objExcel.Workbooks.Open("Test.xls")

            'Code Here

   Catch ex As Exception
    MsgBox(ex.Message, MsgBoxStyle.Exclamation, strTitle)

End Try

Maybe I'm missing the point here but where is your Copy and where is your Paste? — Zac

BachPhi BachPhi · Accepted Answer · 2020-11-27T15:18:40

There is no need to RE-CreateObject since you already declare and use New

        Dim objExcel As Excel.Application = New Excel.Application
    Dim objWB As Excel.Workbook
    Dim objWs As Excel.Worksheet
    Try
        DataGridView1.SelectAll()
        Clipboard.SetDataObject(DataGridView1.GetClipboardContent())
        objWB = objExcel.Workbooks.Add(Type.Missing)
        objExcel.Visible = True
        objWs = CType(objWB.Sheets.Add(Count:=1), Excel.Worksheet)
        objWs = objWB.ActiveSheet
        objWs.Range("A1").Select()
        objWs.Range("A1").PasteSpecial(Excel.XlPasteType.xlPasteAll)

    Catch ex As Exception
        'MsgBox(ex.Message, MsgBoxStyle.Exclamation, strTitle)

    End Try

Copying data from a datagridivew into Excel

2 Answers