如何编写宏以打开一个Excel文件并将数据粘贴到包含宏的文件中？

Question

我有两个Excel文件，其中一个包含宏代码，另一个是共享工作簿。我已经编写了一些代码，它打开了共享工作簿文件并选择了指定标签页上的所有数据。

唯一的问题是我不确定如何编写代码，将这些数据自动粘贴到带有宏的文件中？

Sub ImportData_Click()

' open the source workbook and select the source sheet
Workbooks.Open Filename:="Test.xlsm"
Sheets("Make").Select

' copy the source range
Sheets("Make").Range("A1:Z630").Select
Selection.Copy

' select current workbook and paste the values starting at U4
Sheets("Make").Select
Sheets("Make").Range("A1:Z630").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

' close the source workbook
Windows("Test.xlsm").Activate
ActiveWorkbook.Close

End Sub

- Joshua Buckley

Answer 1

ThisWorkbook.Activate

应该能解决问题。根据您的宏，将其粘贴在“'选择当前工作簿并从U4开始粘贴值”注释后。

- Bogey

Answer 2

你需要记住自己来的地方：

Sub ImportData_Click()

    Dim rDest As Range
    Set rDest = ThisWorkbook.Sheets("Make").Range("A1:Z360")

    ' open the source workbook and select the source sheet
    Workbooks.Open Filename:="C:\TestFolder\Test.xlsm"
    Sheets("Make").Select

    ' copy the source range
    Sheets("Make").Range("A1:Z630").Select
    Selection.Copy

    rDest.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False

    ActiveWorkbook.Close

End Sub

- Gary's Student

Answer 3

   Private Sub CommandButton1_Click()
   Dim mode As String
Dim RecordId As Integer
Dim sourcewb As Workbook
Dim targetwb As Workbook
Dim SourceRowCount As Long
Dim TargetRowCount As Long
Dim SrceFile As String
Dim TrgtFile As String
Dim TitleId As Integer
Dim TestPassCount As Integer
Dim TestFailCount As Integer
TitleId = 4
'TestPassCount = 0
'TestFailCount = 0

'Retrieve number of records in the TestData SpreadSheet
Dim TestDataRowCount As Integer
TestDataRowCount = Worksheets("TestData").UsedRange.Rows.Count

If (TestDataRowCount <= 2) Then
  MsgBox "No records to validate.Please provide test data in Test Data SpreadSheet"
Else
  For RecordId = 3 To TestDataRowCount
    RefreshResultSheet
    'Source File row count
    SrceFile = Worksheets("TestData").Range("D" & RecordId).Value
    Set sourcewb = Workbooks.Open(SrceFile)
    With sourcewb.Worksheets(1)
      SourceRowCount = .Cells(.Rows.Count, "A").End(xlUp).Row
      sourcewb.Close
    End With

    'Target File row count
    TrgtFile = Worksheets("TestData").Range("E" & RecordId).Value
    Set targetwb = Workbooks.Open(TrgtFile)
    With targetwb.Worksheets(1)
      TargetRowCount = .Cells(.Rows.Count, "A").End(xlUp).Row
      targetwb.Close
    End With

    ' Set Result Test data value
    TitleId = TitleId + 3
    Worksheets("Result").Range("A" & TitleId).Value = Worksheets("TestData").Range("A" & RecordId).Value

    'Compare Source and Target Row count
    Resultid = TitleId + 1
    Worksheets("Result").Range("A" & Resultid).Value = "Source and Target record Count"
    If (SourceRowCount = TargetRowCount) Then
       Worksheets("Result").Range("B" & Resultid).Value = "Passed"
       TestPassCount = TestPassCount + 1
    Else
      Worksheets("Result").Range("B" & Resultid).Value = "Failed"
      TestFailCount = TestFailCount + 1
    End If
  Next RecordId
End If
UpdateTestExecData TestPassCount, TestFailCount
End Sub

Sub RefreshResultSheet()
  Worksheets("Result").Activate
  Worksheets("Result").Range("B1:B4").Select
  Selection.ClearContents
  Worksheets("Result").Range("D1:D4").Select
  Selection.ClearContents
  Worksheets("Result").Range("B1").Value = Worksheets("Instructions").Range("D3").Value
  Worksheets("Result").Range("B2").Value = Worksheets("Instructions").Range("D4").Value
  Worksheets("Result").Range("B3").Value = Worksheets("Instructions").Range("D6").Value
  Worksheets("Result").Range("B4").Value = Worksheets("Instructions").Range("D5").Value
End Sub

Sub UpdateTestExecData(TestPassCount As Integer, TestFailCount As Integer)
  Worksheets("Result").Range("D1").Value = TestPassCount + TestFailCount
  Worksheets("Result").Range("D2").Value = TestPassCount
  Worksheets("Result").Range("D3").Value = TestFailCount
  Worksheets("Result").Range("D4").Value = ((TestPassCount + TestFailCount) / TestPassCount)
End Sub

- user6345260

---

这是一个非常冗长的回答，针对一个非常具体的问题。对于将来偶然发现这篇文章的人来说，拆分为逻辑步骤或提供更通用的解决方案会更有帮助。 - Tom Lord