レースIDを作成する知識を仕事に活かす

こんにちは。伊川です。YouTubeのご視聴者様から下記のような質問がありました。

質問内容

この記事を読んでほしい方

・文字列をVBAで操作したとおもっている人

・ACCESSでExeclのように関数を利用して、文字列を操作したい人

質問のキーワード

この質問を回答するためには、99の連番があるということです。そのメルクマールがあるのであれば、何とかなります。

Excelのワークシート関数を使って文字列を分解してみる

関数を使って分解すると下図のようになります。

セルB3　　=LEFT(A3,FIND(“99”,A3)+1)

セルC3　　=RIGHT(A3,LEN(A3)-FIND(“99”,A3)-1)

セルD3　　=LEFT(C3,2)

セルE3　　=MID(C3,3,LEFT(C3,2))

セルF3　　=B3&” “&D3&” “&E3

こんな感じで分解できます。

よく、検索ワードを【Excel　名前　分割】で検索すると、色々なサイトがHITしますが、その中に記載のあるコードの応用です。

ACCESSでも文字列を分解できる

ACCESSで同じことを表現すると、下記のようになります。

ACCESSの場合は、Excelのようにセルには入力できないので、下図のようにします。

?

ACCESSは、FIND関数は使えないので、下記のように抽出条件を記載します。

文字列分解_1: Left([文字列],InStr([文字列],”99″)+1)

文字列分解_2: Right([文字列],Len([文字列])-InStr([文字列],”99″)-1)

文字列分解_3: Left([文字列分解_2],2)

文字列分解_4: Mid([文字列分解_2],3,Left([文字列分解_2],2))

文字列分解_5: [文字列分解_1] & ” ” & [文字列分解_3] & ” ” & [文字列分解_4]

FIND関数ではなくInStr関数を利用します。

ACCESSとExcelのコードを比較

セルB3　　=LEFT(A3,FIND(“99”,A3)+1)

文字列分解_1: Left([文字列],InStr([文字列],”99″)+1)

Excelは、セルで指定するのに対して、ACCESSは、フィールド名で指定します。

あとは、FIND関数と、InStr関数を使うとう違いです。

これで、すでにACCESSに単純に15桁の数字が入力されていても、Excelと同じように分解できます。

さらに追加の質問が来た

追加方法は素人なので アクセスのクイックアクセス　外部データ→新しいデータソース→ファイルから→EXCEL→レコードのコピーを次のテーブルに追加する を行っております。

vbaでテーブルへの追加方法もございますでしょうか？

2つ目の質問のキーワードは、

・vbaでテーブルへの追加方法

これが、キーワードになります。

つまり、Excelで、15桁の文字列を分解してから、ACCESSへ転送すればいいんじゃね？という発想ができれば、もうすぐゴールになります。

ExcelのVBAで15桁の文字を整形する

難しいと思われるかもしれませんが、すでに、ここまでの中で、答えが出ています。気づいた方は、伊川の動画をしっかり観てくれていると思います。

下記がACCESSの抽出文になります。

文字列分解_1: Left([文字列],InStr([文字列],”99″)+1)

これがVBAの抽出文になります。

Cells(i, 2) = Left(Cells(i, 1), InStr(Cells(i, 1), “99”) + 1)

なにが気づきましたか？

そうです。ACCESSの抽出文の

文字列分解_1: Left([文字列],InStr([文字列],”99″)+1)　の文字列の部分をCells(i, 2)へ変更するだけです。

文字列分解_1: Left([文字列],InStr([文字列],”99″)+1)

Cells(i, 2) = Left(Cells(i, 1), InStr(Cells(i, 1), “99”) + 1)

簡単ですよね。

VBAのコードを記載しましょう。

Dim i As Long

maxrow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To maxrow

Cells(i, 2) = Left(Cells(i, 1), InStr(Cells(i, 1), “99”) + 1)

‘文字列分解_1:left([文字列],InStr([文字列],”99″)+1))⇒ACCESSの抽出文

Cells(i, 3) = Right(Cells(i, 1), Len(Cells(i, 1)) – InStr(Cells(i, 1), “99”) – 1)

‘文字列分解_2: Right([文字列],Len([文字列])-InStr([文字列],”99″)-1) ⇒ACCESSの抽出文

Cells(i, 4) = Left(Cells(i, 3), 2)

‘文字列分解_3: Left([文字列],2) ⇒ACCESSの抽出文

Cells(i, 5) = Mid(Cells(i, 3), 3, Left(Cells(i, 3), 2))

‘文字列分解_4: Mid([文字列分解_2],3,Left([文字列分解_2],2)) ⇒ACCESSの抽出文

Cells(i, 6) = Cells(i, 2) & “　” & Cells(i, 4) & “　” & Cells(i, 5)

Next　i

15桁の整形したExcelのデータをACCESSへ転送する

これが、ACCESSへ転送するための魔法のコードです。

今回は、下記のコードの説明は割愛します。

また、次の機会に説明します。詳しくはサンプルファイルを参照してください。

Dim con As New ADODB.Connection

Dim rs As New ADODB.Recordset

Dim conStr As String

conStr = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source= ” & ActiveWorkbook.Path & “￥転送.accdb“

con.Open ConnectionString:=conStr

rs.Open Source:=”MT_サンプル追加“, ActiveConnection:=con, _

CursorType:=adOpenKeyset, LockType:=adLockOptimistic

Dim j As Long

j = 2

With rs

Do Until Cells(j, 1) = “”

.AddNew

.Fields(“文字列“).Value = Cells(j, 1)

.Fields(“文字列改造“).Value = Cells(j, 6)

.Update

j = j + 1

Loop

End With

rs.Close

con.Close

MsgBox “データ取込完了”

ExcelからACCESSへ転送するための条件

まとめ

質問者様は、CSVファイルから、ACCESSへ貼り付けているとのことでしが。

伊川も同じような状況ですが、ExcelのVBAを利用して、文字列を整形して、ACCESSへ転送しています。

これが一番時間を短縮できる方法だと思っています。

ExcelからACCESSへの転送については、またの機会に詳しく説明したいと思っています。

Related posts:

ExcelとACCESSの連携　発達障害、算数を理解していない幼児と九九の練習　その1 ACCESSとExcelの連携　在庫管理　その11 ACCESSとExcelの連携　お好みのデータをExcelへ転送 【ACCESSとExcelの連携】リストボックスの値をセルに転記