2019/02/10

Amazonの出荷指示データから、伝票作成用フォーマットデータを作成するマクロ

Excel VBA

Amazonから出力される商品の出荷指示データから、必要な情報を、運送会社の指定フォーマットにコピペして、出力するVBAマクロを作成したので、ソースコードを解説します。

効率化する前の既存ワークフロー
システム要件
必要なプログラム
ソースコードの解説

スポンサードサーチ

効率化する前の既存ワークフロー

現在のワークフローは以下のようになっています。

Amazonで商品が売れると、出荷指示データが吐き出される（テキスト）
運送会社の指定フォーマットに、1件ずつ必要な項目をコピペ（エクセル）
運送会社の専用ページに指定フォーマットのファイルをアップロード

図にするとこんな形です。

システム要件

Amazonから出力される、出荷指示データをコピペする作業に、手間がかかります。
そこで、Amazonから発行された、出荷指示データ（テキスト）を読み込み、指定フォーマットに自動入力するプログラムを作成します。

システムのおおまかな流れ

出荷指示データを、Excelにインポート
運送会社の指定フォーマット（エクセル）に必要な項目を自動でコピペ
Excel形式でデータをエクスポートする

こんな形をイメージしてます。

スポンサードサーチ

必要なプログラム

システムを作成するために、必要となるプログラムをリストアップしました。

必要なプログラム一覧

テキストデータをエクセルにインポートする
インポートしたデータを整形する
指定フォーマットに必要な項目をコピペする
指定フォーマットデータに、名前をつけてエクスポートする

ソースコードの解説

実際に作成したソースコードを順番に解説していきます。

テキストデータをエクセルにインポートする

Amazonから出力されたテキストデータを、エクセルのシートにインポートする処理です。

Sub textFile_get()
Dim openCsv As String
Dim toSh As Worksheet
Dim buf As String
Dim cols As Variant
Dim row As Long

openCsv = Application.GetOpenFilename("textファイル,*.txt")
If openFile = "False" Then Exit Sub

Set toSh = Worksheets("Sheet1")

row = 1
Open openCsv For Input As #1
Do Until EOF(1)
Line Input #1, buf
cols = Split(buf, vbTab)
With toSh.Range(Cells(row, 1), Cells(row, UBound(cols) + 1))
.NumberFormatLocal = "@"
.Value = cols
End With
row = row + 1
Loop
Close #1

End Sub

ブックを起動すると、ファイル選択のダイアログが表示されるので、対象のテキストファイルを選択します。

こんな形で、エクセルにテキストデータがインポートされます。

インポートしたデータを整形する

インポートしたデータを、指定フォーマットにコピペする前に、3箇所ほどテキストを整形する必要があります。
それぞれ、自動整形する処理を作ります。

当日の日付を、指定のフォーマットで自動入力する
商品名が25文字以上の場合は、頭から切り詰める
住所のフィールドが別れているため、各セルの文字列を結合する

それぞれのソースコードになります。

文字列を切り詰める処理

作業対象の行番号を引数にして、25文字に切り詰めた品名を返します

Function Sinamei(ByVal row As Long)

Dim strSina As String

strSina = ""
strSina = Left(Range("L" & row), 25)

Sinamei = strSina
End Function

日付を指定フォーマットで返す処理

当日の日付を「20190210」のようなフォーマットで返します。

Function Syukkayoteibi()

Syukkayoteibi = Format(Date, "yyyymmdd")

End Function

文字列を連結して返す処理

作業対象の行番号を引数にして、連結した文字列（住所）を返します。
文字列を連結するには　& を使います。

Function Mojirenketu(ByVal row As Long)

    Dim a As String
    
    a = ""
    a = a & Range("R" & row).Value
    'a = a & Range("S" & row).Value
    
    Mojirenketu = a
    
End Function

スポンサードサーチ

指定フォーマットに必要な項目をコピペする

