X

Excel VBA 質問スレ Part75

■ このスレッドは過去ログ倉庫に格納されています
0001デフォルトの名無しさん (ｽﾌｯ Sd7f-wadS)
垢版 |
2022/02/09(水) 14:24:32.62ID:I0u44nFvd
!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
0003デフォルトの名無しさん (ﾜｯﾁｮｲ bfe6-AiWJ)
垢版 |
2022/02/10(木) 15:15:12.01ID:nfSYASk10
時々お世話になっています。

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

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

他に簡単な手順ってあるでしょうか？
ヒントでもいただけるとうれしいです。
よろしくお願いします。
0005デフォルトの名無しさん (ｽｯﾌﾟ Sdbf-zD41)
垢版 |
2022/02/10(木) 15:44:08.91ID:M1zV1i+Cd
>>3
一般的だと思うけどフォルダ内の検索はそれ自身を一つの関数Aにしてその関数の中で関数Aを呼び出すと思う
関数は例えばこんな感じ　Sub ProcA(ByVal pFolder As String, ByRef pDate as Date)
中はこんな感じどっちが先でも構わない
pFolder内のフォルダ一覧を取得してフォルダ分ProcAを呼ぶ
pFolder内のファイル一覧を取得して最新日時をpDateに戻す

なので手順は
①特定のフォルダを取得する（エクセル内にパスの指定あり）
②ProcAを呼ぶ（これでサブフォルダ内も含めて全部検索してくれる）
③pDateが最新日時
0007デフォルトの名無しさん (ﾜｯﾁｮｲ bfe6-AiWJ)
垢版 |
2022/02/10(木) 16:04:20.32ID:nfSYASk10
>>4
ありがとうございます。

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

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

勉強になります。ありがとうございます。
0008デフォルトの名無しさん (ﾜｯﾁｮｲ bfe6-AiWJ)
垢版 |
2022/02/10(木) 16:09:01.41ID:nfSYASk10
>>5
ありがとうございます。
処理時間が短くなって良さそうですね！
こちらの方向で考えてみます！

ありがとうございます！
0009デフォルトの名無しさん (ﾜｯﾁｮｲ bfe6-AiWJ)
垢版 |
2022/02/10(木) 16:10:41.24ID:nfSYASk10
>>6
今回はフォルダの更新日時も取得したかったので、
powershellが使えればこのコードが有効だったと思います。

私の勉強不足で残念ですが・・・
0010デフォルトの名無しさん (ﾜｯﾁｮｲ ff2c-2zGl)
垢版 |
2022/02/10(木) 16:25:27.01ID:J5PrGMnU0
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 ) } # 更新時刻が最も最近のファイルパス
0011デフォルトの名無しさん (ﾜｯﾁｮｲ ff63-foD4)
垢版 |
2022/02/10(木) 16:53:52.57ID:jnAzFgRy0
前スレで「VBA内で今まで使えてたKillが急に使えなくなった」野郎ですが、システムの復元で先月に戻したら直った。
たぶんOfficeの最新のアップデートがクソ。

VBA内で作って使い終わったADODBを削除する部分。
10年くらい使い続けてるし、connectionとかちゃんとcloseしてるのに。
0012デフォルトの名無しさん (ﾜｯﾁｮｲ 372f-2zGl)
垢版 |
2022/02/10(木) 18:14:16.34ID:lf0ILJru0
>>7
そもそもＶＢＡで作る意味がわからんが
自分で作れもしないものを俺が死んでもメンテしろとか...


なにをやるかだけをドキュメントなりコメントなりで残せよ
あとの人はそれ見て自分でできるやり方でやればいいだけ
0013デフォルトの名無しさん (ﾜｯﾁｮｲ bfe6-AiWJ)
垢版 |
2022/02/10(木) 18:19:09.16ID:nfSYASk10
>>10
Rubyでもそんなに短くなるんですね。
もしかしてVBAが一番大変なんじゃないかと思えてきます。

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

ありがとうございました。
0014デフォルトの名無しさん (ﾜｯﾁｮｲ 372f-2zGl)
垢版 |
2022/02/10(木) 18:22:09.15ID:lf0ILJru0
ふと思ったが、ファイル列挙を再帰で処理するときになにも考えずに
うっかり上位フォルダへのショートカットとか処理したらループするのな

