転記先ファイルを生成してデータを流し込む。

この講座は有料講座です。
講座の購入後ご覧になれます。

ログインアカウントの新規作成

解説

演習問題テーマ:転記先ファイルを生成してデータを流し込む。

条件に一致するデータを抽出して別リストに書き出す方法のアレンジです。転記先ファイルの生成からマクロで一貫して行います。

この教材についての過去の質問・感想

11124 : 小川慶一の回答 (2019-02-11 18:11:59)

受講生 さん:

解決できてよかったですね。
今回でてきたエラーは、演習の難易度以前の問題です。簡単なものから手をならしつつ取り組まないと、難易度の高い問題には対処できません。
ベーシックの問題は、「見た瞬間から手がよどみなく手が動いて、頭をほとんど使わないでも勝手に解ける」というくらいまで練習してください。


> > 小川先生
> >助言ありがとうございました。
> >エラー解消することができました。
>
> >小川先生の仰るように基礎編フォローアップベイシックに戻ってもう一度やり直してみたいと思います。


11119 : 受講生さんのコメント (2019-02-11 01:27:39)

小川慶一さん:

> ゲストさん:
>
> エクセルの設定を変えたら、ファイルを閉じてからまた開きます。
> または、エクセルを終了してからまた開いてみてください。
>
> 以下は、参考。
>
> ☆講座「エクセルマクロ・VBA導入編」
> ○エクセルマクロ・VBAを利用するための推奨設定 - Excel2010、Excel2013、Excel2016の場合
> https://online.pc5bai.com/movie/index/10/8
>
> ☆ 講座「エクセルマクロを書くときによくある問題とその対処法」
> ○マクロが無効に設定されています
> https://online.pc5bai.com/movie/index/10/8
>
>
> そのレベルのエラーへの対処にそれだけ苦労するようだと「エクセルマクロ・VBA基礎編フォローアップ アドバンスド」の問題には相当苦労されるものと予想します。
> 「エクセルマクロ・VBA基礎編フォローアップ ベーシック」の演習内容をまずはしっかり習得されることを強く推奨します。

> 小川先生
>助言ありがとうございました。
>エラー解消することができました。

>小川先生の仰るように基礎編フォローアップベイシックに戻ってもう一度やり直してみたいと思います。


11116 : 小川慶一の回答 (2019-02-10 22:55:17)

ゲストさん:

エクセルの設定を変えたら、ファイルを閉じてからまた開きます。
または、エクセルを終了してからまた開いてみてください。

以下は、参考。

☆講座「エクセルマクロ・VBA導入編」
○エクセルマクロ・VBAを利用するための推奨設定 - Excel2010、Excel2013、Excel2016の場合
https://online.pc5bai.com/movie/index/10/8

☆ 講座「エクセルマクロを書くときによくある問題とその対処法」
○マクロが無効に設定されています
https://online.pc5bai.com/movie/index/10/8


そのレベルのエラーへの対処にそれだけ苦労するようだと「エクセルマクロ・VBA基礎編フォローアップ アドバンスド」の問題には相当苦労されるものと予想します。
「エクセルマクロ・VBA基礎編フォローアップ ベーシック」の演習内容をまずはしっかり習得されることを強く推奨します。


> 小川先生
> いつも楽しく勉強させていただいております。
>
> 1つ質問があります。
> >[1] ワークブック「template.xls」を開き、「sample.xls」という名称で保存せよ。そして、新たに生成した「sample.xls」を閉じる。
> という問題で動画にもあるようにマクロの自動記録を使ってコードを作りました。
> しかしいざ実行してみると以下のようなエラーが出てしまい実行できませんでした。このエラーを調べて開発タブのマクロのセキュリティ部分をすべてのマクロを有効するに変えたのですがエラーは消えず実行することができませんでした。このエラーを解消する方法はあるのでしょうか?
>
> <エラー内容>
> このオブジェクトのマクロは無効に設定されています。マクロを有効にする方法についてはオンラインヘルプまたはホストアプリケーションのドキュメントを参照してください。


11115 : ゲストさんのコメント (2019-02-10 22:44:56)

小川先生
いつも楽しく勉強させていただいております。

