Excel VBA 質問スレ Part75

**デフォルトの名無しさん** (ｽﾌｯ Sd7f-wadS) · 2022/02/09(水) 14:24:32.62

!extend:checked:vvvvv:1000:512
!extend:checked:vvvvv:1000:512
↑2行に減ってるけど、同じ内容を3行に増やして貼り付けるナリ

ExcelのVBAに関する質問スレナリ
コード書き込みや作成依頼もOKナリ

※前スレ
Excel VBA 質問スレ Part74
https://mevius.5ch.net/test/read.cgi/tech/1639932059/

VIPQ2_EXTDAT: checked:vvvvv:1000:512:: EXT was configured

2022/02/10(木) 10:32:50.12

>>1
乚

2022/02/10(木) 15:15:12.01

時々お世話になっています。

方針を確認したいです。
やりたい事は、特定のフォルダを指定して、その中にあるフォルダ・ファイルの中から最新の更新日を取得する
です。

今考えているやり方は、
①特定のフォルダを取得する（エクセル内にパスの指定あり）
②サブフォルダを含むすべてのフォルダパスを取得
③サブフォルダを含むすべてのファイルパスを取得
④取得したフォルダパスとファイルパスの最新更新日時を取得
⑤その中から一番最新の日時を取得
です。

他に簡単な手順ってあるでしょうか？
ヒントでもいただけるとうれしいです。
よろしくお願いします。

2022/02/10(木) 15:37:12.23

powershellならこれだけなんだが

ls -recurse|sort-object -property LastWriteTime -Descending|select-object -First 1|%{$_.LastWriteTime}

VBAからpowershell呼び出せば？

2022/02/10(木) 15:44:08.91

>>3
一般的だと思うけどフォルダ内の検索はそれ自身を一つの関数Aにしてその関数の中で関数Aを呼び出すと思う
関数は例えばこんな感じ　Sub ProcA(ByVal pFolder As String, ByRef pDate as Date)
中はこんな感じどっちが先でも構わない
pFolder内のフォルダ一覧を取得してフォルダ分ProcAを呼ぶ
pFolder内のファイル一覧を取得して最新日時をpDateに戻す

なので手順は
①特定のフォルダを取得する（エクセル内にパスの指定あり）
②ProcAを呼ぶ（これでサブフォルダ内も含めて全部検索してくれる）
③pDateが最新日時

2022/02/10(木) 15:50:58.22

>>4
これだとフォルダの日時も対象になってしまわない？

2022/02/10(木) 16:04:20.32

>>4
ありがとうございます。

powershellだとそんなに短くなるんですね・・・

ただ私を含め、powershellに詳しい人が周りにいないのと、
私が死んでもメンテナンスできる状態にしておきたいので、今回はVBAで作成をしたいです。
（でもそのコードは保存させていただきます。）

勉強になります。ありがとうございます。

2022/02/10(木) 16:09:01.41

>>5
ありがとうございます。
処理時間が短くなって良さそうですね！
こちらの方向で考えてみます！

ありがとうございます！

2022/02/10(木) 16:10:41.24

>>6
今回はフォルダの更新日時も取得したかったので、
powershellが使えればこのコードが有効だったと思います。

私の勉強不足で残念ですが・・・

2022/02/10(木) 16:25:27.01

Ruby のglob では、** で、フォルダ以下を再帰的に処理する

# 絶対パスのディレクトリ名の後ろに、* を付けること！
# . で始まる、隠し directory, file を除く

glob_pattern = "C:/Users/Owner/Documents/test/**/*"

puts Dir.glob( glob_pattern )
.select { |full_path| File.file?( full_path ) } # ファイルのみ
.max_by { |full_path| File.mtime( full_path ) } # 更新時刻が最も最近のファイルパス

2022/02/10(木) 16:53:52.57

前スレで「VBA内で今まで使えてたKillが急に使えなくなった」野郎ですが、システムの復元で先月に戻したら直った。
たぶんOfficeの最新のアップデートがクソ。

