お気持ちのみですが合計で250ポイント~差し上げます。


以下の作業を自動化する EXCEL VBAマクロを作って下さい。

***作業***
Microsoft Access経由でリンクしている15個程度のテーブルへ、エクセルファイルからデータをコピー&ペーストしています。

作業は単純作業で、エクセルファイルの2行目から下(1行目はテーブルのフィールド名と一致する文字列が記載)をアクセス経由で開いたデータベースへコピー&ペーストしています。
***



***要件***
コメント欄に記載
***

回答の条件
  • 1人2回まで
  • 登録:2009/01/14 11:46:05
  • 終了:2009/01/19 17:58:17

ベストアンサー

id:Mook No.1

Mook回答回数1312ベストアンサー獲得回数3912009/01/19 02:36:12

ポイント500pt

一応下記のやり取りで想定した動作をするマクロです。

下記を想定しています。


マクロ実行ファイルのSheet1

A列・・・ID(処理の範囲:空のセルで終了)
B列・・・EXCEL ファイルのフルパス(例:D:\Data\sample.xls)
C列・・・格納先のテーブル名(例:sampleTable1)
D列・・・書き込み結果、エラー情報の出力

読み込みファイル(sample.xls) の例(上記のB列で指定)

1行目・・・・・カラム名(例:userName, price, address 等)
2行目以降・・・データ(例:山田, 25000, 東京都千代田区千代田1番)

マクロを実行するためには下記の準備が必要です。

(1)ODBC 5.1をダウンロードしてインストールしてください。

   (コントロールパネルのプログラムの追加と削除で、すでに存在していればそのままでOKです。)

(2)VBEで「ツール」⇒「参照設定」から「Microsoft ActiveX Data Object(最新版を選択) Library」にチェック


マクロは下記のとおりです。先頭の ConnectionString の中身を正しい接続情報にしてください。

Option Explicit

Const ConnectionString = "Driver={MySQL ODBC 5.1 DRIVER};" _
                    & " SERVER=localhost;" _
                    & " DATABASE=sample_db;" _
                    & " USER=sample_user;" _
                    & " PASSWORD=sample2;"

'----------------------------------------------------
Sub dataExport()
'----------------------------------------------------
    Dim srcWS As Worksheet
    Set srcWS = ThisWorkbook.Worksheets("Sheet1")

    Dim lastRow As Long
    lastRow = srcWS.Range("A1").End(xlDown).Row
        
    Dim con As New ADODB.Connection
    con.Open ConnectionString
    
    Dim ll As Long
    For ll = 2 To lastRow
        srcWS.Cells(ll, "D").Value = XL2DB(con, srcWS.Cells(ll, "B").Value, srcWS.Cells(ll, "C").Value)
    Next

End Sub

'----------------------------------------------------
Function XL2DB(con As ADODB.Connection, srcPath As String, tableName As String) As String
'----------------------------------------------------
    If Dir(srcPath) = "" Then
        XL2DB = "EXCEL ファイルがありません"
        Exit Function
    End If
    
    Dim wb As Workbook
    Set wb = Workbooks.Open(srcPath)
    
    Dim ws As Worksheet
    Set ws = wb.Worksheets(1)
    
    Dim rs As ADODB.Recordset
    
    Dim i As Long
    Dim sql As String
    For i = 2 To ws.Range("A1").End(xlDown).Row
        sql = makeSQL(ws, tableName, i)
        On Error GoTo DB_ERR
        con.Execute sql
        On Error GoTo 0
    Next
    wb.Close

    GoTo NORMAL_END
DB_ERR:
    If XL2DB = "" Then
        XL2DB = "Line " & i & ":" & Err.Description
    Else
        XL2DB = XL2DB & vbNewLine & "Line " & i & ":" & Err.Description
    End If
    Resume Next
NORMAL_END:
End Function