メインのプログラムです。インポートしたデータの数を取得して、データの数だけ指定フォーマットにコピペを繰り返します。「依頼主情報」など、固定の情報も入力します。※個人情報は、サンプルデータに置き換えています。

Sub harituke()

Dim b As String
Dim Lastrow As Long
Dim n As Long
Dim Cont As Long


Cont = 2
Lastrow = Range("A65536").End(xlUp).row
For Cont = 2 To Lastrow



Worksheets("format").Range("E" & Cont).Value = Syukkayoteibi
Worksheets("format").Range("I" & Cont).Value = Range("J" & Cont)
Worksheets("format").Range("K" & Cont).Value = Range("W" & Cont)
Worksheets("format").Range("L" & Cont).Value = Mojirenketu(Cont)
Worksheets("format").Range("N" & Cont).Value = Range("T" & Cont)
Worksheets("format").Range("P" & Cont).Value = Range("Q" & Cont)
Worksheets("format").Range("M" & Cont).Value = Range("S" & Cont)
Worksheets("format").Range("T" & Cont).Value = "【電話番号が入ります】"
Worksheets("format").Range("V" & Cont).Value = "【郵便番号が入ります】"
Worksheets("format").Range("W" & Cont).Value = "【住所が入ります】"
Worksheets("format").Range("Y" & Cont).Value = "【店舗名が入ります】"
Worksheets("format").Range("AB" & Cont).Value = Sinamei(Cont)
Worksheets("format").Range("AN" & Cont).Value = "【電話番号が入ります】"
Worksheets("format").Range("AP" & Cont).Value = "【コードが入ります】"

Next Cont
End Sub

指定フォーマットデータを当日の日付でエクスポートする

コピペが終わったら、エクセルのシートをエクスポートします。その際、ファイル名には当日の日付を入力します。操作回数を減らすため、エクスポート先はデスクトップにしました。ソースコードを少し変更すれば、任意の場所にエクスポートすることもできます。

Sub OutputFile()
    Dim w As Worksheet
    Dim myFile As String
        
    Worksheets("format").Activate
    Range("A2").Select

    Sheets("format").Copy
    ActiveWorkbook.SaveAs _
    Filename:=CreateObject("WScript.Shell").specialfolders("Desktop") & "\" & Syukkayoteibi & "_送り状.xls", _
    FileFormat:=xlOpenXMLWorkbook

End Sub

これでソースコードはだいたい完成です。

スポンサードサーチ

ブックを起動したタイミングで、マクロが実行されるようにする

最後に、エクセルを起動したタイミングで、マクロが自動で起動するようにします。
ThisWorkbookオブジェクトで、Workbook_Open()というイベントプロシージャにソースコードを入れると、ブックを開いたタイミングでマクロが実行されます。

Workbook_Open()に、作成したソースコードと、入力されているデータを初期化する処理を追加しました。

Private Sub Workbook_Open()
Dim Lastrow1 As Long
Dim Lastrow2 As Long

Lastrow1 = 0
Lastrow2 = 0

Lastrow1 = Worksheets("Sheet1").Range("A65536").End(xlUp).row
Lastrow2 = Worksheets("format").Range("E65536").End(xlUp).row

Worksheets("Sheet1").Range("A1:AC" & Lastrow1).ClearContents
If Not Lastrow2 = 1 Then
Worksheets("format").Range("A2:CQ" & Lastrow2).ClearContents
End If

Worksheets("Sheet1").Activate
Call textFile_get
Call harituke
Call OutputFile

End Sub

ここまで作成したソースコードをまとめます。

ThisWorkbookオブジェクト

Private Sub Workbook_Open()
Dim Lastrow1 As Long
Dim Lastrow2 As Long

Lastrow1 = 0
Lastrow2 = 0

Lastrow1 = Worksheets("Sheet1").Range("A65536").End(xlUp).row
Lastrow2 = Worksheets("format").Range("E65536").End(xlUp).row

Worksheets("Sheet1").Range("A1:AC" & Lastrow1).ClearContents
If Not Lastrow2 = 1 Then
Worksheets("format").Range("A2:CQ" & Lastrow2).ClearContents
End If


