Listview to Class

[marina9]

Is it possible to pass an entire listview control into a class function? What I have is a form that contains a listview control. The listview has field values obtained from a recordset. I have a button on the form that should export the listview values into an Excel spreadsheet. I would like to create class that contains all the coding for the Excel export rather than placing the code into the "Export" button of the form. Is there a way to pass an entire listview control and it's contents into a class? Thanks for any help!

 

[chiph]

Sure, you just declare the parameter in your class's method to be of type ListBox.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[marina9]

Thanks for the reply Chiph, but I am trying to use a listview instead of a listbox. Is there a way to pass a listview with all it's properties (columnheaders, subitems, etc.)?

 

[marina9]

I finally figured it out, so I wanted to post the results for anyone else who may be having this issue:

============ Code in the form ==============

Private Sub cmdExport_Click()
'call export sub and pass listview control
Call Export(ltvBSResults)
End Sub

Private Sub Export(Listview As Listview)
Dim objExport As CExcel
Dim intComplete As Integer

Set objExport = New CExcel

objExport.Listview = Listview
intComplete = objExport.Export

If intComplete = 1 Then
MsgBox "Export is complete", vbOKOnly, "Excel Export"
End If
End Sub

============ Code in the class ==============

Option Explicit

Private LV As Listview

Property Let Listview(Listview As Object)
Set LV = Listview
End Property

Public Function Export() As Integer

Dim objExcel As Excel.Application
Dim xlsWorkbook As Excel.Workbook
Dim intC As Integer
Dim intCol As Integer

Set objExcel = New Excel.Application
Set xlsWorkbook = objExcel.Workbooks.Add

objExcel.WindowState = xlMinimized
objExcel.Visible = True

'print titles to excel spreadsheet
If TypeOf LV Is Listview Then
For intC = 1 To LV.ColumnHeaders.Count
objExcel.ActiveSheet.Cells(1, intC) = LV.ColumnHeaders(intC).Text
Next

'print contents of listview in excel
For intC = 1 To LV.ListItems.Count
objExcel.ActiveSheet.Cells(intC + 1, 1) = LV.ListItems.Item(intC).Text
For intCol = 1 To (LV.ColumnHeaders.Count - 1)
objExcel.ActiveSheet.Cells(intC + 1, intCol + 1) = LV.ListItems(intC).ListSubItems(intCol).Text
Next
Next
End If

'format excel spreadsheet
With objExcel
.Columns("B:B").Select
.Selection.NumberFormat = "0"
.Rows("1:1").Select
.Selection.Font.Bold = True
.Columns("A:L").Select
.Selection.Columns.AutoFit
.Selection.HorizontalAlignment = xlLeft
.Range("A1").Select
End With

Set objExcel = Nothing

Export = 1

End Function


Hope this helps someone else!