Quantcast

[belajar-excel] Bagaimana cara mengambil data dari workbook lain

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[belajar-excel] Bagaimana cara mengambil data dari workbook lain

lkm jktind
sumbernya :  J:\Saham\LK.xlsm   dan  J:\Saham\OHLC.xlsm.
digunakan di : J:\Saham\Komparasi.xlsm.

sudah di buat, tapi semalam  menjadi error lagi .
Pesan error nya :  Can't find project or library

Sub Kon_isi_data_LK()
Dim KP_rng As Range, KP_rngLoop As Range
Dim Brs As Long, Klm As Long
Dim t As Long

' Dim LK
Dim LK_Lnk_rng As Range, LK_Qtr_rng As Range, LK_kur_rng As Range,
LK_dat_rng As Range
Dim LK_C As Long, LK_R As Long, CR As String
Dim x As Long
Dim O_Tgl_rng As Range, O_Tic_rng As Range, O_Cls_rng As Range, O_Lis_rng
As Range

Workbooks("LK.xlsm").Activate
Sheets("DT_LK").Activate
        LK_C = Range("A1").End(xlToRight).Column
        LK_R = Range("A1").End(xlDown).Row
        Set LK_Lnk_rng = Sheets("DT_LK").Range("A4:A" & LK_R)
        Set LK_Qtr_rng = Sheets("DT_LK").Range(Cells(1, 11), Cells(1, LK_C))
        Set LK_kur_rng = Sheets("DT_LK").Range(Cells(2, 11), Cells(2, LK_C))
        Set LK_dat_rng = Sheets("DT_LK").Range(Cells(4, 11), Cells(LK_R,
LK_C))

Workbooks("OHLC.xlsm").Activate
Sheets("OHLC").Range("A2").Select
    x = Range("A2").End(xlDown).Row
        Set O_Tgl_rng = Sheets("OHLC").Range("A2", Range("A2").End(xlDown))
        Set O_Tic_rng = Sheets("OHLC").Range("B2", Range("B2").End(xlDown))
        Set O_Cls_rng = Sheets("OHLC").Range("J2", Range("J2").End(xlDown))
        Set O_Lis_rng = Sheets("OHLC").Range("T2:T" & x)
O_Tgl_rng.Select
O_Lis_rng.Select


Workbooks("Komparasi.xlsm").Activate
Worksheets("KP_01").Select
Set KP_rng = Sheets("KP_01").Range("A5", Range("A5").End(xlDown))
t = 5
Range("A5").Activate
For Each KP_rngLoop In KP_rng
    CR = "C3"
    With KP_rngLoop
        Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
0)
        Klm = WorksheetFunction.Match(Range("C1"), LK_Qtr_rng, 0)
        Cells(t, 3).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
    CR = "D3"
        Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
0)
        Klm = WorksheetFunction.Match(Range("D1"), LK_Qtr_rng, 0)
        Cells(t, 4).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
    CR = "E3"
        Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
0)
        Klm = WorksheetFunction.Match(Range("E1"), LK_Qtr_rng, 0)
        Cells(t, 5).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
    CR = "F3"
        Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
0)
        Klm = WorksheetFunction.Match(Range("F1"), LK_Qtr_rng, 0)
        Cells(t, 6).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
    CR = "G3"
        Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
0)
        Klm = WorksheetFunction.Match(Range("G1"), LK_Qtr_rng, 0)
        Cells(t, 7).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
    CR = "H3"
        Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
0)
        Klm = WorksheetFunction.Match(Range("H1"), LK_Qtr_rng, 0)
        Cells(t, 8).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
    CR = "I3"
        Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
0)
        Klm = WorksheetFunction.Match(Range("I1"), LK_Qtr_rng, 0)
        Cells(t, 9).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
    CR = "J3"
        Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
0)
        Klm = WorksheetFunction.Match(Range("J1"), LK_Qtr_rng, 0)
        Cells(t, 10).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
    CR = "K3"
        Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
0)
        Klm = WorksheetFunction.Match(Range("K1"), LK_Qtr_rng, 0)
        Cells(t, 11).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
    CR = "N3"
        Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
0)
        Klm = WorksheetFunction.Match(Range("N1"), LK_Qtr_rng, 0)
        Cells(t, 14).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
    CR = "O3"
        Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
0)
        Klm = WorksheetFunction.Match(Range("O1"), LK_Qtr_rng, 0)
        Cells(t, 15).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
    CR = "P3"
        Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
