Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10全般 : その他)
EXCELからCSVファイルをACCSESSに追加・編集を行いたい
投稿日時: 20/11/11 12:47:13
投稿者: mndkyui

いつもお世話になっています。
 
EXCELからACSESSを実行し、データベースを作成・編集するところまでやりたいのですが、
アクセスの方法がわかっておらず教えていただきたいです。
 
【やりたいこと】
CSVデータとして出力したデータ一覧を、ACCSESSにインポートしたい。
その上でSQL操作をしたい。
 
 
【困っていること】
EXCELVBAからACCSEEにアクセス・SCVデータを入れる操作をする方法を探したのですが、
いまいち要領を得られない状態です。
ご面倒ではありますが、参考になるサイトまたは基礎をご教示いただきたいです。
 
 
以上、どうぞよろしくお願いいたします。

回答
投稿日時: 20/11/11 14:21:14
投稿者: sk

引用:
EXCELからACSESSを実行し、データベースを作成・編集する

引用:
CSVデータとして出力したデータ一覧を、ACCSESSにインポート

引用:
その上でSQL操作をしたい。

・Access データベース( accdb ファイル)を新規作成する方法
 
・Access データベース上に新規のテーブル定義を作成する方法
 
・CSV ファイルを既存の Access データベースにインポートする方法
 (既存のテーブルにレコードを追加、または新規テーブルとして作成)
 
・既存の Access データベースにアクセスし、任意の
 ( CSV ファイルから取り込まれたレコードが格納された)
 テーブルを参照する何らかの SQL を実行する方法
 
・上記の SQL の実行結果を何らかの形で出力する方法
 
とりあえず、以上のどれを行ないたいのか、どれが出来ていて、
どれが出来ていないのかを具体的に明記されることをお奨めします。
 
もし「ほとんど分からない」ということであれば、まずは
本来の目的を踏まえた上で)取り掛かるべき問題を
どれか 1 つに絞り、順番に解決するようにされた方がよいでしょう。

投稿日時: 20/11/11 14:47:29
投稿者: mndkyui

sk さんの引用:

・Access データベース( accdb ファイル)を新規作成する方法
 
・Access データベース上に新規のテーブル定義を作成する方法
 
・CSV ファイルを既存の Access データベースにインポートする方法
 (既存のテーブルにレコードを追加、または新規テーブルとして作成)
 
・既存の Access データベースにアクセスし、任意の
 ( CSV ファイルから取り込まれたレコードが格納された)
 テーブルを参照する何らかの SQL を実行する方法
 
・上記の SQL の実行結果を何らかの形で出力する方法
 
とりあえず、以上のどれを行ないたいのか、どれが出来ていて、
どれが出来ていないのかを具体的に明記されることをお奨めします。
 
もし「ほとんど分からない」ということであれば、まずは
本来の目的を踏まえた上で)取り掛かるべき問題を
どれか 1 つに絞り、順番に解決するようにされた方がよいでしょう。

 
>sk さん
 
アドバイスありがとうございます。
以上を受けて改めて教えていただきたいことを下記に記載します。
 
skさんの上げていただいた内容を踏まえると、私が実施したいことは
・既存のAccess データベースにアクセスし、既存のテーブルにCSVデータを追加したい(テーブルA)
・テーブルAでのSQLの実行結果を、新規テーブルで作成したい(テーブルB)
・テーブルBをEXCELシートに出力したい
 
以上になります。
どれができていてどれができていないかについてですが、
【できていること】
・SQL文の実行
・SQL実行結果を新規テーブルに出力する方法
・新規テーブルを作成する方法
 
【できていないこと】
・EXCELからAccessのファイルを開く処理
・特定のテーブル(上記で言うテーブルB)の内容をEXCELのシートに出力する処理
 
以上になります。
skさんのおっしゃっている内容に添えていなければ教えてください。
改めて、どうぞよろしくお願いいたします。

回答
投稿日時: 20/11/11 16:19:08
投稿者: sk

引用:
【できていること】
SQL文の実行
SQL実行結果を新規テーブルに出力する方法
・新規テーブルを作成する方法

これらについては、主にどのオブジェクトの機能を
使用されているのでしょうか
 
・Access.DoCmd オブジェクト(の RunSQL メソッドを用いて
  SELECT ... INTO ... FROM ... 文を実行している等)
 
・DAO.Database オブジェクト(の Execute メソッド)
 
・ADODB.Connection オブジェクトや ADODB.Command オブジェクト
 (の Execute メソッド)

投稿日時: 20/11/11 16:28:37
投稿者: mndkyui

sk さんの引用:
引用:
【できていること】
SQL文の実行
SQL実行結果を新規テーブルに出力する方法
・新規テーブルを作成する方法