VBA内で作って使い終わったADODBを削除する部分。
10年くらい使い続けてるし、connectionとかちゃんとcloseしてるのに。

2022/02/10(木) 18:14:16.34

>>7
そもそもＶＢＡで作る意味がわからんが
自分で作れもしないものを俺が死んでもメンテしろとか...

なにをやるかだけをドキュメントなりコメントなりで残せよ
あとの人はそれ見て自分でできるやり方でやればいいだけ

2022/02/10(木) 18:19:09.16

>>10
Rubyでもそんなに短くなるんですね。
もしかしてVBAが一番大変なんじゃないかと思えてきます。

すみません。Rubyは私が全く使えないため、今回は見送らせていただきます。
コードまで記載していただいたのに申し訳ないです。

ありがとうございました。

2022/02/10(木) 18:22:09.15

ふと思ったが、ファイル列挙を再帰で処理するときになにも考えずに
うっかり上位フォルダへのショートカットとか処理したらループするのな

素のVBAでやるならDir関数だろうけど、これショートカットとかどう処理するんだろ

2022/02/10(木) 18:23:45.71

>>12
VBA（エクセル）であれば多少使える人が周りにいるため、メンテナンスが可能だと思いました。
自動化した処理はコード内のコメントでも残しますし、ドキュメントにも記載します。

>>自分で作れもしないものを俺が死んでもメンテしろとか...
　これは確かに・・・自分で作れていない以上ぐうの音も出ません。

2022/02/10(木) 18:27:43.99

こんな簡単な処理もできないのにVBAならできると言うか

2022/02/10(木) 18:56:39.50

>>14
VBA内のコードだけならそうだろうけど今風だとFileSystemObject使うかもね
自分はフォルダ検索とか嫌って程使うツールを作ってるから検索用のライブラリずっと使いまわしてるわ

>>15
VBAなんて所詮俺ツールぐらいの気持ちで考えて自分が死んだら使わないでねぐらいでいいと思うけどね

2022/02/10(木) 19:15:17.51

>>3
Power Query で親のフォルダ指定すると、サブフォルダも含めた
ファイル名、拡張子、アクセス日時、更新日時、作成日時、パス
の一覧を作ってくれる。

作成日時か更新日時で降順して1から始まるインデックス付けて、
BinaryとかRecordの余計な列は削除して、クエリーテーブルとしてsheetに読込ませたら、
あとは外のセルからINDEX・MATCH関数で
「インデックスの列が1」の行のパス&"\"&ファイル名を表示させる。

VBAはこのbookを開いたときにクエリー更新させるとか、
ボタンでクエリー更新させるとか。

Power Query内にセルに指定したフォルダパスを取り込むときは
そのセルを名前定義しておいて、

let
　定義名 = Excel.CurrentWorkbook(){[Name="定義名"]}[Content]{0}[Column1],
　ソース = Folder.Files(定義名)
in
　ソース

これが元ネタ
https://stackoverflow.com/questions/49281763/powerquery-use-a-cell-values-in-a-url

2022/02/10(木) 19:24:51.89

>>15
たとえばエクスプローラー開いて
フォルダ指定してから右上の枠に更新日時:>2022/1/1
って入力すると2022/1/1 以降のファイル一覧とか見れるよ

10 (ﾜｯﾁｮｲ ff2c-2zGl) · 2022/02/11(金) 13:48:46.29

処理が複雑になるにつれて、より本格的なプログラミング言語に変えていかないといけない。
これを適材適所・目的別最適化と言う

小中高大学みたいなもの。
VBAが小学生の宿題なら、Rubyは大学生の宿題

VBA > PowerShell, Bash > Ruby

これが分からない会社は、すべてのシステムをVBAで作って、
色々なモジュールを共通化して、
会社の資産にしてみたいに発展させていって、最後には破綻する

