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

質問内容

さて動画の4分ごろ メモ帳で数字にスペースを入れるのを見て思いついたのですが
アクセスで VBAもしくは更新クエリを使いフォームに自動で表示出来ないかと思いました。
悩んでいる事は
数字がランダムで15桁ありその中に必ず99の連番があります
(99の位置は不定)例) 111119911222222見やすくする為に99の後にスペースを入れ さらにその2文字後ろにもスペースを入れたいのです例) 111119911222222 → 1111199 11 222222急いでおりませんお手隙の際、ご指導頂けたら幸いです。

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

・文字列を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へ転送するための条件

サンプルファイルを作動させるためには転送するExcelとACCESSが同一フォルダ内にあること。

赤のマーカー部分と、青色のマーカー部分を変えること。

下図のように、Microsoft Office 16.0 Object Libraryにチェックを入れること。


まとめ

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

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

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

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

ExcelとACCESSの連携は、最強のコンビである
スポンサーリンク

Twitterでフォローしよう

おすすめの記事