Trong bài viết này, chúng tôi sẽ tìm nạp dữ liệu từ sổ làm việc đã đóng vào hộp Danh sách trong biểu mẫu người dùng bằng cách sử dụng VBA.

Dữ liệu thô cho ví dụ này nằm trong phạm vi A2: B10 trên sổ làm việc “23SampleData.xls”, được đặt trong đường dẫn tệp “D: \ Excelforum \ ExcelForum office \ excel tip old code \ Shared Macro \ 23 \”.

ArrowSampleData

Chúng tôi đã tạo hai nút lệnh trên trang tính chính để chạy hai biểu mẫu người dùng khác nhau. Mỗi nút lệnh được liên kết với các dạng người dùng khác nhau.

ArrowMainSheet

Giải thích logic

Trong ví dụ này, hai cách khác nhau được sử dụng để tìm nạp dữ liệu từ sổ làm việc đã đóng. Đó là:- . Mở sổ làm việc đã đóng và lấy dữ liệu. Sử dụng kết nối ADODB

Mở sổ làm việc đã đóng và lấy dữ liệu

Có thể đặt thuộc tính RowSource của điều khiển ListBox để lấy dữ liệu từ sổ làm việc khác bằng cách gán giá trị cho thuộc tính RowSource như sau:

‘[Filename.xls] Sheet1 ?! $ B $ 1: $ B $ 15 Điều khiển ListBox sẽ chỉ hiển thị các giá trị khi sổ làm việc khác đang mở.

Vì vậy, để tìm nạp dữ liệu từ sổ làm việc đã đóng, chúng tôi sẽ tạo macro để mở sổ làm việc khác mà người dùng không nhận thấy nó và tìm nạp dữ liệu từ sổ làm việc để thêm các mục trong Hộp danh sách và đóng sổ làm việc.

Nhấp vào nút “Chọn” sẽ kích hoạt biểu mẫu người dùng “UserForm1”.

Sự kiện khởi tạo của biểu mẫu người dùng được sử dụng để thêm các mục trong hộp danh sách. Sự kiện này trước hết mở sổ làm việc đã đóng và sau đó chỉ định giá trị trong phạm vi cho biến thể “ListItems”. Sau khi gán giá trị, sổ làm việc được đóng và các mục được thêm vào hộp danh sách.

ArrowClickingSelectButton

Hộp danh sách được sử dụng để chọn tên từ các giá trị danh sách hiện có. Nhấn nút “OK” sẽ hiển thị tên đã chọn.

ArrowSelectOutput

Sử dụng kết nối ADODB

ActiveX Data Objects (ADO) là một giao diện cấp cao, dễ sử dụng cho kết nối OLE DB. Nó là một giao diện lập trình để truy cập và thao tác dữ liệu trong cơ sở dữ liệu.

Để tạo kết nối ADODB, chúng ta sẽ cần thêm thư viện ADO vào dự án.

Để thêm tham chiếu, hãy chọn từ menu Công cụ> Tham chiếu.

ArrowAddingReference

Nhấp vào nút “Kết nối ADODB” trên trang tính sẽ kích hoạt biểu mẫu người dùng “UFADODB”. Trong sự kiện khởi tạo của biểu mẫu người dùng này, chúng tôi đã sử dụng kết nối ADODB để tìm nạp dữ liệu từ sổ làm việc đã đóng. Chúng tôi đã tạo một hàm tùy chỉnh do người dùng xác định (UDF) “ReadDataFromWorkbook” để thiết lập kết nối và tìm nạp dữ liệu từ sổ làm việc đã đóng sang mảng.

Chúng tôi đã sử dụng một UDF “FillListBox” khác để thêm các mục vào hộp Danh sách trong quá trình khởi tạo biểu mẫu người dùng. Hộp Danh sách sẽ hiển thị dữ liệu trong hai cột, một cột chứa tên và cột thứ hai chứa tuổi.

ArrowClickingADODBConnection

Nhấn nút “OK” sau khi chọn mục trong hộp Danh sách sẽ hiển thị thông báo thông tin về mục đã chọn.

ArrowADODBOutput

Vui lòng theo dõi bên dưới để biết mã

Option Explicit

Sub running()

UserForm1.Show

End Sub

Sub ADODBrunning()

