'//AccessVBA-----------------------------------------
Function GetDataFromOracle() As Variant
Dim conn As Object
Dim rs As Object
Dim data As Variant
Dim i As Integer, j As Integer
Dim queryName As String
' ADO接続を作成
Set conn = CreateObject("ADODB.Connection")
' 接続文字列を設定（適切に変更してください）
conn.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=YourDataSource;User Id=YourUsername;Password=YourPassword;"
' 接続を開く
conn.Open
' Accessのクエリ名を指定
queryName = "YourQueryName" ' ここに実行したいクエリ名を入力
' SQLクエリを実行（クエリ名を使用）
Set rs = conn.Execute("SELECT * FROM [" & queryName & "]")
' データを配列に格納
If Not rs.EOF Then
rs.MoveLast
rs.MoveFirst
ReDim data(1 To rs.RecordCount, 1 To rs.Fields.Count)
For i = 1 To rs.RecordCount
For j = 1 To rs.Fields.Count
data(i, j) = rs.Fields(j - 1).Value
Next j
rs.MoveNext
Next i
End If
' 結果を返す
GetDataFromOracle = data
' 後処理
rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing
End Function
'//ExcelVBA-------------------------------------------
Sub ExportDataFromOracle()
Dim accessApp As Object
Dim data As Variant
Dim i As Integer, j As Integer
Dim ws As Worksheet
' Accessアプリケーションを起動
Set accessApp = CreateObject("Access.Application")
accessApp.OpenCurrentDatabase "C:\path\to\your\database.accdb" ' Accessのデータベースパス
' AccessのVBA関数を呼び出し
data = accessApp.Run("GetDataFromOracle")
' シートを指定（"Sheet1"を指定）
Set ws = ThisWorkbook.Sheets("Sheet1")
' Excelシートにデータを書き込む
For i = LBound(data, 1) To UBound(data, 1)
For j = LBound(data, 2) To UBound(data, 2)
ws.Cells(i + 1, j).Value = data(i, j) ' シートの指定
Next j
Next i
' 後処理
accessApp.Quit
Set accessApp = Nothing
End Sub
