レッスン-投稿一覧パソコン仕事５倍塾ポータル

投稿/コメントを表示します。

エクセルマクロ・VBA基礎編

ゲストさんの投稿

(投稿ID: 4947) 添付ファイルのダウンロード権限がありません

小川様

動画の作成ありがとうございました。
今後自分が作成したいプログラムが、異なるファイル間でのデータ転記システムなので、非常に参考となる講義でした。

質問がございます。
今回はフォルダ内に保存したファイル名が一定の場合のみに有効なプログラムであると思いますが、ファイル名が転記作業毎に変わる（別のファイルになる）場合のプログラムはどうすればよいでしょうか？転記作業の度にＶＢＡのファイル名を変更するのは現実的ではないですよね。

この講義を受けて、自分が思い描くプログラムを実現するための課題が明確になりました。自分に不足している知識を積み重ねていきたいので、アドバイスを頂戴したいです。

私が実現したいプログラムは以下の通りです
①会社規定の製品情報が記載されたデータ転記元のExcelファイルがある
②_①のファイルは製品毎に用意されており200～300種類ほどある
③転記元のファイルからデータをユーザーから指示されるExcelファイルに転記する。書式貼りつけでなく、値貼りつけもしくは数式貼りつけが指示されている
④_③の理由から転記先のフォーマットが一定でないのでマクロで完全自動化するのが困難

転記元と転記先のファイル名の識別と作業実行が課題になっております。

私が実現したいプログラムのイメージをファイルで添付致しますのでご確認をお願い致します。

2020/12/10 22:40

小川慶一さんのコメント

(コメントID: 6977)

ゲストさん:

こんにちは。
参考になったということであれば何よりです。

添付ファイル、ざっと拝見しました。

> 私が実現したいプログラムは以下の通りです
> ①会社規定の製品情報が記載されたデータ転記元のExcelファイルがある
> ②_①のファイルは製品毎に用意されており200～300種類ほどある
> ③転記元のファイルからデータをユーザーから指示されるExcelファイルに転記する。書式貼りつけでなく、値貼りつけもしくは数式貼りつけが指示されている
> ④_③の理由から転記先のフォーマットが一定でないのでマクロで完全自動化するのが困難

