开发者

How to copy a cell or a range of cells from one Excel spreadsheet to another Excel Spreadsheet with VS 2008 VB

I have created a GUI (the easy part) in VS 2008 VB that has two text boxes (one to select the target Excel file, and the other to select the destination Excel file). They both are accompanied by a browse button (uses OpenDialog to browse files select - ReadOnly). Additionally, I have two more text boxes which I have re-sized to midgets, to accommodate row/s and column/s (if a user wanted to copy cells 1-6 and columns A -D, they would simply enter 1-6 in the first text box, and A-D in the second text box). Lastly, I have a copy button at the bottom of the GUI, to be pressed once all fields have relevant data, as to copy the selected cell/s. I'm befuddled on how to copy a cell or a range of cells, by prompting the user for a number and a column and how to actually implement the logic of copying the cells. I've seen examples, but I have not successfully been able to apply them to my particular application. Any help is appreciated ... Thanks!

VS 2008 VB Code:

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub

    Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged


    End Sub

    Private Sub OpenFileDialog1_FileOk(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles OpenFileDialog1.FileOk
        Dim strm As System.IO.Stream
        strm = OpenFileDialog1.OpenFile()
        TextBox1.Text = OpenFileDialog1.FileName.ToString()
        If Not (strm Is Nothing) Then
            strm.Close()
        End If


    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim selectedFile As String = String.Empty
        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
            selectedFile = OpenFileDialog1.FileName
        End If
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim selectedFile As String = String.Empty
        If OpenFileDialog2.ShowDialog = Windows.Forms.DialogResult.OK Then
            selectedFile = OpenFileDialog2.FileName
        End If
    End Sub

    Private Sub TextBox2_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox2.TextChanged

    End Sub

    Private Sub OpenFileDialog2_FileOk(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles OpenFileDialog2.FileOk
        Dim strm As System.IO.Stream
        strm = OpenFileDialog2.OpenFile()
        TextBox2.Text = OpenFileDialog2.FileName.ToString()
    开发者_StackOverflow    If Not (strm Is Nothing) Then
            strm.Close()
        End If
    End Sub

        Private Sub CopyButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Copy_Btn.Click

    End Sub
End Class


If I understand correctly, you're mostly interested in how to take the row range input (e.g. "1-5") and the column range input (e.g. "A-C") and copy the range those inputs define into another sheet (or workbook?). I pulled together some VBA to show how you can do this. I didn't take the time to build in error handling or input validation, but for properly formatted input, this approach will work. Beware that I have this sample code set up to copy from sheet 1 of your active workbook and paste into sheet 2 of the active workbook.

Sub promptncopy()

Dim rowStr As String, colStr As String, tmpAdd As String
Dim hypr As Integer, hypc As Integer
Dim sIn As Worksheet, sOut As Worksheet
Dim rIn As Range, rOut As Range

'Input boxes for row and column ranges.  It sounds like you already have some code like this.
rowStr = InputBox("Enter row numbers you want to copy as range (e.g. 1-5).", "Enter Rows")
If rowStr = "" Then
    Exit Sub
End If
colStr = InputBox("Enter column letters you want to copy as range (e.g. A-E).", "Enter Columns")
If colStr = "" Then
    Exit Sub
End If

'Records position of hyphen in input strings.
hypr = InStr(rowStr, "-")
hypc = InStr(colStr, "-")

'Combines colStr and rowStr to form appropriate address for input range.
'Handles cases where colStr and/or rowStr are single values rather than ranges.
If hypc <> 0 Then
    If hypr <> 0 Then
        tmpAdd = Left(colStr, hypc - 1) & Left(rowStr, hypr - 1) & ":" & Right(colStr, Len(colStr) - hypc) & Right(rowStr, Len(rowStr) - hypr)
    Else
        tmpAdd = Left(colStr, hypc - 1) & rowStr & ":" & Right(colStr, Len(colStr) - hypc) & rowStr
    End If
Else
    If hypr <> 0 Then
        tmpAdd = colStr & Left(rowStr, hypr - 1) & ":" & colStr & Right(rowStr, Len(rowStr) - hypr)
    Else
        tmpAdd = colStr & rowStr
    End If
End If

'Sets In and Out sheets to first and second sheets in workbook, respectively.
Set sIn = Sheets(1)
Set sOut = Sheets(2)
Set rIn = sIn.Range(tmpAdd)
Set rOut = sOut.Range("A1").Resize(rIn.Rows.Count, rIn.Columns.Count)
'Prints values (not formulas!) from input range to output range.
rOut = rIn.Value

End Sub
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