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

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

エクセルマクロ・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