1つ質問があります。
>[1] ワークブック「template.xls」を開き、「sample.xls」という名称で保存せよ。そして、新たに生成した「sample.xls」を閉じる。
という問題で動画にもあるようにマクロの自動記録を使ってコードを作りました。
しかしいざ実行してみると以下のようなエラーが出てしまい実行できませんでした。このエラーを調べて開発タブのマクロのセキュリティ部分をすべてのマクロを有効するに変えたのですがエラーは消えず実行することができませんでした。このエラーを解消する方法はあるのでしょうか?

<エラー内容>
このオブジェクトのマクロは無効に設定されています。マクロを有効にする方法についてはオンラインヘルプまたはホストアプリケーションのドキュメントを参照してください。


9819 : 小川慶一の回答 (2018-04-22 08:33:16)

受講生 さん:

> 適当にスペースを入れても、《改行の段階で自動調整してくれる》との先入観に依るものです。

ダブルクオートの中は文字列なので、調整はしてくれません。
(されたら、状況によってはかえって迷惑ですね。。)

僕も、「ファイル名の先頭が半角スペース」というのは、通常のエクセル操作ではできないことということもあり、想定していませんでした。




> 小川様
>
> ご指摘の通りでした。
>
> 最初のサンプルコードでは、確かに■の部分に「半角スペース」が一つ入っていました。
> C:\Users\NEC\Desktop\ks204_mondai\■" & fname, FileFormat:= _
>
> ■(「半角スペース」)を削除のところ、問題なく作動しました。
>
> 適当にスペースを入れても、《改行の段階で自動調整してくれる》との先入観に依るものです。
>
> 同じ過ちを繰り返さないように注意いたします。


9817 : 受講生さんのコメント (2018-04-22 08:13:10)

小川様

ご指摘の通りでした。

最初のサンプルコードでは、確かに■の部分に「半角スペース」が一つ入っていました。
C:\Users\NEC\Desktop\ks204_mondai\■" & fname, FileFormat:= _

■(「半角スペース」)を削除のところ、問題なく作動しました。

適当にスペースを入れても、《改行の段階で自動調整してくれる》との先入観に依るものです。

同じ過ちを繰り返さないように注意いたします。


9808 : 小川慶一の回答 (2018-04-21 15:05:35)

受講生 さん:

分かりました。たぶん、これかな?と。

最初にいただいたサンプルコードですと、

> "C:\Users\NEC\Desktop\ks204_mondai\ " & fname

↑ ks204_mondai\ のあと、「半角スペース」がひとつ入っています。
それで、生成されるファイル名が、

"n.xls"

ではなく、

" n.xls"

になっています。
(一文字目は半角スペース、二文字目は小文字のn、三文字目はドット、...)

というのが問題の根本ではないかな、と。

この点も、お確かめいただけますでしょうか。





> 小川様
>
> 早速の返信、ありがとうございました。
> ご提示いただきました4つのマクロは、全て正常に作動いたしました。
> 以上ご連絡申し上げます。
>


9807 : 小川慶一の回答 (2018-04-21 14:39:49)

受講生 さん:

そうですか。

だとすると、ちょっと問題の本質がよくわからないです。

前回の僕のコメントに戻りますが、

>「実行時エラー'9' インデックスが有効範囲にありません。」
>は、シートやファイルを指定する際、(カッコ)内の文字列がおかしいと出てくるエラーです。

というわけで。

まずは、いただいたサンプルコードで、目的とするエクセルファイル、あるいはそのファイルの中のエクセルシートは実際に存在するのか?ということから確かめてみたいです。

次のステップとして、以下のサンプルコードで動作確認お願い出来ますでしょうか。
どこで、どんなエラーが出る止まったのか?あるいは、無事に動作したか?と言うことについて教えてください。

Sub Macro2_revision1()

    Dim fname
    fname = "n.xls"
    
    Workbooks.Open Filename:="C:\Users\NEC\Desktop\ks204_mondai\template.xls"
    ActiveWorkbook.SaveAs Filename:= _
    "C:\Users\NEC\Desktop\ks204_mondai\ " & fname, FileFormat:= _
    xlOpenXMLWorkbook, CreateBackup:=False
    
    Range("A1").Value = Workbooks(fname).Name '[A]
    Range("A2").Value = Workbooks(fname).Worksheets("転記先").Name '[B]
    
    Workbooks(fname).Worksheets("転記先").Range("B10").Value = 100
    
    ActiveWorkbook.Close
