MS ACCESS MAILING LIST: Re: [belajar-access] class dan Class Module

Contoh class module berikut sudah jadi library sehari-hari bergaul dengan access to excel :)

Class: cls_Excel
property: Recordset, Company, Title, ReportDate
method: ExportRStoExcel

contoh penggunaan:

Private Sub PrintList()      Dim cExcel As cls_Excel      On Error GoTo errHandle     Set cExcel = New cls_Excel     With cExcel         .Company = GetSetup("CompName") 'Get Company Name from Setup Table         .Title = Me.Caption         .ReportDate = Date         Set .Recordset = mrs_Data 'mrs_Data is an ADO recordset object         .ExportRStoExcel     End With     DoEvents     Set cExcel = Nothing     Exit Sub  errHandle:     ErrMsg MC_CLASSNAME & "PrintList()" 'ErrMsg is global function to dispaly error message End Sub

penjelasan kode diatas:
cExcel adalah obyek dari class module cls_Excel (lihat sourcenya di bawah).
Saat kita create instan cExcel, maka metode initialize dari class akan dijalankan untuk menciptakan satu obyek excel application di memory.
Setelah itu, kita set beberapa property dari obyek cExcel sebelum mengexport recordset ke excel application.
Metode ExportRStoExcel akan sekaligus membuka lembar baru dari excel untuk menyalin data dari dalam recordset ke cell-cell excel, lalu display ke screen. Sheet pivot sudah disiapkan, sehingga user sudah bisa langsung bermain analisa.
Setelah itu, barulah obyek cExcel di lepas dari memory.

Ini class-nya:

Option Explicit  Const MC_CLASSNAME = "cls_Excel::"  Private mrs_Data As ADODB.Recordset Private mstr_Company As String Private mstr_Title As String Private mstr_ReportDate As String Private mobj_Excel As Object  Property Get Recordset() As ADODB.Recordset     Set Recordset = mrs_Data End Property Property Set Recordset(rs As ADODB.Recordset)     Set mrs_Data = rs     mrs_Data.Filter = rs.Filter End Property  Property Let Company(data As String)     mstr_Company = data End Property  Property Let Title(data As String)     mstr_Title = data End Property  Property Let ReportDate(data As Date)     mstr_ReportDate = "As of date : " & Format(data, "dd/mmm/yyyy") End Property  Public Sub ExportRStoExcel()     Dim objWorkbook As Object     Dim objWorksheet As Object     Dim objRange As Object     Dim objField As ADODB.Field     Dim i As Integer      On Error GoTo errHandle     ' Run Excel and open a new workbook     Set objWorkbook = mobj_Excel.Workbooks.Add      ' Get a reference to the first worksheet     Set objWorksheet = objWorkbook.Sheets(1)     objWorksheet.Name = "Data"      ' Get a range reference for the first row     Set objRange = objWorksheet.Range("A1")     objRange.Cells(1, 1) = mstr_Company      Set objRange = objWorksheet.Range("A2")     objRange.Cells(1, 1) = mstr_Title          Set objRange = objWorksheet.Range("A3")     objRange.Cells(1, 1) = mstr_ReportDate          Set objRange = objWorksheet.Range("A5")     ' Loop through the fields and put the field name in the first row     i = 0     For Each objField In mrs_Data.Fields         i = i + 1         objRange.Cells(1, i) = objField.Name     Next      ' Get a reference to the second row     Set objRange = objWorksheet.Range("A6")      ' Copy recordset to the range starting from the second row     Call objRange.CopyFromRecordset(mrs_Data, mrs_Data.RecordCount, mrs_Data.Fields.Count)      ' Bold the first row     objWorksheet.Rows("1:5").Font.Bold = True     objWorksheet.Range("A5").Select     objWorksheet.PivotTableWizard     objWorkbook.ActiveSheet.Name = "Pivot"     objWorksheet.Select     ' Expend the columns to show entire content     'objWorksheet.Cells.EntireColumn.AutoFit      ' Show Excel Sheet to user     objWorksheet.Move objWorkbook.Sheets("Pivot")     mobj_Excel.Visible = True      ' Return Excel Sheet to calling routine     'Set ExportRStoExcel = objWorksheet          Exit Sub  errHandle:     ErrMsg MC_CLASSNAME & "cmdAction_Click()" End Sub  Private Sub Class_Initialize()     Set mrs_Data = New ADODB.Recordset     Set mobj_Excel = CreateObject("Excel.Application") End Sub  Private Sub Class_Terminate()     Set mrs_Data = Nothing     Set mobj_Excel = Nothing End Sub

On 4/27/2011 7:32 AM, CGSATU wrote:

Ada yang bisa kasih contoh sederhana , penggunaan sekaligus class dan Class Module....

Kalau Class sudah biasa pakai, kalau Class Module, mestinya bisa masukkan yang lain-lain semisal property...namun cari contohnya yang sederhana apa ya......

Sudah ngubek di web, belum nemu....

cgsatu