1345042787 宅配3社のCSV伝票番号を参照し、エクセルメールに貼り付けたい!





元々の出荷用に作成したエクセルシート(住所印刷)を参照に、ヤマト運輸、佐川急便、郵便事業会社の出荷CSVから追跡番号などピックアップして
エクセルのメール送信用シートに貼り付けたいのです。

画像を見ていただければ、イメージがつかみやすくなるかと思います。


あとは追記いたします。

回答の条件
  • 1人5回まで
  • 13歳以上
  • 登録:2012/08/15 23:59:47
  • 終了:2012/08/23 00:00:04
id:naranara19

元の住所氏名など発送をまとめたxlsファイルが【住所印刷】

ヤマト運輸の伝票xlsファイルが【ヤマト出力用】
佐川急便の伝票csvファイルが【佐川出力用】
郵便事業会社の伝票csvファイルが【出荷実績一覧】

メールを送るxlsファイルが【エクセルメール】


といいます。実際には【】をのぞいた中身です。




それらがすべてデスクトップ上にあり、パスはC:\Users\yamada\Desktop\
とします。

【住所印刷】ファイルのみを開いた状態でマクロをスタートさせると、
住所印刷のファイルに書いてある名前を読み取り、宅配各社の名前とその同じ行にある、該当する追跡番号を読み取り、

エクセルメールの該当箇所にコピー&ペーストされるという形です。



エクセルファイルのそれぞれ、1行目の見出し語を頼りに感知してほしいのです。
(貼り付け先のエクセルメールだけ9行目が見出しです)




参照して貼り付けてほしい該当箇所はそれぞれ下記のようになります。



【住所印刷】


2行目(A列)商品名 (E列)計 (F列)送付先氏名 (L列)指定 (P列)メールアドレス1


【佐川出力用】
2行目(A列)お問合せ送り状№ (J列)お届け先名称1


【ヤマト出力用】
2行目(D列)伝票番号  (P列)お届け先名


【出荷実績一覧】
2行目(M列)お届け先 氏名 (DW列)お問い合わせ番号


※↑実際には(●列)の後にある言葉は1行目のそれぞれ該当列にあります。たとえば、【住所印刷】で商品名とあるセルは1Aで、2Aには「りんご」など具体的な商品名が入ります。



貼り付け先の
【エクセルメール】
(※このシートはシェアソフトのため、標準モジュールなどは開けませんが、貼り付けなどの操作は可能です)

※見出しが9行目。実際の貼り付け開始行が11行目となります。

9行目(D列)[TO]を探し出して、同列11行目から下に【住所印刷】のメールアドレスを貼り付け。
9行目(F列)[BCC] を探し出して、あらかじめマクロ先頭で設定しておいた、BCCアドレス「xyz@yahoo.co.jp」を貼り付け
9行目(N列)[項目8] を探し出して、同列11行目から下に【住所印刷】の商品名を貼り付け。
9行目(AE列)[項目25] を探し出して、同列11行目から下に【住所印刷】の商品名を貼り付け。
9行目(Z列)[項目20] を探し出して、同列11行目から下に【住所印刷】の指定を貼り付け。

  
以上です。




★列はファイルの出力方法によりかわりますので、1行目の見出し言葉を探り出してから、下の行を検索していくようにしてください。

★伝票番号のヒット条件は送付先氏名が、各宅配会社のファイルの名前と完全一致するかにします(同姓同名は無視します)

★実際には各ファイル、3行目以降も見出しの情報が続きますが100行まで検索するようにしていただき、その検索数値も調節できるように上に設定しておいてくださいませ。(書き換えはこちらでします)

★CSVファイルの伝票などは、開くと、数値が4.01998E+11などと桁オーバーの表記がされることがありますので注意してください。


ご質問がございましたら、いただけたら幸いです。
きちんとマクロを書いていただいた方にはポイントを差し上げます。

ベストアンサー

id:gong1971 No.1

gong1971回答回数443ベストアンサー獲得回数682012/08/22 22:53:38

ポイント500pt

※必ずデータのバックアップを取ってからご使用ください。
※質問文のみでは不明な点があったので、このままでは正常に動作しないかもしれません。
 また、私が質問文を間違えて理解しているかもしれません。
 エラーが出た際、また不正な処理をした際は、詳細をコメントにてお知らせください。
 (こちらで仮ファイルを用意してテストした限りは正常動作しました)
※処理後のデータに間違いがないか、よくご確認ください。
※追跡番号の元ファイルから何行読み込むかは、変数dnで指定してください。

