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

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

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

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

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

解決済

EXCEL VBA でEXCELデータとCSVデータをJoinして読込む方法

km0815.lj
km0815.lj

総合スコア10

VBA

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

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

2回答

0グッド

0クリップ

408閲覧

投稿2022/10/12 03:49

前提

これまで2つのエクセルブックのデータをVBAでJoinし別のエクセルブックを作成していましたが
上流のシステムが変更になり、1つがCSV(UTF-8(BOM付き))のデータになりました。

CSVになったデータのプロパティ設定を変更しただけでは、エラーになり読み込めませんでした。
EXCELデータとCSVデータをJoinして読込む方法はありますでしょうか。

実現したいこと

EXCELデータとCSVデータをJoinして読込む。

EXCELデータ

項目1項目2
A1B1
A2B2
A3B3

CSVデータ
項目1,項目3
A1,C1
A2,C2

結果

項目1項目2項目3
A1B1C1
A2B2C2
A3B3

※実際はWhere句でデータの絞り込み等を行っています。

発生している問題・エラーメッセージ

実行時エラー -214721865(80040e) オブジェクト 'C:\temp\CSV\test[BOM].CSV' が見つかりませんでした。 オブジェクトが存在していること、名前やパス名が正しいことを確認してください。

該当のソースコード

VBA

1'エクセルファイル読み込み用 2Dim SelFile1 As Variant 3Dim ConFile1 As String 4Dim cn1 As New ADODB.Connection 5Dim Rs1 As New ADODB.Recordset 6Dim sEXTENDED1 As String 7Dim sSrcDir1 As String ' 接続先フォルダ 8Dim select1 As String 9Dim FSO1, PathName1 As String, FileName1 As String 10 11'CSVファイル読み込み用 12Dim SelFile2 As Variant 13Dim ConFile2 As String 14Dim cn2 As New ADODB.Connection 15Dim Rs2 As New ADODB.Recordset 16Dim sEXTENDED2 As String 17Dim sSrcDir2 As String ' 接続先フォルダ 18Dim select2 As String 19Dim FSO2, PathName2 As String, FileName2 As String 20 21Dim Wb As Workbook 22 23’※EXCELとCSVを個別に読込テストしたロジックも残していますので、無意味な個所もあり。 24Sub TEST1() 25 26 'エクセルファイル指定 27 SelFile1 = Application.GetOpenFilename("Microsoft Excelブック,*.xls?") 28 If VarType(SelFile1) = vbBoolean Then 29 Exit Sub 30 End If 31 ConFile1 = "TEXT;" & SelFile1 32 33 'CSVファイル指定 34 SelFile2 = Application.GetOpenFilename("CSVファイル(*.csv),*.csv?") 35 If VarType(SelFile2) = vbBoolean Then 36 Exit Sub 37 End If 38 ConFile2 = "TEXT;" & SelFile2 39 40 Set FSO1 = CreateObject("Scripting.FileSystemObject") 41 FileName1 = FSO1.GetFileName(SelFile1) 42 PathName1 = FSO1.GetParentFolderName(SelFile1) 43 sSrcDir1 = PathName1 44 45 Set FSO2 = CreateObject("Scripting.FileSystemObject") 46 FileName2 = FSO2.GetFileName(SelFile2) 47 PathName2 = FSO2.GetParentFolderName(SelFile2) 48 sSrcDir2 = PathName2 49 50 'CSV(UTF-8)用にSchema.iniを作成 51 Open PathName2 & "\Schema.ini" For Output As #1 52 Print #1, "[" & FileName2 & "]" 53 Print #1, "CharacterSet=65001" 54 Print #1, "Format = CSVDelimited" 55 Print #1, "ColNameHeader = True" 56 Print #1, "MaxScanRows = 0" 57 Close #1 58 59 ' プロパイダの設定 60 cn1.Provider = "Microsoft.ACE.OLEDB.16.0" 61 62 ' 読み込むファイルの格納フォルダのパス 63 cn1.Properties("Data Source") = sSrcDir1 64 65 ' その他のプロパティの設定 66 sEXTENDED1 = "Excel 12.0" 67 sEXTENDED1 = sEXTENDED1 & ";HDR=Yes" 68 cn1.Properties("Extended Properties").Value = sEXTENDED1 69 70 ' 接続開始 71 cn1.Open SelFile1 72 73 ' プロパイダの設定 74 cn2.Provider = "Microsoft.ACE.OLEDB.16.0" 75 ' 読み込むファイルの格納フォルダのパス 76 cn2.Properties("Data Source") = sSrcDir2 77 78 ' その他のプロパティの設定 79 sEXTENDED2 = "TEXT" 80 sEXTENDED2 = sEXTENDED2 & ";FMT=Delimited" 81 sEXTENDED2 = sEXTENDED2 & ";HDR=Yes" 82 cn2.Properties("Extended Properties").Value = sEXTENDED2 83 84 ' 接続開始 85 cn2.Open 86 87 select1 = "SELECT t01.*,t02.* " 88 select1 = select1 & "FROM [" & SelFile1 & "].[Sheet1$] as t01 " 89 select1 = select1 & "LEFT OUTER JOIN [" & SelFile2 & "] as t02 " 90 select1 = select1 & "on t01.[項目1] = t02.[項目1] " 91 ' SQL実行 92 Rs1.Open select1, cn1 93 94 If Rs1.EOF Then 95 ' 結果が1行もない場合終わり 96 Else 97 ' 結果をそのまま表示 98 Set Wb = Workbooks.Add 99 Wb.Worksheets(1).Select 100 101 Cells(20, 1).CopyFromRecordset Rs1 102 'Cells(20, 20).CopyFromRecordset Rs2 '個別に読み込んだ結果の格納時に使用 103 104 End If 105 106 Rs1.Close 107 cn1.Close 108 Rs2.Close 109 cn2.Close 110 111PROC_EXIT: 112 On Error Resume Next 113 114 ' 後処理 115 Set Rs1 = Nothing 116 Set cn1 = Nothing 117 Set Rs2 = Nothing 118 Set cn2 = Nothing 119 120 Exit Sub 121 122PROC_ERR: 123 MsgBox "ADO接続(CSV/TEXT)エラー:" & Err.Description & "(" & Err.Number & ")" & vbCrLf & sSrcDir1 & vbCrLf & sSrcDir2, vbCritical 124 GoTo PROC_EXIT 125 126End Sub