End Sub


これで、[A]の部分で止まれば、ファイル名の指定が怪しい。
[B]の部分で止まれば、シート名の指定が怪しいということで絞込みが出来ます。

[A]でおかしかったならば、例えば、余計なファイルを開いていないか?
エクセルファイル名は、拡張子を含めて一字一句間違えなく同じか?
といったあたりがまず確認したいポイントとして浮かびます。

[B]でおかしかったならば、例えば、シート名は本当に適切か?
元のファイルたる「template.xls」のシートの名前は本当に「転記先」か?
といったあたりがまず確認したいポイントとして浮かびます。
[B]のシート名について言うと、特に、いつの間にかに、何かの作業をした関係で、「転記先」という文字列の前後あるいはその中に余計なスペース等が紛れていることがあります。

この件について、今の段階で僕が何かかけるとしたら、このくらいですね。

ともあれ、まずは僕が上に用意したテストコードを実際に動作させてみて、その上で結果を報告いただければと思います。





> 小川様
>
> 早速の返信、ありがとうございました。
> ご提示いただきました4つのマクロは、全て正常に作動いたしました。
> 以上ご連絡申し上げます。


9806 : 受講生さんのコメント (2018-04-21 13:30:01)

小川様

早速の返信、ありがとうございました。
ご提示いただきました4つのマクロは、全て正常に作動いたしました。
以上ご連絡申し上げます。


9805 : 小川慶一の回答 (2018-04-21 12:24:14)

受講生 さん:

「実行時エラー'9' インデックスが有効範囲にありません。」
は、シートやファイルを指定する際、(カッコ)内の文字列がおかしいと出てくるエラーです。

Sub ErrorTest()
    '以下の[b], [c] では、「実行時エラー'9' インデックスが有効範囲にありません。」というエラーが出ます。

    '[a]指定されたセルが存在しないからエラー
    Range("10B").Value = 100
    
    '[b]アクティブなファイルにシート「天気予報」がない場合は、指定されたシートが存在しないからエラー
    Worksheets("天気予報").Range("B10").Value = 100
    
    '[c]ファイル「オレのこだわりファイル.xls」が開かれていない場合はエラー
    Worksheets("オレのこだわりファイル.xls").Worksheets("Sheet1").Range("B10").Value = 100
End Sub


さて、では、なぜ今回うまくいかないか?ということですが…。
ちょっと根深そうです。

ついては、まずは、以下のテストを実行しだけませんか。

ks204.xls にテストの結果をお知らせいただければ幸いです。
新しいモジュールを追加して、以下の4つのマクロ貼り付け順番に実行してみてください。

以下のそれぞれのテストでは、エラーが出て途中終了してしまった場合は、その次に実行するテストの前に「template.xls」を手動で閉じてください。

Sub TPTEST_1()
    Dim fname
    fname = "n1.xls"
    Workbooks.Open Filename:=ThisWorkbook.Path & "\template.xls"
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & fname, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    
    Workbooks(fname).Worksheets("転記先").Range("B10").Value = 100

    ActiveWorkbook.Close
End Sub
Sub TPTEST_2()
    Dim fname
    fname = "n2.xls"
    Workbooks.Open Filename:=ThisWorkbook.Path & "\template.xls"
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & fname, FileFormat:=xlExcel8, CreateBackup:=False
    
    Workbooks(fname).Worksheets("転記先").Range("B10").Value = 100

    ActiveWorkbook.Close
End Sub
Sub TPTEST_3()
    Dim fname
    fname = "n3"
    Workbooks.Open Filename:=ThisWorkbook.Path & "\template.xls"
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & fname & ".xls", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    
    Workbooks(fname).Worksheets("転記先").Range("B10").Value = 100

    ActiveWorkbook.Close
