Ado İle Kapalı Excel Dosyalarında İşlem

Kod:

Sub ReadFromDifferentWorkbook(sourceFileName As String, sourceSheetName As String, targetFileName As String, targetSheetName As String)

    

    Dim targetFile As String

    targetFile = ThisWorkbook.Path & Application.PathSeparator & targetFileName '"Company Sales Data.xlsx"

    

    Dim connection As New ADODB.connection 'ThisWorkbook.FullName

    connection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _

                    "Data Source=" & targetFile & ";" & _

                    "Extended Properties=""Excel 12.0;" & _

                    "HDR=Yes;"";"

    

    Dim sourceFile As String

    sourceFile = ThisWorkbook.Path & Application.PathSeparator & sourceFileName '"Company Sales Data.xlsx"

    

    Dim sourceSheet As String

    sourceSheet = "[Excel 12.0;HDR=YES;DATABASE=" & sourceFile & "]"

    

    Dim query As String

    query = "Insert Into [" & targetSheetName & "$] Select Company,Sales From " & sourceSheet & ".[" & sourceSheetName & "$] " 'CompanyOut Sales

    

    connection.Execute query

    

    connection.Close

End Sub

Kod:

Dim sourceFileName As String

    Dim sourceSheetName As String

    Dim targetFileName As String

    Dim targetSheetName As String

    

    sourceFileName = "Company Sales Data.xlsx"

    sourceSheetName = "Sales"

    targetFileName = "ADO Video Code.xlsm"

    targetSheetName = "CompanyOut"

    

    Call ReadFromDifferentWorkbook(sourceFileName, sourceSheetName, targetFileName, targetSheetName)