維持・保守コストが膨大になって、おまけに低品質で、誰も修正できなくなる

システムみたいなものは修正・保守必須で本格的なものだから、
Rubyみたいなプロ用の言語じゃないとダメ

ちょっとした秀丸マクロとか、そういうのはダメ。
適材適所・目的別最適化みたいな事を分かっていない会社が多すぎる

2022/02/11(金) 13:55:56.83

不等号が逆な時点で説得力ゼロ
矢印のつもりなら、さらに頭が悪いとしか思わん

2022/02/11(金) 13:58:55.07

www

2022/02/11(金) 14:17:29.48

だってルビキチだもん

2022/02/11(金) 14:33:23.73

無意味な喩えする奴頭悪いから嫌いなんだよね

2022/02/11(金) 14:47:23.80

>>21
ほんとに逆なのお？

2022/02/11(金) 14:48:15.45

そもそも自社開発するようなスキルを持ち合わせてるならそんな三択じゃない他のを選ぶだろうにｗ
RubyRuby言ってる人はそれしかできないのかね？ｗ

2022/02/11(金) 15:17:29.69

VBAもRubyもPwerShellもVSCodeも、片手間のちょっとしたツールを作るには便利だけど、がっつりアプリの開発には使えんわ
みんな同レベル
あとは適切に使い分けできるかという、使用者の頭脳レベルの問題

2022/02/11(金) 15:23:45.94

なんでVSCode出てきた

2022/02/11(金) 15:53:04.17

まだRubyキチガイ居るのか…
何年他の言語スレに粘着してるんだ

2022/02/11(金) 16:07:37.46

お前らみたいに相手する奴居るからだろ…

2022/02/11(金) 16:11:41.66

>>28
知ってるの並べただけでしょ

2022/02/11(金) 16:37:33.68

>>20
突っ走り過ぎ
何もできないとVBA出来るは違う
ないとあるはかなり違うんだよ
シロウトが自由にPCを動かせるようになるVBAには価値があると思うよ
VBAで不足と感じだしたら他言語化を模索するようになるんじゃないかな？
自分がそうだし

2022/02/11(金) 19:56:50.35

オワコンRuby

2022/02/11(金) 23:02:03.22

いつ始まってたの

2022/02/12(土) 10:46:52.90

C#、Python、Javascript、Rust辺りはともかく
Rubyはないわー

2022/02/12(土) 12:29:03.80

Perlと同じく消えるRuby

2022/02/12(土) 18:19:04.47

>>34
10-15年ぐらい前は結構盛り上がってた気がする
PHPの代わりにサーバーはruby、pythonの代わりにrubyで計算、ゲームは・・・苦手だった気がする
色々できて便利な言語という触れ込みだった

で、総意は「ロクなライブラリもないし、器用貧乏な言語に変える必要がない」だった

2022/02/12(土) 18:19:52.19

しかし最後にこんなキチガイに言語を潰されるのは、さすがにちょっと不憫かなーとは思う

2022/02/12(土) 18:47:01.50

使いたければ黙って使っていればいいのにここに書くことじゃない
よそでやれ

2022/02/12(土) 23:09:11.76

すみません、スレみて質問していいのかと思いましたが、相談に乗ってください。

予定の10日前になったらセルの色変え、
(予定日前まで色変えで)予定日、予定日以降は色無しとしたいのですが
どうすればいいでしょうか？

よろしくお願いします

2022/02/12(土) 23:11:31.31

条件付き書式が一番いい気がする

2022/02/12(土) 23:18:33.99

>>40
条件付き書式でいいんじゃね
VBAいらんだろ。どっかエクセルの使い方のスレで聞け

2022/02/13(日) 00:20:49.39

>>20
言語の問題より可読性が問題だな。ワラワラ

2022/02/13(日) 00:34:59.07

>>40
スレチだけど上の人が言ってるように条件式書式ならこんな感じのを使えばいいかと
=AND(TODAY()-10<=RC,RC<TODAY())
これは日付の入っているセルに対して質問にあるような条件で色付けたりできるよ
※R1C1形式になってるので注意