0)
        Klm = WorksheetFunction.Match(Range("P1"), LK_Qtr_rng, 0)
        Cells(t, 16).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
    CR = "Q3"
        Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
0)
        Klm = WorksheetFunction.Match(Range("Q1"), LK_Qtr_rng, 0)
        Cells(t, 17).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
    CR = "U3"
        Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
0)
        Klm = WorksheetFunction.Match(Range("U1"), LK_Qtr_rng, 0)
        Cells(t, 21).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
    CR = "V3"
        Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
0)
        Klm = WorksheetFunction.Match(Range("V1"), LK_Qtr_rng, 0)
        Cells(t, 22).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
    CR = "W3"
        Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
0)
        Klm = WorksheetFunction.Match(Range("W1"), LK_Qtr_rng, 0)
        Cells(t, 23).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)

        Cells(t, 18).Value =
Application.WorksheetFunction.SumIfs(O_Cls_rng, O_Tic_rng, Range("A" & t),
O_Tgl_rng, Range("R1"))
        Cells(t, 19).Value =
Application.WorksheetFunction.SumIfs(O_Cls_rng, O_Tic_rng, Range("A" & t),
O_Tgl_rng, Range("S1"))
        Cells(t, 20).Value =
Application.WorksheetFunction.SumIfs(O_Lis_rng, O_Tic_rng, Range("A" & t),
O_Tgl_rng, Range("T1"))
    End With

    t = t + 1
Next KP_rngLoop

End Sub

Mohon bantuan nya .

Salam

Lukman

--- Mods ---
Script diatas tidak dapat membantu menemukan penyebab error.
Lampiran workbook yang datanya sudah dikurangi jumlah record malah lebih membantu.
------------
Kid
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [belajar-excel] Bagaimana cara mengambil data dari workbook lain

Kid
Administrator
Topik ini sudah 2 hari belum ada merespon. Penanya juga tidak segera
menyertakan potongan workbook (tanpa data tapi berisi VBA).
Error yang terjadi berbunyi :
      Can't find project or library
Error ini menunjukkan tidak ditemukannya sebuah object atau suatu library
yang harusnya ada.
Bisa jadi, dalam workbook milik penanya ada sebuah userform atau dalam
worksheet ada object control yang belum dikenali komputer setempat. Misal
DTPicker atau Calendar.

Wassalam,
Kid.




On Mon, Apr 15, 2013 at 11:41 AM, lkm jktind <[hidden email]> wrote:

