3
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

EXCEL VBAでSQLを実行

Last updated at Posted at 2024-03-14

とりあえずSQLを実行してExcelに貼り付けるだけのVBAを作りました。ソース内にSQLを書くと、試行錯誤する時にめんどくさいので、外においたテキストファイルからSQLを読み込むようにしています。
実際にリスト作成の処理を作る前段階でのSQLの検証用に使っています。

Sub Execute()

    Dim objCon As Object
    Dim objDs As Object
    Dim objRange As Range
    Dim objSheet As Worksheet
    
    Set objCon = CreateObject("ADODB.Connection")
    objCon.ConnectionString = "DRIVER=SQLite3 ODBC Driver;Database=C:\bin\SQlite\chinook.db"
    
    objCon.Open
    
    Set objDs = objCon.Execute(GetSQL())
    
    Set objSheet = ThisWorkbook.Worksheets.Add
    Set objRange = objSheet.Range("A1")
       
    Dim i As Long
    For i = 0 To objDs.Fields.Count - 1
        objRange.Offset(0, i).Value = objDs.Fields(i).Name
    Next
    Set objRange = objRange.Offset(1, 0)
    
    Do Until objDs.EOF
        For i = 0 To objDs.Fields.Count - 1
            objRange.Offset(0, i).Value = objDs.Fields(i)
        Next
        Set objRange = objRange.Offset(1, 0)
        objDs.MoveNext
    Loop
    
    objDs.Close
    objCon.Close
    
    Set objSheet = Nothing
    Set objRange = Nothing
    Set objDs = Nothing
    Set objCon = Nothing
       
End Sub

Function GetSQL() As String

    Dim strSourceFile As String
    Dim strLine As String
    strSourceFile = ThisWorkbook.Path & "\sql.txt"
    
    GetSQL = ""
    Open strSourceFile For Input As #1
        Do Until EOF(1)
            Line Input #1, strLine
            GetSQL = GetSQL & strLine & vbCrLf
        Loop
    Close
    
End Function

エラー処理とか、データ型を意識した出力とかは行っていません。あしからず。

3
2
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
3
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?