bu kod ile kapalı olan excelden işlem yaptığımız Excel dosyasına veri alabiliyoruz bunun neresini değiştirerek kapalı durumda olan bir Excel dosyasına veri yazabiliriz

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

    

    Dim connection As New ADODB.connection

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

                    "Data Source=" & ThisWorkbook.FullName & ";" & _

                    "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