1

I have a working VBA code that exports data from MS Access data and pastes it into MS Excel Sheet cells and using cell range as RowSource to appear data in the ListBox.

Is there a way to paste directly the imported data into ListBox instead of pasting into Sheet cells?

Sub IBDocsLibSearch()

    Dim cnn As ADODB.Connection 'dim the ADO collection class
    Dim rs As ADODB.Recordset 'dim the ADO recorset class
    Dim dbPath As String
    Dim MyDbPassword As String
    Dim SQL As String
    Dim i As Integer
    Dim var1

    Application.ScreenUpdating = False

    IBDocLibSheet.Range("A2:I500000").ClearContents

    dbPath = LinkSheet.Range("C4").Value 'Inbound Checklist Database Location
    MyDbPassword = PWSheet.Range("C3").Value 'Password to connect the Excel to Access

    Set var1 = IBUserForm.IBDTextSerialNo 

    'Initialise the collection class variable
    Set cnn = New ADODB.Connection

    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath & ";Jet OLEDB:Database Password=" & MyDbPassword

    SQL = "SELECT * FROM DB_IBDocuments WHERE SerialNo = '" & var1.Value & "'"

    Set rs = New ADODB.Recordset 

    rs.Open SQL, cnn

    If rs.EOF And rs.BOF Then
        'Close the recordset and connection
        rs.Close
        cnn.Close
        'Clear Memory
        Set rs = Nothing
        Set cnn = Nothing
    Application.ScreenUpdating = True
    Exit Sub
    End If

    IBDocLibSheet.Range("A2").CopyFromRecordset rs '----This is where to paste the extracted data

    'To show results in Listbox
    IBUserForm.IBDListBox.RowSource = "IBL_DocLib"

    'Close the recorset and connections
    rs.Close
    cnn.Close
    'Clear memory
    Set rs = Nothing
    Set cnn = Nothing

    Application.ScreenUpdating = True
End Sub
2

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.