エクセルのマクロで文字の置換について

質問

エクセルのマクロを使った文字の置換についてお聞きしたことがありましす。
やりたいイメージは例として添付の画像としてアップしましたが、

・エクセルシートに英数文字列の表がある（例では2行2列ですが、実際は6行48列ぐらいです）
・その表を各英数文字列の間に半角カンマ","を入れて、メモ帳に貼り付けたい
　　※実際メモ帳に貼り付ける動作は手動で行うので、クリップボード上で「各英数文字列
　　　の間に半角カンマ","が入っている状態」で構いません。
・各英数文字列の最初の2文字は必ず"0a"、その後の英数字は3桁または4桁です。

ちなみに、エクセルシートで英数文字列の表をそのままコピーしてメモ帳でペーストすると
各英数文字列の間にスペース（空白）が入ってしまいます。
その後、メモ帳で置換などを使い半角カンマ","を入れてもいいのですが、そこまでをマクロで行いたいと思っております。

上記のことがそもそもエクセルのマクロで出来るかどか分かりませんが、なにか有用な方法がございましたらご教授お願い致します。
　※使用OS：Windows7、エクセルバージョン：2007

回答No.3

tsubuyuki · ベストアンサー

作業ブックやらテキスト保存やら必要無いパターンもどうぞ。
言ってしまえば、クリップボードに直接渡すやり方です。
（厳密に言うとちょっとだけ違いますが、気にしなくてＯＫです。）

Sub CBSample()
Dim buf As String, CB As New DataObject

With CB
　　.SetText ""
　　.PutInClipboard
End With

For i = 3 To Range("C3").End(xlDown).Row
　　buf = ""
　　For j = 3 To Range("C3").End(xlToRight).Column
　　　　If buf = "" Then
　　　　　　buf = Cells(i, j).Value
　　　　Else
　　　　　　buf = buf & "," & Cells(i, j).Value
　　　　End If
　　Next

　　　　With CB
　　　　　　.GetFromClipboard
　　　　　　.SetText .GetText & buf & vbNewLine
　　　　　　.PutInClipboard
　　　　End With
　　Next
End Sub

ただし、「Microsoft Forms 2.0 Object Library」の参照が必要です。
ツール→参照設定　から探してチェックを入れるか、
（適当に）ユーザーフォームを一つ作ると参照に追加されます。
このユーザーフォームは、白いままで大丈夫ですし、すぐに解放しても大丈夫ですよ。
詳細は別途お調べくださいませ。

あ、データの範囲をＣ３セルから
　「３行目のデータがある一番右の列」
　「Ｃ列目のデータがある一番下の行」
と設定していますので、ここら辺は適当に加工くださいませ。

お礼

ありがとうございます。
まさに求めていました方法でした！
No.4の回答も参考にさせて頂きます。

回答No.4

tsubuyuki

Sub CBSample()
Dim buf As String, CB As New DataObject

　　With CB
　　　　.SetText ""
　　　　.PutInClipboard
　　End With

　　For i = 3 To Range("C3").End(xlDown).Row
　　　　buf = ""
　　　　For j = 3 To Range("C3").End(xlToRight).Column
　　　　　　If buf = "" Then
　　　　　　　　buf = Cells(i, j).Value
　　　　　　Else
　　　　　　　　buf = buf & "," & Cells(i, j).Value
　　　　　　End If
　　　　Next

　　　　With CB
　　　　　　.GetFromClipboard
　　　　　　.SetText .GetText & buf & vbNewLine
　　　　　　.PutInClipboard
　　　　End With
　　Next

'　　If MsgBox("メモ帳を起動しますか？", vbYesNo) = vbYes Then　'必要ならどうぞ
　　　　Shell "C:\windows\NOTEPAD.EXE", vbNormalFocus
'　　　　　　For i = 1 To 1000　　　　'擬似Wait　必要に応じて増やす
'　　　　　　　　DoEvents
'　　　　　　Next
　　　　SendKeys "^V", True
'　　End If
End Sub

オマケです。
メモ帳を起動して、貼り付けます。
ＳｅｎｄＫｅｙｓを使っちゃっているので、
ＰＣによっては挙動が怪しくなる可能性もあります。
おかしいなと思ったら、擬似Waitの数字を増やせばなんとかなるかもしれません。
最近のＰＣだったら大抵は無くても大丈夫だと思います。
現状はコメント扱いをしていますので、
必要と思ったら各行頭の「 ’」を削除してお使いください。

ちなみに現状でのコードでセル数１００００セル、メモ帳起動まででも
多分、気にならない速度ですよ。

回答No.2

米沢 栄蔵（@YON56）

保存したいデータ範囲のセルがC2～Z10であるとして、
それを.txt形式のファイル(AAA.txt)として保存するマクロと、
そのデータを.xls形式のファイル(BBB.xls)に保存したいデータ範囲と同じ位置に
書き込むマクロを書きます。

AAA.txtのFullNameをC:\XXX\YYY\AAA.txt
BBB.xlsのFullNameをC:\XXX\ZZZ\BBB.xlsとします。

Dim A,,AA,BB

AA="C:\XXX\YYY\AAA.txt"
BB="C:\XXX\ZZZ\BBB.xls"

'ファイル(AAA.txt)として保存するマクロ
Open AA For Output As #33 '33は任意(私の趣味)
For A =2 To 10
Write #33,Cells(A,3 ), Cells(A, 4), Cells(A, 5), Cells(A,6) _
, Cells(A, 7), Cells(A,8), ・・・・・・・・・・・・・, Cells(A,26)
Next A
Close #33

'ファイル(BBB.xls)に書き込むマクロ
Open BB For Input As #34 '34は任意(私の趣味)
A=2
Do While Not EOF(34)
Input #34, Cells(A,3 ), Cells(A, 4), Cells(A, 5), Cells(A,6) _
, Cells(A, 7), Cells(A,8), ・・・・・・・・・・・・・, Cells(A,26)
A=A+1
Loop
Close #34

AAは自動保存されますが、BBは手動保存です。
BBは用紙と考え事前に用意しておきます。
その際、データ書き込み位置の選択は自由です。
保存したいデータは必ずしも連続している必要はありません。
読み込みたいデータは連続した形式で読み込まれますので、
欲しくないデータを処理する措置が必要です。(データを書き込んだ後""にするとか)

マクロの作動速度は極めて速いので驚かないように。

お礼

ありがとうございます。
ファイルに保存するところのマクロはとても参考になりました！

回答No.1

hallo-2007

仮に、シートを追加して、データの部分のみコピーします。
そのまま、名前をつけて保存
ファイルの種類を　ＣＳＶ（カンマ区切り）を選択して
適当な場所に、適当な名前で保存してみてください。
ご希望のテキストファイルが出来ます。
マクロが必要であれば、マクロの記録で得られるはずです。

お礼

ありがとうございます。
実はこの方法で試しましたが、ファイルへの出力はやりたくなかったので
他の方法を探していました。