複数のエクセルシートの内容をVBAで抽出しSQLServerに格納したい。

Question

現在VBAで複数のエクセルシート（内容は履歴書のようなもの）をVBAで抽出しSQLServerに格納するプログラミングを作成しています。
しかし今回VBAを初めて触るということと、あまりプログラミング経験がないことも手伝って、DBに接続程度のプログラムしか作れていない状況です。(データの抽出？どうやって？という状況)

なにとぞお勧めな勉強方法、参考になるソース、検索の仕方等を教えてもらえればと考えています。

後よろしければADO接続でデータをsqlでインサートするサンプルがもらえればありがたいです。

Answer 1

excel vbaからのsqlserverの接続例を示します。
自作関数get_sqlserverに引数（database名、sql文）を渡すと
結果を取り込みできます（データを取得するシート名はsqlserverという名前にしてます）。

DBSrv = "DESKTOP-VKLFBER\SQLEXPRESS"
DBName = "testdb"
の部分はご自身の環境に合わせて修正をお願いします。

Sub do_sqlserver()

'■ExcelVBA参照設定:ADO

Dim db As String
Dim sql As String
Dim rc As VbMsgBoxResult
 
Worksheets("sqlserver").Range("a1").CurrentRegion.Clear

rc = MsgBox("データ取り込みを行いますか？", vbYesNo + vbQuestion)


    If rc = vbYes Then
        
        db = "testdb"
        sql = "select * from tbl"

        Call get_sqlserver(db, sql)’自作sqlserver接続関数に接続する
    Else
        MsgBox "処理を中止します", vbCritical
  End If

End Sub

’sqlserever接続関数
Sub get_sqlserver(DatabaseName As String, strSQL As String)
 
    On Error GoTo ErrorProc
 
    Dim DBSrv As String
    Dim rs As Recordset
    Dim strConn As String
    Dim i As Integer
    Dim j As Integer
    '----------------------------------------------------
    ' DBSrvにDBサーバ名、DBNameにデータベース名
    '----------------------------------------------------
    DBSrv = "DESKTOP-VKLFBER\SQLEXPRESS"
    'DBSrv = "DBSERVER\SQLEXPRESS,49391" 'ポート指定付
    DBName = DatabaseName 
 

    '■Windows認証
    strConn = "Provider=SQLOLEDB;Data Source='" & DBSrv & "';Initial Catalog='" & DBName & "';Trusted_Connection=Yes"
    
    '■SQL Server認証
    'strConn = "Provider=SQLOLEDB;Data Source='" & DBSrv & "';Initial Catalog='" & DBName & "';UID=XXXX;PWD=XXXX;"
 

    Set rs = New ADODB.Recordset

    rs.Open strSQL, strConn, adOpenForwardOnly, adLockReadOnly, adCmdText
    
    rs.MoveFirst
    
   
    With Worksheets("sqlserver")
        
        .Cells.Clear
       i = 1
            Do Until rs.EOF
                For j = 0 To rs.Fields.Count - 1
                    If i = 1 Then .Cells(i, j + 1) = rs.Fields(j).Name
                    .Cells(i + 1, j + 1) = rs(j).Value
                Next j
                rs.MoveNext
                i = i + 1
            Loop
     End With
        
    rs.Close
    Set rs = Nothing
   
    MsgBox "SQL SERVERのデータを取得しました！！"
 
    Exit Sub
 

ErrorProc:
 
    MsgBox Err.Number & vbCrLf & Err.Description
 
End Sub

Comments

[tokumeikibou162]

そういえばなんですけど、こちらの処理の場合は
Worksheets("sqlserver").Range("a1").CurrentRegion.Clearが取り込むデータを取得するになるんですかね？

[mako1972]

シートを一旦クリアするというイミです。

このヘンがsqlの結果を
ワークシートに展開してる部分ですね。

With Worksheets("sqlserver")

.Cells.Clear
i = 1
Do Until rs.EOF
For j = 0 To rs.Fields.Count - 1
If i = 1 Then .Cells(i, j + 1) = rs.Fields(j).Name
.Cells(i + 1, j + 1) = rs(j).Value
Next j
rs.MoveNext
i = i + 1
Loop
End With

テーブルのフィールド名やフィールドを
自動で取得するコードですね。

[tokumeikibou162]

成る程、有難うございます。

[mako1972]

関数にした事で余計面倒かもですか。
accessでadoで検索すると
いいかもしれません。

[tokumeikibou162]

なるほどありがとうございます。
SQLseverのドライバーが分からずほかの方法を探していたので、検索してみます。

[mako1972]

