質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

SQL Server

SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。

Q&A

解決済

4回答

2432閲覧

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

tokumeikibou162

総合スコア19

VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

SQL Server

SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。

0グッド

0クリップ

投稿2020/07/22 02:44

編集2020/07/22 03:29

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

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

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

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答4

0

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

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

excel

1 2Sub do_sqlserver() 3 4'■ExcelVBA参照設定:ADO 5 6Dim db As String 7Dim sql As String 8Dim rc As VbMsgBoxResult 9 10Worksheets("sqlserver").Range("a1").CurrentRegion.Clear 11 12rc = MsgBox("データ取り込みを行いますか?", vbYesNo + vbQuestion) 13 14 15 If rc = vbYes Then 16 17 db = "testdb" 18 sql = "select * from tbl" 19 20 Call get_sqlserver(db, sql)’自作sqlserver接続関数に接続する 21 Else 22 MsgBox "処理を中止します", vbCritical 23 End If 24 25End Sub

excel

1’sqlserever接続関数 2Sub get_sqlserver(DatabaseName As String, strSQL As String) 3 4 On Error GoTo ErrorProc 5 6 Dim DBSrv As String 7 Dim rs As Recordset 8 Dim strConn As String 9 Dim i As Integer 10 Dim j As Integer 11 '---------------------------------------------------- 12 ' DBSrvにDBサーバ名、DBNameにデータベース名 13 '---------------------------------------------------- 14 DBSrv = "DESKTOP-VKLFBER\SQLEXPRESS" 15 'DBSrv = "DBSERVER\SQLEXPRESS,49391" 'ポート指定付 16 DBName = DatabaseName 17 18 19 '■Windows認証 20 strConn = "Provider=SQLOLEDB;Data Source='" & DBSrv & "';Initial Catalog='" & DBName & "';Trusted_Connection=Yes" 21 22 '■SQL Server認証 23 'strConn = "Provider=SQLOLEDB;Data Source='" & DBSrv & "';Initial Catalog='" & DBName & "';UID=XXXX;PWD=XXXX;" 24 25 26 Set rs = New ADODB.Recordset 27 28 rs.Open strSQL, strConn, adOpenForwardOnly, adLockReadOnly, adCmdText 29 30 rs.MoveFirst 31 32 33 With Worksheets("sqlserver") 34 35 .Cells.Clear 36 i = 1 37 Do Until rs.EOF 38 For j = 0 To rs.Fields.Count - 1 39 If i = 1 Then .Cells(i, j + 1) = rs.Fields(j).Name 40 .Cells(i + 1, j + 1) = rs(j).Value 41 Next j 42 rs.MoveNext 43 i = i + 1 44 Loop 45 End With 46 47 rs.Close 48 Set rs = Nothing 49 50 MsgBox "SQL SERVERのデータを取得しました!!" 51 52 Exit Sub 53 54 55ErrorProc: 56 57 MsgBox Err.Number & vbCrLf & Err.Description 58 59End Sub

投稿2020/07/22 11:48

編集2020/07/27 06:43
mako1972

総合スコア383

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

tokumeikibou162

2020/07/30 07:39

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

2020/07/30 07:57 編集

シートを一旦クリアするというイミです。 このヘンが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 テーブルのフィールド名やフィールドを 自動で取得するコードですね。
mako1972

2020/07/30 11:20

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

2020/07/30 12:25

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

2020/07/30 12:36 編集

>>SQLseverのドライバーが分からず. ということは、実は検証できてないのかーーぃ?? ネットで調べてということだと思っていました・・。 あらま・・。そこなのかな。 ODBC接続がわからなくても、 Ms access持ってるなら、ADOの練習もできるけど(なくてもいいのですが)。
tokumeikibou162

2020/07/30 12:38

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

2020/07/30 12:46 編集

そっか・・。sqlserverにあるデータベースのテーブルに対して 結果を取得できているものとしてお話していました。 ODBC接続のテストは通っているという意味ですね。 テーブルを作成しているならVBAマクロが実行できると思うんですけど。 ConnectionStringの部分が、他の事例でいう。接続変数になっていると思うのですが・・。 ここを他のVBAサンプルに置き換えるといいかと・・。
mako1972

2020/07/30 12:51 編集

ま、前向きに考えると ファイル名から学生番号を取得して、個人を特定する 履歴書データを収集して一つのしーとにまとめる Sqlserverに登録する(問題ここ!) まで来たということでしょうか・・・。 そもそも、データベースを作成したあとにテーブルは作成していると思いますけど・・。
mako1972

