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.

Hello World - Your First Android App

Recently I was looking into the option of porting my vb.net application to android. So here is a tutorial on how to write your very first android applications.

What you need:

Java knowledge
Java SDK v6.0 or above
MotoDev (Based on Eclispe) - http://developer.motorola.com/docstools/motodevstudio/download/
Android SDK (latest) - http://developer.android.com/sdk/index.html

I also recommend reading about how the android system works, the manifest file, gui file(main) ,R.java etc.


To start out instal JDK, Android SDK and update, MotoDev studio.
Set up a AVD.  
In motoDev now create a new Project and give it a appropiate name.


You will notice that the an Android application is based on the Activity class, and always reference to R.java.


Change the code in activity to corospond with the following:
import android.widget.TextView;

public void onCreate(Bundle savedInstanceState) 

{
       super.onCreate(savedInstanceState);
         TextView tv = new TextView(this);
       tv.setText("Hello, Android");
       setContentView(tv);
   }

The enlarged code is the code changed or added.

The onCreate() method is used to start your activity.

In android objects is referred to views. 

TextView will be the equvalint to an Label in .net programming or swing.

Now Run the application.

Dynamic Create Controls on Runtime

Here is my quick guide on how to generate ImageHolder Buttons on runtime. This is perfect and works nice with inventory systems or payroll applications.(ID picture of employee)

First of all create a new vb.net project, drag a panel onto your form.
Double click on your form to open up the Form_Load() method and Return to design view.

Paste Excel Data Into DataGrid in VB.net

How to paste excel clipboard data in datagrid:

Private Sub PasteClipboard()

        Dim s As String

        Try

      

            s = Clipboard.GetText()

            Dim i, ii As Integer

            Dim tArr() As String = s.Split(ControlChars.NewLine)

            Dim arT() As String

            Dim cc, iRow, iCol As Integer

            iRow = DataGridView1.SelectedCells(0).RowIndex

            iCol = DataGridView1.SelectedCells(0).ColumnIndex

            For i = 0 To tArr.Length - 1

                If tArr(i) <> "" Then

                    arT = tArr(i).Split(vbTab)

                    cc = iCol

                    For ii = 0 To arT.Length - 1

                       If cc > DataGridView1.ColumnCount - 1 Then Exit For

                       If iRow > DataGridView1.Rows.Count - 1 Then Exit Sub

                        With DataGridView1.Item(cc, iRow)

                            .Value = arT(ii).TrimStart

                        End With

                        cc = cc + 1

                    Next

                    iRow = iRow + 1

                End If

            Next

        Catch ex As Exception

            MsgBox("Please redo Copy and Click on cell")

        End Try

    End Sub

Just change the value of DataGridView1 to the objectname of your DataGrid. This can paste rows and columns.