[belajar-excel] Ambil Tabel dari web ke excel dengan VBA

[zainul_ulum[at]yahoo[dot]com]

Be-Excel,

 

Saya membuat VBA untuk memngambil data kurs pajak mingguan sbb:

'====start vba===

Const ALAMAT_WEB As String = "http://www.depkeu.go.id/ind/currency/";

 

Sub AmbilTableKursPajakMingguan()

    Dim rgTarget As Range, aWebQry As QueryTable, sht As Worksheet

    Set sht = ActiveSheet

    

    Application.ScreenUpdating = False

    With sht.UsedRange

        If .Rows.Count > 0 Then

        .Columns.Delete 'hapus hasil query sebelumnya

        End If

    End With

 

    Set rgTarget = sht.Range("$A$1")

    Set aWebQry = ActiveSheet.QueryTables.Add(Connection:= _

        "URL;" & ALAMAT_WEB, _

        Destination:=rgTarget)

    

    With aWebQry

        .Name = "KursPajakMingguan"

        .AdjustColumnWidth = False

        .WebSelectionType = xlSpecifiedTables

        .WebTables = "1"

        .WebFormatting = xlWebFormattingNone

        .Refresh BackgroundQuery:=False

    End With

    RapihkanTable sht

    Application.ScreenUpdating = True

End Sub

 

Private Sub RapihkanTable(sht As Worksheet)

    

    sht.Columns("A:I").Delete 'hapus kolom yang kosong

    sht.Rows("1:4").Delete ' hapus baris yg kosong

    sht.Range("A5").CurrentRegion.Columns.AutoFit

    

    'rapihkan judul

    With sht.Range("A2:F4")

        .HorizontalAlignment = xlCenter

        .VerticalAlignment = xlCenter

        .WrapText = True

        .MergeCells = True

    End With

 

    Dim cn As Object

    For Each cn In sht.Parent.Connections 'menghapus connection yang ada

        cn.Delete

    Next

 

End Sub

'====end vba

Pertanyaan:

Bagaimana caranya agar yang diambil hanya table dan isinya saja? Sehingga 
procedure RapihkanTable() tidak diperlukan.

JIka prosedur 

 

FetchDataFromWebsite.xlsm
Description: application/vnd.ms-excel.sheet.macroenabled.12