素のVBAでやるならDir関数だろうけど、これショートカットとかどう処理するんだろ
0015デフォルトの名無しさん (ﾜｯﾁｮｲ bfe6-AiWJ)
垢版 |
2022/02/10(木) 18:23:45.71ID:nfSYASk10
>>12
VBA（エクセル）であれば多少使える人が周りにいるため、メンテナンスが可能だと思いました。
自動化した処理はコード内のコメントでも残しますし、ドキュメントにも記載します。

>>自分で作れもしないものを俺が死んでもメンテしろとか...
　これは確かに・・・自分で作れていない以上ぐうの音も出ません。
0017デフォルトの名無しさん (ｽｯﾌﾟ Sdbf-zD41)
垢版 |
2022/02/10(木) 18:56:39.50ID:M1zV1i+Cd
>>14
VBA内のコードだけならそうだろうけど今風だとFileSystemObject使うかもね
自分はフォルダ検索とか嫌って程使うツールを作ってるから検索用のライブラリずっと使いまわしてるわ

>>15
VBAなんて所詮俺ツールぐらいの気持ちで考えて自分が死んだら使わないでねぐらいでいいと思うけどね
0018デフォルトの名無しさん (ﾜｯﾁｮｲ ffda-wadS)
垢版 |
2022/02/10(木) 19:15:17.51ID:tWYuVSEa0
>>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
002010 (ﾜｯﾁｮｲ ff2c-2zGl)
垢版 |
2022/02/11(金) 13:48:46.29ID:901NTZe+0
処理が複雑になるにつれて、より本格的なプログラミング言語に変えていかないといけない。
これを適材適所・目的別最適化と言う

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

VBA > PowerShell, Bash > Ruby

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

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

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

ちょっとした秀丸マクロとか、そういうのはダメ。
適材適所・目的別最適化みたいな事を分かっていない会社が多すぎる
0023デフォルトの名無しさん (ﾜｯﾁｮｲ bfda-HF6V)
垢版 |
2022/02/11(金) 14:17:29.48ID:LtQN2K+P0
だってルビキチだもん
0027デフォルトの名無しさん (ﾌﾞｰｲﾓ MM1b-wadS)
垢版 |
2022/02/11(金) 15:17:29.69ID:dkQ+i8pZM
VBAもRubyもPwerShellもVSCodeも、片手間のちょっとしたツールを作るには便利だけど、がっつりアプリの開発には使えんわ
みんな同レベル
あとは適切に使い分けできるかという、使用者の頭脳レベルの問題
0028デフォルトの名無しさん (ｽｯｯﾌﾟ Sdbf-HF6V)
垢版 |
2022/02/11(金) 15:23:45.94ID:+e4i5uLyd
なんでVSCode出てきた
0032デフォルトの名無しさん (ﾜｯﾁｮｲ 9f7c-V4UE)
垢版 |
2022/02/11(金) 16:37:33.68ID:EYT5V/+O0
>>20
突っ走り過ぎ
何もできないとVBA出来るは違う
ないとあるはかなり違うんだよ
シロウトが自由にPCを動かせるようになるVBAには価値があると思うよ
VBAで不足と感じだしたら他言語化を模索するようになるんじゃないかな？
自分がそうだし
0033デフォルトの名無しさん (ﾜｯﾁｮｲ d78e-hIaH)
垢版 |
2022/02/11(金) 19:56:50.35ID:/HLRVZix0
オワコンRuby
0036デフォルトの名無しさん (ﾜｯﾁｮｲ 1f8e-ebC9)
垢版 |
2022/02/12(土) 12:29:03.80ID:zSrTwFpP0
Perlと同じく消えるRuby
0037デフォルトの名無しさん (ﾜｯﾁｮｲ 6368-B80Q)
垢版 |
2022/02/12(土) 18:19:04.47ID:rN9arOXg0
>>34
10-15年ぐらい前は結構盛り上がってた気がする
PHPの代わりにサーバーはruby、pythonの代わりにrubyで計算、ゲームは・・・苦手だった気がする
色々できて便利な言語という触れ込みだった

