ListBox-kontroller med verdier fra en annen arbeidsbok

Fra en annen åpen arbeidsbok:

Det er mulig å sette RowSource-egenskapen til en ListBox- eller ComboBox-kontroll til å hente data fra en annen arbeidsbok ved for eksempel å fylle inne dette:

'[Filnavn.xls]Ark1'!$B$1:$B$15

For at kontrollen skal vise verdiene fra den andre arbeidsboken må denne være åpen.

Fra en lukket arbeidsbok som åpnes og lukkes igjen uten at brukeren ser dette

Ved hjelp av makroen nedenfor kan man fylle inn en ListBox-kontroll med data fra en annen arbeidsbok som er lukket. Makroen åpner den andre arbeidsboken uten at brukeren ser dette, fyller inn de ønskede dataene og lukker arbeidsboken uten å lagre noen endringer. Makroen skal skrives inn i UserForm-ens modulark.

Private Sub UserForm_Initialize()
Dim ListItems As Variant, i As Integer
Dim SourceWB As Workbook
    With Me.ListBox1
        .Clear ' remove existing entries from the listbox
        ' turn screen updating off, prevent the user from seeing the source workbook being opened
        Application.ScreenUpdating = False 
        ' open the source workbook as ReadOnly
        Set SourceWB = Workbooks.Open("C:\FolderName\SourceWorkbook.xls", False, True)
        ListItems = SourceWB.Worksheets(1).Range("B2:B21").Value ' get the values you want
        SourceWB.Close False ' close the source workbook without saving changes
        Set SourceWB = Nothing
        ListItems = Application.WorksheetFunction.Transpose(ListItems) ' convert values to a vertical array
        For i = 1 To UBound(ListItems)
            .AddItem ListItems(i) ' populate the listbox
        Next i
        .ListIndex = -1 ' no items selected, set to 0 to select the first item
        Application.ScreenUpdating = True
    End With
End Sub

Fra en lukket arbeidsbok ved hjelp av ADO

Ved hjelp av disse makroene kan man fylle en listeboks/komboboks med data fra en arbeidsbok uten å åpne den. Makroene skal skrives inn i UserForm-ens modulark.

Private Sub UserForm_Initialize()
' fill ListBox1 with data from a closed workbook
' can also be used from other applications to read data from an open workbook
Dim tArray As Variant
    tArray = ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls", "A1:B21")
    FillListBox Me.ListBox1, tArray
    Erase tArray
End Sub

Private Sub FillListBox(lb As MSForms.ListBox, RecordSetArray As Variant)
' fills lb with data from RecordSetArray
Dim r As Long, c As Long
    With lb
        .Clear
        For r = LBound(RecordSetArray, 2) To UBound(RecordSetArray, 2)
            .AddItem
            For c = LBound(RecordSetArray, 1) To UBound(RecordSetArray, 1)
                .List(r, c) = RecordSetArray(c, r)
            Next c
        Next r
        .ListIndex = -1 ' no item selected
    End With
End Sub

Private Function ReadDataFromWorkbook(SourceFile As String, SourceRange As String) As Variant
' requires a reference to the Microsoft ActiveX Data Objects library 
' (menu Tools, References in the VBE)
' if SourceRange is a range reference:
'   this function can only return data from the first worksheet in SourceFile
' if SourceRange is a defined name reference:
'   this function can return data from any worksheet in SourceFile
' SourceRange must include the range headers
' examples:
' varRecordSetData = ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls", "A1:A21")
' varRecordSetData = ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls", "A1:B21")
' varRecordSetData = ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls", "DefinedRangeName")
Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset
Dim dbConnectionString As String
    dbConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};ReadOnly=1;DBQ=" & SourceFile
    Set dbConnection = New ADODB.Connection
    On Error GoTo InvalidInput
    dbConnection.Open dbConnectionString ' open the database connection
    Set rs = dbConnection.Execute("[" & SourceRange & "]")
    On Error GoTo 0
    ReadDataFromWorkbook = rs.GetRows ' returns a two dim array with all records in rs
    dbConnection.Close ' close the database connection
    rs.Close
    Set rs = Nothing
    Set dbConnection = Nothing
    On Error GoTo 0
    Exit Function
InvalidInput:
    MsgBox "The source file or source range is invalid!", vbExclamation, _
        "Get data from closed workbook"
    Set rs = Nothing
    Set dbConnection = Nothing
End Function

Fra en lukket arbeidsbok ved hjelp av formler/linker

Dersom man trenger å hente opplysninger fra en annen arbeidsbok kan man lage et (skjult) regneark som inneholder regnearkformler som henter opplysninger fra den lukkede arbeidsboken. Formelen kan f.eks. se slik ut:

='C:\FolderName[SourceWbName.xls]WorkSheetName'!A1

Deretter kopierer man denne formelen så langt nedover og bortover som man behøver for å hente inn de ønskede verdiene fra den lukkede arbeidsboken.

UserForm-ens RowSource egenskap setter man til dette regnearkområdet (i det skjulte regnearket) som inneholder dataene fra den lukkede (eller åpne) arbeidsboken.
Den eneste ulempen med denne metoden er at brukeren vil bli spurt om koblingene mellom de to arbeidsbøkene skal oppdateres hver gang arbeidsboken med linkene åpnes.

Dokumentet er sist oppdatert 2000-09-16 22:18:23 Utskriftsvennlig versjon