2022/02/13(日) 07:35:55.02

>>44
Excelの総合で質問もして教えて頂けました。
解説ありがとうございます。
勉強になりました。

2022/02/14(月) 12:57:56.87

この画像みたいなExcelファイル(1枚目)をデータベース化する為に、情報を抽出して横並びに整理したい(2枚目)のですが、アドバイスいただきたいです。
行数や列数がそれぞれ異なり、罫線の枠内でも、変に結合や分割されており、関数のみでは対応しきれませんでした。
※元々がPDFでしか入力できないデータの為、それをExcel変換しているせいで構成が崩れています。

罫線の枠構成は崩れていない為、マクロで処理するとしたら、各セルの罫線を判定して、囲われた範囲の値を順番に取得していくしかないのかな？と思っています。
枠内の左上隅(左と上に罫線があるセル)から、右下隅(右と下に罫線があるセル)までの値を取得して連結、次の枠へ進む、という処理しか思いつかないのですが、他にいい案はあるでしょうか？
https://i.imgur.com/oMaEhvs.png
https://i.imgur.com/gnZIoe7.png

2022/02/14(月) 13:06:54.20

>>46
Power Queryでも、このセル結合だらけ(結合されてたりされてなかったり・・・)の
データクレンジングは厳しいかも知れない。
ステップが100超えるかも・・・

2022/02/14(月) 13:10:47.83

一度全ての結合解除するとかは？
同じ項目に相当する入力値の行き先が泣き別れたら終わるけども

2022/02/14(月) 13:31:47.44

>>46
ID以外に空欄がないのなら、データの区切り、たとえばB列の水平の罫線の位置だけ検出すればいい
あとはその範囲を左上から舐めて、空白セルを無視しながらデータを取り出すだけ
結合は全部解除した方がロジックは簡単になると思う
そんなに難しくない

テスト用のデータを用意するのが面倒だから、そのサンプルをエクセル形式かスプレッドシートにでも入れて公開してよ

2022/02/14(月) 13:53:01.79

>>46
すげぇな！ここまでのは見たことないぞ
行数が同じなら、
C6:C11を文字列で結合すれば良いかと思ったけど
行数も違うのか

セル内改行したり、セルをわけたりで行数がバラバラなのね

やるなら、
C6　11111-11
c7　11111-12
C8　11111-21
C9　11111-22
・・・
みたいにすれば、まぁなんとか行けるかなと思う
今のままじゃ無理だ

2022/02/14(月) 13:58:47.62

と思ったら罫線を基準にすればいいのか

2022/02/14(月) 14:07:53.31

CSVで吐き出して
それを加工したほうが早くね？

2022/02/14(月) 14:16:38.09

csvにしたら大惨事だろう

2022/02/14(月) 14:28:40.98

いくつも返信ありがとうございます。
サンプルは後程公開しようと思います。

セル結合解除したらこうなります。
罫線範囲の左上に値があればまだマシなのですが、途中だったり分割されてたりがネックです。また、空白のセルも多々存在します。

VBA初心者の為、合理的な処理かは分かりませんが、今はこの矢印の順でセルを移動しながら、罫線の判定と値の取得する流れで考えています。
https://i.imgur.com/raK9Xyw.png

2022/02/14(月) 14:44:13.39

https://atmarkit.itmedia.co.jp/ait/articles/2002/27/news011.html
　↑　ここでは一旦Word経由で取り込めと勧めているが、果たして

にしても、 > ※元々がPDFでしか入力できないデータ　　とは？
PDFの原稿があって、その枠の中にテキスト貼り付ける方式なのか？

2022/02/14(月) 14:53:57.21

>>50
この画像の範囲にはセル内改行はないぞ
よく見ると背景と同じ色の白い罫線が隠れてる

2022/02/14(月) 14:55:08.92

