Warm tip: This article is reproduced from serverfault.com, please click

excel-VBA从封闭的工作簿中导入数据并将其粘贴为特定的工作表中的值

(excel - VBA to import data from closed workbook and paste into specific worksheet as values)

发布于 2020-12-01 13:19:51

我有一个代码非常适合从另一个封闭的工作簿中导入数据。我不知道如何修改代码,因此它将导入的数据粘贴为值。

你能帮我找到解决办法吗?

Option Explicit
Public Sub Import_SM_Export_DataFromAnotherWorkbook()
 ' Get workbook...
    Dim targetWorkbook As Workbook
    Set targetWorkbook = Application.ThisWorkbook

    ' get the customer workbook
    Dim Filter As String
    Filter = "Text files (*.csv),*.csv"

    Dim Caption As String
    Caption = "Please Select an input Security Matrix file "

    Dim Ret As Variant
    Ret = Application.GetOpenFilename(Filter, , Caption)

    If VarType(Ret) = vbBoolean And Ret = False Then Exit Sub

    Dim wb As Workbook
    Set wb = Workbooks.Open(Ret)

    'copy into a specific worksheet in your target workbook
    wb.Worksheets(CopyFromHere).UsedRange.Copy targetWorkbook.Worksheets("PasteHere").Range("A1")

    'close opened workbook without saving
    wb.Close SaveChanges:=False
End Sub
Questioner
Radosław Sokolowski
Viewed
11
horst 2020-12-01 21:35:01

使用.PasteSpecial粘贴为值:

wb.Worksheets(CopyFromHere).UsedRange.Copy 
targetWorkbook.Worksheets("PasteHere").Range("A1").PasteSpecial Paste:=xlPasteValues

另外,你应该在Application.CutCopyMode = False之后进行设置

但是理想情况下,你无需完全复制粘贴就可以传输数据。有点像这样:

dim sourceRng as String
sourceRng = wb.Worksheets(CopyFromHere).UsedRange.Address

targetWorkbook.Worksheets("PasteHere").Range(sourceRng).Value = wb.Worksheets(CopyFromHere).Range(sourceRng).Value