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

How do I write a macro to open an excel file and paste data into the file containing the macro?

提问by Joshua Buckley

I have two excel files. One of which contains a macro. The other is a shared workbook. I have some code already written. It opens up the shared woorkbook file and it has selected all of the data on a specified tab.

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

The only problem is I'm unsure on how to write code that will automatically paste this data into the file with the macro?

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

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

采纳答案by Bogey

ThisWorkbook.Activate

should do the trick. Judging by your macro, paste it after your "' select current workbook and paste the values starting at U4" comment.

应该做的伎俩。根据您的宏判断，将其粘贴在“'选择当前工作簿并粘贴从 U4 开始的值”注释之后。

回答by Gary's Student

You need to remember where you came from:

你需要记住你来自哪里：

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

回答by user6345260

   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