Çözüldü veri aktarımı 2

Private Sub CommandButton1_Click()

Dim kitap As String, syf As String
ThisWorkbook.Sheets("anasayfaa").Range("C2:w" & Rows.Count).ClearContents

Select Case Range("B2").Value
    Case "Afirmasi" 'kapali kitap adi
        kitap = "Afirmasi.xlsx"
        syf = "afirmasi$A2:w" 'sayfa adi
    Case "bfirmasi"
        kitap = "bfirmasi.xlsx"
        syf = "bfirmasi$A2:w"
    Case "cfirmasi"
        kitap = "cfirmasi.xlsx"
        syf = "cfirmasi$A2:w"
End Select
If kitap <> "" And syf <> "" Then test kitap, syf
End Sub

Sub test(kitap As String, syf As String)
  Dim rs As Object, con As Object, sql As String
    Set rs = CreateObject("ADODB.Recordset")
    Set con = CreateObject("ADODB.Connection")
    Dim yol As String, yol2 As String
    
    yol = ThisWorkbook.Path & Application.PathSeparator & kitap

    With ThisWorkbook.Sheets("anasayfaa")
        con.Open "Provider=microsoft.ace.oledb.12.0;data source=" & yol & ";extended properties=""Excel 12.0;hdr=no"""
        sql = "select * from [" & syf & "]"
        rs.Open sql, con, 1, 3
       .Range("B2").CopyFromRecordset rs
    End With
    rs.Close
    con.Close
    Set rs = Nothing
    Set con = Nothing
    MsgBox "Bitti", vbInformation, "Bilgi"
End Sub

Private Sub CommandButton1_Click()

Dim kitap As String, syf As String
ThisWorkbook.Sheets("anasayfaa").Range("C2:w" & Rows.Count).ClearContents

Select Case Range("B2").Value
    Case "Afirmasi" 'kapali kitap adi
        kitap = "Afirmasi.xlsx"
        syf = "afirmasi$" 'sayfa adi
    Case "bfirmasi"
        kitap = "bfirmasi.xlsx"
        syf = "bfirmasi$"
    Case "cfirmasi"
        kitap = "cfirmasi.xlsx"
        syf = "cfirmasi$"
End Select
If kitap <> "" And syf <> "" Then test kitap, syf
End Sub

Sub test(kitap As String, syf As String)
  Dim rs As Object, con As Object, sql As String
    Set rs = CreateObject("ADODB.Recordset")
    Set con = CreateObject("ADODB.Connection")
    Dim yol As String, yol2 As String
    
    yol = ThisWorkbook.Path & Application.PathSeparator & kitap

    With ThisWorkbook.Sheets("anasayfaa")
        con.Open "Provider=microsoft.ace.oledb.12.0;data source=" & yol & ";extended properties=""Excel 12.0;hdr=yes"""
        sql = "select * from [" & syf & "]"
        rs.Open sql, con, 1, 3
       .Range("B2").CopyFromRecordset rs
    End With
    rs.Close
    con.Close
    Set rs = Nothing
    Set con = Nothing
    MsgBox "Bitti", vbInformation, "Bilgi"
End Sub