質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
87.20%
ファイル

ファイルとは、文字列に基づいた名前又はパスからアクセスすることができる、任意の情報のブロック又は情報を格納するためのリソースです。

VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

マクロ

定義された処理手続きに応じて、どのような一連の処理を行うのかを特定させるルールをマクロと呼びます。

配列

配列は、各データの要素(値または変数)が連続的に並べられたデータ構造です。各配列は添え字(INDEX)で識別されています。

受付中

VBAマクロ 大量Excelファイルのデータ転記

2134
2134

総合スコア0

ファイル

ファイルとは、文字列に基づいた名前又はパスからアクセスすることができる、任意の情報のブロック又は情報を格納するためのリソースです。

VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

マクロ

定義された処理手続きに応じて、どのような一連の処理を行うのかを特定させるルールをマクロと呼びます。

配列

配列は、各データの要素(値または変数)が連続的に並べられたデータ構造です。各配列は添え字(INDEX)で識別されています。

1回答

0評価

3クリップ

21閲覧

投稿2020/04/22 03:02

前提・実現したいこと

Excelファイルから、Excelファイルへデータを転記するExcelマクロを作成したいです。
下記ご確認いただき、知恵をお貸しいただきたいです。

【内容】
顧客からの問い合わせを、問い合わせ1件につき1つのExcelファイルで管理しています。
問い合わせファイル内には、問い合わせの内容・対応した内容・対応日などを記載しており、問い合わせファイルは対応のステータス毎(未対応、対応中、対応完了)にフォルダを分けて管理しています。
3つのフォルダに別れた全ての問い合わせファイルを、以下条件の元、一覧として作成するマクロを作成したいです。(集計などを行うために一覧にしたいです)


【前提条件】

  1. 一覧ファイル(マクロブック)は、未対応フォルダ、対応中フォルダ、対応完了フォルダと同フォルダ内にある。