以下、質問文で不明だった点。
・[項目8]、[項目25]ともに商品名を貼り付けていますが、これで正しいですか?
・追跡番号を貼り付ける列が分かりませんでした。
 また「送付先氏名」「計」は貼り付けには使用しませんか?
・「列はファイルの出力方法によりかわります」この条件でかなり複雑になっています。
 使用するファイルの内、列が固定のものがあれば、もう少しシンプルになります。

以上、不明な点がありましたら、コメントでお知らせください。

Sub Macro1()

dn = 100
dn1 = dn - 1

ChDir "C:\Users\yamada\Desktop"
Workbooks.Add
ActiveWorkbook.SaveAs Filename:="tmp.xls"

'### 佐川出力用
Workbooks.Open Filename:="佐川出力用.csv"
Cells.Find(What:="お届け先名称1", After:=ActiveCell, SearchOrder:=xlByRows).Activate
ActiveCell.Offset(1, 0).Activate
Range(ActiveCell, ActiveCell.Offset(dn1, 0)).Select
Selection.Copy

Workbooks("tmp.xls").Activate
Range("A1").Select
ActiveSheet.Paste

Windows("佐川出力用.csv").Activate
Cells.Find(What:="お問合せ送り状№", After:=ActiveCell, SearchOrder:=xlByRows).Activate
ActiveCell.Offset(1, 0).Activate
Range(ActiveCell, ActiveCell.Offset(dn1, 0)).Select
Selection.Copy

Workbooks("tmp.xls").Activate
Range("B1").Select
ActiveSheet.Paste

Application.CutCopyMode = False
Windows("佐川出力用.csv").Close

'### ヤマト出力用
Workbooks.Open Filename:="ヤマト出力用.xls"
Cells.Find(What:="お届け先名", After:=ActiveCell, SearchOrder:=xlByRows).Activate
ActiveCell.Offset(1, 0).Activate
Range(ActiveCell, ActiveCell.Offset(dn1, 0)).Select
Selection.Copy

Workbooks("tmp.xls").Activate
Range("A" & dn + 1).Select
ActiveSheet.Paste

Windows("ヤマト出力用.xls").Activate
Cells.Find(What:="伝票番号", After:=ActiveCell, SearchOrder:=xlByRows).Activate
ActiveCell.Offset(1, 0).Activate
Range(ActiveCell, ActiveCell.Offset(dn1, 0)).Select
Selection.Copy

Workbooks("tmp.xls").Activate
Range("B" & dn + 1).Select
ActiveSheet.Paste

Application.CutCopyMode = False
Windows("ヤマト出力用.xls").Close

'### 出荷実績一覧
Workbooks.Open Filename:="出荷実績一覧.csv"
Cells.Find(What:="お届け先 氏名", After:=ActiveCell, SearchOrder:=xlByRows).Activate
ActiveCell.Offset(1, 0).Activate
Range(ActiveCell, ActiveCell.Offset(dn1, 0)).Select
Selection.Copy

Workbooks("tmp.xls").Activate
Range("A" & dn * 2 + 1).Select
ActiveSheet.Paste

Windows("出荷実績一覧.csv").Activate
Cells.Find(What:="お問い合わせ番号", After:=ActiveCell, SearchOrder:=xlByRows).Activate
ActiveCell.Offset(1, 0).Activate
Range(ActiveCell, ActiveCell.Offset(dn1, 0)).Select
Selection.Copy

Workbooks("tmp.xls").Activate
Range("B" & dn * 2 + 1).Select
ActiveSheet.Paste

Application.CutCopyMode = False
Windows("出荷実績一覧.csv").Close

'### Vlookup
Windows("住所印刷.xls").Activate
Range("A1").Select
Cells.Find(What:="送付先氏名", After:=ActiveCell, SearchOrder:=xlByRows).Activate
nc = ActiveCell.Column