'----------------------------------------------------
Function makeSQL(ws As Worksheet, tableName As String, dLine As Long) As String
    makeSQL = "INSERT INTO " & tableName & " (" & ws.Cells(1, 1).Value
    
    Dim sl As Long
    For sl = 2 To ws.Range("A1").End(xlToRight).Column
        makeSQL = makeSQL & "," & ws.Cells(1, sl).Value
    Next
    
    If IsNumeric(ws.Cells(dLine, 1)) Then
        makeSQL = makeSQL & " ) VALUES ( " & ws.Cells(dLine, 1).Value
    Else
        makeSQL = makeSQL & " ) VALUES ( '" & ws.Cells(dLine, 1).Value & "'"
    End If
    
    For sl = 2 To ws.Range("A1").End(xlToRight).Column
        If IsNumeric(ws.Cells(dLine, sl)) Then
            makeSQL = makeSQL & ", " & ws.Cells(dLine, sl).Value
        Else
            makeSQL = makeSQL & ", '" & ws.Cells(dLine, sl).Value & "'"
        End If
    Next
    
    makeSQL = makeSQL & " );"
End Function

データは数値と文字列に応じて形式を整えていますが、文字列型に数字のデータを入れる場合、

うまく動かない可能性があります。

その際は、カラムにデータ型を指定する必要がありますので、その際はコメントください。


一応動作確認済みです。

id:ReoReo7

いつも大変助かっています。

ありがとうございます。試してみたいと思います。

*追記

仕様を変更して申し訳ないのですが、複数の使用者・複数のPCでの使用が考えられ、インストールなどの作業はなるべく無しで使用できるようにしたいと考えています。そこで、ODBC5.1などのインストールの作業はなるべく省略したいと考えています。

別に、Access(.mdbファイルは使用者全員が持つ)経由で開く方法を、本プログラムとは別の2つ目のお答えとしてご提示願えませんでしょうか?