> **
>
>
> sumbernya : J:\Saham\LK.xlsm dan J:\Saham\OHLC.xlsm.
> digunakan di : J:\Saham\Komparasi.xlsm.
>
> sudah di buat, tapi semalam menjadi error lagi .
> Pesan error nya : Can't find project or library
>
> Sub Kon_isi_data_LK()
> Dim KP_rng As Range, KP_rngLoop As Range
> Dim Brs As Long, Klm As Long
> Dim t As Long
>
> ' Dim LK
> Dim LK_Lnk_rng As Range, LK_Qtr_rng As Range, LK_kur_rng As Range,
> LK_dat_rng As Range
> Dim LK_C As Long, LK_R As Long, CR As String
> Dim x As Long
> Dim O_Tgl_rng As Range, O_Tic_rng As Range, O_Cls_rng As Range, O_Lis_rng
> As Range
>
> Workbooks("LK.xlsm").Activate
> Sheets("DT_LK").Activate
> LK_C = Range("A1").End(xlToRight).Column
> LK_R = Range("A1").End(xlDown).Row
> Set LK_Lnk_rng = Sheets("DT_LK").Range("A4:A" & LK_R)
> Set LK_Qtr_rng = Sheets("DT_LK").Range(Cells(1, 11), Cells(1, LK_C))
> Set LK_kur_rng = Sheets("DT_LK").Range(Cells(2, 11), Cells(2, LK_C))
> Set LK_dat_rng = Sheets("DT_LK").Range(Cells(4, 11), Cells(LK_R,
> LK_C))
>
> Workbooks("OHLC.xlsm").Activate
> Sheets("OHLC").Range("A2").Select
> x = Range("A2").End(xlDown).Row
> Set O_Tgl_rng = Sheets("OHLC").Range("A2", Range("A2").End(xlDown))
> Set O_Tic_rng = Sheets("OHLC").Range("B2", Range("B2").End(xlDown))
> Set O_Cls_rng = Sheets("OHLC").Range("J2", Range("J2").End(xlDown))
> Set O_Lis_rng = Sheets("OHLC").Range("T2:T" & x)
> O_Tgl_rng.Select
> O_Lis_rng.Select
>
> Workbooks("Komparasi.xlsm").Activate
> Worksheets("KP_01").Select
> Set KP_rng = Sheets("KP_01").Range("A5", Range("A5").End(xlDown))
> t = 5
> Range("A5").Activate
> For Each KP_rngLoop In KP_rng
> CR = "C3"
> With KP_rngLoop
> Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
> 0)
> Klm = WorksheetFunction.Match(Range("C1"), LK_Qtr_rng, 0)
> Cells(t, 3).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
> CR = "D3"
> Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
> 0)
> Klm = WorksheetFunction.Match(Range("D1"), LK_Qtr_rng, 0)
> Cells(t, 4).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
> CR = "E3"
> Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
> 0)
> Klm = WorksheetFunction.Match(Range("E1"), LK_Qtr_rng, 0)
> Cells(t, 5).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
> CR = "F3"
> Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
> 0)
> Klm = WorksheetFunction.Match(Range("F1"), LK_Qtr_rng, 0)
> Cells(t, 6).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
> CR = "G3"
> Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
> 0)
> Klm = WorksheetFunction.Match(Range("G1"), LK_Qtr_rng, 0)
> Cells(t, 7).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
> CR = "H3"
> Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
> 0)
> Klm = WorksheetFunction.Match(Range("H1"), LK_Qtr_rng, 0)
> Cells(t, 8).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
> CR = "I3"
> Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
> 0)
> Klm = WorksheetFunction.Match(Range("I1"), LK_Qtr_rng, 0)
> Cells(t, 9).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
> CR = "J3"
> Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
> 0)
> Klm = WorksheetFunction.Match(Range("J1"), LK_Qtr_rng, 0)
> Cells(t, 10).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
> CR = "K3"
> Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
> 0)
> Klm = WorksheetFunction.Match(Range("K1"), LK_Qtr_rng, 0)
> Cells(t, 11).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
> CR = "N3"
> Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
> 0)
> Klm = WorksheetFunction.Match(Range("N1"), LK_Qtr_rng, 0)
> Cells(t, 14).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
> CR = "O3"
> Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
> 0)
> Klm = WorksheetFunction.Match(Range("O1"), LK_Qtr_rng, 0)
> Cells(t, 15).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
> CR = "P3"
> Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
> 0)
> Klm = WorksheetFunction.Match(Range("P1"), LK_Qtr_rng, 0)
> Cells(t, 16).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
> CR = "Q3"
> Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
> 0)
> Klm = WorksheetFunction.Match(Range("Q1"), LK_Qtr_rng, 0)
> Cells(t, 17).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
> CR = "U3"
> Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
> 0)
> Klm = WorksheetFunction.Match(Range("U1"), LK_Qtr_rng, 0)
> Cells(t, 21).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
> CR = "V3"
> Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
> 0)
> Klm = WorksheetFunction.Match(Range("V1"), LK_Qtr_rng, 0)
> Cells(t, 22).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
> CR = "W3"
> Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
> 0)
> Klm = WorksheetFunction.Match(Range("W1"), LK_Qtr_rng, 0)
> Cells(t, 23).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
>
> Cells(t, 18).Value =
> Application.WorksheetFunction.SumIfs(O_Cls_rng, O_Tic_rng, Range("A" & t),
> O_Tgl_rng, Range("R1"))
> Cells(t, 19).Value =
> Application.WorksheetFunction.SumIfs(O_Cls_rng, O_Tic_rng, Range("A" & t),
> O_Tgl_rng, Range("S1"))
> Cells(t, 20).Value =
> Application.WorksheetFunction.SumIfs(O_Lis_rng, O_Tic_rng, Range("A" & t),
> O_Tgl_rng, Range("T1"))
> End With
>
> t = t + 1
> Next KP_rngLoop
>
> End Sub
>
> Mohon bantuan nya .
>
> Salam
>
> Lukman
>
> --- Mods ---
> Script diatas tidak dapat membantu menemukan penyebab error.
> Lampiran workbook yang datanya sudah dikurangi jumlah record malah lebih
> membantu.
> ------------
>  
>
Loading...