Worksheets("Sheet1").Activate
Call textFile_get
Call harituke
Call OutputFile

End Sub

標準モジュール

'指定フォーマットに必要な項目をコピペする
Sub harituke()

Dim b As String
Dim Lastrow As Long
Dim n As Long
Dim Cont As Long


Cont = 2
Lastrow = Range("A65536").End(xlUp).row
For Cont = 2 To Lastrow



Worksheets("format").Range("E" & Cont).Value = Syukkayoteibi
Worksheets("format").Range("I" & Cont).Value = Range("J" & Cont)
Worksheets("format").Range("K" & Cont).Value = Range("W" & Cont)
Worksheets("format").Range("L" & Cont).Value = Mojirenketu(Cont)
Worksheets("format").Range("N" & Cont).Value = Range("T" & Cont)
Worksheets("format").Range("P" & Cont).Value = Range("Q" & Cont)
Worksheets("format").Range("M" & Cont).Value = Range("S" & Cont)
Worksheets("format").Range("T" & Cont).Value = "【電話番号が入ります】"
Worksheets("format").Range("V" & Cont).Value = "【郵便番号が入ります】"
Worksheets("format").Range("W" & Cont).Value = "【住所が入ります】"
Worksheets("format").Range("Y" & Cont).Value = "【店舗名が入ります】"
Worksheets("format").Range("AB" & Cont).Value = Sinamei(Cont)
Worksheets("format").Range("AN" & Cont).Value = "【電話番号が入ります】"
Worksheets("format").Range("AP" & Cont).Value = "【コードが入ります】"

Next Cont
End Sub

'指定フォーマットデータを、当日の日付でエクスポートする
Sub OutputFile()
    Dim w As Worksheet
    Dim myFile As String
    
    Worksheets("format").Activate
    Range("A2").Select
    Sheets("format").Copy
    ActiveWorkbook.SaveAs _
    Filename:=CreateObject("WScript.Shell").specialfolders("Desktop") & "\" & Syukkayoteibi & "_送り状.xls", _
    FileFormat:=xlOpenXMLWorkbook

End Sub

'文字列を連結して返す処理
Function Mojirenketu(ByVal row As Long)

    Dim a As String
    
    a = ""
    a = a & Range("R" & row).Value
    'a = a & Range("S" & row).Value
    
    Mojirenketu = a
    
End Function

'日付を指定フォーマットで返す処理
Function Syukkayoteibi()

Syukkayoteibi = Format(Date, "yyyymmdd")

End Function

'文字列を切り詰める処理
Function Sinamei(ByVal row As Long)

Dim strSina As String

strSina = ""
strSina = Left(Range("L" & row), 25)

Sinamei = strSina
End Function

まとめ

今回のプログラムの制作時間は、2時間ぐらいです。本当は入力チェックなども、した方がいいのですが、利用する方のITリテラシーが高かったので、必要最低限のコーディングを行いました。

今のところ、問題なく動いているようです。作業時間は大幅に短縮されたと、満足していただきました。
部分的な作業の効率化であれば、サクッとコーディングして、使ってもらいながら改良していく感じでもいいですね。

ポートフォリオ作業効率化

Amazonの出荷指示データから、伝票作成用フォーマットデータを作成するマクロ

効率化する前の既存ワークフロー

システム要件

必要なプログラム

ソースコードの解説

テキストデータをエクセルにインポートする

インポートしたデータを整形する

指定フォーマットに必要な項目をコピペする

指定フォーマットデータを当日の日付でエクスポートする

ブックを起動したタイミングで、マクロが実行されるようにする

まとめ

RELATED

エクセルで改行する超カンタンな方法【便利な裏技も紹介】...

エクセルにPDFを挿入する３つの方法...

【エクセル2016】チェックボックスを挿入して一括で連動させる方法...

【コピペOK】VBAのフォームサイズを画面に合わせる方法...