o, gtu.. modif aja lg function header Export2XL mu. tambahin parameter bertipedata string yg isinya ntar bwt nampung query mu. Misal :
Public Function Export2XLdariSQL(InitRow As Long, DBAccess As String, SQLQuery As String) As Long *nama sengaja kuganti, function Export2XL mu kmu pertahankan ae utk kenang2an... hehehe ^_^ lumayan utk nambah2 koleksi code mu... n ngga make adCmdTable lagi. Tp makae command text. jadi, barismu yg ini cmd.CommandText = DBTable cmd.CommandType = adCmdTable kmu ubah jadi ini : cmd.CommandType = adCmdText cmd.CommandText = SQLQuery '<~ variabel ini namanya u sesuaikan dgn header function mu... udah... jadi ntr cara manggilnya, misal u ada code spt postingan u yg terakhir, u eksekusi function u jadi something like this : call export2XLdariSQL 3, "BIBLIO.MDB", " select * from tbl_merchantdebit where kota like '" & txtcari3.Text & "%'" beres... klo mo connect ke SQL server? u gabung2 aja code dari postinganku yg prtama, n code yg ini... udah dapet ngeliat polanya kan? -- regards, Rizky Prihanto ~~~~~~~~~~~~~~~~~~~ Office : C4 Computer Communities Club(http://www.cee-papat.com) Personal : http://www.software-arsitek.web.id On 11/5/07, Doni Rahadian <[EMAIL PROTECTED]> wrote: > > Dear All and Mas Rizky, terimakasih banyak program sudah berjalan, tapi > aku > masih punya salah satu problem baru, > gimana caranya klo kita mau reportnya itu di filter misalnya field kota > hanya kota cimahi saja > dan saya sudah punya listing filternya adalah sbb : > > Private Sub txtcari3_Change() > If txtcari3.Text = Empty Then > Adodc1.RecordSource = "select * from tbl_merchantdebit" > Else > Adodc1.RecordSource = " select * from tbl_merchantdebit " & _ > " where kota like '" & txtcari3.Text & "%'" > > End If > Adodc1.Refresh > > klo kita input txtcari3.Text = cimahi > maka report yang export ke excel pun hanya cimahi saja... > > atas segala bentuk bantuan dan masukannya saya ucapkan terimakasih > > On 11/5/07, Doni Rahadian <[EMAIL PROTECTED]> wrote: > > > > hahahah..., functionnya dah aku rubah, aslinya sih : > > > > cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & > > DBAccess > > -- > > kalo yang saya lampirkan sih dah saya robah koneksinya ke sql server. > > saya coba dulu ya bro Rizky koding bikinanmu > > terimakasih bgt atas pencerahannya > > > > > > On 11/5/07, Rizky Prihanto < [EMAIL PROTECTED]> wrote: > > > > > > aneh... bener2 aneh... > > > > > > tu function udah u coba perbaikin ya? connection stringnya udah > mengarah > > > ke > > > SQL Server koq... wakakakaka... malah parameter input "DBAccess as > > > String" > > > di function-header ngga kepake blass... > > > > > > klo connect ke access, mustinya connectionstringnya jadi kya something > > > like > > > this : > > > > > > cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" > > > & DBAccess & _ > > > ";Persist Security Info=False" > > > > > > n klo mo connect ke SQL server, mending function headernya u ganti aja > > > deh > > > misal : > > > > > > Public Function Export2XL(InitRow As Long, DBServer As String, DBUser > as > > > string, DBPassword as string, DBSchema as string, DBTable As String) > As > > > Long > > > > > > ...biar masih memfasilitasi konsep 'reusable' dari sebuah routines. > > > > > > trus connection-stringnya u sesuaikan jadi kya gini : > > > > > > cn.connectionstring = "Provider=SQLOLEDB;" _ > > > & "Data Source = " & DBServer & ";" _ > > > & "Initial Catalog = " & DBSchema & ";" _ > > > & "User ID = " & DBUser & ";" _ > > > & "Password = " & DBPassword & ";" > > > > > > ~~ > > > jadi, klo mo manggil function nya, > > > > > > Export2XL 3, "serv_bandung", "aconx", "barakuda", "Northwind", > > > "Publisher" > > > > > > diasumsikan, kmu connect ke SQL Server di "serv_bandung", make > > > otoritasnya > > > user "aconx" yg punya password "barakuda" dengan database yg mo dipake > > > adalah "Northwind" n tabel yg mo di-export ke excel namanya > "Publisher" > > > > > > hehehe... aku masih merasa aneh contoh mu tu bisa kamu jalanin beneran > > > apa > > > ngga. soalnya ngga ada perintah yang memfasilitasi mbuka koneksi ke > file > > > access BIBLIO.MDB blass di codingmu :D > > > > > > peace love n respect, > > > > > > Rizky Prihanto > > > ~~~~~~~~~~~~~~~~~~~ > > > Office : C4 Computer Communities Club(http://www.cee-papat.com) > > > Personal : http://www.software-arsitek.web.id > > > > > > > > > On 11/4/07, Junior <[EMAIL PROTECTED] <bhu.junior%40gmail.com>> > > > wrote: > > > > > > > > Doni Rahadian wrote: > > > > > saya punya contoh perintah untuk mengimport data di tabel access > ke > > > > excel : > > > > > > > > > > Export2XL 3, ".\BIBLIO.MDB", "PUBLISHERS" > > > > > > > > > > untuk koding di modulenya : > > > > > > > > > > Public Function Export2XL(InitRow As Long, DBAccess As String, > > > DBTable > > > > As > > > > > String) As Long > > > > > > > > > > Dim cn As New ADODB.Connection 'Use for the connection string > > > > > Dim cmd As New ADODB.Command 'Use for the command for the DB > > > > > Dim rs As New ADODB.Recordset 'Recordset return from the DB > > > > > Dim MyIndex As Integer 'Used for Index > > > > > Dim MyRecordCount As Long 'Store the number of record > > > > on > > > > > the table > > > > > Dim MyFieldCount As Integer 'Store the number of > > > > fields > > > > > or column > > > > > Dim ApExcel As Object 'To open Excel > > > > > Dim MyCol As String > > > > > Dim Response As Integer > > > > > > > > > > Set ApExcel = CreateObject("Excel.application") 'Creates an object > > > > > > > > > > ApExcel.Visible = True 'This > > > > enable > > > > > you to see the process in Excel > > > > > ApExcel.Workbooks.Add 'Adds a new > > > > book. > > > > > > > > > > 'Set the connection string > > > > > cn.ConnectionString = "Provider=sqloledb.1;" & _ > > > > > "Password=barakuda;" & _ > > > > > "user id = aconx;" & _ > > > > > "initial catalog=merchant;" & _ > > > > > "data source = svr_bandung" > > > > > > > > > > 'Open the connection > > > > > cn.Open > > > > > > > > > > 'Check that the connection is open > > > > > If cn.State = 0 Then cn.Open > > > > > Set cmd.ActiveConnection = cn > > > > > cmd.CommandText = DBTable > > > > > cmd.CommandType = adCmdTable > > > > > Set rs = cmd.Execute > > > > > 'Count the number of fields or column > > > > > MyFieldCount = rs.Fields.Count > > > > > > > > > > 'Fill the first line with the name of the fields > > > > > For MyIndex = 0 To MyFieldCount - 1 > > > > > ApExcel.Cells(InitRow, (MyIndex + 1)).Formula = rs.Fields > > > > (MyIndex).Name > > > > > 'Write Title to a Cell > > > > > ApExcel.Cells(InitRow, (MyIndex + 1)).Font.Bold = True > > > > > ApExcel.Cells(InitRow, (MyIndex + 1)).interior.colorindex = 36 > > > > > ApExcel.Cells(InitRow, (MyIndex + 1)).WrapText = True > > > > > Next > > > > > > > > > > 'Draw border on the title line > > > > > MyCol = Chr((64 + MyIndex)) & InitRow > > > > > ApExcel.Range("A" & InitRow & ":" & MyCol).Borders.Color = RGB(0, > 0, > > > 0) > > > > > MyRecordCount = 1 + InitRow > > > > > > > > > > 'Fill the excel book with the values from the database > > > > > Do While rs.EOF = False > > > > > For MyIndex = 1 To MyFieldCount > > > > > ApExcel.Cells(MyRecordCount, MyIndex).Formula = rs((MyIndex - > > > > > 1)).Value 'Write Value to a Cell > > > > > ApExcel.Cells(MyRecordCount, MyIndex).WrapText = False 'Format the > > > > Cell > > > > > Next > > > > > MyRecordCount = MyRecordCount + 1 > > > > > rs.MoveNext > > > > > If MyRecordCount > 50 Then > > > > > Exit Do > > > > > End If > > > > > Loop > > > > > > > > > > 'Suggest to the user to save it's work > > > > > Response = MsgBox("Save the Excel Sheet and clik OK", vbOKOnly, > > > "Save > > > > your > > > > > file") > > > > > > > > > > 'Close the connection with the DB > > > > > rs.Close > > > > > > > > > > 'Return the last position in the workbook > > > > > Export2XL = MyRecordCount > > > > > > > > > > End Function > > > > > > > > > > HASILNYA BERJALAN, yang mau saya tanyakan bagaimana koding untuk > > > > database > > > > > menggunakan sql server 2000 > > > > > > > > > > MOHON SEKALI PENCERAHANNYA ...thanks all vb-ers > > > > > > > > > > > > > > > > > > > [Non-text portions of this message have been removed] > > > > > > > > > > > > > > > > [Non-text portions of this message have been removed]
