Trong bài viết này, tôi sẽ giải thích cách bạn có thể lấy tên của tất cả các bảng trong cơ sở dữ liệu truy cập bằng VBA
–
Nhận tên bảng
Trong ví dụ bên dưới, tên của các bảng sẽ được lưu trữ trong một mảng chuỗi. Cơ sở dữ liệu được sử dụng trong các ví dụ này có 3 bảng
- Bảng 1
- Ban 2
- Bàn số 3
Ghi chú. Đoạn mã dưới đây sử dụng ADOX. đối tượng danh mục. Để sử dụng đối tượng này, bạn có thể cần thêm tham chiếu đến thư viện đối tượng của nó. Để biết thêm thông tin xin vui lòng xem liên kết dưới đây
- Truy cập VBA ADOX. Danh mục, Thư viện đối tượng
Sub Example[]
Dim arrTables[1 To 100] As String
Dim objCatalog As ADOX.Catalog
Dim i As Integer
Dim intIndex As Integer
Set objCatalog = New ADOX.Catalog
'connect catalog object to database
objCatalog.ActiveConnection = CurrentProject.Connection
intIndex = 1
'loop through the tables in the catalog object
For i = 0 To objCatalog.Tables.Count - 1
'checks if the table is a user defined table
If objCatalog.Tables.Item[i].Type = "TABLE" Then
arrTables[intIndex] = objCatalog.Tables.Item[i].Name
intIndex = intIndex + 1
End If
Next i
End Sub
Ghi chú. Nếu bạn không muốn thêm tham chiếu vào thư viện, bạn có thể khởi tạo đối tượng Danh mục bằng phương thức bên dưới
Dim objCatalog As Object
Set objCatalog = createobject["ADOX.Catalog"]
Bạn có thể tải xuống tệp và mã được sử dụng trong bài viết này từ liên kết bên dưới
- Nhận tên bảng. khóa kéo
Nếu bạn cần hỗ trợ về mã của mình hoặc bạn đang tìm kiếm một lập trình viên VBA để thuê, vui lòng liên hệ với tôi. Ngoài ra, vui lòng truy cập trang web của tôi www. phần mềm-giải pháp-trực tuyến. com
Khi xử lý các bảng Excel, chúng ta thường có thể quên cách đặt tên quy ước và nhóm của chúng có thể hữu ích như thế nào trong công việc của chúng ta
Trong ví dụ bên dưới, chúng tôi sẽ trình bày cách sử dụng tên bảng và tham chiếu chúng dễ dàng hơn trong các trang tính khác nhau trong sổ làm việc của chúng tôi. Sau đó, chúng tôi sẽ chỉ ra cách tham chiếu động các tên bảng, tôi. e. giữ tham chiếu đến bảng trong các trang tính khác nhau trong khi thay đổi tên bảng
Sử dụng công thức gián tiếp để tham khảo bảng
Ví dụ của chúng tôi, chúng tôi sẽ tạo ba bảng sẽ hiển thị kết quả bán hàng từ ba nhân viên bán hàng hàng đầu của mỗi chi nhánh
Đầu tiên, chúng ta sẽ tạo một danh sách đơn giản, như hình bên dưới
Sau đó, chúng tôi sẽ chọn dữ liệu của mình và chuyển đến Chèn >> Bảng. Một cửa sổ bật lên sẽ xuất hiện và nó sẽ yêu cầu chúng tôi xác nhận lại phạm vi của chúng tôi
Khi chúng tôi nhấp vào OK, bảng của chúng tôi sẽ được tạo
Để xác định tên bảng, chúng ta cần nhấp vào bất kỳ đâu trên bảng, sau đó chuyển đến tab Thiết kế bảng. Khi đó, chúng tôi sẽ chuyển đến Thuộc tính và đổi tên bảng của chúng tôi thành Nhánh1
Chúng tôi cũng sẽ làm điều này cho các nhánh khác và kết quả là chúng tôi sẽ có ba nhánh
Điều tiếp theo, chúng tôi sẽ tạo một trang tính khác mà chúng tôi sẽ gọi đơn giản là „Công thức gián tiếp“ vì đây là công thức chúng tôi sắp sử dụng
Chúng tôi sẽ nhấp vào ô B1, đi tới và sau đó chuyển đến tab Dữ liệu >> Công cụ dữ liệu >> Xác thực dữ liệu
Khi chúng tôi nhấp vào nó, chúng tôi sẽ được chuyển đến cửa sổ bật lên Xác thực dữ liệu. Trên cửa sổ này, chúng tôi sẽ chọn Danh sách bên dưới tùy chọn Cho phép và chúng tôi sẽ nhập tên của các bảng [Nhánh1, Nhánh2 và Nhánh3] trong phần Nguồn
Khi chúng tôi nhấp vào OK, bạn sẽ nhận thấy rằng chúng tôi đã tạo một danh sách thả xuống trong ô B1
Ở hàng thứ tư, trong ô B4, chúng ta sẽ nhập công thức sau
1
=SUM[ GIÁN TIẾP[B1]]
Công thức này sẽ TỔNG các số trong bảng mà chúng ta sẽ chọn ở ô B1. Đối với bài tập của chúng tôi, chúng tôi sẽ chọn Nhánh2
Bạn sẽ nhận thấy rằng chúng tôi được trình bày với con số $106. 137. 00. Đây là con số tương tự mà chúng tôi sẽ nhận được nếu chúng tôi tổng hợp tất cả doanh số bán hàng cho Chi nhánh số 2 trong trang tính đầu tiên
Vì vậy, với công thức Gián tiếp, chúng ta có thể tự động tham chiếu các bảng của mình trong các trang tính khác nhau và sử dụng các công thức khác nhau [MAX, MIN, SUM, v.v. ] để có được kết quả mà chúng tôi muốn từ bảng ban đầu của chúng tôi
Ví dụ: chúng tôi sẽ sử dụng đầu vào công thức MIN kết hợp với INDIRECT trong ô B7
1
=MIN[ GIÁN TIẾP[B1]]
Ta sẽ được kết quả như sau
Cái hay của công thức này là chúng ta chỉ cần thay đổi ô B1 để thu được kết quả tương tự nhưng ở các nhánh khác nhau
Tự động tham chiếu các bảng
Vấn đề mà chúng tôi hiện có là- bất kỳ người dùng nào cũng có thể thay đổi tên của các bảng của chúng tôi. Trong trường hợp đó, danh sách của chúng ta trong sheet “Công thức gián tiếp” sẽ vô giá trị
May mắn thay, cũng có một giải pháp cho vấn đề này và nó có thể được giải quyết bằng cách thêm công thức thông qua VBA
Để làm điều này, chúng tôi sẽ mở Mô-đun của mình trong VBA bằng cách nhấp vào ALT + F11 và sau đó chèn Mô-đun bằng cách nhấp chuột phải vào cửa sổ bên trái
Khi chúng tôi có Mô-đun của mình, chúng tôi sẽ nhập công thức sau vào đó
1
2
3
4
5
6
7
8
Hàm Tên bảng[ô As Range] As String
Mờ Tên Như Chuỗi
Tên = vbNullString
Bật Lỗi Tiếp tục Tiếp theo
Tên = ô. ListObject. Tên
Tên bảng = Tên
Kết thúc Hàm
Chức năng này trông như thế này trong mô-đun của chúng tôi
Những gì nó làm là trước tiên nó tạo một hàm có tên là “TableName” với một tham số được gọi là một ô, sẽ được định nghĩa là một phạm vi. Toàn bộ chức năng sẽ được định dạng dưới dạng một chuỗi, vì chúng ta cần trả về tên của bảng
1
Hàm Tên bảng[ô As Range] As String
Đối với điều tiếp theo, chúng tôi khai báo biến „Name“ là một chuỗi và chúng tôi xác định nó bằng một chuỗi null. Đây là hằng số biểu thị chuỗi null
Chúng tôi xác định rằng nếu chúng tôi có lỗi, chúng tôi sẽ bỏ qua nó
1
2
3
4
Mờ Tên Là Chuỗi
Tên = vbNullString
Bật Lỗi Tiếp tục Tiếp theo
Đối với phần cuối cùng, chúng tôi xác định biến „Name“ bằng với tên của đối tượng chứa ô của chúng tôi [chúng tôi cần chỉ ra bất kỳ ô nào trong bảng của mình để truy xuất tên của bảng trong trang tính],
1
2
3
Tên = ô. ListObject. Tên
Tên bảng = Tên
Chúng tôi sẽ thay đổi tên của bảng đầu tiên thành Branch148 trong thuộc tính bảng. Sau đó, chúng tôi sẽ gọi công thức của mình và trỏ đến bất kỳ ô nào trong bảng đầu tiên [trong trường hợp này là ô B2]
Ta làm tương tự cho bàn 2 [đổi tên thành CN 422] và bàn 3 [đổi tên thành CN 512]. Chúng tôi sẽ nhận được kết quả sau khi chúng tôi áp dụng công thức của chúng tôi
Bước cuối cùng, chúng ta cần vào sheet “Công thức gián tiếp” và thay đổi phạm vi của Xác thực dữ liệu như sau
Bây giờ, nếu chúng tôi tình cờ thay đổi tên bảng của mình trong thuộc tính bảng, chúng tôi có công thức sẽ tạo ra thay đổi này và chúng tôi có xác thực dữ liệu sẽ luôn lấy dữ liệu từ các ô áp dụng công thức của chúng tôi