試したこと

Joinしなければ、EXCELデータ、CSVデータを個別には読み込むことはできました。

補足情報(FW/ツールのバージョンなど)

EXCEL 2016

以下のような質問にはグッドを送りましょう

  • 質問内容が明確
  • 自分も答えを知りたい
  • 質問者以外のユーザにも役立つ

グッドが多くついた質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

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

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

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

下記のような質問は推奨されていません。

  • 間違っている
  • 質問になっていない投稿
  • スパムや攻撃的な表現を用いた投稿

適切な質問に修正を依頼しましょう。

回答2

1

SQLのFROM句に、異なる接続先のテーブルを指定することはできません。
CSVとエクセルシートでは別の接続先になりますので、それをJOINするのは不可能ということになります。

対処法としては、CSVファイルを同じブックのシートに読み込むか、シートをCSVファイルと同じフォルダーに出力すれば、同じ接続先になりますので、Connectionに接続すればJOINが可能になります。

別の方法としては、SQLのIN句で別の接続先を指定できるの Connection はエクセルブックでIN句でCSVに接続という方法もあります。ただし、同じFROM区内では接続先は一つという制限がありますので、IN句で接続する方はサブクエリにする必要があります。

ADOでの異なる種類のファイルへのSQL (FROM ... IN ... / INTO ... IN ...) : スクリプトちょっとメモ

投稿2022/10/13 02:39

hatena19

総合スコア32001

km0815.lj😄を押しています

良いと思った回答にはグッドを送りましょう。
グッドが多くついた回答ほどページの上位に表示されるので、他の人が素晴らしい回答を見つけやすくなります。

下記のような回答は推奨されていません。

  • 間違っている回答
  • 質問の回答になっていない投稿
  • スパムや攻撃的な表現を用いた投稿

