配列
　
　 　 Option Explicit

' Ctrl+*で名前付きセル範囲を表示
Sub sample1()
Dim tgtTable As Range
Dim tgtCriteria As Range
Dim tgtFields As Range

'テーブル・条件・抽出先セット
With Worksheets(2)
Set tgtTable = .Range("B3").CurrentRegion
Set tgtCriteria = .Range("F3:F4")
Set tgtFields = .Range("H3:I3")
End With
'抽出、転記を行う
tgtTable.AdvancedFilter xlFilterCopy, tgtCriteria, tgtFields

Set tgtFields = Nothing
Set tgtCriteria = Nothing
Set tgtTable = Nothing
End Sub
Sub sample2()
Dim tgtTable As Range

Set tgtTable = ThisWorkbook.Names("顧客マスタ").RefersToRange
With tgtTable
Debug.Print .Row
Debug.Print .Column
End With
Debug.Print Range("年度").Value
Set tgtTable = Nothing

Set tgtTable = Range("顧客マスタ").CurrentRegion
With tgtTable
.Columns(1).NumberFormatLocal = "000"
.Columns(4).NumberFormatLocal = "m月d日"
End With
Set tgtTable = Nothing
End Sub

Sub sample3()
With Worksheets(1)
.Range("A1:A4").Replace what:="○", replacement:="●"
End With
Range("変換範囲").Replace what:=" ", replacement:="　"
End Sub

'Microsoft ActiveX Data Objects 2.8 Library
Sub sample10()

Dim objCon As ADODB.Connection
Dim objRS As ADODB.Recordset
Dim i As Integer

'新規オブジェクトをセット
Set objCon = New ADODB.Connection
Set objRS = New ADODB.Recordset
'「Extended Properties=Excel 8.0;」を指定してExcelブックに接続
objCon.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & "\名前付きセル範囲.xls;" & "Extended Properties=Excel 8.0;"
'テーブルへの参照を取得
With objRS
.ActiveConnection = objCon
.Source = "SELECT * FROM 顧客マスタ WHERE 顧客№=3;"
.Open
End With
'フィールド名を転記
For i = 1 To objRS.Fields.Count
With Worksheets(1)
.Cells(14, i).Value = objRS.Fields(i - 1).Name
End With
Next
'テーブルの内容を転記
Range("A15").CopyFromRecordset objRS

'接続を閉じる
objRS.Close
objCon.Close

Set objRS = Nothing
Set objCon = Nothing

End Sub

'Microsoft ActiveX Data Objects 2.8 Library
Sub sample11()
Dim objCon As ADODB.Connection
Dim objRS As ADODB.Recordset
Dim i As Integer

'新規オブジェクトをセット
Set objCon = New ADODB.Connection
Set objRS = New ADODB.Recordset
'「Extended Properties=Excel 8.0;」を指定してExcelブックに接続
objCon.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & ";" & "Extended Properties=TEXT;"
'テーブルへの参照を取得
With objRS
.ActiveConnection = objCon
.Source = "SELECT * FROM TEST.csv WHERE 顧客№=3;"
.Open
End With
'フィールド名を転記
For i = 1 To objRS.Fields.Count
Cells(20, i).Value = objRS.Fields(i - 1).Name
Next
'テーブルの内容を転記
Range("A21").CopyFromRecordset objRS

'接続を閉じる
objRS.Close
objCon.Close

Set objRS = Nothing
Set objCon = Nothing

End Sub