>>55
最新のPDFには入力フォームを重ねる機能があるんよ

2022/02/14(月) 14:59:16.98

>>54
基本それでいいと思うよ
いちいち判定せずとも空白セルも含めてどんどん文字列を足してくだけで目的のデータが自動的に残る

2022/02/14(月) 15:02:51.71

>>56
J21セル

2022/02/14(月) 15:14:55.89

>>51
罫線を検知するVBAは、このあたり使うのかしら？

例えば「実線の下線縛り」だと

If Range("A5").Borders(xlEdgeBottom).LineStyle = xlContinuous Then
　MsgBox "下線あり"
　Else
MsgBox "下線なし"
　End If

セルの上下左右も調べることになる？

http://chaichan.lolipop.jp/vbtips/VBMemo2009-01-072.htm

2022/02/14(月) 15:21:11.73

>>59
見落としてたごめん

2022/02/14(月) 15:47:34.87

PDFからExel変換がまともに出来てないから元のPDFからテキストデータ抜き出して処理した方が早い気がするなぁ

2022/02/14(月) 15:52:40.41

>>60
下だけでいい
ってかはよサンプルくれ。サンプルなしでコード書くのあんまり好きやないんや
とりあえず下線の判定部分だけ置いとくぞ

Function 下線あり(target)
If target.Borders(xlEdgeBottom).LineStyle <> xlLineStyleNone Then
下線あり = True
Else
下線あり = False
End If
End Function

2022/02/14(月) 16:25:13.19

>>63
>>>>60
>>下だけでいい

2個分入っている「例参考3・4」と
1個分だけの「例参考5」を
どう見分けるか、嫌らしいなぁ・・・
と思って。

2022/02/14(月) 16:50:56.63

>>55
word経由だったり、何種類かの変換ソフトを試したけど出来はほぼ変わりませんでした。

PDFの経緯ですが、他社のWEB上でのサービスにて個人情報等を登録管理しており、選択して出力する際にはPDF形式でしか出力できず、今回の悩みになっています。

2022/02/14(月) 16:52:00.24

サンプルです。
よろしくお願いします。
https://docs.google.com/spreadsheets/d/1KXHOGpa6GFBfFflosWycM_IsNS5P9gnk9m2eGG0uXLs/edit?usp=drivesdk

共有の仕方合ってるか不安ですが。。

2022/02/14(月) 17:08:51.74

>>65
そのサービスを提供している会社に相談しろよ
馬鹿なの？

2022/02/14(月) 17:56:38.45

画像見た感じだとそうめんどくさい話ではないかね
住所欄と連絡先欄の住所と電話番号をどういうルールで分解するかがわかれば変換は余裕かと
ただこの手の内容までこのスレで答えるのはいいように使われている感があっていやだなぁｗ

2022/02/14(月) 18:16:24.84

住所の下に下線ない事に気づき、無事死亡

2022/02/14(月) 18:16:43.53

>>66
DL出来たよ。
Power Queryでどこまで出来るか
やってみる。

2022/02/14(月) 18:19:08.21

更にこれ氏名でセルわかれてるんかーい

2022/02/14(月) 18:55:37.73

>>55
混乱させてすみません、PDFでしか「入手」できないデータの誤りでした。
サービス元からは、それ以外の形式での出力はできないとの回答の為、このExcelファイルをなんとか処理して使わざるを得ない状態です。

2022/02/14(月) 18:58:42.50

指定した日付から、その日付が１年間の第何週かを求めそれを引数にして、その週の月曜日と金曜日の日付を返す式を教えてください。

i = WeekNum(指定日）
date1 = 開始日（月曜日）
date2 = 終了日（金曜日)

2022/02/14(月) 19:33:13.83

>>73
・週の初めは何曜日基準？日曜日？月曜日？別の曜日？
・それを引数のそれって何？開始日（月曜日）の関数にiは使ってないのでは？指定日ではだめなの？
・仮に日曜日基準とした場合、2022年1月1日(土)の月曜日は2021年12月26日？それとも別の日？

