Automate Excel Copy

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

Data Validation Class

After having several problems regarding null I have decided to do the right thing and create a Data Validation Class. What this class does is check each data before I use it for null.

If this is the first time you hear about null there is some basic things to learn:

• A null value is a value that doesn't refer to any object. It's the default value of reference-type variables (e.g., class, interface, and delegate).
• The null keyword is a literal that represents a null reference with the .NET environment. 
• There is a difference between null and '0' the value.
• Null is nothing where '0' is assigned as a value. 

To start out create a new class in your project, in my case my class is called DataVal.vb

Working with a database I prefered to assign '0' to all my null values if they are numeric.

I later on run a query that searches my tables for the '0' records and then remove them.

"DELETE * From Table WHERE Field = 0 "

This however depends on the function of your tables and program.

This is the code used in my class:

Public Class DataVal
    Public Function Validate(ByVal input)
        If input = Nothing Then
            input = "0"
            Return input
        End If
        Return input
    End Function
End Class

DV = DataValidate class....

Use this function to set the value of your object, eg. txtName.Text = (DV.Validate(txtName.Text))

It works for integers, doubles basically all data types.

Please comment your solution to validate data.