End Sub
Sub TPTEST_4()
    Dim fname
    fname = "n4"
    Workbooks.Open Filename:=ThisWorkbook.Path & "\template.xls"
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & fname & ".xls", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    
    Workbooks(fname).Worksheets("転記先").Range("B10").Value = 100

    ActiveWorkbook.Close
End Sub


僕の予想では:
TPTEST_1: 無事に動作する
TPTEST_2: 無事に動作する
TPTEST_3: エラー「実行時エラー'9' インデックスが有効範囲にありません。」で強制終了
TPTEST_4: エラー「実行時エラー'9' インデックスが有効範囲にありません。」で強制終了

なのですが...。

よろしくお願いいたします。


> いつも楽しく視聴させていただいております。
>
> さて、本編7分50分前後の動作確認時に発生するエラーについて質問いたします。
>
> エラーについてですが、
> 【 Workbooks(fname).Worksheets("転記先").Range("B10").Value = 100 】において、
> 実行時エラー'9' インデックスが有効範囲にありません。
> ・・・と表示されます。
>
> なぜエラーが発生するのか、ご教示のほどお願い致します。
>
>
> Sub Macro2()
>
> Dim fname
> fname = "n.xls"
>
> Workbooks.Open Filename:="C:\Users\NEC\Desktop\ks204_mondai\template.xls"
> ActiveWorkbook.SaveAs Filename:= _
> "C:\Users\NEC\Desktop\ks204_mondai\ " & fname, FileFormat:= _
> xlOpenXMLWorkbook, CreateBackup:=False
>
> Workbooks(fname).Worksheets("転記先").Range("B10").Value = 100
>
> ActiveWorkbook.Close
> End Sub


9803 : 受講生さんのコメント (2018-04-21 11:22:16)

いつも楽しく視聴させていただいております。

さて、本編7分50分前後の動作確認時に発生するエラーについて質問いたします。

エラーについてですが、
【 Workbooks(fname).Worksheets("転記先").Range("B10").Value = 100 】において、
実行時エラー'9' インデックスが有効範囲にありません。
・・・と表示されます。

なぜエラーが発生するのか、ご教示のほどお願い致します。


Sub Macro2()

Dim fname
fname = "n.xls"

Workbooks.Open Filename:="C:\Users\NEC\Desktop\ks204_mondai\template.xls"
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\NEC\Desktop\ks204_mondai\ " & fname, FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False

Workbooks(fname).Worksheets("転記先").Range("B10").Value = 100

ActiveWorkbook.Close
End Su


7467 : 小川慶一の回答 (2017-01-01 18:13:01)

受講生 さん:

拡張子は指定しない場合デフォルトのものが入ります。


7466 : 受講生さんのコメント (2017-01-01 16:34:59)

お世話になっております。
転記先のファイル名について一つ教えてください。
変数にするまでは「sample.xls」と、.xlsが付いていますが、最終的にfnameの変数に置き換え、「n,t,b」などのファイル名の言葉が入ってくるようになると思います。
その際文末に「&".xls"」は着ける必要はないのでしょうか。
ActiveWorkbook.SaveAs Filename:="D:\ks201-205\ks204\ks204_mondai\" & fname _
ActiveWorkbook.SaveAs Filename:="D:\ks201-205\ks204\ks204_mondai\" & fname&".xls" _


Sub ks204_02()
    Dim fname
    Dim moto
    Dim saki
    Dim slist
    For slist = 4 To 7
        fname = Workbooks("ks204.xls").Worksheets("List").Range("C" & slist).Value
        Workbooks.Open Filename:="D:\ks201-205\ks204\ks204_mondai\template.xls"
        ActiveWorkbook.SaveAs Filename:="D:\ks201-205\ks204\ks204_mondai\" & fname _'ここです。
            , FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
            ReadOnlyRecommended:=False, CreateBackup:=False


3日がかりのその仕事、3分で終わらせる方法教えます。ガラパゴスタディーオンライン講座 ユーザー登録