で、総意は「ロクなライブラリもないし、器用貧乏な言語に変える必要がない」だった
0040デフォルトの名無しさん (ﾜｯﾁｮｲ afd0-1uE6)
垢版 |
2022/02/12(土) 23:09:11.76ID:8DXM8GTy0
すみません、スレみて質問していいのかと思いましたが、相談に乗ってください。

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

よろしくお願いします
0044デフォルトの名無しさん (ｽｯﾌﾟ Sd32-rCQD)
垢版 |
2022/02/13(日) 00:34:59.07ID:TIGHqVMSd
>>40
スレチだけど上の人が言ってるように条件式書式ならこんな感じのを使えばいいかと
=AND(TODAY()-10<=RC,RC<TODAY())
これは日付の入っているセルに対して質問にあるような条件で色付けたりできるよ
※R1C1形式になってるので注意
0046デフォルトの名無しさん (ｽｯﾌﾟ Sd52-Qiwb)
垢版 |
2022/02/14(月) 12:57:56.87ID:wZ5OVHUFd
この画像みたいなExcelファイル(1枚目)をデータベース化する為に、情報を抽出して横並びに整理したい(2枚目)のですが、アドバイスいただきたいです。
行数や列数がそれぞれ異なり、罫線の枠内でも、変に結合や分割されており、関数のみでは対応しきれませんでした。
※元々がPDFでしか入力できないデータの為、それをExcel変換しているせいで構成が崩れています。

罫線の枠構成は崩れていない為、マクロで処理するとしたら、各セルの罫線を判定して、囲われた範囲の値を順番に取得していくしかないのかな？と思っています。
枠内の左上隅(左と上に罫線があるセル)から、右下隅(右と下に罫線があるセル)までの値を取得して連結、次の枠へ進む、という処理しか思いつかないのですが、他にいい案はあるでしょうか？
https://i.imgur.com/oMaEhvs.png
https://i.imgur.com/gnZIoe7.png
0048デフォルトの名無しさん (ﾜｯﾁｮｲ 5eda-K+wn)
垢版 |
2022/02/14(月) 13:10:47.83ID:ecmIkoYm0
一度全ての結合解除するとかは？
同じ項目に相当する入力値の行き先が泣き別れたら終わるけども
0049デフォルトの名無しさん (ﾜｯﾁｮｲ ebce-B80Q)
垢版 |
2022/02/14(月) 13:31:47.44ID:33Tyeh9C0
>>46
ID以外に空欄がないのなら、データの区切り、たとえばB列の水平の罫線の位置だけ検出すればいい
あとはその範囲を左上から舐めて、空白セルを無視しながらデータを取り出すだけ
結合は全部解除した方がロジックは簡単になると思う
そんなに難しくない

テスト用のデータを用意するのが面倒だから、そのサンプルをエクセル形式かスプレッドシートにでも入れて公開してよ
0050デフォルトの名無しさん (ﾜｯﾁｮｲ 6368-B80Q)
垢版 |
2022/02/14(月) 13:53:01.79ID:XTh2f6qa0
>>46
すげぇな！ここまでのは見たことないぞ
行数が同じなら、
C6:C11を文字列で結合すれば良いかと思ったけど
行数も違うのか

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

やるなら、
C6　11111-11
c7　11111-12
C8　11111-21
C9　11111-22
・・・
みたいにすれば、まぁなんとか行けるかなと思う
今のままじゃ無理だ
0054デフォルトの名無しさん (ｽｯﾌﾟ Sd52-Qiwb)
垢版 |
2022/02/14(月) 14:28:40.98ID:wZ5OVHUFd
いくつも返信ありがとうございます。
サンプルは後程公開しようと思います。

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

VBA初心者の為、合理的な処理かは分かりませんが、今はこの矢印の順でセルを移動しながら、罫線の判定と値の取得する流れで考えています。
https://i.imgur.com/raK9Xyw.png
0063デフォルトの名無しさん (ﾜｯﾁｮｲ 6368-B80Q)
垢版 |
2022/02/14(月) 15:52:40.41ID:XTh2f6qa0
>>60
下だけでいい
ってかはよサンプルくれ。サンプルなしでコード書くのあんまり好きやないんや
とりあえず下線の判定部分だけ置いとくぞ

