Excel實戰技巧27:在兩個Excel文件之間傳遞數據而無須打開Excel文件

微信公眾號：excelperfect

 

經常有人需要從關閉的工作簿文件中取值，省掉打開工作簿的操作。而本文介紹的技巧，所涉及到的工作簿文件都不需要打開，就可以完成將某工作簿文件中的數據遷移到指定的工作簿文件中。

 

因為要使用ADO技術，所以首先需要設置對下面兩個庫的引用：

1. Microsoft ActiveX Data Objects 2.xLibrary
2. Microsoft ActiveX Data Objects Recordset 2.x Library

在VBE中，單擊菜單「工具——引用」，在「引用」對話框中，找到並選取上述兩個庫，如下圖1所示。

圖1

 

在VBE中，輸入下面的代碼：

Sub TransferDataBetweenExcelFiles(strInputFileFullName As String, _

              strOutputFileFullName As String,_

              strInputSheetName As String)

    Dim adoConnection As New ADODB.Connection

    Dim adoRcdSource As New ADODB.Recordset

    Dim strProvider As String

    Dim strExtProperties As String

    Dim strFileExt As String

   

    If Len(Dir(strInputFileFullName)) = 0 Then

        MsgBox "要轉移數據的源文件不存在."

        Exit Sub

    End If

   

    strFileExt = Mid(strOutputFileFullName, _

                InStrRev(strOutputFileFullName,".", _

                -1, vbTextCompare), _

                Len(strOutputFileFullName))

   

    If strFileExt = ".xlsx" Then

        strExtProperties = "Excel 12.0XML"

    Else

        strExtProperties = "Excel8.0"

    End If

   

    If CDbl(Application.Version) > 11 Then

        strProvider ="Microsoft.ACE.OLEDB.12.0"

    Else

        strProvider ="Microsoft.JET.OLEDB.4.0"

    End If

   

    adoConnection.Open "Provider="& strProvider & ";Data Source=" & _

       strOutputFileFullName &";Extended Properties=""" & _

       strExtProperties &";HDR=YES"";"

    adoRcdSource.Open "Select * into[" & strInputSheetName & "] From [" & _

       strInputSheetName & "$] IN'" & strInputFileFullName & _

       "'[" & strExtProperties& ";HDR=YES;]", adoConnection

   

    adoConnection.Close

   

    Set adoRcdSource = Nothing

    Set adoConnection = Nothing

End Sub

 

假設C盤中存在一個名為「源文件.xlsx」的工作簿，下面是測試代碼，

Sub test()

    TransferDataBetweenExcelFiles "C:\源文件.xlsx","C:\目標文件.xlsx", "Sheet1"

End Sub

 

運行後，將「源文件.xlsx」工作簿工作表Sheet1中的數據移至名為「目標文件.xlsx」的工作簿，如下圖2所示。

圖2

 

注意，「源文件.xlsx」工作簿一定要存在，但「目標文件.xlsx」工作簿不一定需要存在。如果「目標文件.xlsx」工作簿不存在，則會創建該工作簿並獲取「源文件.xlsx」工作簿指定遷移的工作表，如上圖2所示。

歡迎分享本文，轉載請註明出處。

歡迎在下面留言，完善本文內容，讓更多的人學到更完美的知識。

歡迎關注[完美Excel]微信公眾號：