本講座の動画一覧

  1. 【動画1】 住所情報を都道府県から市区町村レベルで細かく分割する
    【動画1】 住所情報を都道府県から市区町村レベルで細かく分割する 未習得
  2. 【動画2】 保険料額表から標準報酬額ごとの保険料負担額を算出する
    【動画2】 保険料額表から標準報酬額ごとの保険料負担額を算出する 未習得
  3. 【動画3】 セル内の文字列から区切り文字を見つけ、その出現回数+1回だけデータ転記する
    【動画3】 セル内の文字列から区切り文字を見つけ、その出現回数+1回だけデータ転記する 未習得
  4. 【動画4】 セル内の文字列から区切り文字を見つけ、その区切り文字の間にある文字列を切り出す
    【動画4】 セル内の文字列から区切り文字を見つけ、その区切り文字の間にある文字列を切り出す 未習得
  5. 【動画5】 セル内の文字列から区切り文字を見つけ、その区切り文字の間にある文字列を反映しつつデータ転記する
    【動画5】 セル内の文字列から区切り文字を見つけ、その区切り文字の間にある文字列を反映しつつデータ転記する 未習得
  6. 【動画6】 VLookUp関数、Index関数、Match関数より便利なマクロ-該当する行が見つからない場合の処理をアレンジ
    【動画6】 VLookUp関数、Index関数、Match関数より便利なマクロ-該当する行が見つからない場合の処理をアレンジ 未習得
  7. 【動画7】 VLookUp関数、Index関数、Match関数より便利なマクロ-複数シート間、複数ファイル間でのマッチング
    【動画7】 VLookUp関数、Index関数、Match関数より便利なマクロ-複数シート間、複数ファイル間でのマッチング 未習得
  8. 【動画8】 転記先シートを生成してデータを流し込む。
    【動画8】 転記先シートを生成してデータを流し込む。 未習得
  9. 【動画9】 転記先ファイルを生成してデータを流し込む。
    【動画9】 転記先ファイルを生成してデータを流し込む。 未習得
  10. 【動画10】 条件に一致するデータだけのリストを、条件に一致しないデータを削除する方法で作成する。
    【動画10】 条件に一致するデータだけのリストを、条件に一致しないデータを削除する方法で作成する。 未習得
  11. 【動画11】 条件に一致するデータだけのリストを、条件に一致しないデータを削除する方法で同一ファイル内に連続的に作成する。
    【動画11】 条件に一致するデータだけのリストを、条件に一致しないデータを削除する方法で同一ファイル内に連続的に作成する。 未習得
  12. 【動画12】 条件に一致するデータだけのリストを、条件に一致しないデータを削除する方法で複数の別ファイルとして連続的に作成する。
    【動画12】 条件に一致するデータだけのリストを、条件に一致しないデータを削除する方法で複数の別ファイルとして連続的に作成する。 未習得
  13. 【動画13】 ファイル配布→配布先でのデータ記入→記入済データを手元資料に統合
    【動画13】 ファイル配布→配布先でのデータ記入→記入済データを手元資料に統合 未習得
  14. 【動画14】 データ記入用ファイルを配布する-その1
    【動画14】 データ記入用ファイルを配布する-その1 未習得
  15. 【動画15】 データ記入用ファイルを配布する-その2
    【動画15】 データ記入用ファイルを配布する-その2 未習得
  16. 【動画16】 各部署からファイルを回収し、記入済データを手元資料に統合その1
    【動画16】 各部署からファイルを回収し、記入済データを手元資料に統合その1 未習得
  17. 【動画17】 各部署からファイルを回収し、記入済データを手元資料に統合その2
    【動画17】 各部署からファイルを回収し、記入済データを手元資料に統合その2 未習得
  18. 【動画18】 商品ごと、年ごとの販売額合計をピボットテーブルのように出力する(その1)
    【動画18】 商品ごと、年ごとの販売額合計をピボットテーブルのように出力する(その1) 未習得
  19. 【動画19】 商品ごと、年ごとの販売額合計をピボットテーブルのように出力する(その2-1)
    【動画19】 商品ごと、年ごとの販売額合計をピボットテーブルのように出力する(その2-1) 未習得
  20. 【動画20】 商品ごと、年ごとの販売額合計をピボットテーブルのように出力する(その2-2)
    【動画20】 商品ごと、年ごとの販売額合計をピボットテーブルのように出力する(その2-2) 未習得

塾長 小川慶一

メニュー

コメント紹介

もっと見る

ページの先頭へ