Bagaimana cara pengambilan data dari berbagai Workbooks?

  1. 4 minggu lalu

    Para Sesepuh Excel,

    Izin saya ingin bertanya contoh kasus :

    1. Saya ingin mengambil data didalam Workbook seperti lampiran dibawah a.xlsx;
    2. Files Workbook ini memiliki ratusan yang berbeda;
    3. Data dari semua Workbook ingin saya Rekap Customer kedalam 1 Workbook.

    Rumus / Cara mengambil data tersebut kedalam [ A Rekap Customer ].xlsx yang memiliki ratusan file data yang berbeda menjadi satu?

    Terima kasih Banyak !

    @Yugosh ...

    Kalau menggunakan formula, sulit untuk direalisasikan. Fungsi INDIRECT tidak akan berhasil jika file (Workbook) yang dirujuk tidak dibuka (hasilnya akan berupa #REF!). Merujuk langsung ke sel yang dituju satu per satu akan sangat merepotkan karena ada ribuan Workbook. Jadi, mau tidak mau, otomasi proses harus dilakukan dengan macro VBA.

    Pada file terlampir, saya coba buatkan utilitas untuk mengambil data yang ditentukan dari setiap Workbook yang ada pada sebuah folder. Dengan asumsi dan limitasi sebagai berikut :

    — Nama sheet dari setiap Workbook sumber data adalah Sheet1. Bila nama sheet dari setiap Workbook berbeda-beda, hasilnya tidak akan sesuai (kemungkinan hasilnya adalah #REF!).
    — Posisi data yang akan diambil sama seperti contoh yang diberikan, yakni pada posisi sel D1, D2, D4, D5 dan G4.
    — Tipe file yang akan diambil datanya hanya dengan ekstensi XLSX.

    Sebelum memulai proses, letakkan file-file data pada sebuah folder. Kemudian klik tombol LOAD, dan tentukan folder dimana file-file data diletakkan. Klik OK dan tunggu sampai proses selesai. Jika proses berhasil, maka pada setiap sel pada sheet TES akan berisi referensi ke sel sumber data. Sengaja saya buat demikian agar bisa diperiksa hasilnya jika ada yang tidak sesuai.

    Demikian, mudah-mudahan bisa dijadikan solusi.

  2. Saya memiliki file excel yang banyak dengan data yang berbeda.
    jadi saya ingin rekap menjadi satu apakah ada cara tersebut?

  3. Caton

    Nov 11 Terverifikasi Indonesia + 15.164 Poin

    @Yugosh ...

    File contoh sumber data yang dimaksud belum terlampir. Dan bentuk tabel tujuan tidak diketahui. Sebaiknya lampirkan juga file tujuan (Rekap Customer) yang dimaksud. Apakah dalam satu Workbook ada ratusan Worksheet? Atau setiap Workbook ada ratusan data?

    Masalah apakah memungkinkan merekap data eksternal ke dalam satu Workbook menggunakan formula, mungkin saja. Harus dilihat dahulu sumber datanya bagaimana dan tujuan akhirnya bagaimana. Namun, yang paling sering terjadi, prosesnya diotomasi melalui macro VBA...

    Demikian.

  4. @Caton

    Terima kasih om sudah menjawab,

    Contoh Format data : A.xlsx
    Contoh Rekap data : A REKAP CUSTOMER.xlsx

    Saya memiliki contoh data A.xlsx sebanyak 4123 Files Excel, saya ingin mencoba mengabungkan / Mengambil data 4123 Files Excel seperti

    • No Customer
    • Alamat Customer
    • No Polisi Customer
    • Nama Customer
    • Jenis Mobil Customer

    kedalam A REKAP CUSTOMER.xlsx

    Capture12.png sebagai contoh files excel yang saya miliki dalam 1 folder. [ nama file saya sensor ]

    terima kasih banyak.

  5. Caton

    Nov 11 Terverifikasi Jawaban Terpilih Indonesia + 15.164 Poin

    @Yugosh ...

    Kalau menggunakan formula, sulit untuk direalisasikan. Fungsi INDIRECT tidak akan berhasil jika file (Workbook) yang dirujuk tidak dibuka (hasilnya akan berupa #REF!). Merujuk langsung ke sel yang dituju satu per satu akan sangat merepotkan karena ada ribuan Workbook. Jadi, mau tidak mau, otomasi proses harus dilakukan dengan macro VBA.

    Pada file terlampir, saya coba buatkan utilitas untuk mengambil data yang ditentukan dari setiap Workbook yang ada pada sebuah folder. Dengan asumsi dan limitasi sebagai berikut :

    — Nama sheet dari setiap Workbook sumber data adalah Sheet1. Bila nama sheet dari setiap Workbook berbeda-beda, hasilnya tidak akan sesuai (kemungkinan hasilnya adalah #REF!).
    — Posisi data yang akan diambil sama seperti contoh yang diberikan, yakni pada posisi sel D1, D2, D4, D5 dan G4.
    — Tipe file yang akan diambil datanya hanya dengan ekstensi XLSX.

    Sebelum memulai proses, letakkan file-file data pada sebuah folder. Kemudian klik tombol LOAD, dan tentukan folder dimana file-file data diletakkan. Klik OK dan tunggu sampai proses selesai. Jika proses berhasil, maka pada setiap sel pada sheet TES akan berisi referensi ke sel sumber data. Sengaja saya buat demikian agar bisa diperiksa hasilnya jika ada yang tidak sesuai.

    Demikian, mudah-mudahan bisa dijadikan solusi.

  6. @Caton

    Terima kasih banyak om. sangat membantu macro tersebut.
    saya ingin melihat cara pemanggilan makro tersebut apakah itu bisa dilakukan?

    sekali lagi Terima kasih banyak !

  7. Caton

    Nov 12 Terverifikasi Indonesia + 15.164 Poin

    @Yugosh ...

    Sama-sama mas. Berikut script yang saya gunakan:

    Option Explicit
    
    Sub LoadExternalData()
        Dim sPath As String, sFile As String, sPattern As String
        Dim xFolder As FileDialog
        Dim lX As Long, lY As Long
        Dim xItems
        
        Set xFolder = Application.FileDialog(msoFileDialogFolderPicker)
        
        With xFolder
            .Title = "Pilih folder data..."
            .AllowMultiSelect = False
            If .Show <> -1 Then Exit Sub
            sPath = .SelectedItems(1) & "\"
        End With
        
        sFile = Dir(sPath & "*.xlsx")
        If Len(sFile) Then
            lX = 0
            sPattern = "='{PATH}[{FILE}]Sheet1'!"
            sPattern = Replace$(sPattern, "{PATH}", sPath)
            ReDim xItems(0 To 10 ^ 5)
            
            Do While sFile <> vbNullString
                xItems(lX) = Replace$(sPattern, "{FILE}", sFile)
                lX = lX + 1
                sFile = Dir
            Loop
            
            ReDim Preserve xItems(0 To (lX - 1))
            
            Application.ScreenUpdating = False
            Application.EnableEvents = False
            
            lY = 5
            Sheet1.Range("B5:G10004").ClearContents
            For lX = LBound(xItems) To UBound(xItems)
                With Sheet1
                    .Cells(lY, "C").Formula = xItems(lX) & "$D$5"
                    .Cells(lY, "D").Formula = xItems(lX) & "$D$2"
                    .Cells(lY, "E").Formula = xItems(lX) & "$D$4"
                    .Cells(lY, "F").Formula = xItems(lX) & "$G$4"
                    .Cells(lY, "G").Formula = xItems(lX) & "$D$1"
                End With
                lY = lY + 1
            Next
            
            Application.ScreenUpdating = True
            Application.EnableEvents = True
            
        End If
        
    End Sub

    SImpan di Standard Module dan assign tombol ke prosedur di atas.

    Demikian.

  8. @Caton @Yugosh ...

    Sama-sama mas. Berikut script yang saya gunakan:

    Option Explicit
    
    Sub LoadExternalData()
        Dim sPath As String, sFile As String, sPattern As String
        Dim xFolder As FileDialog
        Dim lX As Long, lY As Long
        Dim xItems
        
        Set xFolder = Application.FileDialog(msoFileDialogFolderPicker)
        
        With xFolder
            .Title = "Pilih folder data..."
            .AllowMultiSelect = False
            If .Show <> -1 Then Exit Sub
            sPath = .SelectedItems(1) & "\"
        End With
        
        sFile = Dir(sPath & "*.xlsx")
        If Len(sFile) Then
            lX = 0
            sPattern = "='{PATH}[{FILE}]Sheet1'!"
            sPattern = Replace$(sPattern, "{PATH}", sPath)
            ReDim xItems(0 To 10 ^ 5)
            
            Do While sFile <> vbNullString
                xItems(lX) = Replace$(sPattern, "{FILE}", sFile)
                lX = lX + 1
                sFile = Dir
            Loop
            
            ReDim Preserve xItems(0 To (lX - 1))
            
            Application.ScreenUpdating = False
            Application.EnableEvents = False
            
            lY = 5
            Sheet1.Range("B5:G10004").ClearContents
            For lX = LBound(xItems) To UBound(xItems)
                With Sheet1
                    .Cells(lY, "C").Formula = xItems(lX) & "$D$5"
                    .Cells(lY, "D").Formula = xItems(lX) & "$D$2"
                    .Cells(lY, "E").Formula = xItems(lX) & "$D$4"
                    .Cells(lY, "F").Formula = xItems(lX) & "$G$4"
                    .Cells(lY, "G").Formula = xItems(lX) & "$D$1"
                End With
                lY = lY + 1
            Next
            
            Application.ScreenUpdating = True
            Application.EnableEvents = True
            
        End If
        
    End Sub

    SImpan di Standard Module dan assign tombol ke prosedur di atas.

    Demikian.

    Terima kasih banyak sangat membantu sekali.

 

atau Mendaftar untuk ikut berdiskusi!