このような回答には修正を依頼しましょう。

回答へのコメント

km0815.lj

2022/10/13 04:33

ご回答ありがとうございます。 リンク先のメモも参考になりました。 こちらの方法でも試してみます。

1

ベストアンサー

オブジェクト 'C:\temp\CSV\test[BOM].CSV' が見つかりませんでした。

CSV ファイルのパスには "[" や "]" が含まれないようにして下さい。

vba

1Sub TEST2() 2On Error GoTo PROC_ERR 3 4 Dim cn As New ADODB.Connection 5 Dim rs As New ADODB.Recordset 6 Dim sSQL As String 7 Dim oFSO As Object 8 Dim wb As Workbook 9 10 'エクセルファイル読み込み用 11 Dim vSelFile1 As Variant 12 Dim sConnectString1 As String 13 Dim sPathName1 As String 14 Dim sFileName1 As String 15 Dim sTableName1 As String 16 17 'CSVファイル読み込み用 18 Dim vSelFile2 As Variant 19 Dim sConnectString2 As String 20 Dim sPathName2 As String 21 Dim sFileName2 As String 22 Dim sTableName2 As String 23 24 'エクセルファイル指定 25 vSelFile1 = Application.GetOpenFilename("Microsoft Excelブック,*.xls?") 26 If VarType(vSelFile1) = vbBoolean Then 27 Exit Sub 28 End If 29 30 'CSVファイル指定 31 vSelFile2 = Application.GetOpenFilename("CSVファイル(*.csv),*.csv?") 32 If VarType(vSelFile2) = vbBoolean Then 33 Exit Sub 34 End If 35 36 '不正文字チェック 37 If InStr(1, vSelFile2, "[", vbBinaryCompare) + InStr(1, vSelFile2, "]", vbBinaryCompare) > 0 Then 38 MsgBox "パスに ""["" または ""]"" が含まれている CSV ファイルを読み込むことは出来ません。", _ 39 vbExclamation 40 Exit Sub 41 End If 42 43 '取得したそれぞれのフルパスからフォルダパスとファイル名を取得 44 Set oFSO = CreateObject("Scripting.FileSystemObject") 45 With oFSO 46 sFileName1 = .GetFileName(vSelFile1) 47 sPathName1 = .GetParentFolderName(vSelFile1) 48 sFileName2 = .GetFileName(vSelFile2) 49 sPathName2 = .GetParentFolderName(vSelFile2) 50 End With 51 Set oFSO = Nothing 52 53 'CSV(UTF-8)用にSchema.iniを作成 54 Open sPathName2 & "\Schema.ini" For Output As #1 55 Print #1, "[" & sFileName2 & "]" 56 Print #1, "CharacterSet=65001" 57 Print #1, "Format = CSVDelimited" 58 Print #1, "ColNameHeader = True" 59 Print #1, "MaxScanRows = 0" 60 Close #1 61 62 With cn 63 ' プロパイダの設定 64 .Provider = "Microsoft.ACE.OLEDB.16.0" 65 66 ' 読み込むファイルのパス 67 .Properties("Data Source") = vSelFile1 68 69 ' その他のプロパティの設定 70 sConnectString1 = "Excel 12.0" 71 sConnectString1 = sConnectString1 & ";HDR=Yes" 72 .Properties("Extended Properties").Value = sConnectString1 73 74 ' 接続開始 75 .Open vSelFile1 76 End With 77 78 sTableName1 = "Sheet1$" 79 80 ' csvファイルが保存されているフォルダへの接続文字列 81 sConnectString2 = "TEXT" 82 sConnectString2 = sConnectString2 & ";FMT=Delimited" 83 sConnectString2 = sConnectString2 & ";HDR=Yes" 84 sConnectString2 = sConnectString2 & ";CharacterSet=65001" 85 sConnectString2 = sConnectString2 & ";DATABASE=" & sPathName2 86 87 sTableName2 = sFileName2 88 sTableName2 = Replace(sTableName2, ".", "#", , , vbBinaryCompare) 89 90 sSQL = "SELECT t01.*,t02.* " 91 sSQL = sSQL & "FROM [" & sTableName1 & "] AS t01 " 92 sSQL = sSQL & "LEFT JOIN [" & sConnectString2 & "].[" & sTableName2 & "] AS t02 " 93 sSQL = sSQL & "ON t01.[項目1] = t02.[項目1] " 94 95 Debug.Print sSQL 96 97 ' SQL実行 98 rs.Open sSQL, cn 99 100 If rs.EOF Then 101 ' 結果が1行もない場合終わり 102 Else 103 ' 結果をそのまま表示 104 Set wb = Workbooks.Add 105 With wb.Worksheets(1).Cells(20, 1) 106 .CopyFromRecordset rs 107 .CurrentRegion.Select 108 End With 109 End If 110 111PROC_EXIT: 112 On Error Resume Next 113 114 ' 後処理 115 Set oFSO = Nothing 116 Set wb = Nothing 117 rs.Close 118 Set rs = Nothing 119 cn.Close 120 Set cn = Nothing 121 122 Exit Sub 123 124PROC_ERR: 125 MsgBox "ADO接続(CSV/TEXT)エラー:" & Err.Description & "(" & Err.Number & ")" & vbCrLf & vSelFile1 & vbCrLf & vSelFile2, _ 126 vbCritical 127 128 Resume PROC_EXIT 129End Sub

