我有一个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
这将清除剪贴板。