2022/02/14(月) 19:34:32.34

>>73
そもそもVBAの回答が知りたいの？それとも関数使ってなの？関数ならスレチですよ

2022/02/14(月) 19:35:02.10

>>46
2時間半、思ったより早くできたけど、まぁキツイな
変数名も終わってる

https://jsfiddle.net/5vuatdrq/1/

2022/02/14(月) 19:36:56.50

別に関数でもいいんじゃないの？
俺もコードの中にworksheetfunction使ってるし、気にしたらキリないわ

2022/02/14(月) 19:41:40.69

>>76に追記

「シート1」というシートに元データ
「転記先」というシートを作っておいて下さい

2022/02/14(月) 19:45:27.38

>>66
ひとまず
元ファイルのセル結合を解除しないまま、
別ファイルからPower Queryでsheetをそのまま読込んで、
不要な行/列、空白、改行コードを削除したり
列を合体したりして、ある程度整理してみた。

https://i.imgur.com/8wCdbbJ.png
https://i.imgur.com/LvCak5M.png

やっぱ、どの行までが1個体のデータなのか判別がつかないので、
ここから先は難しい。
それが特定できればsheet上のテーブルの右側に列足して
M言語でなく、使い慣れたExcelのIF関数や文字列関数使って
拾っていくことも出来そうなんだけど・・・

先にVBAで罫線判別して各行にフラグでも立てることが必要っぽい。

>>63 氏に期待

2022/02/14(月) 19:46:56.80

あ、
>>63 氏、出来たのね。

2022/02/14(月) 19:47:44.47

pdfからの変換で毎回セル位置が変わるのか？
変わらないなら一回参照シート作るだけじゃね？

2022/02/14(月) 19:53:08.06

>>73
関数の方が早い
https://i.imgur.com/gJTY8OT.png

vbaに落とし込むなら、worksheetfunctionを使ってくれ

2022/02/14(月) 19:54:22.25

>>73
DatePart("ww", Date, vbMonday, vbFirstFullWeek)

あとはその日が何曜日が調べて
プラスマイナスすれば月金も出るよ

2022/02/15(火) 02:26:45.53

Excel板の住人が発狂して荒らしてくるから関数はダメかも

2022/02/15(火) 03:38:15.06

Fomuraでvbaでセルに
関数放り込んでやれば

2022/02/15(火) 08:44:43.33

>>79
powerqueryである程度処理は可能なんですね。ありがとうございます。
実際は1000件以上のデータで項目も更に多く、行も列もバラバラなので手作業はやはり限界がありそうです。

2022/02/15(火) 08:49:44.20

>>76
本当にありがとうございます。
サンプルの方では問題なく処理されるのを確認できました。
自身が書いたらもっと長々しくぐちゃぐちゃになっていたと思うので、すっきりまとまっていて素晴らしいと思います。

For Each 個人列 In Array(3, 4, 9, 10, 11, 15)
ここは列は固定の指定になっていたのですが、ファイルによっては列もバラバラなので、最初に文字列検索で列を調べてから処理に入るように変えていきたいと思います。

皆さん本当にありがとうございました。

2022/02/15(火) 13:36:45.93

レセコンのデータだろ

2022/02/15(火) 16:04:21.44

A～K列にデータを入力しているシートで、C列にデータがある場合のみその行をコピーする、という動作をさせるには
どう記述すればいいでしょうか？

2022/02/15(火) 17:39:01.90

if文でC列に入力があるかを判定すれば良い
コピーする行はC列に入力がある全ての行か？それとも一番最初のものだけか？またはカーソルがある位置だけを対象にするのか？
もう少し細かく仕様を書いてくれた方が導きやすいぞ

2022/02/15(火) 17:39:49.61

>>89
If Range("C1").Text <> "" Then Range("A1:K1").Copy