>>SQLseverのドライバーが分からず.
ということは、実は検証できてないのかーーぃ？？
ネットで調べてということだと思っていました・・。

あらま・・。そこなのかな。

ODBC接続がわからなくても、
Ms access持ってるなら、ADOの練習もできるけど（なくてもいいのですが）。

[tokumeikibou162]

そう...ですね
ConnectionString = "Driver={SQL Server};" _
& " SERVER= DESKTOP-09F2LJG\SQLEXPRESS;" _
& " DATABASE= test;" _
& " USER= root;" _
& " PASSWORD= root;"
と入力してみたものもつながらず...（ODBCとDB 間は接続テストはできています、）

[mako1972]

そっか・・。sqlserverにあるデータベースのテーブルに対して
結果を取得できているものとしてお話していました。

ODBC接続のテストは通っているという意味ですね。

テーブルを作成しているならVBAマクロが実行できると思うんですけど。

ConnectionStringの部分が、他の事例でいう。接続変数になっていると思うのですが・・。
ここを他のVBAサンプルに置き換えるといいかと・・。

[mako1972]

ま、前向きに考えると

ファイル名から学生番号を取得して、個人を特定する
履歴書データを収集して一つのしーとにまとめる
Sqlserverに登録する（問題ここ!）

まで来たということでしょうか・・・。

そもそも、データベースを作成したあとにテーブルは作成していると思いますけど・・。

[tokumeikibou162]

そうですね、そんな感じです。

[mako1972]

おーぃ。データベース作って
テーブル作ったのでしょうか?
テーブル作らないと何者始まらないすよね。

[tokumeikibou162]

テーブル自体は作ってありますね。

[mako1972]

なら、準備はオケですね。
データ登録してますよね。

となるとexcel ＶＢＡで
sqlserveに接続はせきるのですが
データベースからデータを
取り出せなくて困っている旨と
サンプルを提示するといいかも。
という。

[tokumeikibou162]

なるほど。

[mako1972]

あと少し。

[mako1972]

今更ですが、excel vba とsqlserverとの接続サンプルを試すとして、
ADOの参照設定は行いましたか？
方法は、
VBAエディタメニュー→ツール→参照設定→Miscrosoft Aceivex Data object XX Library

ですけど・・。

[tokumeikibou162]

そちらの方は設定しました。

Answer 2

ADOはいろいろなDBで同じ文法でsqlのinsert分を発行できます。

下記は、接続方法はsqlseverに変えてほしいのですが（サンプルはmysql）
excelシートの内容を連続でinsertはAODでこういった処理となります。

Sub Mysql_連続insert()

'ExcelでADO・ADODBへの参照設定で下記をチェック（古いバージョンでも可）
'Microsoft ActiveX 'Data Objects 6.1 Library


Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Dim connectionString As String
Dim sqlStr As String

Dim id As String
Dim name As String
Dim password As String
Dim section As String


'接続文字列
connectionString = "Driver={MySQL ODBC 8.0 ANSI Driver};" _
                & " SERVER=localhost;" _
                & " DATABASE=db_users;" _
                & " USER=root;" _
                & " PASSWORD=test;"

'ADODB.Connection生成
Set cn = New ADODB.Connection

On Error GoTo Err

'MySQLに接続
cn.Open connectionString

For i = 2 To 6000

    With Sheets("data1")
        id = .Range("A" & i).Value
        name = .Range("B" & i).Value
        password = .Range("C" & i).Value
        section = .Range("D" & i).Value

    End With

        sqlStr = "insert into t_users (id,name,password,section) values ('" & id & "','" & name & "','" & password & "','" & section & "')"
        Set rs = cn.Execute(sqlStr)

Next

cn.Close
Set rs = Nothing
Set cn = Nothing

MsgBox "データの登録がおわりました"
Exit Sub

Err:
    Set rs = Nothing
    Set cn = Nothing
    MsgBox (Err.Description)

End Sub

Comments

[tokumeikibou162]

因みになんですが
Dim id As String
id = .Range("A" & i).Value
でエクセルシートから値をとっている認識で間違いないですかね？

[mako1972]

その通りです。

With Sheets("data1")
id = .Range("A" & i).Value
name = .Range("B" & i).Value
password = .Range("C" & i).Value
section = .Range("D" & i).Value
End With

とありますが、data1というシートの各列をループして取り出すという意味となります。
id = Sheets("data1") .Range("A" & i).Value
name= Sheets("data1") .Range("B" & i).Value
password = Sheets("data1") .Range("C" & i).Value
section = Sheets("data1") .Range("D" & i).Value