─┬─ 一覧ファイル
''''├─ 未対応フォルダ
''''├─ 対応中フォルダ
''''└─ 対応完了フォルダ

  1. 一覧ファイルへは、問い合わせファイルを1レコードとし、ファイル内すべての内容(問い合わせ内容・対応内容・対応日など)を転記する。

※一覧ファイルのB列~BM列に64このデータを転記する。A列は、レコードの1件目から順に自動で番号を採番する。)
※また一覧ファイルの11行目までは、タイトル行としているため、12行目から転記を始める。

  1. 一覧ファイルの特定のカラム(AQ列:対応完了日)が空白でない場合(対応が完了している)は、該当する問い合わせファイルから転記は行わず、

AQ列が空白の場合(対応が完了していない)のみ、該当する問い合わせファイルから転記を行う。(処理時間短縮のため)

  1. 問い合わせファイルは、日に日に新規で追加される。

  2. 処理効率化のため、データを転記する際は、配列を利用したい。

【補足】
問い合わせファイルのファイル名は一意であり、ファイル名は、「◯_aaa.xlsx」となっています。
aaa(以下、管理番号と呼ぶ)の部分は、問い合わせファイル内のセルを参照しており、一覧に転記した場合、管理番号は一覧ファイルのD列に転記される。


【作成したソースコード概要】

① 一覧のAQ列が空白ではない(対応が完了してる)レコードの管理番号を取得する
② 一覧からAQ列が空白(対応が完了していない)レコードをクリアする
③ 問い合わせがファイルのパスを取得する
④ ①で取得した管理番号をキーに、問い合わせファイルのファイル名の一部(管理番号の部分)と一致しない場合、⑤の処理を実行
⑤ 問い合わせファイルを開いて、データを一覧に転記する
⑥ 作成した一覧を、ソート、リファレンスナンバーを採番


上記イメージでソースコードを作成しようと思っていますが、ソースコード作成のアドバイスや、配列に格納して転記する方法もご教示いただければと思います。
エンジニアではないため拙い部分のあり、また最近VBA独学で勉強しているため基本を理解していないところもあるかもしれませんが、ご了承ください。
宜しくお願い致します。

発生している問題・エラーメッセージ

作成した下記ソースコードでは、★の箇所で「インデックスが有効範囲にありません」とエラーになります。 他に下記ソースコードに問題がある場合も、ご指摘いただければ幸いです。

該当のソースコード

VBA

Option Explicit Dim intRet As Integer Dim wb As Workbook Dim ws As Worksheet Dim rng As Range Dim finNum() As Variant '対応完了の管理番号 Dim nyNum() As Variant '対応未完了の管理番号 Dim i As Integer '対応完了の配列変数 Dim j As Integer '対応未完了の配列変数 Dim num As Variant Dim file As Object Dim cnt As Integer Public Sub 一覧作成() '処理時間計測用変数 Dim starttime As Double Dim endtime As Double Dim processtime As Double '一覧作成の実行確認 intRet = MsgBox("表を最新の状態にします。よろしいですか?", vbOKCancel, "確認") If intRet <> vbOK Then Exit Sub End If '開始時間取得 starttime = Timer '画面更新を停止 With Application .ScreenUpdating = False .EnableEvents = False .Calculation = xlCalculationManual End With 'シートの非表示行を表示する Cells.EntireColumn.Hidden = False Set wb = ActiveWorkbook Set ws = ActiveSheet '一覧にある管理NOを取得する For Each rng In ws.Range("AQ12", ws.Cells(ws.Rows.Count, "AQ").End(xlUp)) If ws.Cells(rng.row, 43) <> "" Then For i = LBound(finNum) To UBound(finNum) '/★ここで上記エラーになります finNum(i) = ws.Cells(rng.row, 4) Next ' ElseIf ws.Cells(rng.row, 43) = "" Then ' For j = LBound(nyNum) To UBound(nyNum) ' nyNum(j) = ws.Cells(rng.row, 4) ' Next End If Next '対応完了日(AQ列)が空白の行の値をクリアする Dim rw As Integer For Each rng In ws.Range("AQ12", ws.Cells(ws.Rows.Count, "AQ").End(xlUp)) If ws.Cells(rng.row, 43) = "" Then rw = ws.Cells(rng.row, 43).row Rows(rw).ClearContents End If Next '問い合わせファイルのパスを取得 Dim Fld() As String Dim a As Integer Dim fso As Object 'FileSystemObject Dim BaseNames() As String Dim Names() As String Dim intPos As Integer Const strDefFild As String = "問合票" Fld(0) = wb.Path & "\10 未対応" Fld(1) = wb.Path & "\20 対応中" Fld(2) = wb.Path & "\30 対応済み" Set fso = CreateObject("Scripting.FileSystemObject") For a = 0 To 2 For Each file In fso.GetFolder(Fld(a)).Files cnt = cnt + 1 BaseNames(cnt) = fso.GetBaseName(file.Name) intPos = InStr(BaseNames(cnt), strDefFild) Names(cnt) = Mid(BaseNames(cnt), intPos + 2) '対応完了以外の問合票ファイルを一覧へ取り込み(対応未完了 + 新規) If Names(cnt) <> finNum(i) Then Call 取り込み End If Next Next Call 項番採番 '終了時間取得 endtime = Timer '処理時間取得 processtime = endtime - starttime Range("A3").Select MsgBox "一覧作成しました。" & "(処理時間:" & processtime & "秒)" '画面更新を再開 With Application .Calculation = xlCalculationAutomatic .EnableEvents = True .ScreenUpdating = True End With End Sub '★問合票ファイルを一覧へ取り込み Public Sub 取り込み() Dim row As Integer Dim clm As Variant Dim s As Worksheet For row = 12 To cnt For Each clm In ws.Range("A11", s.Cells(11, s.Columns.Count).End(xlToLeft)) With Workbooks.Open(file.Path, UpdateLinks:=False, ReadOnly:=True) Set s = .Sheets("問い合わせ一覧票") file.Cells(row, clm).Value = s.Cells(10, 33).Value   (文字数の関係により省略) file.Cells(row, clm).Value = s.Cells(2, 45).Value file.Cells(row, clm).Value = s.Cells(2, 46).Value End With Next clm Next End Sub '作成した一覧をソート、項番採番 Sub 項番採番() Dim L As Integer Dim n As Integer Set wb = ActiveWorkbook Set ws = ActiveSheet 'ソート Range("A12", ws.Cells(ws.Rows.Count, "D").End(xlUp).Offset(0, -3)).Select Selection.Sort Key1:=Range("D12"), Order1:=xlAscending, _ Key2:=Range("E12"), Order2:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod:=xlPinYin '番号付け L = 12 n = 1 While wb.ws.Cells(L, 4).Value <> "" wb.ws.Cells(L, 1).Value = n L = L + 1 n = n + 1 Wend End Sub

補足情報(FW/ツールのバージョンなど)

Excel2016

良い質問の評価を上げる

以下のような質問は評価を上げましょう

  • 質問内容が明確
  • 自分も答えを知りたい
  • 質問者以外のユーザにも役立つ

評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

気になる質問をクリップする

クリップした質問は、後からいつでもマイページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

  • プログラミングに関係のない質問
  • やってほしいことだけを記載した丸投げの質問
  • 問題・課題が含まれていない質問
  • 意図的に内容が抹消された質問
  • 過去に投稿した質問と同じ内容の質問
  • 広告と受け取られるような投稿

評価を下げると、トップページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

まだ回答がついていません

会員登録して回答してみよう

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
87.20%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問

同じタグがついた質問を見る

ファイル

ファイルとは、文字列に基づいた名前又はパスからアクセスすることができる、任意の情報のブロック又は情報を格納するためのリソースです。

VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

マクロ

定義された処理手続きに応じて、どのような一連の処理を行うのかを特定させるルールをマクロと呼びます。

配列

配列は、各データの要素(値または変数)が連続的に並べられたデータ構造です。各配列は添え字(INDEX)で識別されています。