如何使用VBA将关闭的工作簿中的数据（保持其关闭状态）复制到主工作簿中？

Question

如何使用VBA将关闭的工作簿中的数据（保持其关闭状态）复制到主工作簿中？

5

我需要使用VBA从关闭的工作簿中复制数据到主工作簿，而无需打开它们。我使用 Workbooks.Open 打开4-6个文件。每个需要打开的文件都会显著减缓复制操作。我需要高效的VBA代码来实现在不打开每个文件的情况下复制数据。以下是我的代码示例：

Set x = Workbooks.Open("C:\Bel.xls")
'Now, copy what you want from x:
x.Sheets("Daily Figures").Range("A13:j102").Copy
'Now, paste to y worksheet
y.Activate
Sheets("Data - Daily").Range("N2").PasteSpecial
'Close x:
Application.CutCopyMode = False
x.Close
Sheets("sheet1").Range("M4") = Date

- erezlale

你试过谷歌吗？ExecuteExcel4Macro - Steven Martin

尝试执行以下代码：y.Sheets("Data - Daily").Range("N2").PasteSpecial，然后在打开工作簿之前查看application.screenupdating=false。 - Davesexcel

通常从控制角度打开工作簿比使用xlm宏、ADO和创建直接链接的正常“关闭”方法更好。例如，ADO可能会出现混合数据类型的问题。 - brettdj

另外，尝试使用Application.EnableEvents = false和Application.EnableEvents = true。每次粘贴时，都会触发一个事件。使用Application.EnableEvents = false可以提高速度。 - El Scripto

http://stackoverflow.com/questions/7524064/excel-vba-question-need-to-access-data-from-all-excel-files-in-a-directory-wi/7524229#7524229 - brettdj

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Yan F. · Accepted Answer

尝试这个。使用ADO而不打开源文件，它可以正常工作：

Sub TransferData()
Dim sourceFile As Variant

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

sourceFile = "C:\Bel.xls"

GetData sourceFile, "Daily Figures", "A13:j102", Sheets("Data - Daily").Range("N2"), False, False

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

Public Sub GetData(sourceFile As Variant, SourceSheet As String, _
                   SourceRange As String, TargetRange As Range, Header As Boolean, UseHeaderRow As Boolean)
' 30-Dec-2007, working in Excel 2000-2007
' http://www.rondebruin.nl/ado.htm

Dim rsCon As Object
Dim rsData As Object
Dim szConnect As String
Dim szSQL As String
Dim lCount As Long

' Create the connection string.
If Header = False Then
    If Val(Application.Version) < 12 Then
        szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & sourceFile & ";" & _
                    "Extended Properties=""Excel 8.0;HDR=No"";"
    Else
        szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                    "Data Source=" & sourceFile & ";" & _
                    "Extended Properties=""Excel 12.0;HDR=No"";"
    End If
Else
    If Val(Application.Version) < 12 Then
        szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & sourceFile & ";" & _
                    "Extended Properties=""Excel 8.0;HDR=Yes"";"
    Else
        szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                    "Data Source=" & sourceFile & ";" & _
                    "Extended Properties=""Excel 12.0;HDR=Yes"";"
    End If
End If

If SourceSheet = "" Then
    ' workbook level name
    szSQL = "SELECT * FROM " & SourceRange$ & ";"
Else
    ' worksheet level name or range
    szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];"
End If

On Error GoTo SomethingWrong

Set rsCon = CreateObject("ADODB.Connection")
Set rsData = CreateObject("ADODB.Recordset")

rsCon.Open szConnect
rsData.Open szSQL, rsCon, 0, 1, 1

' Check to make sure we received data and copy the data
If Not rsData.EOF Then

    If Header = False Then
        TargetRange.Cells(1, 1).CopyFromRecordset rsData
    Else
        'Add the header cell in each column if the last argument is True
        If UseHeaderRow Then
            For lCount = 0 To rsData.Fields.Count - 1
                TargetRange.Cells(1, 1 + lCount).Value = _
                rsData.Fields(lCount).Name
            Next lCount
            TargetRange.Cells(2, 1).CopyFromRecordset rsData
        Else
            TargetRange.Cells(1, 1).CopyFromRecordset rsData
        End If
    End If

Else
    MsgBox "No records returned from : " & sourceFile, vbCritical
End If

' Clean up our Recordset object.
rsData.Close
Set rsData = Nothing
rsCon.Close
Set rsCon = Nothing
Exit Sub

SomethingWrong:
    MsgBox "The file name, Sheet name or Range is invalid of : " & sourceFile, _
           vbExclamation, "Error"
    On Error GoTo 0

End Sub