これらについては、主にどのオブジェクトの機能を
使用されているのでしょうか
 
・Access.DoCmd オブジェクト(の RunSQL メソッドを用いて
  SELECT ... INTO ... FROM ... 文を実行している等)
 
・DAO.Database オブジェクト(の Execute メソッド)
 
・ADODB.Connection オブジェクトや ADODB.Command オブジェクト
 (の Execute メソッド)

 
>sk さん
何度も申し訳ないです。
主に3つ目の【ADODB.Connection オブジェクト】を使用しております。[/b]

回答
投稿日時: 20/11/11 16:39:38
投稿者: Suzu

1. そのマクロを実行するマシンには必ずAccessがインストールされていますか?
 
2. CSVファイル は ファイル名は及びパスは固定でしょうか?
  それとも、変わる可能性はありますか?
 
3. CSV の上位 25行 にて、データ型を自動判定されますが矛盾は発生しえませんか?
    例えば
     テキスト型フィールドに対しインポートするフィールドのCSVのデータ
     上位25行の間では、数字のみを含むデータしか無いが、
     26行目以降にアルファベットを含むデータがある
 
4. インポート定義は使用可能ですか?
 
5. テーブルBをテーブル作成クエリ等で作成する場合、
   次回以降 そのテーブルBは、先に削除し、改めてテーブルBを作成する事になります。
   出来れば、テーブルの削除ではなく、データの削除では対応できませんか?
  或いは、テーブルBを作成せずに、選択クエリから直接Excelに出力では対応できませんか?

投稿日時: 20/11/11 16:59:39
投稿者: mndkyui

Suzu さんの引用:
1. そのマクロを実行するマシンには必ずAccessがインストールされていますか?
 
2. CSVファイル は ファイル名は及びパスは固定でしょうか?
  それとも、変わる可能性はありますか?
 
3. CSV の上位 25行 にて、データ型を自動判定されますが矛盾は発生しえませんか?
    例えば
     テキスト型フィールドに対しインポートするフィールドのCSVのデータ
     上位25行の間では、数字のみを含むデータしか無いが、
     26行目以降にアルファベットを含むデータがある
 
4. インポート定義は使用可能ですか?
 
5. テーブルBをテーブル作成クエリ等で作成する場合、
   次回以降 そのテーブルBは、先に削除し、改めてテーブルBを作成する事になります。
   出来れば、テーブルの削除ではなく、データの削除では対応できませんか?
  或いは、テーブルBを作成せずに、選択クエリから直接Excelに出力では対応できませんか?

 
>Suzu さん
 
ありがとうございます。一つずつ回答します。
 
1.使用するPCに搭載されているofficeはExcel・Accsessは標準搭載と考えてください
2.CSVファイルは変動です。この点に関しては、EXCELの方から取り込むSCVファイルを指定する処理を施す予定です(GetOpenFilenameメソッドを使う想定です)
3.SCVデータの1行目にのみ各列名を設定しています。それ以外はすべて矛盾なしのデータです。
 その意味で言うと、1行目と2行目以降で矛盾が生じるという事にはなります
4.ごめんなさい、定義可不可は不明です。
5.データ削除でも対応可能です。
 
どうぞよろしくお願いいたします

回答
投稿日時: 20/11/11 18:11:45
投稿者: sk

引用:
主に3つ目の【ADODB.Connection オブジェクト】を使用しております。

引用:
【できていないこと】
・EXCELからAccessのファイルを開く処理
・特定のテーブル(上記で言うテーブルB)の内容をEXCELのシートに出力する処理

