Microsoft ExcelでVBAを使用して、閉じたブックからの値でListBoxコントロールを塗りつぶし
この記事では、VBAを使用して、閉じたブックからユーザーフォームのリストボックスにデータをフェッチします。
この例の生データは、ファイルパス「D:\ Excelforum \ ExcelForumoffice \ excel tip oldcode \ SharedMacro \ 23 \」に配置されている「23SampleData.xls」ブックの範囲A2:B10にあります。
2つの異なるユーザーフォームを実行するために、メインワークシートに2つのコマンドボタンを作成しました。各コマンドボタンは、異なるユーザーフォームにリンクされています。
ロジックの説明
この例では、閉じたブックからデータをフェッチするために2つの異なる方法が使用されています。これらは:- 。閉じたブックを開き、データを取得します。 ADODB接続の使用
閉じたブックを開いてデータを取得します
次のようにRowSourceプロパティに値を割り当てることで、他のブックからデータを取得するようにListBoxコントロールのRowSourceプロパティを設定できます。
‘[filename.xls] Sheet1?!$ B $ 1:$ B $ 15 ListBoxコントロールは、他のブックが開いている場合にのみ値を表示します。
したがって、閉じたブックからデータをフェッチするには、ユーザーが気付かないうちに他のブックを開くマクロを作成し、ブックからデータをフェッチしてリストボックスに項目を追加し、ブックを閉じます。
「選択」ボタンをクリックすると、ユーザーフォーム「UserForm1」がアクティブになります。
ユーザーフォームの初期化イベントは、リストボックスに項目を追加するために使用されます。このイベントは、最初に閉じたブックを開き、次に範囲内の値を「ListItems」バリアントに割り当てます。値を割り当てた後、ブックが閉じられ、アイテムがリストボックスに追加されます。
リストボックスは、既存のリスト値から名前を選択するために使用されます。 「OK」ボタンを押すと、選択した名前が表示されます。
ADODB接続の使用
ActiveXデータオブジェクト(ADO)は、OLEDB接続用の高レベルで使いやすいインターフェイスです。これは、データベース内のデータにアクセスして操作するためのプログラミングインターフェイスです。
ADODB接続を作成するには、プロジェクトにADOライブラリを追加する必要があります。
参照を追加するには、[ツール]メニュー> [参照]から選択します。
ワークシートの「ADODB接続」ボタンをクリックすると、「UFADODB」ユーザーフォームがアクティブになります。このユーザーフォームの初期化イベントでは、ADODB接続を使用して、閉じたブックからデータをフェッチしました。接続を確立し、閉じたブックから配列にデータをフェッチするために、カスタムのユーザー定義関数(UDF)「ReadDataFromWorkbook」を作成しました。
別のUDF「FillListBox」を使用して、ユーザーフォームの初期化中にリストボックスに項目を追加しました。リストボックスには2つの列でデータが表示されます。1つの列には名前が含まれ、2番目の列には年齢が含まれます。
リストボックスで項目を選択した後、「OK」ボタンを押すと、選択した項目に関する情報メッセージが表示されます。
コードについては以下に従ってください
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
このブログが気に入ったら、FacebookやFacebookで友達と共有してください。
皆様からのご意見をお待ちしております。私たちの仕事を改善し、あなたのために改善する方法をお知らせください。 [email protected]までご連絡ください