docmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel12Xml,"SELECT * FROM MKS","c:\test.xlsx",true
jadi cExcel bisa digunakan meng-ekspor recordset2 yang hanya di buka di memori kompi saja.
:)
aksan kurdin
On 4/27/2011 1:07 PM, sudarsono wrote:
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.
<deleted>
--- 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
> >
> >
>
No comments:
Post a Comment