実現できたら実務へのインパクトが大きそうですね (^^

フォルダ内のファイル一覧を取得して個別に条件分岐する方法というのが課題のうちの1つということかと思います。
それについては、「ガッツリやる方法を」ということであれば、ウチの講座内では「外部連携講座」が守備範囲です。

一方、簡便な方法としては、dir関数を使うことができます。さっそく試してみてください。
https://www.moug.net/tech/exvba/0100048.html

「値でなく式を転記したい」という件については、以下を参考に。
とはいえ、DPR的に考えると、基本的には、値転記だけでやりきったほうが何かと良いかとは思います。(利用する手段が減るのでメンテナンスしやすい、開発も楽。想定外の値によって式の出力結果がおかしくなるといったことのリスクが少ない)

参考: エクセル仕事を劇的に楽にする「DPRフレームワーク」
https://forum.pc5bai.com/lesson/course/35

Sub formula_copy_sample()
    '以下を実行したあと、セルC1, C4, C7に入っている式の内容を確認してみてください
    '.Formula, .FormulaR1C1 については、詳しくは発展編1で扱います
    Range("A1").Value = 1
    Range("A2").Value = 2
    
    Range("A4").Value = 11
    Range("A5").Value = 12
    
    Range("A7").Value = 101
    Range("A8").Value = 102
    
    Range("C1").Value = "=Sum(A1:A2)" '式の埋め込みは.valueでできる(.formulaでも可)
    
    Range("C4").Formula = Range("C1").Formula '式をそのままコピー
    
    Range("C7").Formula = Range("C1").FormulaR1C1 '式を相対参照でコピー
End Sub

2020/12/11 01:36

田中宏明さんのコメント

(コメントID: 6978)

ゲストさん:

こんにちは。私はオンライン受講生です。
>
転記元と転記先のファイル名の識別と作業実行が課題になっております。

Excelシートにフォルダ名とファイル名を記載した転記元と転記先の対比表を作成し、マクロ実行時にそれを読み取って転記元と転記先を特定する形が良いと思いました。
的外れな意見でしたら申し訳ありませんが、私の経験ということでご参考まで。

2020/12/11 04:09

ゲストさんのコメント

(コメントID: 6980)

小川慶一さん:

コメントありがとうございます。

いただいたアドバイスを実行致します！
> ゲストさん:
>
> こんにちは。
> 参考になったということであれば何よりです。
>
> 添付ファイル、ざっと拝見しました。
>
> > 私が実現したいプログラムは以下の通りです
> > ①会社規定の製品情報が記載されたデータ転記元のExcelファイルがある
> > ②_①のファイルは製品毎に用意されており200～300種類ほどある
> > ③転記元のファイルからデータをユーザーから指示されるExcelファイルに転記する。書式貼りつけでなく、値貼りつけもしくは数式貼りつけが指示されている
> > ④_③の理由から転記先のフォーマットが一定でないのでマクロで完全自動化するのが困難
>
> 実現できたら実務へのインパクトが大きそうですね (^^
>
> フォルダ内のファイル一覧を取得して個別に条件分岐する方法というのが課題のうちの1つということかと思います。
> それについては、「ガッツリやる方法を」ということであれば、ウチの講座内では「外部連携講座」が守備範囲です。
>
> 一方、簡便な方法としては、dir関数を使うことができます。さっそく試してみてください。
> https://www.moug.net/tech/exvba/0100048.html
>
>
> 「値でなく式を転記したい」という件については、以下を参考に。
> とはいえ、DPR的に考えると、基本的には、値転記だけでやりきったほうが何かと良いかとは思います。(利用する手段が減るのでメンテナンスしやすい、開発も楽。想定外の値によって式の出力結果がおかしくなるといったことのリスクが少ない)
>
> 参考: エクセル仕事を劇的に楽にする「DPRフレームワーク」
> https://forum.pc5bai.com/lesson/course/35
>
>

Sub formula_copy_sample()
>     '以下を実行したあと、セルC1, C4, C7に入っている式の内容を確認してみてください
>     '.Formula, .FormulaR1C1 については、詳しくは発展編1で扱います
>     Range("A1").Value = 1
>     Range("A2").Value = 2
>     
>     Range("A4").Value = 11
>     Range("A5").Value = 12
>     
>     Range("A7").Value = 101
>     Range("A8").Value = 102
>     
>     Range("C1").Value = "=Sum(A1:A2)" '式の埋め込みは.valueでできる(.formulaでも可)
>     
>     Range("C4").Formula = Range("C1").Formula '式をそのままコピー
>     
>     Range("C7").Formula = Range("C1").FormulaR1C1 '式を相対参照でコピー
> End Sub

2020/12/11 16:41

ゲストさんのコメント

(コメントID: 6981)

田中宏明さん:

コメントありがとうございます。
私の知識不足のため、田中さんのアドバイスを理解できず申し訳ございません。
勉強を重ねた後、田中さんのアドバイスを実行させていただきたいと思います。
> ゲストさん:
>
> こんにちは。私はオンライン受講生です。
> >
> 転記元と転記先のファイル名の識別と作業実行が課題になっております。
>
> Excelシートにフォルダ名とファイル名を記載した転記元と転記先の対比表を作成し、マクロ実行時にそれを読み取って転記元と転記先を特定する形が良いと思いました。
> 的外れな意見でしたら申し訳ありませんが、私の経験ということでご参考まで。

2020/12/11 16:43

小川慶一さんのコメント

(コメントID: 6982)

ゲストさん:

この件は、所定のフォルダ内のファイルのリストをプログラム実行後に作成しようという趣旨ではないかと思います。
とりあえずやってみて、またひっかかったりしたらまた相談してください。

ひきつづきよろしくお願いいたします。

2020/12/13 19:24

ゲストさんのコメント

(コメントID: 6988) 添付ファイルのダウンロード権限がありません

小川慶一さん:

お久しぶりです。
今回は別シートから転記するプログラムではなく、replaceメソッドでセルの値を置換することを目指したプログラムを作成しました。値を置換するプログラムの方が難易度が低いと考えたためです。個々のプログラムの動作確認はできましたが、一連の処理をフォルダ内全てのファイル実行させることができませんでした。複数のプログラムを連続実行させることが今後の課題です。

①Dir関数については以下の通りにプログラムを記載し、イミディエイトウィンドウでファイルを参照していることが分かりました。

Sub Sample2()
    Dim buf As String
    buf = Dir("C:\Users\ユーザー名\Desktop\フォルダ名\*.xlsx")
       Do While Len(buf) > 0
        Debug.Print buf
        buf = Dir()
    Loop
End Sub

②置換作業をしたいファイルのセルを指定する

Sub cell_choice2()

Worksheets("物資内訳書・製造工程表（2020新様式）").Activate
Worksheets("物資内訳書・製造工程表（2020新様式）").Range("C18:G18").Select
'指定したシートのセルを選択する
macro6

End Sub

③replaceメソッドで指定したセルの値を任意の値に置換する

Sub macro6()
   Dim myRange As Range
   Dim keyWord1 As String, keyWord2 As String
    Dim bool As Boolean

    Set myRange = Range("C18:G18")
   keyWord1 = "東京都千代田区"
    keyWord2 = "東京都新宿区"
   bool = myRange.Replace(keyWord1, keyWord2, LookAt:=xlWhole)
End Sub

参考までにプログラムを連続実行したいフォルダの画像を添付致します。ご確認お願い致します。

> ゲストさん:
>
> この件は、所定のフォルダ内のファイルのリストをプログラム実行後に作成しようという趣旨ではないかと思います。
> とりあえずやってみて、またひっかかったりしたらまた相談してください。
>
> ひきつづきよろしくお願いいたします。
>

2020/12/22 21:41

小川慶一さんのコメント

(コメントID: 6991)

ゲストさん:

こんにちは。

楽しまれていますね。
期待通りに動作するプログラムを最終的に書けそうだということであれば何よりです。

> 今回は別シートから転記するプログラムではなく、replaceメソッドでセルの値を置換することを目指したプログラムを作成しました。
> 値を置換するプログラムの方が難易度が低いと考えたためです。

慣れると、どちらでもあまり変わらないですね。
僕個人の好みとしては、元の表をいじるプログラムを書くのはあまり好みではありません。元の表の検証可能性を失ってしまうので。

DPR的に考えると、Dを潰してRにしてしまうことになります。
当面欲しいのはRだったとしても、DはDで残しておいたほうが安全ですし、Dが参照可能であることでRの価値も高まります。

2020/12/22 23:12

５倍速！メールマガジン

このメールマガジンの詳細はこちら

受講生の声

たった1ヶ月で人生が劇的に変わりました

佐藤信さん

役員クラスの方から高い評価を受けるようになりました。

佐賀県 - 岩本徹さん

今までまったく知らなかったショートカットキーを使いまくっています。

東京都 - 鷹觜慶さん

年齢60才間近、営業で外回りの時間が多い私でも退社時間が1-2時間程度早くなりました。

東京都 - 宗内隆明さん

受講前もすでにパソコン得意でしたが、そんな私でも受講して本当に良かったと思えた講座です。

東京都 - 佐伯とも子さん

ここまで包括的に仕事に役立つ深い学びを受けれた講座は初めてでした

東京都 - 飯田倫子さん

受講生の声一覧へ

サイト運営者紹介

小川慶一講師/教材/システム開発者紹介

この学習サイトの教材制作、サポート、システム開発をすべてやっています。

より詳しい紹介はこちら

ショートカットワーク報告

2025年03月26日 22:33

CommaToXls-excel
初回でゴールド賞をとれた！

cat22さん

2025年03月26日 22:12

QuoteToXls-excel
ゴールド賞を達成したこと

cat22さん

2025年03月24日 22:28

QuoteToXls-excel
マイペースでも続いていること

cat22さん

2025年03月19日 20:10

TxtToXls-excel
初回でゴールド賞をとれた！

cat22さん

2025年03月18日 20:52

CommaSpaceToXls
プラチナ賞をとれたこと！

cat22さん

ショートカットワーク報告へ

ショートカットワークコメント

2025年04月16日 08:30

CommaToXls-excel
だいぶ間が空いてしまいました。すみません。実務での活用状況についても報告に含めていただければと …

小川慶一さん

2025年03月18日 09:07

CommaToXls
> エスケープ文字をつけ忘れてうまくいかなくてもあきらめなかったところ「自分ならできる」という…

小川慶一さん

2025年03月18日 09:06

QuoteToXls
> FnとWinキーを押し間違えてウィンドウ表示が変ってしまうところそのこと自体は問題ありませ…

小川慶一さん

2025年03月15日 13:23

TxtToXls
着実に上達されていると感じます (^^

小川慶一さん

2025年02月26日 16:37

TabTo-excel
> Ctrl＋Fn+Shift+End これは大変ですよ。僕も、4キー同時打鍵はしたくありませ…

小川慶一さん

ショートカットワーク一覧へ

コーディングワーク報告

2025年04月02日 21:01

複数の表の間でのマッチング
何も見なくてもスラスラ解けた

cat22さん

2025年04月02日 20:47

複数列の中から、最大値の格納されたセルの情報を出力する
昨日より早くかけたこと

cat22さん

2025年04月02日 20:37

最大値の格納されたセルの情報を出力する
昨日よりスピードがあがった

cat22さん

2025年04月01日 22:03

複数列の中から、最大値の格納されたセルの情報を出力する
動画の真似をしてかけたこと

cat22さん

2025年04月01日 21:27

最大値の格納されたセルの情報を出力する
（１）で類似問題を実施しているので、思ったより早く解けた

cat22さん

コーディングワーク報告へ

コーディングワークコメント

2025年04月16日 08:32

複数の表の間でのマッチング
実務での活用状況どうでしょうか。細かい操作ミスについては気にしないでください。速やかに無意識に修…

小川慶一さん

2025年03月18日 09:10

文字列を加工してデータ転記する(その4)
別のところでも書きましたが、イメージトレーニング大切です。次に出てきたときには瞬時に対応するぞ！…

小川慶一さん

2025年03月18日 09:10

文字列を加工してデータ転記する(その4)
こういうのは慣れです (^^

小川慶一さん

2025年03月18日 09:09

文字列を加工してデータ転記する(その4)
別海も悪いはないです。両方のパターンを自在に使い分けられるようになるとさらによいですね！

小川慶一さん

2025年03月18日 09:06

文字列を加工してデータ転記する(その5)
イメージトレーニングしましょう！

小川慶一さん

コーディングワークへ