如何使用 VS 2008 VB 将一个单元格或一系列单元格从一个 Excel 电子表格复制到另一个 Excel 电子表格

Question

我在 VS 2008 VB 中创建了一个 GUI（简单的部分），它有两个文本框（一个用于选择目标 Excel 文件，另一个用于选择目标 Excel 文件）。它们都附带一个浏览按钮（使用 OpenDialog 浏览文件选择 - 只读）。此外，我还有两个文本框，我已将其大小调整为侏儒，以容纳行和列（如果用户想要复制单元格 1-6 和列 A -D，他们只需输入 1-6在第一个文本框中，AD 在第二个文本框中）。最后，我在 GUI 底部有一个复制按钮，当所有字段都有相关数据时按下该按钮，以复制选定的单元格。我对如何通过提示用户输入数字和列来复制单元格或单元格范围以及如何实际实现复制单元格的逻辑感到困惑。我看过一些例子，但我还没有成功地将它们应用到我的特定应用程序中。感谢任何帮助...谢谢！

VS 2008 VB 代码：

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()
        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

Answer 1

如果我理解正确，您最感兴趣的是如何获取行范围输入（例如“1-5”）和列范围输入（例如“AC”）并将这些输入定义的范围复制到另一个工作表（或工作簿） ？）。我整理了一些 VBA 来展示如何做到这一点。我没有花时间构建错误处理或输入验证，但对于格式正确的输入，此方法将起作用。请注意，我已将此示例代码设置为从活动工作簿的工作表 1 复制并粘贴到活动工作簿的工作表 2 中。

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