Function 下線あり(target)
If target.Borders(xlEdgeBottom).LineStyle <> xlLineStyleNone Then
下線あり = True
Else
下線あり = False
End If
End Function
0065デフォルトの名無しさん (ｽｯﾌﾟ Sd52-Qiwb)
垢版 |
2022/02/14(月) 16:50:56.63ID:wZ5OVHUFd
>>55
word経由だったり、何種類かの変換ソフトを試したけど出来はほぼ変わりませんでした。

PDFの経緯ですが、他社のWEB上でのサービスにて個人情報等を登録管理しており、選択して出力する際にはPDF形式でしか出力できず、今回の悩みになっています。
0067デフォルトの名無しさん (ﾜｯﾁｮｲ 1f8e-nldK)
垢版 |
2022/02/14(月) 17:08:51.74ID:xN/gSLrN0
>>65
そのサービスを提供している会社に相談しろよ
馬鹿なの？
0068デフォルトの名無しさん (ｽｯﾌﾟ Sd32-rCQD)
垢版 |
2022/02/14(月) 17:56:38.45ID:qxU338Vtd
画像見た感じだとそうめんどくさい話ではないかね
住所欄と連絡先欄の住所と電話番号をどういうルールで分解するかがわかれば変換は余裕かと
ただこの手の内容までこのスレで答えるのはいいように使われている感があっていやだなぁｗ
0072デフォルトの名無しさん (ｽｯﾌﾟ Sd52-Qiwb)
垢版 |
2022/02/14(月) 18:55:37.73ID:wZ5OVHUFd
>>55
混乱させてすみません、PDFでしか「入手」できないデータの誤りでした。
サービス元からは、それ以外の形式での出力はできないとの回答の為、このExcelファイルをなんとか処理して使わざるを得ない状態です。
0073デフォルトの名無しさん (ﾜｯﾁｮｲ cbcc-piVT)
垢版 |
2022/02/14(月) 18:58:42.50ID:/RDvxF8W0
指定した日付から、その日付が１年間の第何週かを求めそれを引数にして、その週の月曜日と金曜日の日付を返す式を教えてください。

i = WeekNum(指定日）
date1 = 開始日（月曜日）
date2 = 終了日（金曜日)
0074デフォルトの名無しさん (ｽｯﾌﾟ Sd32-rCQD)
垢版 |
2022/02/14(月) 19:33:13.83ID:qxU338Vtd
>>73
・週の初めは何曜日基準？日曜日？月曜日？別の曜日？
・それを引数のそれって何？開始日（月曜日）の関数にiは使ってないのでは？指定日ではだめなの？
・仮に日曜日基準とした場合、2022年1月1日(土)の月曜日は2021年12月26日？それとも別の日？
0079デフォルトの名無しさん (ﾜｯﾁｮｲ 16da-B80Q)
垢版 |
2022/02/14(月) 19:45:27.38ID:O4GcyUrK0
>>66
ひとまず
元ファイルのセル結合を解除しないまま、
別ファイルからPower Queryでsheetをそのまま読込んで、
不要な行/列、空白、改行コードを削除したり
列を合体したりして、ある程度整理してみた。

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

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

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

>>63 氏に期待
0086デフォルトの名無しさん (ｽｯﾌﾟ Sd52-Qiwb)
垢版 |
2022/02/15(火) 08:44:43.33ID:H1nNFxJWd
>>79
powerqueryである程度処理は可能なんですね。ありがとうございます。
実際は1000件以上のデータで項目も更に多く、行も列もバラバラなので手作業はやはり限界がありそうです。
0087デフォルトの名無しさん (ｽｯﾌﾟ Sd52-Qiwb)
垢版 |
2022/02/15(火) 08:49:44.20ID:H1nNFxJWd
>>76
本当にありがとうございます。
サンプルの方では問題なく処理されるのを確認できました。
自身が書いたらもっと長々しくぐちゃぐちゃになっていたと思うので、すっきりまとまっていて素晴らしいと思います。

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

皆さん本当にありがとうございました。
0090デフォルトの名無しさん (ﾌﾞｰｲﾓ MM32-j2zp)
垢版 |
2022/02/15(火) 17:39:01.90ID:6Pf72uqdM
if文でC列に入力があるかを判定すれば良い
コピーする行はC列に入力がある全ての行か？それとも一番最初のものだけか？またはカーソルがある位置だけを対象にするのか？
もう少し細かく仕様を書いてくれた方が導きやすいぞ
0092デフォルトの名無しさん (ﾜｯﾁｮｲ 6368-B80Q)
垢版 |
2022/02/15(火) 18:55:31.55ID:MJaSxl0D0
>>89
オートフィルタで空白以外にして、コピーペースト

