3 Replies - 1388 Views - Last Post: 23 August 2013 - 08:39 AM Rate Topic: -----

#1 Kolkata2013  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 22-August 13

How to do Excel Right and Control Down in VB.Net

Posted 22 August 2013 - 10:52 PM

I have two excelsheets Sheet1 and Sheet2 both in the same excel workbook. In sheet1 I have commdity in Cell A2, customer Desc in cell B2, Name Alex in C2, Name David in D2, Name Anna in E2 and the Total at the last column.I want to copy Column A, Column B and the column with total to sheet2 from VB.Net.
While in VBA I used below codes to copy data from sheet1 to sheet2 and it worked fine.
Attached Image
Sub test()
    Sheets("Sheet1").Select
    Range("A2:B4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A2").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("A2").Select
    Selection.End(xlToRight).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("C2").Select
    ActiveSheet.Paste
End Sub


The above code is of VBA. In VB.Net I am using this code to copy data from sheet1 to sheet2 as below.

Private Sub Button1_Click

    Dim xlApp As New Excel.Application
    Dim xlwb As Excel.Workbook
    Dim xlsheet, xlsheet2 As Excel.Worksheet
    Dim lCol As Long = 0
    Dim xlsourceRange, xlDestRange As Excel.Range
    with xlApp
        .visible = True
        xlwb = .workbooks.open("D:\test.xlsx")
        xlsheet = xlwb.Sheets("Sheet1")
        xlsheet2 = xlwb.Sheets("Sheet2")
        xlSourceRange = xlSheet.Range("A2:B2000")'This is copies my Column A and Column B
        xlDestRange = xlsheet2.Range("A2")
        xlSourceRange.Copy(xlDestRange)

    End with
End Sub



The real problem is to copy the total which is in column F. I am not able copy it to sheet2 because data is not fixed in sheet1 it is incremental towards the right. I mean in present senario I have three customer from Column C to E and the total is in column F. But if there is an addition of two more customer the Total column moves from column F to column H.

I am just worndring how to convert this VBA code to VB.Net
    Range("A2").Select
    Selection.End(xlToRight).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("C2").Select
    ActiveSheet.Paste


I have uploaded the excel..Any help in this is really appriciated.

This post has been edited by andrewsw: 23 August 2013 - 01:36 AM
Reason for edit:: Please use CODE tags, and indent the code


Is This A Good Question/Topic? 0
  • +

Replies To: How to do Excel Right and Control Down in VB.Net

#2 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3371
  • View blog
  • Posts: 11,420
  • Joined: 12-December 12

Re: How to do Excel Right and Control Down in VB.Net

Posted 23 August 2013 - 01:43 AM

Click into A2 and press Ctrl-A. If this is the area you wish to copy then you can use

Range("A2").CurrentRegion.Copy

possibly just deleting the first row.

Otherwise,

Range(Range("A2"), Range("A2").End(xlToRight).End(xlDown)).Copy

In VB.NET you need to use the fully-qualified enumeration for xlToRight and xlDown, or find their numerical values. When you type this in VB.NET the enumerated values are likely to appear in auto-complete for you.

This post has been edited by andrewsw: 23 August 2013 - 01:44 AM

Was This Post Helpful? 0
  • +
  • -

#3 Kolkata2013  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 22-August 13

Re: How to do Excel Right and Control Down in VB.Net

Posted 23 August 2013 - 07:15 AM

Hi Andrew,
Thanks a lot for the answer. My main aim is to copy the last column from sheet1 to sheet2 trough VB.Net programme. The VBA code above do the copy and pasting last column. Since there is no option of .Select or .selection in VB.Net. Don't know what to code to copy the Last column from Excel Sheet1 to Sheet2.

This post has been edited by andrewsw: 23 August 2013 - 08:35 AM
Reason for edit:: Removed unnecessary quote

Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3371
  • View blog
  • Posts: 11,420
  • Joined: 12-December 12

Re: How to do Excel Right and Control Down in VB.Net

Posted 23 August 2013 - 08:39 AM

You can select something using Select() but code can be written without selecting anything (if you know enough VBA).
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1