[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『複数のフォーマットファイルを一覧化したい』（さや）
ＭＳで、データ追跡機能付きテンプレートウィザードのフォローを切り捨てたため
どうにか同じような仕組みができればと頭を悩ませています。
複数の申し込みフォーマットファイルを
帳票形式のフォーマットファイルにマージさせたいのですが、
初心者につき、うまく組み込みが思いつきません。
どなたか良案をご教示いただけないでしょうか。
例）
・店舗名
・店舗ＣＤ
・申請者
・商品1
・商品1価格
・備考1
・商品2
・商品2価格
・備考3
※最大「商品5」まであります。
という複数(ファイル数は可変します)の単票ファイルを
帳票(リスト)形式に集約させるには、
どのように組めばよろしいでしょうか。
よろしくお願い致します。
＜ 使用 Excel：Excel2013、使用 OS：Windows7 ＞
あとは該当するファイルを順次WorkBooks.Openし、1データが1行になるよう1セルずつ集計用シートに代入。
これを繰り返して、データの一覧表を作ってみてはいかがでしょうか。
（???） 2016/06/08(水) 11:48
単票ファイル（フォーマット）は、
個別に提出されてくるものなので
同じＢＯＯＫにはありません。
また、
店舗名はＢ５セル
店舗ＣＤはＦ５
商品1はＢ１９
商品1価格はＨ１９・・・というようなフォーマットになっており、
マージファイルは一覧リストになるので
各項目が横並びになっていて、
1ファイル1行・・・というイメージです。
いかがでしょうか？
よろしくお願いします。
（さや） 2016/06/08(水) 11:52
よろしくご指導ください。
（さや） 2016/06/08(水) 12:54
ファイル名を順次に得るにはDir関数。
ブックを開くにはWorkbooks.Open。
セルの参照はRangeかCellsです。
まずはご自分でできるところまで作ってみて、調べても試しても判らない箇所を、個別に聞いてください。
自分ではやる気無し。丸投げ〜、というならば、商品2以降がどのセルにあるのか等、全ての情報を書いてください。
（???） 2016/06/08(水) 13:07
初心者につき、依頼内容が煩雑で
気分を害するような明記でしたら、申し訳ありませんでした。
（さや） 2016/06/08(水) 13:27
どんなに丁寧な文章よりも、稚拙でも良いから具体的なコードを見せてください。レイアウトは全てのセルの位置を教えてください。それが解決の近道です。
（???） 2016/06/08(水) 14:23
Sub Sample1()
Dim Fpath As String
Dim Fname As String
Dim 項目名 As Variant
Dim adr項目 As Variant
Dim rngS As Range
Dim rngD As Range
Dim Sht As Worksheet
Dim i As Long
項目名 = Array("店舗名", "店舗ＣＤ", "申請者", "商品1", "商品1価格")
adr項目 = Array("B5", "F5", "I5", "B19", "H19")
Fpath = ThisWorkbook.Path & "\"
Fname = Dir(Fpath & "*.xlsx")
Do Until Fname = ""
If rngD Is Nothing Then
Set rngD = Workbooks.Add.Worksheets(1).Range("A1")
rngD.Resize(, UBound(項目名) + 1).Value = 項目名
Set rngD = rngD.Offset(1)
End If
If LCase(Fname) <> LCase(ThisWorkbook.Name) Then
Set Sht = Workbooks.Open(Fpath & Fname).Worksheets(1)
For i = 0 To UBound(adr項目)
Set rngS = Sht.Range(adr項目(i))
rngD.Offset(, i).Value = rngS.Value
Next
Set rngD = rngD.Offset(1)
Sht.Parent.Close False
End If
Fname = Dir()
Loop
End Sub
（暇人） 2016/06/08(水) 14:32
新しいファイルに単票のデータを貼り付ける・・・かんじですよね？
説明不足で申し訳ありませんが、帳票の方もフォーマットが決まっており、
「原稿リスト」というファイルの「並替順」というSheetの
A4までを使ってヘッダー（項目名含む）としています。
ご教示いただいたコードを参考に、
なんとか自身で組めないものかやってはみたんですが、
初心者のため（？）、思った動きになってくれません。
ちなみに、
反映させる項目数が増えた場合（例えば50項目など）でも、
同じコードを修正すれば対応しますか？
→30を超えたあたり？で黄色くなってしまいました。
また、細かい説明をすると
「商品1」「価格1」と記載した部分、
実際は項目名の上に「商品1・2・・・」とあり、下段に「商品」「価格」・・と
同じ項目名を用いています。
（例え：単票ファイル※元データ）
A4（項目名）：申請者｜B-I4（入力欄）：＊＊＊
A5（項目名）：店舗CD｜B-D（入力欄）：00000｜E5（項目名）：店舗名｜F-I5（入力欄）：＃＃＃＃
A7（項目名）：申請日｜B-D7（入力欄）：YYYY/MM/DD
A10〜I10：「商品1」
A11（項目名）：商品｜B11（入力欄）：●●｜G11（項目名）：価格｜H11（入力欄）：××
A12（項目名）：備考｜B12（入力欄）：◆◆
A14〜I14：「商品2」
A15（項目名）：商品｜B15（入力欄）：■■｜G15（項目名）：価格｜H15（入力欄）：▼▼
A16（項目名）：備考｜B16（入力欄）：★★
・
・
・
（例え：帳票ファイル※反映ファイル）
・3-4行目（項目名）
A-D3：基本情報｜E-G3：商品1｜H-J3：商品2・・・
A4:申請者｜B4:店舗CD｜C4:店舗名｜D4:申請日｜E4:商品｜F4:価格｜G4:備考｜H4:商品｜I4:価格｜J4:備考・・・
・5行目以下（入力欄）
こんなイメージです。伝わるでしょうか？
→サンプルとして項目をあげていますが、実際はもっと項目が多数あります。
どう説明したら良いのかわかっておらず、
補足続きで申し訳ありません。
なんとか よろしくお願いします。
（さや） 2016/06/08(水) 17:21
いくつか確認です。
１．単票ブックと帳票ブックがあるわけですけど、マクロブックは、これらとは別の独立したブックを考えていますか？
それとも、帳票ブックがマクロブックですか？
２．どれらブックは、どのフォルダにありますか？
３．方法としては、
１）その時点の単票ブック【だけで】帳票ブックを作成する。
２）その時点の単票ブックを、帳票ブックに【追加する】
いずれも可能ですけど、どちらですか？
４．レイアウトは以下ですね。
１）単票（商品数は可変ですか？固定ですか？）
|[A] |[B] |[C]|[D]|[E] |[F]|[G] |[H] |[I]
[4] |申請者| | | | | | | |
[5] |店舗CD| | | |店舗名| | | |
[6] | | | | | | | | |
[7] |申請日| | | | | | | |
[8] | | | | | | | | |
[9] | | | | | | | | |
[10]|商品1 | | | | | | | |
[11]|商品 |●●| | | | |価格|ＸＸ|
[12]|備考 |◆◆| | | | | | |
[13]| | | | | | | | |
[14]|商品2 | | | | | | | |
[15]|商品 |■■| | | | |価格|▼▼|
[16]|備考 |★★| | | | | | |
２）帳票
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |[H] |[I] |[J] |[K] |[L] |[M] |[N] |[O] |[P]
[3]|基本情報| | | |商品1| | |商品2| | |商品3| | |商品4| |
[4]|申請者 |店舗CD|店舗名|申請日|商品 |価格|備考|商品 |価格|備考|商品 |価格|備考|商品 |価格|備考
５行目から２行１組のデータ
（β） 2016/06/09(木) 10:35
・商品は5までですね？
・帳票シートの3,4行目は見出しであり、ここには何もセットしなくて良い、という事でしょうか？
・1ブックの情報が1行になるとの事だったので、5行目に1ファイル目、6行目に2ファイル目…という感じでしょうか？
（???） 2016/06/09(木) 10:47
暇人さんのコードを手直ししてみたというならば、そのコードを貼ってくれれば、隠された部分も見えそうに思うのですが。
（???） 2016/06/09(木) 10:54
???さんの確認質問の補足的な質問になりますが。
アップされた例では、商品、備考、価格の３項目だけですが、仮に、商品、備考、価格、項目A、項目B、項目C、項目D、項目E の ８項目だった場合は
単票シートの商品1 のレイアウトは、どうなりますか？ で、この時 商品2 は、どこから始まりますか？ また、帳票シートの商品1 のレイアウトは、どうなりますか？
それと、何項目でもいいのですが、それら項目は、レイアウト固定ですね。 たとえば、現在の ３項目レイアウトだったとして、あるものは 備考 が A11,B11 にある、なんてことはないですね。
（β） 2016/06/09(木) 11:30
回答させていただきます。
１．単票ブックと帳票ブックがあるわけですけど、マクロブックは、これらとは別の独立したブックを考えていますか？それとも、帳票ブックがマクロブックですか？ どちらでも。とはいえ、帳票ブックに入れるのが解りやすいですかね。
２．それらブックは、どのフォルダにありますか？ まだパスは未定ですが、指定することは可能です。例えば単票ブックの方は「INPUT」フォルダなどを作るとマージしやすいでしょうか。
３．方法としては、 １）その時点の単票ブック【だけで】帳票ブックを作成する。 ２）その時点の単票ブックを、帳票ブックに【追加する】 いずれも可能ですけど、どちらですか？ 基本的には2で考えています。
ただ、この作業がシーズン毎に発生し、帳票をリセットするのであれば
2で考えるとすると、使用する帳票ブックをコピーして新しく使いなおせば良いでしょうか。
４．レイアウト その通りです。
→このように表記すれば良かったんですね。知識不足でお手数おかけしました。
ただ、5行目以下は 1単票ブックにつき1行です。
・商品は5までですね？ 実は・・・5までを想定して質問を出させていただいたのですが、申請チームからの要望で10商品くらいまで増やせないかという提案が入っています。
また、今回の例として3項目をあげさせていただきましたが、実際は 基本情報17項目・1商品につき11項目を持っております。
なので、基本情報17＋11×10で、最大127項目になります。
・帳票シートの3,4行目は見出しであり、ここには何もセットしなくて良い、という事でしょうか？ その通りです。
帳票シートもいわゆる「フォーマット」が固定されている とお考えください。
※カラーがついたりしているので、フォーマットで使用した方が良いと考えています。
・1ブックの情報が1行になるとの事だったので、5行目に1ファイル目、6行目に2ファイル目…という感じでしょうか？ その通りです。
処理時に存在する単票ブックを全てマージできれば、順番の指定はありません。
また、単票ブックの入力項目に空白があっても、帳票ブックに反映する位置は固定でマージしたいです。
単票シートの商品1 のレイアウトは、どうなりますか？ 単票ブックの入力箇所は固定です。
[A] |[B] ｜・・｜[G] |[H]
[10] 基本デザイン
[11] 入力箇所
[12]※スペース
[13] 厳選食材
[14] 食材名1 | |・・｜食材番号1|
[15] 食材名2 | |・・｜食材番号1|
[16] 食材名3 | |・・｜食材番号1|
[17]※スペース
[18]※スペース
[19] 商品1（メインメニュー）
[20] メニュー名 ｜・・・・・・｜価格
[21] 備考
[22] 食材使用
[23] 画像名
[24]※スペース
・
・
・
※19〜24（実際は、もう少し項目がありますが）が繰り返し下に続きます。
お手数おかけしますが、ご教示いただけますと幸いです。
どうぞよろしくお願い致します。
（さや） 2016/06/09(木) 11:52
例えば、商品1の●●は、B11セルですよね？ 商品2はB15セル、とあったのに、今度のレイアウトでは食材名2になっているという。この調子では、1商品が何行使っているのかも定かではないので、商品1,2,5のセルが全部判るようにしてください。肝心な所を省略しない！
（???） 2016/06/09(木) 13:10
ちなみに、βさんのような表記をするのに簡単なツールはあったりしますか？
全部を手作業で表記作成するとなると、少し時間がかかりそうなので。
何から何までお手数おかけして申し訳ありません！！
（さや） 2016/06/09(木) 13:30
[[20110209184943]] 『[談]シートレイアウトの投稿どうしてますか？』（ｍｏｍｏ）
（???） 2016/06/09(木) 13:40
（とおりすがり） 2016/06/09(木) 13:46
なので、ひな形だけ提供して後は自分で調べて、というパターンにしたかったのですが、作るための元ネタが二転三転しているという。せめて、暇人さんのコードをどう直したのか、それに答えて欲しかったのですが。
とりあえず、βさんのレイアウトと仮定した場合のマクロ例なぞ。
違っている定義は修正してみてください。どこのセルから持ってくるか、という情報を配列化してあるだけです。
元ブックは、集計用ブックを置いたフォルダ下に「input」フォルダを作成し、ここに全て置いてください。
あとはActiveXのボタンをシートにひとつ貼り、以下のマクロを貼り付けてください。
Private Sub CommandButton1_Click()
Dim cFile As String
Dim cPath As String
Dim iR As Long
Dim i As Long
Dim vw As Variant
vw = Array("B4", "B5", "F5", "B7", "B11", "H11", "B12", "B15", "H15", "B16", "B19", "H19", "B20", "B23", "H23", "B24", "B27", "H27", "B28")
iR = 4
cPath = ActiveWorkbook.Path & "\input\"
cFile = Dir(cPath & "*.xls*")
While cFile <> ""
With Workbooks.Open(cPath & cFile, False, True)
With .Sheets(1)
iR = iR + 1
For i = 0 To UBound(vw)
Me.Cells(iR, i + 1).Value = .Range(vw(i))
Next i
End With
.Close False
End With
cFile = Dir
Wend
End Sub
（???） 2016/06/09(木) 13:55
まずは、単票ブックです。
|[A] |[B]|[C]|[D]|[E] |[F]|[G] |[H]|[I]
[3] |基本情報 | | | | | | | |
[4] |社員番号 | | | |社員名 | | | |
[5] |店舗コード | | | |店舗名 | | | |
[6] |フェア名 | | | |フェアサブタイトル| | | |
[7] |フェア開始日 | | | |フェア終了日 | | | |
[8] |価格表示 | | | |メニュー6品の場合 | | | |
[9] | | | | | | | | |
[10]|基本デザイン | | | | | | | |
[11]|7種類から選択 | | | | | | | |
[12]| | | | | | | | |
[13]|厳選食材 | | | | | | | |
[14]|食材名1 | | | | | |食材番号1 | |
[15]|食材名2 | | | | | |食材番号2 | |
[16]|食材名3 | | | | | |食材番号3 | |
[17]| | | | | | | | |
[18]|メニュー1（メインメニュー）| | | | | | | |
[19]|メニュー名 | | | | | |価格 | |
[20]|メニューPR文 | | | | | | | |
[21]|食材使用 | | | | | | | |
[22]|食材名 | | | | | |食材番号 | |
[23]|アイコン | | | | | | | |
[24]|画像名 | | | | | | | |
[25]|メインメニュー追加一品 | | | | | | | |
[26]|メインメニュー追加一品名 | | | | | |追加一品価格| |
[27]| | | | | | | | |
[28]|メニュー2 | | | | | | | |
[29]|メニュー名 | | | | | |価格 | |
[30]|食材使用 | | | | | | | |
[31]|食材名 | | | | | |食材番号 | |
[32]|アイコン | | | | | | | |
[33]|画像名 | | | | | | | |
[34]| | | | | | | | |
[35]|メニュー3 | | | | | | | |
[36]|メニュー名 | | | | | |価格 | |
[37]|食材使用 | | | | | | | |
[38]|食材名 | | | | | |食材番号 | |
[39]|アイコン | | | | | | | |
[40]|画像名 | | | | | | | |
[41]| | | | | | | | |
[42]|メニュー4 | | | | | | | |
[43]|メニュー名 | | | | | |価格 | |
[44]|食材使用 | | | | | | | |
[45]|食材名 | | | | | |食材番号 | |
[46]|アイコン | | | | | | | |
[47]|画像名 | | | | | | | |
[48]| | | | | | | | |
[49]|メニュー5 | | | | | | | |
[50]|メニュー名 | | | | | |価格 | |
[51]|食材使用 | | | | | | | |
[52]|食材名 | | | | | |食材番号 | |
[53]|アイコン | | | | | | | |
[54]|画像名 | | | | | | | |
[55]| | | | | | | | |
[56]|メニュー6 | | | | | | | |
[57]|メニュー名 | | | | | |価格 | |
[58]|食材使用 | | | | | | | |
[59]|食材名 | | | | | |食材番号 | |
[60]|アイコン | | | | | | | |
[61]|画像名 | | | | | | | |
帳票ブックのレイアウト（＋単票から反映させるセル指定）です。
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |[H] |[I] |[J] |[K] |[L] |[M] |[N] |[O] |[P] |[Q] |[R] |[S] |[T] |[U] |[V] |[W] |[X] |[Y] |[Z] |[AA] |[AB] |[AC] |[AD]|[AE] |[AF] |[AG] |[AH] |[AI] |[AJ] |[AK]|[AL] |[AM] |[AN] |[AO] |[AP] |[AQ] |[AR]|[AS] |[AT] |[AU] |[AV] |[AW] |[AX] |[AY]|[AZ] |[BA] |[BB] |[BC] |[BD] |[BE] |[BF]|[BG] |[BH] |[BI] |[BJ] |[BK]
[3]|基本情報| | | | | | | | | |基本デザイン |厳選食材| | | | | |メニュー1（メインメニュー）| | | | | | | | | | |メニュー2 | | | | | | |メニュー3 | | | | | | |メニュー4 | | | | | | |メニュー5 | | | | | | |メニュー6 | | | | | |
[4]|社員番号|社員名|店舗コード|店舗名|フェア名|フェアサブタイトル|フェア開始日|フェア終了日|価格表示|メニュー6品の場合|7種類から選択|食材名1 |食材番号1|食材名2|食材番号2|食材名3|食材番号3|メニュー名 |価格|メニューPR文|食材使用|食材名|食材番号|アイコン|画像名|メインメニュー追加一品|メインメニュー追加一品名|追加一品価格|メニュー名|価格|食材使用|食材名|食材番号|アイコン|画像名|メニュー名|価格|食材使用|食材名|食材番号|アイコン|画像名|メニュー名|価格|食材使用|食材名|食材番号|アイコン|画像名|メニュー名|価格|食材使用|食材名|食材番号|アイコン|画像名|メニュー名|価格|食材使用|食材名|食材番号|アイコン|画像名
[5]|B4 |F4 |B5 |F5 |B6 |F6 |B7 |F7 |B8 |F8 |B11 |B14 |H14 |B15 |H15 |B16 |H16 |B19 |H19 |B20 |B21 |B22 |H22 |B23 |B24 |B25 |B26 |H26 |B29 |H29 |B30 |B31 |H31 |B32 |B33 |B36 |H36 |B37 |B38 |H38 |B39 |B40 |B43 |B44 |H44 |B45 |H45 |B46 |B47 |B50 |H50 |B51 |B52 |H52 |B53 |B54 |B57 |H57 |B58 |B59 |H59 |B60 |B61
[6]| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
遅くなってしまい、申し訳ありません。
また、「開発依頼」の業務を依頼されたわけではなく
私が帳票ブックを作成する側・・になります。
※実際は、私以外にも複数人でこの作業をする可能性があります。
ので、いくつもある単票ブックのマージを簡単にできるとありがたいので、
やり方・組み方など 教えてください！・・というのが、そもそものお願いでした。
単に私がスキル不足だったことが悪かったと反省しております。
また、こちらの活用方法が間違っているようでしたら
併せてお詫び致します。
いろいろと不備・不手際が多い中 ご対応いただき、ありがとうございます。
まずは、ご教示いただいたコードで試してみます。
（さや） 2016/06/09(木) 15:06
Private Sub CommandButton1_Click()
Dim cFile As String
Dim cPath As String
Dim iR As Long
Dim i As Long
Dim vw As Variant
vw = Array("B4", "F4", "B5", "F5", "B6", "F6", "B7", "F7", "B8", "F8", "B11", "B14", "H14", "B15", "H15", "B16", "H16", "B19", "H19", "B20", "B21", "B22", "H22", "B23", "B24", "B25", "B26", "H26")
iR = 41
cPath = ActiveWorkbook.Path & "\input\"
cFile = Dir(cPath & "*.xls*")
While cFile <> ""
With Workbooks.Open(cPath & cFile, False, True)
With .Sheets(1)
iR = iR + 1
For i = 0 To UBound(vw)
Me.Cells(iR, i + 1).Value = .Range(vw(i))
Next i
End With
.Close False
End With
cFile = Dir
Wend
End Sub
お手数ばかりで申し訳ありませんが、
帳票ブックへの反映は、どのように組み込めばよろしいですか？
先にも明記させていただいた通り、
帳票リストはA5から1単票ブック1行でデータ挿入です。
よろしくお願い致します。
（さや） 2016/06/09(木) 16:28
iR = 41 の部分を、5行目に合うように iR = 4に変更すれば、反映されるんですね！？
申し訳ありません。
もう少し、自身でやってみます。
またうまく動作しないようでしたら、ご相談させてください。
よろしくお願い致します。
（さや） 2016/06/09(木) 16:31
うまく動くようになった後は、処理前に Application.ScreenUpdating = False し、処理後に True にすると、処理が速くなりますよ。
（Application.ShowWindowsInTaskbar も止めておくと、ちかちかしなくて良いかも？）
（???） 2016/06/09(木) 17:12
19:38 1か所、領域指定が固定になっていたところを変動化しました。
???さんの二番煎じですが書きましたのでアップしておきます。
・単票ファイルのフォルダは、どこでもいいのですがデスクトップ上の Input というフォルダにしました。 ・マクロブックの "帳票" というシート名のデータ最終行に追加します。 ・（さや） 2016/06/09(木) 15:06 でアップされた 帳票シートのレイアウト、これを活用します。 別途、マクロブックに "パターン" という名前のシートを準備し、アップされたイメージの４行目と５行目の２行を１行目、２行目に コピペしておいてください。コードでは、２行目のセル情報だけを参照していますが、どの列にどのセルからデータを持ってきているということが 項目名とともに記述されているのでわかりやすいと思います。 また、将来、レイアウト変更があった時も、このパターンシートを変更すれば、コードは変更なしで対応できます。
Sub Sample()
Dim dicF As Object
Dim mapping As Variant
Dim fPAth As String
Dim fName As Variant
Dim i As Long
Dim j As Long
Dim shT As Worksheet
Dim shF As Worksheet
Application.ScreenUpdating = False
Set dicF = CreateObject("Scripting.Dictionary") '取り込みファイル名登録辞書
Set shT = ThisWorkbook.Sheets("帳票") 'マクロブックの帳票シート
mapping = ThisWorkbook.Sheets("パターン").Range("A1").CurrentRegion.Rows(2).Value 'マクロブックの項目取り込み情報
fPAth = CreateObject("WSCript.Shell").SpecialFolders("DeskTop") & "\Input\" '単票ブックフォルダ
fName = Dir(fPAth & "*.xlsx")
Do While fName <> ""
dicF(fName) = True
fName = Dir()
Loop
ReDim v(1 To dicF.Count, 1 To UBound(mapping, 2))
For Each fName In dicF
Set shF = Workbooks.Open(fPAth & fName).Sheets(1) '単票ブックの最初のシート
i = i + 1 '配列内セット行
For j = 1 To UBound(mapping, 2)
v(i, j) = shF.Range(mapping(1, j)).Value
Next
shF.Parent.Close False '単票ブックを閉じる
Next
'配列から一括転記
shT.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(UBound(v, 1), UBound(v, 2)).Value = v
End Sub
（β） 2016/06/09(木) 17:59
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.