投稿2022/10/13 02:28

sk.exe

総合スコア155

km0815.lj😍を押しています

良いと思った回答にはグッドを送りましょう。
グッドが多くついた回答ほどページの上位に表示されるので、他の人が素晴らしい回答を見つけやすくなります。

下記のような回答は推奨されていません。

  • 間違っている回答
  • 質問の回答になっていない投稿
  • スパムや攻撃的な表現を用いた投稿

このような回答には修正を依頼しましょう。

回答へのコメント

km0815.lj

2022/10/13 02:53

ご回答ありがとうございます。 実際のファイルは[]ではなく()になっておりますが、エラーメッセージでは[]で表示されていました。 デバックでSQL文を表示すると以下の様になっておりました。 SELECT t01.*,t02.* FROM [C:\temp\CSV\test.xlsx].[Sheet1$] as t01 LEFT OUTER JOIN [C:\temp\CSV\test(BOM).CSV] as t02 on t01.[項目1] = t02.[項目1] また()もやめ、testBOM.CSVとしても 「C:\temp\CSV\testBOM.CSV' が見つかりませんでした」となりました。
sk.exe

2022/10/13 02:59

修正版のコードの方で実行してみて下さい。
km0815.lj

2022/10/13 03:03

すみません。 記載いただいたソースで実行すれば正しく動作致しました。 これで解決しそうです。 ありがとうございました。 ひとつご確認ですが、CSVのファイル名を「.」を「#」にしているのは、なぜでしょうか。 もしよろしければご教授ねがいます。
sk.exe

2022/10/13 04:28

・このコードにおいて使用されているのは Access のデータベースエンジン(ACE)である。 ・Access の命名規則において、"." をオブジェクトの名前に使用しないことが推奨されている。 https://learn.microsoft.com/en-us/office/troubleshoot/access/error-using-special-characters ・ACE によってテキストファイルにアクセスする際、そのファイル名をテーブル名として扱うことになるが、ファイル拡張子(.csv, .txtなど)を含んでいるため、上記の非推奨事項に引っ掛かる。 ・この場合、"." を "#" に置き換えてエスケープすることが可能である。 ・実際のところ、テーブル名(ファイル名)を[]で囲んで記述していれば "." のままでも一応は SQL を実行することは出来るが、安全性を考慮して念のため "#" に置換するようにしている。 私の意図をまとめるとこういうことです。 必須というわけではないので、そちらの分かりやすい方でどうぞ。
km0815.lj

2022/10/13 04:30

ご丁寧なご説明ありがとうございます。 良くわかりました。

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

ただいまの回答率
86.12%

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

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

質問する

関連した質問

同じタグがついた質問を見る

VBA

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

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。