でもいいのですがExcel vba の省略形でwith endで記述しています。
マクロ実行が当該シートでしたらシート名を省略して
id=range("a1").valueでいいのですが、実行するシートを明示しておく癖をつけておいた方が
いいですよ！という・・。

for nextで記述する場合は
cells(XX,XX).valueで記述しすることを推奨していますが
わかりやすく。サンプルということで！

[mako1972]

For i = 2 To 6000

With Sheets("data1")
id = .Range("A" & i).Value
name = .Range("B" & i).Value
password = .Range("C" & i).Value
section = .Range("D" & i).Value

End With

sqlStr = "insert into t_users (id,name,password,section) values ('" & id & "','" & name & "','" & password & "','" & section & "')"
Set rs = cn.Execute(sqlStr)

Next

A２セルから（タイトル行をのぞく）D60000の約6000件のデータをインサートしているという処理になります。

Access ADOで検索しても同じようなサンプルが探せると思いますよ。
（実際はsqlserverでしょうけど）

[tokumeikibou162]

なるほど、ありがとうございます。

[mako1972]

結局は、sqlserverのテーブルに、sql分を発行するためのsqlがわかってから
ADOでinserやselect文を発行するためのExcel VBAコードを検証するという・・・。

まわりくどい説明ですけど。いま手元にSQLserverがないのでという。
エクセルシートのサンプルを提示して違うレスで立てると詳しい方が
一連のコードを提示してくれると思いますよ。

複数のシートを抽出し、という部分とsqlserverに登録という内容を
分けて質問された方がいいと思います。

根本的に、課題解決されていないですよね。

[tokumeikibou162]

少なくとも全然何もできなかったころに比べて、かなり進んだのでとても助かりました。

複数のシートと登録に関しては、改めて質問したいと思います。（まずは自分であらかた調べてみます。）

[mako1972]

余計な回答して申し訳ないですね。

[tokumeikibou162]

いえいえ勉強になります。

Answer 3

windowsでデータベースがからむと・・。odbc接続、ado接続が必要となります。

そのキーワードで検索してみるといいですよ・・。

これは定番なので、自己解決しないとダメな内容だと思います。

Comments

[tokumeikibou162]

一応ado接続自体は
https://itlogs.net/vba-database/
を参考にできました。

[mako1972]

いい人ですね。mysqlでもpostgresqlでもＡＤＯ接続が基本となりますので
sqlserverがどうのこうのとかは最終的に関係ないと思います。
まずは、ADO接続でデータのインサート、selectを簡単なサンプルで試せばいいと思います。
という回答ですみません。
結局windowsだとodbc接続でAODを使うのですからサンプルは共通です。
この辺が理解するか、試せるかどうかで拡張性がありますので
サンプルは示せるのですが、敢えてということで！！

[tokumeikibou162]

ありがとうございます。

Answer 4

「VBA入門」や「SQL入門 SQLServer」などでググるといろいろ見つかると思いますよ。
あとはVBAの定番サイト
Office TANAKA

ご質問「複数のエクセルシート・・・」ですが、これも色々な方法がありますね。

• 抽出対象のExcelブック内にVBAコードを書いてSQLServerにアクセスする
• 専用のExcelブックにVBAコードを書いて、抽出対象のExcelブックを取り込んでSQLServerアクセスする
• ACCESS-VBAで抽出対象ExcelブックをACCESSに取り込んでSQLServerアクセスする

等など

Comments

[tokumeikibou162]

ありがとうございます。早速閲覧してみます。
因みに方法としては（専用のExcelブックにVBAコードを書いて、抽出対象のExcelブックを取り込んでSQLServerアクセスする）でやろうとしています。

[mako1972]

ADO接続でデータをsqlでインサートするサンプルが欲しいという質問でいいのでは。
主がサンプルを示していないのですから。

[tokumeikibou162]

なるほど、そういう風に質問をあげなおしてみます

[mako1972]

私はよくやるのですが、ACCESSでリンクテーブルをSQLserverから作成しておいて
Accessを介してに対してSQLを発行するのもよくやります・・・。
（mysqlとかpostgresqlとか・・）

下記のサイトもいいかもしれまんせんけど・。

データベース（SQL Server）に接続する（ADO）
https://excelwork.info/excel/databasesqlserver/

Excel VBAとSQL Serverの連携を楽にしたい
https://qiita.com/sawadyrr5/items/c57f7325128f32f52449

私の意見は無視してください。

[mako1972]

SQLServerに接続し、Insert文を発行とかいうサンプルが欲しいだけですよね・・。

http://www.cocoaliz.com/excelVBA/index.php/45/