Re: [indoprog-vb] Export SQL To Excel Or Access

[Arief Wijana]

cara lain 1 :

    dengan menggunakan DTS Export / Import wizart  milik SQL Servernya.
    didalam wizart itu sendiri akan menawarkan 
    apakah wizart  itu akan di save ?
    coba klik check Save DTS Package dan pilih Visual Basic File
    hasilnya akan terbentuk Module VB yang siap untuk di pergunakan




Cara Lain 2:

1. Create table excelnya terlebih dahulu menggunakan ADO

    Dim tb As DAO.TableDef       

    Set DbReceipt = DBEngine.OpenDatabase("C:\Tes.XLS", False, False, "Excel 
8.0")
                            
    Set tb = DbReceipt.CreateTableDef("Receipts")
    With tb
        .Fields.Append .CreateField("RCPHSEQ", dbDouble)
        .Fields.Append .CreateField("VDCODE", dbText, 12)
        .Fields.Append .CreateField("DESCRIPTIO", dbText, 60)
        .Fields.Append .CreateField("REFERENCE", dbText, 60)
    End With
    DbReceipt.TableDefs.Append tb

2. Looping file yang mau di export, masukan ke file Excel yang telah di Create 
( per Record  )

    Dim Line As Integer
    Dim adrRec As New ADODB.Recordset
    adrRec.Open "Select * from TDistributionBuku where CIP='" & 
txtReference.text & "' ORDER BY HALAMAN,CODE", conADO, adOpenKeyset, 
adLockReadOnly
    
    If Not adrRec.EOF Then

        Set RsHeaderReceipt = DbReceipt.OpenRecordset("Receipts")
       
        Dim sHal As String
        
        'detail
        Line = 0
        Dim rHseq As Integer
        rHseq = 0
        Do While Not adrRec.EOF
            
                rHseq = rHseq + 1
                'Header
                With RsHeaderReceipt
                    .AddNew
                    !RCPHSEQ = rHseq
                    !Descriptio = txtDescripsi.text
                    !Reference = "CIP#: " & txtReference.text & " " & 
adrRec!Halaman
                    !VDCODE = txtVendor.text
                    Line = 0
                    .Update
                End With
                
       
                adrRec.MoveNext
        Loop
        
        
    End If
    
    adrRec.Close
    Set adrRec = Nothing
    Set RsHeaderReceipt = Nothing
    Set RsReceipt = Nothing
    Set DbReceipt = Nothing





Semoga membantu


Thank's




  ----- Original Message ----- 
  From: Vincent S 
  To: indoprog-vb@yahoogroups.com 
  Sent: Monday, January 22, 2007 1:57 PM
  Subject: Re: [indoprog-vb] Export SQL To Excel Or Access


  Ada, tapi running nya dari command prompt..
  bcp "[query]" queryout "[filename]" -C -S[servername] -U[username] 
-P[password]
  di query kalau misalnya select * from customer jadi:
  select * from [databasename]..customer
  semoga membantu

  ----- Original Message ----
  From: Igit Syah Riza <[EMAIL PROTECTED]>
  To: indoprog-vb@yahoogroups.com
  Sent: Thursday, January 11, 2007 12:25:20 PM
  Subject: [indoprog-vb] Export SQL To Excel Or Access

  Met siang temen - temen semua,
  Ada yang pernah export Data dari Sql Server ke Excel atau Access nggak ? Tapi 
dari code di Vb bukan dari Wizardnya SQL Server. Kalo ada share donk codenya...

  Thanks..

  Igit

  [Non-text portions of this message have been removed]

  __________________________________________________________
  TV dinner still cooling? 
  Check out "Tonight's Picks" on Yahoo! TV.
  http://tv.yahoo.com/

  [Non-text portions of this message have been removed]



   

[Non-text portions of this message have been removed]