lc = Range("A1").SpecialCells(xlLastCell).Column + 1
lr = Range("A1").SpecialCells(xlLastCell).Row
Cells(1, lc).FormulaR1C1 = "tmp"
Range(Cells(2, lc), Cells(lr, lc)).FormulaR1C1 = "=VLOOKUP(RC" & nc & ",[tmp.xls]Sheet1!R1C1:R" & dn * 3 & "C2,2,0)"
Range(Cells(2, lc), Cells(lr, lc)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues

Workbooks("tmp.xls").Save
Windows("tmp.xls").Close
Kill "tmp.xls"

'### Paste
Workbooks.Open Filename:="エクセルメール.xls"

'# [TO]
Range("A9").Select
Cells.Find(What:="[TO]", After:=ActiveCell, SearchOrder:=xlByRows).Activate
ActiveCell.Offset(2, 0).Activate

Windows("住所印刷.xls").Activate
Range("A1").Select
Cells.Find(What:="メールアドレス1", After:=ActiveCell, SearchOrder:=xlByRows).Activate
cc = ActiveCell.Column
Range(Cells(2, cc), Cells(lr, cc)).Select
Selection.Copy

Windows("エクセルメール.xls").Activate
ActiveSheet.Paste

'# [BCC]
Range("A9").Select
Cells.Find(What:="[BCC]", After:=ActiveCell, SearchOrder:=xlByRows).Activate
ActiveCell.Offset(2, 0).Activate
Range(ActiveCell, ActiveCell.Offset(lr - 2, 0)).FormulaR1C1 = "xyz@yahoo.co.jp"

'# [項目8]
Range("A9").Select
Cells.Find(What:="[項目8]", After:=ActiveCell, SearchOrder:=xlByRows).Activate
ActiveCell.Offset(2, 0).Activate

Windows("住所印刷.xls").Activate
Range("A1").Select
Cells.Find(What:="商品名", After:=ActiveCell, SearchOrder:=xlByRows).Activate
cc = ActiveCell.Column
Range(Cells(2, cc), Cells(lr, cc)).Select
Selection.Copy

Windows("エクセルメール.xls").Activate
ActiveSheet.Paste

'# [項目25]
Range("A9").Select
Cells.Find(What:="[項目25]", After:=ActiveCell, SearchOrder:=xlByRows).Activate
ActiveCell.Offset(2, 0).Activate

Windows("住所印刷.xls").Activate
Range("A1").Select
Cells.Find(What:="商品名", After:=ActiveCell, SearchOrder:=xlByRows).Activate
cc = ActiveCell.Column
Range(Cells(2, cc), Cells(lr, cc)).Select
Selection.Copy

Windows("エクセルメール.xls").Activate
ActiveSheet.Paste

'# [項目20]
Range("A9").Select
Cells.Find(What:="[項目20]", After:=ActiveCell, SearchOrder:=xlByRows).Activate
ActiveCell.Offset(2, 0).Activate

Windows("住所印刷.xls").Activate
Range("A1").Select
Cells.Find(What:="指定", After:=ActiveCell, SearchOrder:=xlByRows).Activate
cc = ActiveCell.Column
Range(Cells(2, cc), Cells(lr, cc)).Select
Selection.Copy

Windows("エクセルメール.xls").Activate
ActiveSheet.Paste

End Sub

他1件のコメントを見る
id:gong1971

【1】この場所に'tmp.xls'という…

→処理中「tmp.xls」という仮ファイルを作成します。
 マクロが正常に終了すると削除されるはずですが、
 途中終了した時は、該当のメッセージが表示されます。
 「はい」で進めて問題ありません。
 ※元データとして「tmp.xls」というファイルは使用されてないですよね?


【2】[項目20]のところですが、…

→「指定」というワードを検索しています。セルL1に「指定」と入力されていますか?
 セルAB1に「指定」と入力されていないですか?


【質問文に不足していた項目について】

→【'# [TO]】以降、【'# [BCC]】ブロック以外はすべて同じ処理をしています。
 処理ブロック中、書き換えるのはCells.FindのWhatのワード部分2箇所のみです。
 他のブロックを参照にCells.FindのWhatのワードを書き換えて追加してください。

 最初のCells.Findがエクセルメールの項目名、次のCells.Findが住所印刷の項目名です。

 なお、追跡番号について、住所印刷での項目名は「tmp」です。
 ※住所印刷ファイルで「tmp」が含まれる項目名が有る場合、
  マクロの次の行を適宜書き換えてください。

Cells(1, lc).FormulaR1C1 = "tmp"

2012/08/23 13:33:37
id:gong1971

■補足

処理の過程で、住所印刷ファイルの末尾列に追跡番号を付加しています。
住所印刷ファイルは保存せずに閉じてください。
もしくは、追跡番号の列を削除してから閉じてください。

2012/08/23 13:40:03

その他の回答(0件)

id:gong1971 No.1

gong1971回答回数443ベストアンサー獲得回数682012/08/22 22:53:38ここでベストアンサー

ポイント500pt

※必ずデータのバックアップを取ってからご使用ください。
※質問文のみでは不明な点があったので、このままでは正常に動作しないかもしれません。
 また、私が質問文を間違えて理解しているかもしれません。
 エラーが出た際、また不正な処理をした際は、詳細をコメントにてお知らせください。
 (こちらで仮ファイルを用意してテストした限りは正常動作しました)
※処理後のデータに間違いがないか、よくご確認ください。
※追跡番号の元ファイルから何行読み込むかは、変数dnで指定してください。

以下、質問文で不明だった点。
・[項目8]、[項目25]ともに商品名を貼り付けていますが、これで正しいですか?
・追跡番号を貼り付ける列が分かりませんでした。
 また「送付先氏名」「計」は貼り付けには使用しませんか?
・「列はファイルの出力方法によりかわります」この条件でかなり複雑になっています。
 使用するファイルの内、列が固定のものがあれば、もう少しシンプルになります。

以上、不明な点がありましたら、コメントでお知らせください。

Sub Macro1()

dn = 100
dn1 = dn - 1

ChDir "C:\Users\yamada\Desktop"
Workbooks.Add
ActiveWorkbook.SaveAs Filename:="tmp.xls"

'### 佐川出力用
Workbooks.Open Filename:="佐川出力用.csv"
Cells.Find(What:="お届け先名称1", After:=ActiveCell, SearchOrder:=xlByRows).Activate
ActiveCell.Offset(1, 0).Activate
Range(ActiveCell, ActiveCell.Offset(dn1, 0)).Select
Selection.Copy

Workbooks("tmp.xls").Activate
Range("A1").Select
ActiveSheet.Paste

Windows("佐川出力用.csv").Activate
Cells.Find(What:="お問合せ送り状№", After:=ActiveCell, SearchOrder:=xlByRows).Activate
ActiveCell.Offset(1, 0).Activate
Range(ActiveCell, ActiveCell.Offset(dn1, 0)).Select
Selection.Copy

Workbooks("tmp.xls").Activate
Range("B1").Select
ActiveSheet.Paste

Application.CutCopyMode = False
Windows("佐川出力用.csv").Close

'### ヤマト出力用
Workbooks.Open Filename:="ヤマト出力用.xls"
Cells.Find(What:="お届け先名", After:=ActiveCell, SearchOrder:=xlByRows).Activate
ActiveCell.Offset(1, 0).Activate
Range(ActiveCell, ActiveCell.Offset(dn1, 0)).Select
Selection.Copy

Workbooks("tmp.xls").Activate
Range("A" & dn + 1).Select
ActiveSheet.Paste

Windows("ヤマト出力用.xls").Activate
Cells.Find(What:="伝票番号", After:=ActiveCell, SearchOrder:=xlByRows).Activate
ActiveCell.Offset(1, 0).Activate
Range(ActiveCell, ActiveCell.Offset(dn1, 0)).Select
Selection.Copy

Workbooks("tmp.xls").Activate
Range("B" & dn + 1).Select
ActiveSheet.Paste

Application.CutCopyMode = False
Windows("ヤマト出力用.xls").Close

'### 出荷実績一覧
Workbooks.Open Filename:="出荷実績一覧.csv"
Cells.Find(What:="お届け先 氏名", After:=ActiveCell, SearchOrder:=xlByRows).Activate
ActiveCell.Offset(1, 0).Activate
Range(ActiveCell, ActiveCell.Offset(dn1, 0)).Select
Selection.Copy

Workbooks("tmp.xls").Activate
Range("A" & dn * 2 + 1).Select
ActiveSheet.Paste

Windows("出荷実績一覧.csv").Activate
Cells.Find(What:="お問い合わせ番号", After:=ActiveCell, SearchOrder:=xlByRows).Activate
ActiveCell.Offset(1, 0).Activate
Range(ActiveCell, ActiveCell.Offset(dn1, 0)).Select
Selection.Copy

Workbooks("tmp.xls").Activate
Range("B" & dn * 2 + 1).Select
ActiveSheet.Paste

Application.CutCopyMode = False
Windows("出荷実績一覧.csv").Close

'### Vlookup
Windows("住所印刷.xls").Activate
Range("A1").Select
Cells.Find(What:="送付先氏名", After:=ActiveCell, SearchOrder:=xlByRows).Activate
nc = ActiveCell.Column

lc = Range("A1").SpecialCells(xlLastCell).Column + 1
lr = Range("A1").SpecialCells(xlLastCell).Row
Cells(1, lc).FormulaR1C1 = "tmp"
Range(Cells(2, lc), Cells(lr, lc)).FormulaR1C1 = "=VLOOKUP(RC" & nc & ",[tmp.xls]Sheet1!R1C1:R" & dn * 3 & "C2,2,0)"
Range(Cells(2, lc), Cells(lr, lc)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues

Workbooks("tmp.xls").Save
Windows("tmp.xls").Close
Kill "tmp.xls"

'### Paste
Workbooks.Open Filename:="エクセルメール.xls"

'# [TO]
Range("A9").Select
Cells.Find(What:="[TO]", After:=ActiveCell, SearchOrder:=xlByRows).Activate
ActiveCell.Offset(2, 0).Activate

Windows("住所印刷.xls").Activate
Range("A1").Select
Cells.Find(What:="メールアドレス1", After:=ActiveCell, SearchOrder:=xlByRows).Activate
cc = ActiveCell.Column
Range(Cells(2, cc), Cells(lr, cc)).Select
Selection.Copy

Windows("エクセルメール.xls").Activate
ActiveSheet.Paste

'# [BCC]
Range("A9").Select
Cells.Find(What:="[BCC]", After:=ActiveCell, SearchOrder:=xlByRows).Activate
ActiveCell.Offset(2, 0).Activate
Range(ActiveCell, ActiveCell.Offset(lr - 2, 0)).FormulaR1C1 = "xyz@yahoo.co.jp"

'# [項目8]
Range("A9").Select
Cells.Find(What:="[項目8]", After:=ActiveCell, SearchOrder:=xlByRows).Activate
ActiveCell.Offset(2, 0).Activate

Windows("住所印刷.xls").Activate
Range("A1").Select
Cells.Find(What:="商品名", After:=ActiveCell, SearchOrder:=xlByRows).Activate
cc = ActiveCell.Column
Range(Cells(2, cc), Cells(lr, cc)).Select
Selection.Copy

Windows("エクセルメール.xls").Activate
ActiveSheet.Paste

'# [項目25]
Range("A9").Select
Cells.Find(What:="[項目25]", After:=ActiveCell, SearchOrder:=xlByRows).Activate
ActiveCell.Offset(2, 0).Activate

Windows("住所印刷.xls").Activate
Range("A1").Select
Cells.Find(What:="商品名", After:=ActiveCell, SearchOrder:=xlByRows).Activate
cc = ActiveCell.Column
Range(Cells(2, cc), Cells(lr, cc)).Select
Selection.Copy

Windows("エクセルメール.xls").Activate
ActiveSheet.Paste

'# [項目20]
Range("A9").Select
Cells.Find(What:="[項目20]", After:=ActiveCell, SearchOrder:=xlByRows).Activate
ActiveCell.Offset(2, 0).Activate

Windows("住所印刷.xls").Activate
Range("A1").Select
Cells.Find(What:="指定", After:=ActiveCell, SearchOrder:=xlByRows).Activate
cc = ActiveCell.Column
Range(Cells(2, cc), Cells(lr, cc)).Select
Selection.Copy

Windows("エクセルメール.xls").Activate
ActiveSheet.Paste

End Sub

他1件のコメントを見る
id:gong1971

【1】この場所に'tmp.xls'という…

→処理中「tmp.xls」という仮ファイルを作成します。
 マクロが正常に終了すると削除されるはずですが、
 途中終了した時は、該当のメッセージが表示されます。
 「はい」で進めて問題ありません。
 ※元データとして「tmp.xls」というファイルは使用されてないですよね?


【2】[項目20]のところですが、…

→「指定」というワードを検索しています。セルL1に「指定」と入力されていますか?
 セルAB1に「指定」と入力されていないですか?


【質問文に不足していた項目について】

→【'# [TO]】以降、【'# [BCC]】ブロック以外はすべて同じ処理をしています。
 処理ブロック中、書き換えるのはCells.FindのWhatのワード部分2箇所のみです。
 他のブロックを参照にCells.FindのWhatのワードを書き換えて追加してください。

 最初のCells.Findがエクセルメールの項目名、次のCells.Findが住所印刷の項目名です。

 なお、追跡番号について、住所印刷での項目名は「tmp」です。
 ※住所印刷ファイルで「tmp」が含まれる項目名が有る場合、
  マクロの次の行を適宜書き換えてください。

Cells(1, lc).FormulaR1C1 = "tmp"

2012/08/23 13:33:37
id:gong1971

■補足

処理の過程で、住所印刷ファイルの末尾列に追跡番号を付加しています。
住所印刷ファイルは保存せずに閉じてください。
もしくは、追跡番号の列を削除してから閉じてください。

2012/08/23 13:40:03
  • id:naranara19
    大変ご丁寧にありがとうございました。完璧に動作しました。スキルのない私でも操作や追加がしやすくてすごくうれしいコードでした。多々こちらに不備があったなか、完璧にしあげてくださったことに深く感謝します。

    これからも、ご活躍期待しております。また私が投稿した際にはどうかよろしくお願いいたします。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません