Working with a List

[basura]

Hello VBA Wiseguys ;-)

I'm not too familiar with VBA programming (I do have Java experience) and I would like to do the following:

From a multiple select listbox I want the selected rows to be exported in a Excel document. Furthermore I want to keep track of the rows that I have exported.

This is as far as I came:

Dim listVar As ???
Set listVar = Me!
[List0]
DoCmd.OutputTo acOutputTable, listVar , "MicrosoftExcel(*.xls)", "C:\RESULT_TABLE.xls", True

As you can see, I have the multiple select Listbox working but I don't know how or to what I can convert the List. The doc specs say:

DoCmd.OutputTo objecttype[, objectname][, outputformat][, outputfile][, autostart][, templatefile]

Objecttype - may be any of the following constants:

• acOutputForm​
• acOutputModule ​
• acOutputQuery ​
• acOutputReport ​
• acOutputTable​

Anyone that may help me out ? Maybe some good examples ?

Thanks for any help provided !

 

[namliam]

Type Selected in your immediate window, hit F1... See how far that example gets you...

 

[basura]

Type Selected in your immediate window, hit F1... See how far that example gets you...

Selected Property Example
The following example uses the Selected property to move selected items in the lstSource list box to the lstDestination list box. The lstDestination list box's RowSourceType property is set to Value List and the control's RowSource property is constructed from all the selected items in the lstSource control. The lstSource list box's MultiSelect property is set to Extended. The CopySelected( ) function is called from the cmdCopyItem command button.

Sub cmdCopyItem_Click()
    CopySelected Me
End Sub

Function CopySelected(frm As Form) As Integer
    Dim ctlSource As Control
    Dim ctlDest As Control
    Dim strItems As String
    Dim intCurrentRow As Integer
    Set ctlSource = frm!lstSource
    Set ctlDest = frm!lstDestination
    For intCurrentRow = 0 To ctlSource.Listcount - 1
        If ctlSource.Selected(intCurrentRow) Then
            strItems = strItems & ctlSource.Column(0, _
                 intCurrentRow) & ";"
        End If
    Next intCurrentRow
    ' Reset destination control's RowSource property.
    ctlDest.RowSource = ""
    ctlDest.RowSource = strItems
End Function

Thx for your response, I tried that allready but I got stucked on
ctlDest which is of type Control.... How can I convert this to an object that is accepted by the doCmd.outputTo ?

 

[namliam]

Well the part you are intrested in is retrieving the selected items which is this part:

    For intCurrentRow = 0 To ctlSource.Listcount - 1
        If ctlSource.Selected(intCurrentRow) Then
            strItems = strItems & ctlSource.Column(0, _
                 intCurrentRow) & ";"
        End If
    Next intCurrentRow

Along with some declerations offcourse.
Now you can fetch this into "strItems" beeing a variable or do something else, like write to a file or table.
But you cannot use outputto or anything to export it directly.

Type Open and hit F1 to find out how to write to a file (VBA option)
Type Recordset and hit F1 to write to a table, use the DAO variant! Not ADO as Access = DAO.

Good Luck ! And post back if you run into more problems.

 

[basura]

It works

After doing some searching on the internet and the directional tips i got it working. Thanks a lot !