[belajar-access] Re: class dan Class Module

Sungguh menarik bila dapat mengirim data baik barupa tabel atau query ke sheet di excel.

walaupun dengan macro transfetSpredSheet dapat dilakukan.

dalam hal ini contoh module dan class module yang bagus.

Tambahan sesuai dengan kaidah OOP :
1. variabel Private hanya dapat di akses
oleh class tsb. Tak dapat diakses dari kelas turunannya .
2. property dan public berarti
dimana dapat di akses di kelas tsb maupun turunannya.
3. variabel public dan property bedanya dimana ?
menurut pengalaman saya , jika berada di desain GUI , property
akan muncul. ( jadi di vba editot , ya sama aja ).

Trim's master , atas koding -nya ..

catatan :
silakan berkreasi dgn koding tsb...

--- In belajar-access@yahoogroups.com, Aksan Kurdin <aksan.kurdin@...> wrote:
>
> 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
> >
> >
>

------------------------------------

SPAM IS PROHIBITEDYahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/belajar-access/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/belajar-access/join
(Yahoo! ID required)

<*> To change settings via email:
belajar-access-digest@yahoogroups.com
belajar-access-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
belajar-access-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/