UFADODB.Show

End Sub

'Add below code in UFADODB userform

Option Explicit

Private Sub CommandButton1_Click()

Dim name1 As String

Dim age1 As Integer

Dim i As Integer

'Assign the selected value in list box to variable name1 and age1

For i = 0 To ListBox1.ListCount - 1

If ListBox1.Selected(i) Then

name1 = ListBox1.Value

age1 = ListBox1.List(ListBox1.ListIndex, 1)

Exit For

End If

Next

'Unload the userform

Unload Me

'Displaying output

MsgBox "You have selected " & name1 & ". His age is " & age1 & " yrs."

End Sub

Private Sub UserForm_Initialize()

'Filling ListBox1 with data from a closed workbook

Dim tArray As Variant

'Calling function ReadDataFromWorkbook for getting data from specified range to array

'Change path according to your requirement, "Sample_data" is named defined range

tArray = ReadDataFromWorkbook("D:\Excelforum\ExcelForum office\excel tip old code\Shared Macro\23\23SampleData.xls", "Sample_Data")

'Calling function FillListBox  for adding items in List Box

'Assign List box object and tarray as parameter

FillListBox Me.ListBox1, tArray

'Releasing array variables and deallocate the memory used for their elements.

Erase tArray

End Sub

Private Sub FillListBox(lb As MSForms.ListBox, RecordSetArray As Variant)

'Filling List box lb with data from RecordSetArray

Dim r As Long, c As Long

With lb

.Clear

'Assigning value to listbox

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

'Selecting no item in the List box by default

.ListIndex = -1

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)

Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset

Dim dbConnectionString As String

'Declaring a connection string and the driver require for establishing connection

dbConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};ReadOnly=1;DBQ=" & SourceFile

'Creating a new ADODB connection

Set dbConnection = New ADODB.Connection

On Error GoTo InvalidInput

'Open the database connection

dbConnection.Open dbConnectionString

'Getting the recordset from defined named range

Set rs = dbConnection.Execute("[" & SourceRange & "]")

On Error GoTo 0

'Returns a two dimensional array with all records in rs

ReadDataFromWorkbook = rs.GetRows

'Close the recordset and database connection

rs.Close

dbConnection.Close

Set rs = Nothing

Set dbConnection = Nothing

Exit Function

'Code for handling error

InvalidInput:

MsgBox "The source file or source range is invalid!", _

vbExclamation, "Get data from closed workbook"

End Function

'Add below code in UserForm1

Option Explicit

Private Sub CommandButton1_Click()

Dim name1 As String

Dim i As Integer

'Assign the selected value to variable name1

For i = 0 To ListBox1.ListCount - 1

If ListBox1.Selected(i) Then

name1 = ListBox1.Value

Exit For

End If

Next

'Unload the userform

Unload Me

'Display the selected name

MsgBox "You have selected " & name1 & "."

End Sub

Private Sub UserForm_Initialize()

Dim ListItems As Variant, i As Integer

Dim SourceWB As Workbook

'Turning screen updates off

Application.ScreenUpdating = False

With Me.ListBox1

'Remove existing entries from the listbox

.Clear

'Open the source workbook as ReadOnly

Set SourceWB = Workbooks.Open("D:\Excelforum\ExcelForum office\excel tip old code\Shared Macro\23\23SampleData.xls", _

False, True)

'Get the range of values you want

ListItems = SourceWB.Worksheets(1).Range("A2:A10").Value

'Close the source workbook without saving changes

SourceWB.Close False

Set SourceWB = Nothing

Application.ScreenUpdating = True

'Convert values to a vertical array

ListItems = Application.WorksheetFunction.Transpose(ListItems)

For i = 1 To UBound(ListItems)

'Populate the listbox

.AddItem ListItems(i)

Next i

'Selecting no items by default, set to 0 to select the first item

.ListIndex = -1

End With

End Sub

Nếu bạn thích blog này, hãy chia sẻ nó với bạn bè của bạn trên Facebook và Facebook.

Chúng tôi rất muốn nghe ý kiến ​​từ bạn, hãy cho chúng tôi biết cách chúng tôi có thể cải thiện công việc của mình và làm cho nó tốt hơn cho bạn. Viết thư cho chúng tôi [email protected]