Merubah data connetion properties pd table

  1. 6 bulan yang lalu

    Mohon pencerahan Mas ter.

    1. bagaimana Merubah data "connetion properties" pada table sesuai range ?
    2. dan rename table tersebut.
  2. Caton

    Apr 3 Terverifikasi Indonesia + 11.443 Poin

    @NOBO...

    Maksudnya mengubah apanya mas bro? Apa mungkin seperti dalam diskusi ini?

  3. -image-

    apakah gamber diatas berlaku menghubungkan antar dua file saja? mas

  4. Caton

    Apr 3 Terverifikasi Indonesia + 11.443 Poin

    @NOBO...

    ... apakah gamber diatas berlaku menghubungkan antar dua file saja ...

    Bisa dibilang demikian. Gambar tersebut merupakan Connection Properties dari Ms. Query yang bertujuan mengambil data dari sebuah Workbook pada kolom tertentu dengan kriteria tertentu...

  5. saya masih pembuatan rekapnya mas
    gimana cara vba nya memasukan
    connection file : ?
    connection string : ?
    command type : table
    command text : sheetname$

    pada coloum yang sudah saya siapkan
    makasih mas sudah membalas

  6. Caton

    Apr 3 Terverifikasi Indonesia + 11.443 Poin

    @NOBO ...

    Setahu saya ada beberapa cara, tergantung bagaimana koneksinya akan dibentuk. Saya kasih contoh berdasarkan gambar di atas:

    Public Sub QueryExternalDB()
        Dim sFilePath As String, sCmd As String
        Dim xlListObject As ListObject
        Dim xConnections
        
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        
        sFilePath = ThisWorkbook.Path & "\SampleDatabase.xlsx"
        
        xConnections = Array( _
            "ODBC;DBQ=" & sFilePath & ";DefaultDir=" & ThisWorkbook.Path & _
            ";Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}", _
            ";DriverId=1046;FIL=excel 12.0;MaxBufferSize=2048;PageTimeout=5;MaxScanRows=8;" & _
            ";ReadOnly=1;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;")
        
        sCmd = vbNullString
        sCmd = sCmd & "SELECT BasisData.KODE, BasisData.MULAI, BasisData.SELESAI " & vbCrLf
        sCmd = sCmd & "FROM " & Chr$(96) & sFilePath & Chr$(96) & ".BasisData BasisData " & vbCrLf
        sCmd = sCmd & "WHERE (BasisData.MULAI Like '%SEP%') " & vbCrLf
        sCmd = sCmd & "ORDER BY BasisData.KODE, BasisData.MULAI"
        
        With ActiveSheet
            .Cells.Delete
            Set xlListObject = .ListObjects.Add(SourceType:=xlSrcExternal, _
                Source:=Array(xConnections(0), xConnections(1)), Destination:=Range("$A$1"))
            
            With xlListObject.QueryTable
                .CommandText = Array(sCmd)
                .CommandType = xlCmdSql
                .RowNumbers = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .BackgroundQuery = True
                .RefreshStyle = xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = False
                .RefreshPeriod = 0
                .PreserveColumnInfo = True
                .ListObject.DisplayName = "Connection_Test"
                .Refresh BackgroundQuery:=False
            End With
            
            .Cells.RowHeight = 15
            .Range("A:C").EntireColumn.AutoFit
            .Range("A1").Select
        End With
    
        Application.ScreenUpdating = True
        Application.EnableEvents = True
    
    End Sub

    atau dapat juga menggunakan script seperti berikut:

    Sub LoadExternalData()
        Dim sFileName As String, sQuery As String, sCmd As String, sConn As String
        Dim xlWBConn As WorkbookConnection
        Dim xlQueryTable As QueryTable
    
        sFileName = ActiveWorkbook.Path & "\" & "SampleDatabase.xlsx"
        
        On Error Resume Next
        sQuery = "Test BasisData"
        ThisWorkbook.Connections(sQuery).Delete
        Err.Clear
        On Error GoTo 0
        
        sCmd = vbNullString
        sCmd = sCmd & "SELECT BasisData.KODE, BasisData.MULAI, BasisData.SELESAI "
        sCmd = sCmd & "FROM " & Chr$(96) & sFileName & Chr$(96) & ".BasisData BasisData  "
        sCmd = sCmd & "WHERE (BasisData.MULAI Like '%SEP%')  "
        sCmd = sCmd & "ORDER BY BasisData.KODE, BasisData.MULAI"
        
        sConn = "ODBC"
        sConn = sConn & ";DBQ=" & sFileName & ";DefaultDir=" & ThisWorkbook.Path
        sConn = sConn & ";Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}"
        sConn = sConn & ";DriverId=1046;FIL=excel 12.0;MaxBufferSize=2048;PageTimeout=5"
        
        With Sheet1
            .Cells.Delete
            Set xlQueryTable = .QueryTables.Add(sConn, .Range("A1"), sCmd)
            xlQueryTable.Refresh
        End With
        
    End Sub

    Demikian.

  7. mas
    kalo data nya seperti ini bagaimana vbanya.
    mohon pencerahan.

  8. Caton

    Apr 6 Terverifikasi Indonesia + 11.443 Poin

    @NOBO ...

    Data yang digunakan adalah Excel (Book1.xlsx), namun Connection Driver-nya menggunakan Access? Pada contoh script di atas, mas bisa memasukkan Connections String ke dalam objek xConnections dan Command Text ke dalam variabel sCmd. Saya ambil contoh untuk script kedua sebagaimana yang sampaikan di atas sebelumnya:

    Sub LoadExcelData()
        Dim sFileName As String, sQuery As String, sCmd As String, sConn As String
        Dim xlQueryTable As QueryTable
        Dim xConnections
    
        sFileName = ActiveWorkbook.Path & "\" & "TestData.xlsx"
        
        On Error Resume Next
        sQuery = "Excel DataSource"
        ThisWorkbook.Connections(sQuery).Delete
        Err.Clear
        On Error GoTo 0
        
        sConn = "OLEDB;"
        sConn = sConn & "Provider=Microsoft.ACE.OLEDB.12.0;Password=" & vbNullString & ";"
        sConn = sConn & "User ID=Admin;Data Source=" & sFileName & ";Mode=Share Deny Write;"
        sConn = sConn & "Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";"
        sConn = sConn & "Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";"
        sConn = sConn & "Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;"
        sConn = sConn & "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;"
        sConn = sConn & "Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;"
        sConn = sConn & "Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;"
        sConn = sConn & "Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;"
        sConn = sConn & "Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False"
    
        sCmd = "DATA$"
        
        With ActiveSheet
            .Cells.Delete
            Set xlQueryTable = .QueryTables.Add(sConn, .Range("A1"), sCmd)
            With xlQueryTable
                .BackgroundQuery = True
                .CommandType = xlCmdTable
                .Name = sQuery
                .SourceDataFile = sFileName
                .WorkbookConnection.Name = sQuery
                .Refresh
            End With
        End With
    
    End Sub

    Demikian.

 

atau Mendaftar untuk ikut berdiskusi!