(標準モジュール)
------------------------------------------------------------
Sub ImportTextToAccessByADO()
On Error GoTo Err_ImportTextToAccessByADO
 
    Dim strDatabasePath As String
     
    'Access データベースのフルパス
    strDatabasePath = "C:\FolderName\DatabaseName.accdb"
     
    If Dir(strDatabasePath) = "" Then
        MsgBox "データベースファイル """ & strDatabasePath & """ が見つかりません", _
               vbExclamation, _
               "ファイル参照エラー"
        Exit Sub
    End If
 
    Dim strTextFileFullPath As String
    Dim strTextFileName As String
 
    'テキストファイルのフルパスを指定
    strTextFileFullPath = "C:\FolderName\TextFileName.csv"
    'テキストファイルの名前を取得
    strTextFileName = Dir(strTextFileFullPath)
 
    If strTextFileName = "" Then
        MsgBox "テキストファイル """ & strTextFileFullPath & """ が見つかりません", _
               vbExclamation, _
               "ファイル参照エラー"
        Exit Sub
    End If
     
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
 
    Set cn = New ADODB.Connection
     
    'Access データベースとの接続
    With cn
        .CursorLocation = adUseServer
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                            "Data Source=" & strDatabasePath
        .Open
    End With
 
    Dim strTextFolderName As String
    Dim strConnect As String
    Dim strSQL As String
             
    'テキストファイルが保存されているフォルダパスの取得
    strTextFolderName = Left(strTextFileFullPath, _
                             Len(strTextFileFullPath) - Len(strTextFileName) - 1)
     
    '接続文字列の設定(あらかじめ Access データベース側でリンク定義を作成しておくこと
    strConnect = "Text;DSN=リンク定義名" & _
                 ";FMT=Delimited;HDR=NO;IMEX=2" & _
                 ";CharacterSet=932;ACCDB=YES" & _
                 ";DATABASE=" & strTextFolderName
     
    'Access データベースエンジンから CSV ファイルに直接リンクし、そのレコードを[テーブルA]に追加する INSERT INTO 文
    strSQL = "INSERT INTO [テーブルA] ([フィールド1], [フィールド2], [フィールド3])" & _
             " SELECT [フィールド1], [フィールド2], [フィールド3]" & _
             " FROM [" & Replace(strTextFileName, ".", "#") & "]" & _
             " IN '' '" & strConnect & "';"
             
    Debug.Print strSQL
    cn.Execute strSQL
     
    '[テーブルA]のレコードを[フィールド1]と[フィールド2]でグループ化して
    'グループごとに[フィールド3]の合計を集計した結果を
    '新規テーブル[テーブルB]として作成する SELECT ... INTO ... FROM ... 文
    '(既に[テーブルB]という名前のテーブルが存在する場合、元のテーブルは定義ごと削除される)
    strSQL = "SELECT [テーブルA].[フィールド1], [テーブルA].[フィールド2], Sum([テーブルA].[フィールド3]) AS [フィールド3の合計]" & _
             " INTO [テーブルB]" & _
             " FROM [テーブルA]" & _
             " GROUP BY [テーブルA].[フィールド1], [テーブルA].[フィールド2];"
             
    Debug.Print strSQL
    cn.Execute strSQL
     
    Set rs = New ADODB.Recordset
             
    strSQL = "SELECT * FROM [テーブルB]" & _
             " ORDER BY [フィールド1], [フィールド2];"
    Debug.Print strSQL
    rs.Open strSQL, cn, adOpenStatic, adLockReadOnly
     
    '新規ブックにレコードセットの内容を複写
    Workbooks.Add.Worksheets(1).Cells(1, 1).CopyFromRecordset rs
             
Exit_ImportTextToAccessByADO:
On Error Resume Next
             
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
     
    Exit Sub
 
Err_ImportTextToAccessByADO:
     
    Dim ErrMsg As String
    ErrMsg = Err.Number & ": " & Err.Description
    Debug.Print ErrMsg
    MsgBox ErrMsg, vbCritical, "AccessAutomation"
         
    Resume Exit_ImportTextToAccessByADO
End Sub
------------------------------------------------------------
 
ざっくりとしたサンプルとしては以上のような感じでしょうか。

回答
投稿日時: 20/11/12 12:03:44
投稿者: Suzu

skさんから既にサンプルコードが出ていますので、skさんのを参考にして頂ければ良いと思います。
 
質問に対しての回答を頂いておりますので、その質問の意図を説明させて頂きます。

引用:
1. そのマクロを実行するマシンには必ずAccessがインストールされていますか?
1.使用するPCに搭載されているofficeはExcel・Accsessは標準搭載と考えてください

取り込みに、Docmd.TransferText を使えるか
その場合に、インポート定義を使えるか の確認でした。
 
 
引用:
2. CSVファイル は ファイル名は及びパスは固定でしょうか?
  それとも、変わる可能性はありますか?
2.CSVファイルは変動です。この点に関しては、EXCELの方から取り込むSCVファイルを指定する処理を施す予定です(GetOpenFilenameメソッドを使う想定です)

ダイアログが必要なのか、インポート定義を使用できない場合、ファイル固定であれば、schema.ini を使用しやすいと考えました。
ダイアログ、schema.ini は、ネット上にでもサンプルがありますので、
そちらを参考にして頂ければ良いでしょう。
 
引用:
3. CSV の上位 25行 にて、データ型を自動判定されますが矛盾は発生しえませんか?
3.SCVデータの1行目にのみ各列名を設定しています。それ以外はすべて矛盾なしのデータです。
 その意味で言うと、1行目と2行目以降で矛盾が生じるという事にはなります

インポート定義(またはschema.ini)が必要かどうかの確認でした。
例では挙げていませんでしたが、「0」から始まる数字のみで、
文字列フィールドに入れようとすると、0が除去される事があります。
その場合はインポート定義が必要です。
インポート定義ではなく、schema.ini の場合には、取り込むcsvファイルと同じパスに配置する必要があり、また、先頭のファイル名の部分を書き換える必要があります。
 
skさんのコードの変数名をお借りするなら、下記の様なコードになるでしょうか。
このコードを実行するワークブックと同じフォルダに
Schema.ini の 1行目のファイル名部分を削除したファイルを「Schema」と言うファイル名称にて
保存しておき、「Schema」をテンプレートとして一行目にファイル名を追加しCSVファイルと同じフォルダに保存します。
必要に応じて、CSVを取り込んだ後に、Schema.ini は削除してください
 
Function ChangeSchema(strTextFolderName As String, strTextFileName As String) As Boolean
    Dim strSourceSchemaFileFullPath As String
    Dim strSchemaFileFullPath As String
    Dim i As Integer, j As Integer
    Dim buf As String
 
    strSourceSchemaFileFullPath = ThisWorkbook.Path & "\schema"
    strSchemaFileFullPath = strTextFolderName & "\schema.ini"
 
    If Len(Dir(strSchemaFileFullPath)) > 0 Then
        Kill strSchemaFileFullPath
    End If
 
    i = FreeFile
    Open strSourceSchemaFileFullPath For Input As #i
    j = FreeFile
    Open strSchemaFileFullPath For Append As #j
    Print #j, "[" & strTextFileName & "]"
 
    Do Until EOF(i)
        Line Input #i, buf
        Print #j, buf
    Loop
    Close #j
    Close #i
End Function
 
CSVに、ヘッダーがあるのであれば、
接続文字列 HDR=NO は、NOではなくYESに変えてください。
 
 
引用:
4. インポート定義は使用可能ですか?
4.ごめんなさい、定義可不可は不明です。

データ矛盾が起きないのであれば、インポート定義/Schema.ini は無くても動作する事があります。
その辺りは、テストを行ってみてください。
 
 
引用:
5. テーブルBをテーブル作成クエリ等で作成する場合、
   次回以降 そのテーブルBは、先に削除し、改めてテーブルBを作成する事になります。
   出来れば、テーブルの削除ではなく、データの削除では対応できませんか?
  或いは、テーブルBを作成せずに、選択クエリから直接Excelに出力では対応できませんか?
5.データ削除でも対応可能です。

 
SELECT 〜 INTO ステートメント
skさんは、
引用:
'(既に[テーブルB]という名前のテーブルが存在する場合、元のテーブルは定義ごと削除される)
との事ですが、当方ではエラーになります。
また、テーブル作成ですと、テーブル構造の削除・作成 を行うので、
ファイル破損の可能性が高くなるので、好ましくはないと思っています。
 
・DELETE * FROM テーブルB にてレコード削除 → INSERT INTO 〜SELECT
・既存のテーブル作成クエリSQLを、選択クエリのSQLに変え、直接レコードセットを開く
Bテーブルを後に使用しないのであれば、直接レコードセットを開いて良いと思います。

回答
投稿日時: 20/11/12 13:08:54
投稿者: sk

Suzu さんの引用:
skさんは、
引用:
'(既に[テーブルB]という名前のテーブルが存在する場合、元のテーブルは定義ごと削除される)

との事ですが、当方ではエラーになります。

ご指摘いただきありがとうございます。
おっしゃる通り、ADO を使用した場合はエラーになります。
失礼しました。
 
あらかじめ先に用意していた DoCmd.RunSQL メソッドを使ったサンプルを
流用した際、コメントを直しそびれていました。
( RunSQL メソッドを使用して SELECT ... INTO ... FROM 文を実行した場合は、
Access 側の方で先に[テーブルB]の削除が行われます)
 
Suzu さんの引用:
また、テーブル作成ですと、テーブル構造の削除・作成 を行うので、
ファイル破損の可能性が高くなるので、好ましくはないと思っています。

その点については私も同意見です。
(ただ SELECT 文の実行結果を Excel ワークシート上に
出力するだけなら、[テーブルB]自体なくてもよいでしょう)

投稿日時: 20/11/12 15:01:06
投稿者: mndkyui

>sk さん
 サンプルコードをありがとうございます。
 こちらで動かし、確認をしようと思います。
 抽象的なお話だったにも関わらず対応していただき、ありがとうございます。
 
 
>Suzuさん
 質問の意図の解説をありがとうございました。iniファイルの部分はとても有用だと思ったのですが、
 今回私が想定している処理の中には少し不向きかなと考えております。 
 ほかの解説の中身も、後ほど調べて理解に努めようと思います。
 また、補足なども細かく助かります。
 
 
お二方ありがとうございました。こちらで問題の内容には一区切りついたと思いますので
締めさせていただこうと思います。