| |
検索について質問します。
下のような構文でアクセスDBから情報をワークシート”DATA”にひっぱて来て、
このシート上の”C”列に各都道府県のデータがあります。
ここで”C"列上の検索・・例えばTextboxに東京と入力して検索CommandButtonで検索
してListboxに表示させるには、どうすればよいでしょうか、よろしくご指導お願いします。
------------------------------------------------------------------------------
Private Sub CommandButton4_Click()
Dim count_DATA As Integer
Dim IRow As Long
'シートクリア
Worksheets("DATA").Activate
Worksheets("DATA").Range(Cells(2, 1), Cells(500, 49)).Value = ""
'アクセスから情報取り込み
Dim myCon As ADODB.Connection
Dim myRS As ADODB.Recordset
Set myCon = New ADODB.Connection
myCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=\c\管理DB.mdb"
Set myRS = New ADODB.Recordset
On Error Resume Next 'DB空の処理
myRS.Open "データベース", myCon, adOpenStatic, adLockOptimistic
myRS.Update
myRS.MoveFirst
Do Until myRS.EOF = True
count_DATA = 2
Do Until Worksheets("DATA").Cells(count_DATA, "A").Value = ""
count_DATA = count_DATA + 1
Loop
Worksheets("DATA").Cells(count_DATA, "A").Value = myRS![番号]
Worksheets("DATA").Cells(count_DATA, "B").Value = myRS![日付]
Worksheets("DATA").Cells(count_DATA, "C").Value = myRS![エリア]
〜
Worksheets("DATA").Cells(count_DATA, "AN").Value = mmyRS![Memo]
myRS.MoveNext
Loop
Set myRS = Nothing
myCon.Close: Set myCon = Nothing
'ソート
count_DATA = 2
Do Until Worksheets("DATA").Cells(count_DATA, "A").Value = ""
count_DATA = count_DATA + 1
Loop
Worksheets("DATA").Range(Cells(2, "A"), Cells(count_DATA, "AW")).Sort
Key1 = Worksheets("DATA").Cells(2, "AW")
'表示
With Worksheets("DATA")
IRow = .Range("A" & Rows.Count).End(xlUp).Row + 1
End With
With ListBox1
.ColumnHeads = True
.ColumnCount = 4
.ColumnWidths = "25;55;40;60;"
'.Text = "DATA!A2:J100"
.RowSource = "DATA!A2:J100"
End With
End Sub
|
|
Excel
- 08/7/28(月) 17:45 -