Sub Macro1()
最終行 = Cells(Rows.Count, 1).End(xlUp).Row
'オートフィルタの後にコピー
Range(Cells(1, 1), Cells(最終行, 11)).AutoFilter
ActiveSheet.Range("$A$1:$K$6").AutoFilter Field:=3, Criteria1:="<>"
Columns("A:K").Select
Selection.Copy

'mに貼り付けてフィルタ解除
Range("M1").PasteSpecial
Application.CutCopyMode = False
Selection.AutoFilter
End Sub
0093デフォルトの名無しさん (ﾜｯﾁｮｲ b707-75kg)
垢版 |
2022/02/16(水) 09:01:55.23ID:r3Bj6a+b0
>>90
確かにそうですね　別ファイルにC列にデータがある行のみをエクスポートするようなものを作ろうとしていたので
C列にデータがあるかの判断→あればコピー→エクスポート先との差分のみを貼り付け
としようとしています
差分の貼り付けは参考になりそうなコードがあったのでそれと組み合わせて考えようと思っていました

>>91
ありがとうございます、シンプルで応用が利きそうで助かります

>>92
その発想はありませんでした、ありがとうございます
0094デフォルトの名無しさん (ﾜｯﾁｮｲ 0b30-v0o9)
垢版 |
2022/02/16(水) 21:42:26.46ID:jzL/oN3O0
選択した範囲のセルから、「セルの色」と「文字」で絞った数を調べたいのですが、どのように書けば宜しいでしょうか？
（色に関しては、指定したセルの色を参照したいです。）

例：A1～D10の中で、E1と同じ色で、「テスト」と書かれているセルの数
みたいなことです。

出来ればグーグルのスプレッドシートでも使いたいです…
0095デフォルトの名無しさん (ﾜｯﾁｮｲ 6368-B80Q)
垢版 |
2022/02/16(水) 22:02:32.25ID:tqdkY3B80
>>94
GASは全然書けないんだよなぁ

Sub foo()
Set 検索範囲 = Range("a1:d10")
Set 検索 = Range("e1")
カウント = 0
For Each test In 検索範囲
If test.Interior.Color = 検索.Interior.Color And _
test.Value = 検索.Value Then
カウント = カウント + 1
End If
Next
MsgBox カウント
End Sub
0096デフォルトの名無しさん (ﾜｯﾁｮｲ 632f-TxqZ)
垢版 |
2022/02/16(水) 22:17:26.65ID:VM6eHjoj0
>>94
べたに指定範囲のセルをループ回して比較すればいいんじゃね
Sub test()
MsgBox CountX(Range("A1:D10"), Range("E1"), "テスト")
End Sub

Function CountX(target As Range, colorCell As Range, str As String)
Dim count As Integer
Dim c As Range
For Each c In target
Debug.Print c.Interior.color
If c.Text = str And c.Interior.color = colorCell.Interior.color Then
count = count + 1
End If
Next
CountX = count
End Function
こんな感じか
0098デフォルトの名無しさん (ｽｯﾌﾟ Sd32-OUII)
垢版 |
2022/02/17(木) 15:22:29.06ID:BBg74W1Zd
fileSystemObjetを使うときに下のコードのようにインスタンスを格納する変数を省略できるのはなぜですか？
ひょっとしてwithでくくるときに一時的に作られる変数にでもいれているのでしょうか？

with New fileSystemObject
ステートメント
End With
0101デフォルトの名無しさん (ﾜｯﾁｮｲ 6368-B80Q)
垢版 |
2022/02/17(木) 16:21:30.38ID:lbVsnXnA0
このwithが参照してるオブジェクトって確認する方法ありますか？

Sub foo()
With Cells(1, 1)
Debug.Print .Value
End With
End Sub

とりあえずウォッチ式に「.value」入れてみてもエラーで見れなかった
with　と入れてもエラー
どこに格納されているんだろう
■ このスレッドは過去ログ倉庫に格納されています
ニューススポーツなんでも実況