2020/07/30 13:03 編集

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

2020/07/30 13:06

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

2020/07/30 13:12

なら、準備はオケですね。 データ登録してますよね。 となるとexcel VBAで sqlserveに接続はせきるのですが データベースからデータを 取り出せなくて困っている旨と サンプルを提示するといいかも。 という。
mako1972

2020/07/30 20:29

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

0

ベストアンサー

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

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

mysql

1Sub Mysql_連続insert() 2 3'ExcelでADO・ADODBへの参照設定で下記をチェック(古いバージョンでも可) 4'Microsoft ActiveX 'Data Objects 6.1 Library 5 6 7Dim cn As ADODB.Connection 8Dim rs As ADODB.Recordset 9 10Dim connectionString As String 11Dim sqlStr As String 12 13Dim id As String 14Dim name As String 15Dim password As String 16Dim section As String 17 18 19'接続文字列 20connectionString = "Driver={MySQL ODBC 8.0 ANSI Driver};" _ 21 & " SERVER=localhost;" _ 22 & " DATABASE=db_users;" _ 23 & " USER=root;" _ 24 & " PASSWORD=test;" 25 26'ADODB.Connection生成 27Set cn = New ADODB.Connection 28 29On Error GoTo Err 30 31'MySQLに接続 32cn.Open connectionString 33 34For i = 2 To 6000 35 36 With Sheets("data1") 37 id = .Range("A" & i).Value 38 name = .Range("B" & i).Value 39 password = .Range("C" & i).Value 40 section = .Range("D" & i).Value 41 42 End With 43 44 sqlStr = "insert into t_users (id,name,password,section) values ('" & id & "','" & name & "','" & password & "','" & section & "')" 45 Set rs = cn.Execute(sqlStr) 46 47Next 48 49cn.Close 50Set rs = Nothing 51Set cn = Nothing 52 53MsgBox "データの登録がおわりました" 54Exit Sub 55 56Err: 57 Set rs = Nothing 58 Set cn = Nothing 59 MsgBox (Err.Description) 60 61End Sub

投稿2020/07/22 04:07

mako1972

総合スコア383

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

tokumeikibou162

2020/07/22 04:57

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

2020/07/22 05:20 編集

その通りです。 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

2020/07/22 05:11 編集

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 A2セルから(タイトル行をのぞく)D60000の約6000件のデータをインサートしているという処理になります。 Access ADOで検索しても同じようなサンプルが探せると思いますよ。 (実際はsqlserverでしょうけど)
tokumeikibou162

2020/07/22 06:05

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

2020/07/22 06:24 編集

結局は、sqlserverのテーブルに、sql分を発行するためのsqlがわかってから ADOでinserやselect文を発行するためのExcel VBAコードを検証するという・・・。 まわりくどい説明ですけど。いま手元にSQLserverがないのでという。 エクセルシートのサンプルを提示して違うレスで立てると詳しい方が 一連のコードを提示してくれると思いますよ。 複数のシートを抽出し、という部分とsqlserverに登録という内容を 分けて質問された方がいいと思います。 根本的に、課題解決されていないですよね。
tokumeikibou162

2020/07/22 06:35

少なくとも全然何もできなかったころに比べて、かなり進んだのでとても助かりました。 複数のシートと登録に関しては、改めて質問したいと思います。(まずは自分であらかた調べてみます。)
mako1972

2020/07/22 06:53

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

0

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

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

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

投稿2020/07/22 03:05

mako1972

総合スコア383

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

mako1972

2020/07/22 03:30

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

0

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

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

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

等など

投稿2020/07/22 02:58

DreamTheater

総合スコア1095

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

tokumeikibou162

2020/07/22 03:08

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

2020/07/22 03:14 編集

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

2020/07/22 03:26

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

2020/07/22 03:39 編集

私はよくやるのですが、ACCESSでリンクテーブルをSQLserverから作成しておいて Accessを介してに対してSQLを発行するのもよくやります・・・。 (mysqlとかpostgresqlとか・・) 下記のサイトもいいかもしれまんせんけど・。 データベース(SQL Server)に接続する(ADO) https://excelwork.info/excel/databasesqlserver/ Excel VBAとSQL Serverの連携を楽にしたい https://qiita.com/sawadyrr5/items/c57f7325128f32f52449 私の意見は無視してください。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問