Simple Guide how to automate copying cells or more from excel to other workbooks or another sheet using vb.net.
Here is the code I used, I had the following controls on my form:
2 Radio Buttons
3 Labels
2 Text Boxes
1 Button
Just change the names of the controls in the coding to the names you used!
'we need to import the Excel interop to reference to excel
Imports Excel = Microsoft.Office.Interop.Excel
Imports System.IO
Public Class Form1
Private Sub btnOk_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOk.Click
Copy()
End Sub
Dim XLapp As Excel.Application
Dim XLwrkb As Excel.Workbook
Dim XLsheet As Excel.Worksheet
Dim path As String
Public Sub Copy()
OpenFileDialog1.ShowDialog()
Path = OpenFileDialog1.FileName ' Assigns the path of file chosen by user to the string path
'Open a new excel application
XLapp = New Excel.Application
XLapp.Visible = False 'we don't want the user to know excel is being opened!
'Open the workbok in our excel app
XLwrkb = XLapp.Workbooks.Open(path, [ReadOnly]:=False)
XLsheet = XLwrkb.ActiveSheet ' Reads the first sheet of the workbook
If RadioButton2.Checked = True Then
XLsheet.Range(txtCopy.Text).Copy()
XLsheet.Range(txtPaste.Text).PasteSpecial()
XLwrkb.Save()
XLapp.Quit()
ElseIf RadioButton1.Checked = True Then
'The program will check if the user want to paste the range into a new workbook. It then ask the user to select
' the new workbook it want to paste the cellc in.
OpenFileDialog1.ShowDialog()
path = OpenFileDialog1.FileName
XLsheet.Range(txtCopy.Text).Copy()
'Open a new excel application
XLapp = New Excel.Application
XLapp.Visible = False 'we don't want the user to know excel is being opened!
'Open the workbok in our excel app
XLwrkb = XLapp.Workbooks.Open(path, [ReadOnly]:=False)
XLsheet = XLwrkb.ActiveSheet ' Reads the first sheet of the workbook
XLsheet.Range(txtPaste.Text).PasteSpecial()
XLsheet.Range(txtPaste.Text).PasteSpecial()
XLwrkb.Save()
XLapp.Quit()
End If
End Sub