2009/01/19 17:03:17
  • id:ReoReo7
    ***要件(250ポイント~)***
    以下のエクセルシート(Sheets(Sheet1))のデータを上から読み込んで実行。終了行=1列目(ID)空白。

    Sheet1
    ID,エクセルファイル名(シートはSheets(1)のみ),エクセルファイルの格納されているパス,書き込みたいテーブル名,書き込みたいテーブルの格納されているパス
    1,sample1.xls,c:\excel\,sample1,c:\DB\
    2,sample2.xls,c:\excel\,sample2,c:\DB\

    15,sample15.xls,c:\excel\,sample15,c:\DB

    ※もし抜けている情報があり推測で補えそうなものがあれば、適宜補って注釈で示すか、ご質問下さい。
    ***



    ***追加要件(50ポイント)***
    1:データテーブルのフィールドの制限で書き込めなかった行があれば、その行数を記録。sample1.xlsで4行目と7行目がエラーで書き込めなければ、先ほどのシートの対応する行に
    1,sample1.xls,c:\excel\,sample1,c:\DB\,4+7
    などのように、エラーログを記録。

    2:1:のエラーログにエラーの種類を記載。
    1,sample1.xls,c:\excel\,sample1,c:\DB\,4:not integer!+7:not varchar(10)!
    などのように、エラーログを詳細化(エラーメッセージはアドリブまたはコンピュータのメッセージをそのまま利用)。
    ***
  • id:ReoReo7
    事前質問(ご参考)
    http://q.hatena.ne.jp/1231895336
  • id:Mook
    >アクセス経由で開いたデータベースへ
    >Microsoft Access経由でリンクしている

    の意味が良くわからないのですが、最終的にデータを格納するDB は 複数の
    Access ファイル(*.mdb)なのですか?

    Access ファイルは DB(ファイル)内に複数テーブルを持ちますので、
    ファイル名、テーブル名、カラム名を特定しないとデータ処理ができません。
  • id:Mook
    動作環境を下記のように変更することは可能ですか?

    マクロ実行ファイルのSheet1
    A列・・・ID(処理の範囲:空のセルで終了)
    B列・・・EXCEL ファイルのフルパス(例:D:\Data\sample.xls)
    C列・・・Access ファイルのフルパス(例:D:\DB\sample.mdb)
    D列・・・格納先のテーブル名(例:sampleTable1)
    E列・・・書き込み結果、エラー情報の出力

    読み込みファイル(sample.xls) の例(上記のB列で指定)
    1行目・・・・・カラム名(例:userName, price, address 等)
    2行目以降・・・データ(例:山田, 25000, 東京都千代田区千代田1番)

    この場合、sample.db 内に sampleTanble1 があり、そのテーブルに userName, price・・・ などの列がある。
    そこへ、sample.xls のデータ 山田,25000・・・ などを書き込むという感じです。
  • id:ReoReo7
    コメントありがとうございます。
    また、問題点を整理して頂き、代替案をご提案頂きありがとうございます。

    ご提案の動作環境は、現在行っている作業を自動化してくれるものなので、問題ありません。
  • id:ReoReo7

    さて、追加的なお願いなので大変だと思いますが、
    現在Accessを経由して開いているファイルを、
    経由せずに開くこと(mdbファイル無しに)がもし可能ならば、
    以下のように仕様を変更できませんか?

    マクロ実行ファイルのSheet1
    A列・・・ID(処理の範囲:空のセルで終了)
    B列・・・EXCEL ファイルのフルパス(例:D:\Data\sample.xls)
    C列・・・
    D列・・・格納先のテーブル名フルパス(例:D:\DB\sampleTable1)
    E列・・・書き込み結果、エラー情報の出力

    この場合、格納先のテーブルはあらかじめ生成されているものとします。
  • id:Mook
    状況が良くわからないのですが、
    マスタデータとなるデータベースは、Access(*.mdb)ファイルではないのですか?
    D列のテーブルとありますが、これはどのようなデータベースを使用しているのでしょうか。

    これが MS SQL や MySQLなど他のDBであるなら、ファイルの指定は不要ですし、
    そもそもACCESS を経由する必要はありません。

    しかし、利用しているのがACCESSであるなら、それはファイルそのものがデータベース
    ですから「D:\DB\sampleTable1」 のような指定では情報が不足しています。

    B列で指定するEXCELファイルは登録用の作業ファイルではないのですか?
    Database に登録したファイルを最終的にどのように利用しているのでしょうか。
    再度 EXCEL に出力しているのですか?それとも ACCESSで直接利用しているのですか?

    このあたりの環境を明確にしていただけるでしょうか。
  • id:ReoReo7
    コメントありがとうございます。

    >マスタデータとなるデータベースは、Access(*.mdb)ファイルではないのですか?
    >D列のテーブルとありますが、これはどのようなデータベースを使用しているのでしょうか。
    >これが MS SQL や MySQLなど他のDBであるなら、ファイルの指定は不要ですし、
    >そもそもACCESS を経由する必要はありません。

    データベースは、おそらくMySQLであると思います。自分のPCにMySQLはインストールされています。(おそらく、というのは、自分が作成したのではなく、「DBを参照しようとしているアプリケーション(市販のものではない)」が作成したものだからです。)
    sampleTable1.frm sampleTable1.MYD sampleTable1.MYI
    の3つのファイルがあります。

    これはAccess経由で開かれていますが、Accessを経由する必要が無いならば経由せずに書き込みたいと考えています。

    >しかし、利用しているのがACCESSであるなら、それはファイルそのものがデータベース
    >ですから「D:\DB\sampleTable1」 のような指定では情報が不足しています。

    ※もちろん、Accessのsample.mdb経由で書き込んでも構いません。いずれの方法にしても、Access経由で、「DBに書き込まれた値」の確認も行いたいと考えていますので、Accessからデータベースを参照できるように.mdbの作成の必要はあるためです。

    >B列で指定するEXCELファイルは登録用の作業ファイルではないのですか?
    >Database に登録したファイルを最終的にどのように利用しているのでしょうか。
    >再度 EXCEL に出力しているのですか?それとも ACCESSで直接利用しているのですか?

    上記「DBを参照しようとしているアプリケーション(市販のものではない)」がMySQLコードで読み書きを行っています。

    この情報で足りますでしょうか?

    よろしくお願いします。
  • id:Mook
    なるほど、確かにMySQL のようですね。となると、だいぶ様子が変わってきます。
    内容の確認は別にして、直接 MySQLとデータのやり取りをする方法もあります。

    Access と MySQL はその某かのソフトが設定をしたのでしょうが、MySQL にアクセスする
    アカウントを知ることはできますか?
    それがわからなければ、EXCEL から MySQL にアクセスすることができません。

    しかし、もし既にそのソフトが設定したODBCのデータソース(下記参照)
    http://homepage2.nifty.com/kasayan/vba/database.htm
    があるのであれば、それを指定することでデータのやり取りは可能となります。

    また現時点で、Access は MySQL のデータを閲覧、更新ができる状態であるという
    認識でよいでしょうか。
  • id:ReoReo7
    いろいろとお世話になっております。

    >Access と MySQL はその某かのソフトが設定をしたのでしょうが、MySQL にアクセスするアカウントを知ることはできますか?

    以下はMySQLのアカウントをセッティングするものでしょうか?
    create database sample_db;
    grant all on sample_db.* to sample_user@'%' identified by 'sample2';
    flush privileges;

    とあります。

    DB名=sample_db
    アカウント=sample_user password=sample2
    ということでしょうか。

    >しかし、もし既にそのソフトが設定したODBCのデータソース(下記参照)があるのであれば、それを指定することでデータのやり取りは可能となります。
    この箇所、良くわらからなかったのですが、関係ありそうな情報では
    1:ODBC.mdbというAccessのファイルがあります。
    2:そのファイル中で、各テーブルのリンクテーブル マネージャは全て
    DNS=sample_db;DATABASE=sample_db
    と示しております。

    >また現時点で、Access は MySQL のデータを閲覧、更新ができる状態であるという認識でよいでしょうか。
    はい、そうです。


    いろいろと至らぬ点ありますがよろしくおねがい致します。
  • id:ReoReo7
    皆様、いろいろとお世話になりました。
    回答を得られたので、質問を終了したいと思います。

    次質問:
    http://q.hatena.ne.jp/1232352540
  • id:ReoReo7
    プログラムの作成をありがとうございました。

    早速ODBC5.1などインストールの上、試してみました。
    本プログラムは今日から使用していきたいと思います。

    さて、2点データへの書き込み仕様を変更したいのでコメントさせて下さい。

    1:

    データ入力前のデータテーブルについて、全てのデータの削除をしてから入力を開始するようにお願いします。


    2:

    >データは数値と文字列に応じて形式を整えていますが、文字列型に数字のデータを入れる場合、
    >うまく動かない可能性があります。
    >その際は、カラムにデータ型を指定する必要がありますので、その際はコメントください。

    これについて、

    EXCEL内データは、
    1行目・・・・・カラム名(例:ID,userName, price, address 等)
    2行目以降・・・データ(例:001,山田, 25000, 東京都千代田区千代田1番)
    のように、1列目に「数値が文字列型で表現されているもの」が代入されるデータも想定しています。現状のプログラムでは、代入する際に0がカットされてしまうようです。結果例としては代入後のDBを見れば、例えば

    1
    10
    11
    12

    19
    2
    20
    21

    のような配置になってしまっています。これを

    001
    002
    003

    のような結果が入力されるようにするには、どうしたら良いでしょうか?

    尚、IDはX001のように、文字列で扱う場合と、00001のように、「数値が文字列型で表現されているもの」の場合があります。

    マクロ実行ファイルのSheet1で
    A列・・・ID(処理の範囲:空のセルで終了)
    B列・・・EXCEL ファイルのフルパス(例:D:\Data\sample.xls)
    C列・・・Access ファイルのフルパス(例:D:\DB\sample.mdb)
    D列・・・格納先のテーブル名(例:sampleTable1)
    E列・・・書き込み結果、エラー情報の出力
    F列・・・EXCELファイルの1列目(ID)のフィールド型(0:文字列(X001など)、n:数値が文字列型で表現されているもの。nは文字列の桁数(0001なら4)。)
    のように仕様を変更しても構いません。
  • id:ReoReo7
    上記2:の問題について、自分でプログラムを変更することによって解決できました。
  • id:ReoReo7
    上記、1:の問題について、自分でプログラムを変更することによって解決できました。 お騒がせ致しました。
  • id:Mook
    ご自身で解決できたようで、なによりです。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません