我有一个VBA脚本,该脚本允许我选择一个文件,然后从该文件复制一个范围并将其粘贴到目标工作表中。但是,每次执行此操作时,都会打开源文件,然后关闭文件,这会提示我是否要将信息保存在剪贴板上。

我不知道只复制数据而不打开源excel是否会更好。

Option Explicit

Sub DCDatabaseCopy()
Dim vFile As Variant
Dim wbCopyTo As Workbook
Dim wsCopyTo As Worksheet
Dim wbCopyFrom As Workbook
Dim wsCopyFrom As Worksheet
Dim DCRowCount As Integer

Set wbCopyTo = ActiveWorkbook
Set wsCopyTo = ActiveSheet

    '-------------------------------------------------------------
    'Open file with data to be copied

    vFile = Application.GetOpenFilename("Excel Files (*.*)," & "*.*", 1, "Select Excel File", "Open", False)

    'If Cancel then Exit
    If TypeName(vFile) = "Boolean" Then
        Exit Sub
    Else
    Set wbCopyFrom = Workbooks.Open(vFile)
    Set wsCopyFrom = wbCopyFrom.Worksheets(1)
    End If

    '--------------------------------------------------------------
    'Copy Range
    DCRowCount = wsCopyFrom.Range("A1", wsCopyFrom.Range("A1").End(xlDown)).Rows.Count

    wsCopyFrom.Range("A1:G" & DCRowCount).Copy
    wsCopyTo.Range("A2").PasteSpecial Paste:=xlPasteValues, _
            Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    'Close file that was opened
    wbCopyFrom.Close SaveChanges:=False
End Sub

最佳答案

关闭工作簿运行之前:

wbCopyFrom.Application.CutCopyMode = False


这将清除剪贴板。

10-07 19:39
查看更多