[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『どこが違っているかを調べて表示する。』(武藤)
同じ構成のシートが2つあります。
1)ws1.cells(i,3) iが1-242
2)ws2.cells(ii,3) iiが1-241
ほんとは、iとiiは同じ個数ではならないのになぜだか違ってしまいました。
(1個不足又は多い)
1)、2)の両者は、音楽のファイル名で最後の拡張子部分がflac又はmp3以外は同じはずです。
それぞれC列はソート済みです。
例えば、
ws1.cells(1,3) ---> aaaa.flac
ws2.cells(1,3) ---> aaaa.mp3
i,iiが同じ数値ならaaaa部分は同じ文字列のはずです。
ws1,ws2でどこが違っているのかチェックしたいのですが良い方法はありますか ?
< 使用 Excel:Office365、使用 OS:Windows10 >
>どこが違っているのかチェックしたい
ws1,ws2のシート名が、Sheet1、Sheet2だとした場合、
Sheet2の空いているセル(H1セルとか)に下式を入れてみたらどうですか?
=LET(r,C1:C300,rs,Sheet1!C1:C300,FILTER(TEXT(ROW(r),"000行目: ")&r&"と"&rs,LEFT(r,FIND(".",r&"."))<>LEFT(rs,FIND(".",rs&".")),"完全合致"))
(半平太) 2022/04/02(土) 10:47
早速、
1行目はそれぞれ見出し行なのでC2:C300に変更してH1に以下をコピペしてみましたが
エラー#REF!と表示されます。
見出し行が原因かと思いH2に同じくコピペしてみましたが同じエラーがでます。
=LET(r,C2:C300,rs,Sheet1!C2:C300,FILTER(TEXT(ROW(r),"000行目: ")&r&"と"&rs,LEFT(r,FIND(".",r&"."))<>LEFT(rs,FIND(".",rs&".")),"完全合致"))
(武藤) 2022/04/02(土) 11:19
>1行目はそれぞれ見出し行なのでC2:C300に変更してH1に以下をコピペしてみましたが >エラー#REF!と表示されます。
取り敢えず、変更しないでそのままコピペしてみてください。 見出しが違うなら、それも不一致の一つとして上がって来るだけなので。
(半平太) 2022/04/02(土) 11:28
もしかして、 このバージョンが勘違いってことはないですか? ↓ >Office365
シート名は実際にも「Sheet1」ですか?
(半平太) 2022/04/02(土) 11:30
すいません、Officeのバージョンが間違っていました。
Office2021です。
気になるのはコピペする時に
値の更新:Sheet1 と思いもよらない表示が出ます。 キャンセルをクリックしてエラー#REF!が表示されます。
画像を添付するのは、過去で控えるように指導されましたが
どんな画像なのか以下に添付します。
(ごめんなさい)
Officeのバージョンの違いが原因でしょうか?
(武藤) 2022/04/02(土) 11:55
>Office2021です。
Office2021なら問題はありません。
>値の更新:Sheet1 と思いもよらない表示が出ます。 Sheet1と言うシート名が無いと言うことです。 数式にある「Shee1」を実際のシート名に変えてください。
後はこの通りにやってください。 ↓← > Sheet2の空いているセル(H1セルとか)に下式を入れてみたらどうですか? > =LET(r,C1:C300,rs,Sheet1!C1:C300,FILTER(TEXT(ROW(r),"000行目: ")&r&"と"&rs,LEFT(r,FIND(".",r&"."))<>LEFT(rs,FIND(".",rs&".")),"完全合致")) ~~~↑~~ 実際のシート名に変える
(半平太) 2022/04/02(土) 12:05
表示される内容がよくわからないので説明を頂きたいです。
表示は、
H1:066行目 - H111:176行目 ------- 連番で表示
H112:194行目 - H160:242行目 ----- 連番で表示
001-065、177-193 の番号(行目)が表示されていません。
(武藤) 2022/04/02(土) 12:33
>表示される内容がよくわからないので説明を頂きたいです。
と言われましても、一目瞭然ではないかと思うんですが・・ こんなのが出てきませんでしたか? ↓ <Sheet2 シート> 行 ______________H______________ 1 001行目: aaとaaaa.flac 2 003行目: aaab.mp3とaaaa.flac
1行目がSheet1が「aa」に対して、Sheet2が「aaaa.flac」で不一致 3行目がSheet1が「aaab.mp3」に対して、Sheet2が「aaaa.flac」で不一致
と言う意味です。
(半平太) 2022/04/02(土) 12:53
つまり、
001-065、177-193 の番号(行目)はws1とws2で行番号が同じ位置に拡張子以外が一致するファイル名が存在する。
それ以外は、一致していないのですね。
思うにこれほど多くの違いが出るとは思っていませんでした。
思うに行がズレているが同じファイル名(拡張子は除く)が存在すると思います。
これをチェックする方法はありますか ?
’-----------------------------------------
何故このような操作が必要なのかと言うと
HDDの空き容量が減ったのでflacをmp3に変換して空き容量を増加させたいと思いました。
flacをmp3に変換するのにあるソフトを利用したのですが
階層が深い(多重階層)パスでファイル名も長い場合そのソフトではエラーが出て変換できないのです。
その為、変換できなかったflacのみをCドライブの直下に集めてmp3に変換して
元の階層に戻すことを考えました。
すでにmp3には変換済みなので
後は、元の階層が分かっているので
元の階層のパス¥変換済みのmp3ファイルのようにEXCELで細工してMOVEしたいと思っています。
当初、ファイル名ソート済みなので行単位で一致すると思いましたが
教えてもらった方法でチェックすると思いがけず多数の不一致が出ました。
(武藤) 2022/04/02(土) 13:17
まぁ、モノの価値は人其々ですが... (無駄レスすみません)
(白茶) 2022/04/02(土) 13:40
そうです。
mp3で残せばflacは、必要ないと私は判断しました。
(武藤) 2022/04/02(土) 13:55
↓仮にこんなテーブルに統合してしまって、
_|___A____|____B_____ 1|TABLE |BASENAME 2|ws1 |aaaa 3|ws1 |bbbb 4|ws1 |cccc 5|ws1 |xxxx 6|ws2 |aaaa 7|ws2 |bbbb 8|ws2 |cccc
↓ピボットテーブル掛ければ
_|___________A____________|___B____|___C____|__D__ 1| | | | 2| | | | 3|データの個数 / BASENAME |TABLE | | 4|BASENAME |ws1 |ws2 |総計 5|aaaa | 1| 1| 2 6|bbbb | 1| 1| 2 7|cccc | 1| 1| 2 8|xxxx | 1| | 1 ←[ws2]に[xxxx]がないと分かりますが、どうですか? 9|総計 | 4| 3| 7
(白茶) 2022/04/02(土) 14:02
「仮にこんなテーブルに統合」にあるテーブルは別シートに作成しました。
個人的に「ピボットテーブル」を作成したことが無いので「ピボットテーブル掛ければ」で示されたテーブルの作り方が判りません。
今から「ピボットテーブル」の作成についてネット情報を調べてみます。
(武藤) 2022/04/02(土) 14:23
>行がズレているが同じファイル名(拡張子は除く)をチェックする方法は
これでやってみて下さい。
H1セル =LET(r,Sheet1!C1:C300,rs,C1:C300,FILTER(rs,ISNA(MATCH(LEFT(rs,FIND(".",rs&".")),LEFT(r,FIND(".",r&".")),0)))) ~~↑~~~ 実際のシート名にする
(半平太) 2022/04/02(土) 14:25
>これでやってみて下さい。
ws2のI1に関数を入力すると14個ほどファイル名がヒットしました。
よく見るとファイル名の最後が(1)や(2)なのでmp3に変換時に同名ファイルが存在していて
ws1に同名のflacが存在していたのでmp3に変換時に
上書き防止のために(1)や(2)を付加して変名されたようです。
ws1で同名ファイルの行番号を探すと201-234が相当(但し、連番中の7個は総当外)でした。
最初の関数でヒットした表示は、下記でした。
H1:066行目 - H111:176行目 ------- 連番で表示
H112:194行目 - H160:242行目 ----- 連番で表示
これを見ると同名の変名箇所しかヒットしていなようです。
例えば、
ws1の67行目のファイルはws2の67番目に無くても
それ以外の番地に存在しているはずなので
それがどこに有るかを見つけたいのです。
試しに067行目をチェックしたらws1,ws2のファイル名は全く別の文字列でした。
白茶さんへ、
ピボットテーブルの作成、難航しています。
何でA3からテーブルが作成されているかも理解し難く。
先が見えていません。
(武藤) 2022/04/02(土) 15:38
済みません、当初の私の理解が甘かったようなので降ります。m(__)m
(半平太) 2022/04/02(土) 15:50
【Excel】ピボットテーブルを使って2つのリストを照合する方法
https://works.forward-soft.co.jp/blog/detail/10254
(白茶) 2022/04/02(土) 16:06
集計作業をやることが無くピボットテーブルの操作も初めてなので難航していますが、
紹介記事を参考に何となく似たようなピボットテーブルが作成できました。
(しかし、まだ理解が出来てないので見た目を同じようにしただけです。)
現在以下のようなに表示されていますが、これで合っていますか ?
ピボットテーブルのフィールド
☑TABLE
☑BASENAME
その他のテーブル...
フィルター
列
TABLE
行
BASENAME
Σ値
個数/BASENAME
(武藤) 2022/04/02(土) 17:20
同名のファイルがあれば flacを「mp3に変換済みflac」フォルダに移動するマクロ
Option Explicit
Sub test() Dim fdg As FileDialog Dim folderFrom As String, folderTo As String Dim cmd As String, s, k As Long Dim fn As String
Set fdg = Application.FileDialog(msoFileDialogFolderPicker) If Not fdg.Show Then Exit Sub folderFrom = fdg.SelectedItems(1) & "\" folderTo = folderFrom & "mp3に変換済みflac\"
cmd = "cmd /c dir """ & folderFrom & "*.flac"" /b/a-d" s = Split(CreateObject("wscript.shell").exec(cmd).stdout.readall, vbCrLf)
For k = 0 To UBound(s) - 1 fn = Replace(s(k), ".flac", ".mp3") If Dir(folderFrom & fn) <> "" Then Name folderFrom & s(k) As folderTo & s(k) End If Next
End Sub
(マナ) 2022/04/02(土) 20:37
>flacとmp3が同じフォルダにある前提で
>同名のファイルがあれば flacを「mp3に変換済みflac」フォルダに移動するマクロ
上記の意味が理解できないのでどう言う事をするマクロなのか?
説明いただけませんか ?
現在は、
C:直下にC:¥Aフォルダーを作成してそこに一度各階層フォルダーにあるflacファイルを
MOVEして集めました。
C:¥A内のflacファイルは、階層が深い(多重階層)パスでファイル名も長い場合エラーが出てmp3に変換できなかった
ソフトでもflacをmp3み変換する事ができました。
この時点で、拡張子は異なるが同じファイル名(拡張子部を除く)が対子で存在しています。
(具体的には、C:\aaa.flacとc:\aaa.mp3です。
このような対子のファイル群が複数存在しています)
>flacとmp3が同じフォルダにある前提で
この前提条件は、現状クリアーしていると思います。
>同名のファイルがあれば flacを「mp3に変換済みflac」フォルダに移動するマクロ
私は、変換済みのmp3を変換元のflacが存在している元の各層階にMOVEしたいのです。 私がマクロコードを読み解けていないのでどうやって一箇所に集めたmp3を元の各層階にmoveするための passを求めるのでしょうか? 考え違いで全く違うアプローチでしょうか ?
(武藤) 2022/04/03(日) 08:08
変換したmp3を移動させるためには
元のフォルダがどこか知る必要があります。
それは、flacを移動させた本人しかわからないのでできません。
↓がマクロを予定していたのであれば、組み込むことは可能かもしれません。
>後は、元の階層が分かっているので >元の階層のパス¥変換済みのmp3ファイルのようにEXCELで細工してMOVEしたいと思っています
(マナ) 2022/04/03(日) 09:57
白茶さんのピポットテーブルを利用する方法で
元のflacと変換済みのmp3のファイル構成がわかりました。
(ピボットテーブルは、sheet6に作成済み)
ピボットテーブルを見るとflacからmp3に変換時に
同名ファイルが存在する為mp3に変換時に末尾に(1),(2),(3),(4)が付加されたmp3ファイルが有りました。
(この違いがほとんどでした)
まず、同名ファイルなどの一括処理出来そうにないのを除いて
対子(ws1とws2)が一対一でフィットしているファイルのみを先に処理する事にしました。
つまり、ピボットテーブルにおいて以下のファイルのみを処理対象にしたい 列ラベル ws1= 1, ws2=1 、総計=2
ws1は、
A列:flacのフルパス --------> 各階層フォルダー
B列:flacのパス部分のみ
C列:flacのファイル名のみ
(C列のファイル名でソート済み)
ws2は、ws1のflacをC:\にコピー後にmp3に変換したファイル郡で構成は、ws1と同じです。
A列:mp3のフルパス
B列:mp3のパス部分のみ -----> C:\A で全て同じ
C列:mp3のファイル名のみ
(C列のファイル名でソート済み)
mp3を元flacのパスにMOVEするには、
ws2のmp3(A列)をターゲットとして
Move先は、ws1のパス部分(B列)+WS2のファイル名(C列)になると思います。
>↓がマクロを予定していたのであれば、組み込むことは可能かもしれません。
以上を踏まえて、
ws1,ws2及びピボットテーブルを利用してマクロでMOVE処理は出来ませんか ?
(武藤) 2022/04/03(日) 11:59
>ws1は、 >A列:flacのフルパス --------> 各階層フォルダー
現在の状態について、念のため確認ですが、
flacは、C:\に移動してしてしまって
元のフォルダにはありませんよね。
(マナ) 2022/04/03(日) 13:16
Everythingと言う検索ソフトを利用すれば「.flac」を指定して
散らばったflacを1度で検索できてフルパスを取得する事が可能です。
>元のフォルダにはありませんよね。
それぞれのflacは、まだ元のフォルダーに存在します。
C:\AにはEverhingを利用してCOPYして後でmp3に変換しました。
flacを削除するのは、どのタイミングでも可能と思い削除していません。
(武藤) 2022/04/03(日) 13:39
Option Explicit
Sub test2() Dim fdg As FileDialog Dim folderTop As String, folderTo As String Dim folderWork As String Dim cmd As String, ss, s, k As Long Dim flac As String, mp3 As String Dim dic As Object, n As Long
Set fdg = Application.FileDialog(msoFileDialogFolderPicker) If Not fdg.Show Then Exit Sub folderTop = fdg.SelectedItems(1) & "\" folderWork = "C:\" Set dic = CreateObject("scripting.dictionary")
cmd = "cmd /c dir """ & folderTop & "*.flac"" /b/s/a-d" ss = Split(CreateObject("wscript.shell").exec(cmd).stdout.readall, vbCrLf) For k = 0 To UBound(ss) - 1 s = Split(ss(k), "\") flac = UBound(s) mp3 = Replace(flac, ".flac", ".mp3") If Dir(folderWork & mp3) <> "" Then folderTo = Mid(ss(k), 1, InStrRev(ss(k), "\")) If Dir(folderTo & mp3) = "" Then Name folderWork & mp3 As folderTo & mp3 End If Else dic(ss(k)) = Empty End If Next
n = dic.Count If n > 0 Then Worksheets.Add.Cells(1).Resize(n).Value = Application.Transpose(dic.keys) End If
End Sub
(マナ) 2022/04/03(日) 15:40
(マナ) 2022/04/03(日) 15:47
早速、コードを試してみましたが、
以下のコードでエラーが発生しました。
ss = Split(CreateObject("wscript.shell").exec(cmd).stdout.readall, vbCrLf)
実行エラー’-2147024891(80070005)’:
アクセスが拒否されました。
又、同時に
Windowsセキュリティに「リスクのある操作がブロックされました」
この操作は管理者によってブロックされました。
アプリまたはプロセスがブロックされています:EXCEL.EXE
ブロックしたユーザー:攻撃対象領域の削減
規則:すべてのOfficeアプリケーションで子プロセスの作成をブロックする
影響を受けた項目:C:\Users\_____\Desktop\Flac_to_mp3.xlsm
(_____ は、ユーザー名ですが伏せます。)
以下コードトレース状況です。
FileDialogでターゲットのMP3が存在するフォルダー(C:\A)を指定して
folderTop ---> "C:\A\"
cmd = "cmd /c dir """ & folderTop & "*.flac"" /b/s/a-d"
Debug.Print cmd で、イミディエイトウインドウに以下表示されてます。 cmd /c dir "C:\A\*.flac" /b/s/a-d
’--------------------------------
Windowsセキュリティ>保護来歴 のフィルターで
該当する「リスクのある操作がブロックされました」をフィルターで 「許可されたアイテム」に指定する必要がありますか?
(武藤) 2022/04/03(日) 16:43
イミディエイトウインドウに以下表示される
「cmd /c dir "C:\A\*.flac" /b/s/a-d」は、ターゲットがflacですが ?
(武藤) 2022/04/03(日) 17:08
C:\A でなく、元のflacがあるフォルダを指定するとどうなりますか。
あと、下記は間違いでした、
>folderWork = "C:\" ↓ folderWork = "C:\A\"
(マナ) 2022/04/03(日) 17:23
(マナ) 2022/04/03(日) 17:27
>C:直下にC:¥Aフォルダーを作成してそこに一度各階層フォルダーにあるflacファイルを
MOVEして集めました。
これは、C:¥Aフォルダーに、改造構造を保ったままコピーしたという意味でしょうか
(マナ) 2022/04/03(日) 18:01
>C:¥A内のflacファイルは、階層が深い(多重階層)パスでファイル名も長い場合エラーが出てmp3に変換できなかった
>ソフトでもflacをmp3み変換する事ができました。
>この時点で、拡張子は異なるが同じファイル名(拡張子部を除く)が対子で存在しています。
(具体的には、C:\aaa.flacとc:\aaa.mp3です。
↑は↓と矛盾しませんか?
>C:\Aは、flacファイルをEverhingを利用してCOPYした後でmp3に変換したMp3ファイルのみが存在します。
>flacは存在しません。
(マナ) 2022/04/03(日) 18:22
煩わしい作業ですいません。
すでに、前コードを利用しない方法で検討されていると思いますが、
「folderWork = "C:/A/"」に変更後の報告を上げておきます。
>C:/A でなく、元のflacがあるフォルダを指定するとどうなりますか
多数ある階層フォルダー(元のflacのあるフォルダー)の一つを指定して
コードを試してみましたが同じ箇所で同じエラーが発生します。
(対象のflacフォルダーが多数あるので1つ1つフォルダーを処理するのは
正直厳しいです。)
そもそも、フルパスの長さが259文字を超えるフォルダーが対象なので
flackをmp3に変換するソフトがエラーで変換できないのが事の始まりです。
(ファイル名前が255文字を超えているモノはチェックの結果無いようです。)
以下のURLにある
「対処4: Win32の長いパスを有効にして制限を解除する」
「方法1: グループポリシーで設定する」を参考にして長いパス名でもエラーが出なくて
flackをmp3に変換するソフトで処理できるか試してみましたがダメでした。
https://itojisan.xyz/trouble/20895/」
’---------------------------------------------------------------------
上記回答を作成中に問い合わせがありましたので下記に回答いたします。
>これは、C:/Aフォルダーに、改造構造を保ったままコピーしたという意味でしょうか
階層構造を保ったままは無理です。
ファイル名のみ集めました。
(M:/AAA/BBB/CCC/DDD.flacなら ------> C:/A:/DDD.flac)
>↑は↓と矛盾しませんか?
「この時点で、拡張子は異なるが同じファイル名(拡張子部を除く)が対子で存在しています。」
すいません。
これは、わかりにくい表現で最終的にはミス記載です。
説明が下手なので具体例で説明します。
たとえば、Everthingで検索の結果以下のflacが合ったとします。
(例なのでフルパスが短いような記載ですが実際は259文字以上の長い文字列です。)
M:/AAA/BBB/CCC/DDD.flac L:/EEE/FFF/GGG/HHH/ddd.flac
これをC:/A/にコピーしたとすると
同じファイル名なので CC:/DDD.flac CC:/DDD(1).flac となります。 (なぜ、259文字以上の文字列のファイルがコピー出来るかは、私には判りませんが コピーは出来ています。)
フルパスの制限が無いので変換ソフトでもMP3に変換する事ができました。
CC:/DDD.mp3 CC:/DDD(1).mp3 となります。
上記は同名の場合ですが、それ以外の同名が無い場合は
L:/MMM/FFG/GGH/OPHH/ABC.flac C:/A/ABC.flac ---> 変換して C:/A/ABC.mp3 (変換時点で両者は存在しますが、その後変換を確認して C:/A/ABC.flacは削除されます。残す意味がないので残していません。)
この説明でご理解な頂いているでしょうか?
(武藤) 2022/04/03(日) 19:08
以下に訂正します。
この説明でご理解い頂いているでしょうか?
(武藤) 2022/04/03(日) 19:43
これは、エラーになりますか。
実際に存在するパスで動作確認してください。
Sub test()
Dim fso As Object
Set fso = CreateObject("scropting.fikesystemobject") fso.movefile "C:/A/ABC.mp3", "L:/MMM/FFG/GGH/OPHH/"
End Sub
(マナ) 2022/04/03(日) 20:35
ライブラリ「Microsoft Scripting Runtime」を追加して
Set fso = CreateObject("scropting.fikesystemobject")
は、
Set fso = CreateObject("Scripting.FileSystemObject")
に訂正の上でコードを起動しましたが
以下でエラーが発生します。
fso.movefile "C:/A/ABC.mp3", "L:/MMM/FFG/GGH/OPHH/"
実行時エラー '76': パスが見つかりません。
(フルパス及ぶパスは、実際に存在するパスで動作確認しました。)
多分、「ファイル/フォルダの絶対パスが259文字を超えている」為と思われます。
試しに、同じフルパスで普段使用しているファイラーでMoveしてみました。
これは、エラー無くMoveできました。
VBAではダメでファイラーではMove出来たmp3
相手先のパスの文字数: 278 mp3の文字数: 69
(2画面のファイラーでも多層階のフルパスを目で追ってMove先を間違いなく指定するのは正直気を使う面倒な作業で
これを200以上あるファイルに対して行うのはかなり厳しいです。)
思うに「ファイル名の8.3形式などの名前に変名して短い名前に変更」してフルパスを259文字以下にすれば出来そうですが
「C:/A/ABC.mp3」の部分を直接短くしても「ファイルが見つかりません」のエラーが出るので
事前に、ファイル名を短くする作業が必要になると思われます。
ファイル名を短くすることは、避けたいので
つまりEXCELでは、思うような結果が出せないのか?と悲観的な現状です。
(武藤) 2022/04/04(月) 04:53
mp3の文字数: 69
は
mp3のファイル名の文字数: 69
です。
(武藤) 2022/04/04(月) 06:28
https://www.excel.studio-kazu.jp/kw/20171220160716.html
上記URLの記事内の記載に
>要はフルパスにすると256バイトを超える場所にファイルコピーできれば良いのですね?
とあって解決に至っているようです。
私の現状も解決策は有るのでは思っています。
つまり、全く的外れかも知れませんが
ws1,ws2の同じC列でファイル名(除く拡張子部分)が同じ行番号をターゲットとして
URLの記事内にあるCopyで処理する事は可能なように思えます。
WS1 B列 Move先のパス
WS2 B列 Move元のパス(C:\a\ですべて同じ)
Ws2 C列 Move元のファイル名
この情報だけでマクロを作成して試してみたいのですが
サンプルコードをお願いします。
(武藤) 2022/04/04(月) 12:31
でわ (´・ω・`) 2022/04/04(月) 13:07
アドバイスあればお願いします。
Sub test4()
Dim Ln As Long, i As Long Dim Ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Set Ws1 = Worksheets("Everything") Set ws2 = Worksheets("Mp3")
Ln = Ws1.Cells(Rows.Count, 1).End(xlUp).Row
Dim FSO As Object Set FSO = CreateObject("Scripting.FileSystemObject")
Dim FNfm As String, FMto As String Dim tmp1 As String, tmp2 As String
For i = 2 To Ln FNfm = ws2.Cells(i, 3) 'MOVE先のファイル名 fnto = Ws1.Cells(i, 3) 'Move元のファイル名 tmp1 = FSO.GetBaseName(Ws1.Cells(i, 3)) '拡張子を除くファイル名 tmp2 = FSO.GetBaseName(ws2.Cells(i, 3)) '拡張子を除くファイル名 If tmp1 = tmp2 Then FSO.MoveFile Source:="C:A\" & FNfm, Destination:=Cells(i, 2) & "\" Else End If Exit Sub
Set Ws1 = Nothing Set ws2 = Nothing Set objFileSys = Nothing
End Sub
(武藤) 2022/04/04(月) 13:11
SUBSTコマンドの利用についてアドバイスありがとうございます。
提示のURLを見たりして調べたところでは、仮想ドライブに長いパスが指定できると言うことのようでした。
例えば、以下のような形式で
subst z: K:\#C\#ComandString\# 1970-2015 ---- [Part1+2]\The Dark --- series\ComandString --- Vol. 5-8-12365 -2016-WRE\ (−−−−−は、省略しているので実際はもっと長いです。)
SUBSTを利用したファイルのMOVE方法の参考記事を探しましたがヒットしません。
何か参考になるコードはありますか ?
(武藤) 2022/04/04(月) 13:32
VBAのコードでは
K:\#C\#ComandString\# 1970-2015 ---- [Part1+2]\The Dark --- series\ComandString --- Vol. 5-8-12365 -2016-WRE\file1.mp4 は
z:\file1.mp4と記述できます。
K:\#C\#ComandString\# 1970-2015 ---- [Part1+2]\The Dark --- series\ComandString --- Vol. 5-8-12365 -2016-WRE\fol2\file2.mp4 は
z:\fol2\file2.mp4と記述できます。
(ax) 2022/04/04(月) 14:11
アドバイスありがとうございます。
つまりフルパスが255(259?)を超えるとても長いフルパスでも
subst z: のように登録してしまえば後はmoveできるのですね。
そうであれば、以下のように修正しましたがどうでしょうか ?
Sub test5() 'SUBST版
Dim Ln As Long, i As Long Dim Ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet Set Ws1 = Worksheets("Everything") Set ws2 = Worksheets("Mp3") Ln = Ws1.Cells(Rows.Count, 1).End(xlUp).Row Dim FSO As Object Set FSO = CreateObject("Scripting.FileSystemObject") Dim FNfm As String, FMto As String Dim tmp1 As String, tmp2 As String Dim sst As String
For i = 2 To Ln FNfm = ws2.Cells(i, 3) 'MOVE先のファイル名 fnto = Ws1.Cells(i, 3) 'Move元のファイル名 tmp1 = FSO.GetBaseName(Ws1.Cells(i, 3)) '拡張子を除くファイル名 tmp2 = FSO.GetBaseName(ws2.Cells(i, 3)) '拡張子を除くファイル名 sst = "subst z:" & Cells(i, 2) & "\" If tmp1 = tmp2 Then FSO.MoveFile Source:="C:A\" & FNfm, Destination:=sst Else End If Exit Sub Set Ws1 = Nothing Set ws2 = Nothing Set objFileSys = Nothing End Sub
(武藤) 2022/04/04(月) 14:29
パスの共通部分を、ドライブに置き換えれば、文字数制限をクリアできるなら、 マクロ実行前に1回SUBSTコマンドを手動で実行しておけばいいと思います。
SUBSTコマンドを何回も実行しないといけないようなフォルダ構成なら、 SUBSTコマンドもマクロで実行することになりますね。 その場合は、Shellコマンドを使います (´・ω・`) 2022/04/04(月) 14:53
z:\The Dark --- series\ComandString --- Vol. 5-8-12365 -2016-WRE\file1.mp3
とすることもできます。
過去ログにこんなのがありました。
[[20180412123828]]『VBAのShell関数のパス名の長さについて』(なお)
(ax) 2022/04/04(月) 15:17
>パスの共通部分を、ドライブに置き換えれば、文字数制限をクリアできるなら、
SUBSTを利用することで文字数の制限がクリアー出来ると思ったのですが
「クリアできるなら」と雲行きが怪しい表現をされています。
制限をクリアー出来ない場合もあるのですか ?
ws1,ws2はファイル名でソートして比較それぞれ行単位で比較出来るようにしています。
ファイル名が対子で一致すればとりあえずMOVEするように考えてマクロコードを考えてみた結果がsub test4(),sub test5()です。
ソートしているので複数ドライブ(j:K: L:)でパスもそれぞれ何度も変わるので
そのたびにFor文でSUBSTを何回も変更するようなコードになりました。
>その場合は、Shellコマンドを使います
sub test5()の修正を予感させますが、
shellコマンドを利用してコードをどのように変更すれば良いでしょうか?
sub test4(),sub test5()のコードが問題ない前提で記載していますが
実際のところまだ試していないので怪しいものですが。。。。
(武藤) 2022/04/04(月) 15:28
>文字数の節約なら
文字数の節約が目的ではありません。
文字数の制限(255,259?)を超えるロングパスへファイルをMOVEするための解決策を求めています。
SUBSTコマンドは、解決策の提案だと思っています。
参考記事である1),2)を読んでみましたが残念ながらよく判りませんでした。
1)『VBAのShell関数のパス名の長さについて』
https://www.excel.studio-kazu.jp/kw/20180412123828.html
上記の連結で下記も参考になりそうでしたが
2)VBAで長いパスが扱えないと思ったら
https://knjname.hateblo.jp/entry/2015/01/27/220219
(武藤) 2022/04/04(月) 16:34
当方では実際のフォルダ構成がわからないので、なんとも言えません。
>shellコマンドを利用してコード このサイトの校内全文検索で、Shell関数のサンプルはでてくると思います このサイトは、サンプルコードの宝庫ですよ。 そのまま使えなくても応用可能なコードがでてくると思いますので、頑張ってください。 (´・ω・`) 2022/04/04(月) 16:51
エラーがいくつか出たので修正して現在、下記状況です。
試しにフルパスが短いファイルで確認してみました。
test4()は旨くMOVEできましたが、
test5()は
FSO.MoveFile Source:="C:\A\" & FNfm, Destination:=sst
でエラーが出ました。
パスが見つかりません。
>当方では実際のフォルダ構成がわからないので、なんとも言えません。
フォルダー構成の以前の問題でツマズイています。
Sub test4()
Dim Ln As Long, i As Long Dim Ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Set Ws1 = Worksheets("Everything") Set ws2 = Worksheets("Mp3")
Ln = Ws1.Cells(Rows.Count, 1).End(xlUp).Row
Dim FSO As Object Set FSO = CreateObject("Scripting.FileSystemObject")
Dim FNfm As String, FNto As String Dim tmp1 As String, tmp2 As String Dim SCE As String, DES As String
For i = 2 To Ln FNfm = ws2.Cells(i, 3) 'MOVE先のファイル名 FNto = Ws1.Cells(i, 3) 'Move元のファイル名 tmp1 = FSO.GetBaseName(Ws1.Cells(i, 3)) '拡張子を除くファイル名 tmp2 = FSO.GetBaseName(ws2.Cells(i, 3)) '拡張子を除くファイル名 If tmp1 = tmp2 Then 'FSO.MoveFile source, destination SCE = "C:\A\" & FNfm DES = Ws1.Cells(i, 2) & "\" FSO.MoveFile Source:=SCE, Destination:=DES Else End If Next
Set Ws1 = Nothing Set ws2 = Nothing Set FSO = Nothing
End Sub
Sub test5() 'SUBST版
Dim Ln As Long, i As Long Dim Ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet Set Ws1 = Worksheets("Everything") Set ws2 = Worksheets("Mp3") Ln = Ws1.Cells(Rows.Count, 1).End(xlUp).Row Dim FSO As Object Set FSO = CreateObject("Scripting.FileSystemObject") Dim FNfm As String, FNto As String Dim tmp1 As String, tmp2 As String Dim sst As String
For i = 2 To Ln FNfm = ws2.Cells(i, 3) 'MOVE先のファイル名 FNto = Ws1.Cells(i, 3) 'Move元のファイル名 tmp1 = FSO.GetBaseName(Ws1.Cells(i, 3)) '拡張子を除くファイル名 tmp2 = FSO.GetBaseName(ws2.Cells(i, 3)) '拡張子を除くファイル名 sst = "subst z: " & Ws1.Cells(i, 2) If tmp1 = tmp2 Then FSO.MoveFile Source:="C:\A\" & FNfm, Destination:=sst Else End If Next
Set Ws1 = Nothing Set ws2 = Nothing Set FSO = Nothing End Sub
(武藤) 2022/04/04(月) 18:48
1)セキュリティーの問題 →設定でなんとかなると思う(たぶん) 2)パスが長すぎる問題 →substで解決 (試した感じでは、割当て-move-解除の繰り返しでいけそう) 3)同名ファイルの問題 →解決できなさそう
(マナ) 2022/04/04(月) 19:15
1)セキュリティーの問題
→これは、コードの不具合が関連していたと思われ、その後修正後出ていません。
2)パスが長すぎる問題
→substで解決を期待してtest5()のコードを考えてみましたが
素人のコードなので
FSO.MoveFile Source:="C:\A\" & FNfm, Destination:=sst
でパスが見つかりませんのエラーが出ました。
私が思うような使い方ではダメなようですがどうすれば良いかご指導下さい。
>割当て-move-解除
以下の解除のコードをFOR文内に含める必要があるとのアドバイスだと思いますが?
割当て=マウント
subst z: [パス]
解除
subst z: /d
3)同名ファイルの問題
こちらは、数が少ないのでファイラーを利用して手動で対処できそうです。
(武藤) 2022/04/05(火) 08:16
> Dim WSH > Set WSH = CreateObject("WScript.Shell") > WSH.Run "%ComSpec% /c subst " & DriveLtr & ": " & "" & Path & "", 0, True
> Set WSH = CreateObject("WScript.Shell") > WSH.Run "subst /d " & DriveLtr & ":", 0, True
現在の MoveFile の部分に subst の修正をするのではなくて、
前後に割当・解除のコードを記載します。
MoveFile は割り当てたパスに修正します。
1.割当 2.MoveFile 3.解除 (ax) 2022/04/05(火) 12:41
>ネットワークドライブの場合も併記されているので分かりにくいですが、
>割当・解除のキモは下記。
記載内容は、以下の参考記事の抜粋ですね。
[[20180412123828]]『VBAのShell関数のパス名の長さについて』(なお)
(https://www.excel.studio-kazu.jp/kw/20180412123828.html)
分かりにくいと言うか、
抜粋部をどう利用するか難しすぎて残念ながらさっぱり理解できません。
(武藤) 2022/04/05(火) 14:15
解除
WSH.Run "subst /d z:" , 0, True
(ax) 2022/04/05(火) 17:49
(ax) 2022/04/05(火) 17:57
アドバイスを受けてコードを修正してみました。
実際に走らせると
WSH.Run "%ComSpec% /c subst z: " & FNfm & "\", 0, True で実行エラー 70: 書き込み出来ません。 さらに Windowsセキュリティ 操作がブロックされました。 管理者の設定によりこのアクションはWindowセキュリティでブロックされました。
はじめ、EXCELで使用できない文字がパス、ファイル名に有るのかと思いましたがそうでは無いようです。
Debug.print でチェックはしました。
どこか?間違っている箇所が有るでしょうか?
’-------------------------------------------------------
Sub test5() 'SUBST版
Dim Ln As Long, i As Long Dim Ws1 As Worksheet, ws2 As Worksheet
Set Ws1 = Worksheets("Everything") Set ws2 = Worksheets("Mp3")
Ln = Ws1.Cells(Rows.Count, 1).End(xlUp).Row
Dim FSO As Object Set FSO = CreateObject("Scripting.FileSystemObject")
Dim FNfm As String, FNto As String Dim tmp1 As String, tmp2 As String
Dim WSH Set WSH = CreateObject("WScript.Shell")
For i = 2 To Ln
FNfm = Ws1.Cells(i, 2) 'Move元のフルパス flac FNto = ws2.Cells(i, 2) 'Move先のフルパス mp3 ---> C:\A\ tmp1 = FSO.GetBaseName(Ws1.Cells(i, 3)) 'Move元のファイル名のみ (拡張子なし) tmp2 = FSO.GetBaseName(ws2.Cells(i, 3)) 'Move先のファイル名のみ (拡張子なし) 'Debug.Print FNfm
Dim Source As String Dim Destination As String
'割当 WSH.Run "%ComSpec% /c subst z: " & FNfm & "\", 0, True If tmp1 = tmp2 Then Source = "C:\A\" & tmp2 & ".mp3" Debug.Print Source Destination = "Z:\" & tmp1 & ".mp3" Debug.Print Destination Stop '移動 FSO.MoveFile Source:=Source, Destination:=Destination Else End If
'解除 WSH.Run "subst /d z:", 0, True Next
Set Ws1 = Nothing Set ws2 = Nothing Set WSH = Nothing Set FSO = Nothing End Sub
(武藤) 2022/04/06(水) 15:24
そういえば パスに半角空白がありましたね。
パスをダブルクオーテーションで囲ってください。
subst z: "C:\DATA\"
(ax) 2022/04/06(水) 23:20 修正 4/7 06:23
チェック点頂いたので報告いたします。
>1.コマンドプロンプトから割り当てできるかためしてみる。
コマンドプロンプ(管理者権限付き、無しの両方で)で subst Z: C:\00_置換後ファイル\test を試すと、エラー出ないです。
ファイラーで確認すると、Z:出現します
subst z: /d も同じくエラー無しです。
ファイラーで確認すると、Z: が消えます。
>2.コードでC:\DATA\ を割り当ててみる
同じ「C:\00_置換後ファイル\test」を試すために
FNfm = "C:\00_置換後ファイル\test"
と変更して試してみましたが、同じ箇所でエラーがでました。
以下、最下部が修正済みの試したコードです。
>そういえば パスに半角空白がありましたね。
>パスをダブルクオーテーションで囲ってください。
> subst z: "C:\DATA\"
試した
FNfm = "C:\00_置換後ファイル\test"
FNfm = FNfm & "\"
WSH.Run "%ComSpec% /c subst z: " & FNfm, 0, True
には、パスに半角空白は有りませんがエラーが出ます。
エラーが出る以下のコードををダブルクオーテーションで囲うとはどのようにするのでしょうか ?
WSH.Run "%ComSpec% /c subst z: " & FNfm, 0, True
Sub test5() 'SUBST版
Dim Ln As Long, i As Long Dim Ws1 As Worksheet, ws2 As Worksheet Set Ws1 = Worksheets("Everything") Set ws2 = Worksheets("Mp3") Ln = Ws1.Cells(Rows.Count, 1).End(xlUp).Row Dim FSO As Object Set FSO = CreateObject("Scripting.FileSystemObject") Dim FNfm As String, FNto As String Dim tmp1 As String, tmp2 As String Dim WSH Set WSH = CreateObject("WScript.Shell") For i = 2 To Ln 'FNfm = Ws1.Cells(i, 2) 'Move元のフルパス flac FNfm = "C:\00_置換後ファイル\test" FNto = ws2.Cells(i, 2) 'Move先のフルパス mp3 ---> C:\A\ tmp1 = FSO.GetBaseName(Ws1.Cells(i, 3)) 'Move元のファイル名のみ (拡張子なし) tmp2 = FSO.GetBaseName(ws2.Cells(i, 3)) 'Move先のファイル名のみ (拡張子なし) 'Debug.Print FNfm Dim Source As String Dim Destination As String '割当 FNfm = FNfm & "\" WSH.Run "%ComSpec% /c subst z: " & FNfm, 0, True Stop If tmp1 = tmp2 Then Source = "C:\A\" & tmp2 & ".mp3" Debug.Print Source Destination = "Z:\" & tmp1 & ".mp3" Debug.Print Destination Stop '移動 FSO.MoveFile Source:=Source, Destination:=Destination Else End If '解除 WSH.Run "subst /d z:", 0, True Next Set Ws1 = Nothing Set ws2 = Nothing Set WSH = Nothing Set FSO = Nothing End Sub (武藤) 2022/04/07(木) 09:52
Dim WSH Set WSH = CreateObject("WScript.Shell") FNfm = "C:\00_置換後ファイル\test" FNfm = FNfm & "\" WSH.Run "%ComSpec% /c subst z: " & FNfm, 0, True
ダブルクオーテーションは
FNfm = """" & FNfm & "\"""
とします。
だんだん遠回りになって行きますが
バッチファイルのsubstなら実行できますか。
割当.batファイルに
subst Z: C:\00_置換後ファイル\test
と記述しておき
WSH.Run "C:\00_置換後ファイル\割当.bat", 0, True
割当.batを書きかえては実行を繰り返す
(ax) 2022/04/07(木) 10:46
新規Bookで以下のコードを標準モジュールに作成して試してみましたが
(test1(),test2())
どちらも同じエラーが以下の同じ箇所で出ます。
WSH.Run "%ComSpec% /c subst z: " & FNfm, 0, True
実行エラー 70: 書き込み出来ません。 さらに Windowsセキュリティ 操作がブロックされました。 管理者の設定によりこのアクションはWindowセキュリティでブロックされました。
’--------------------------
Option Explicit
Sub test()
Dim WSH Dim FNfm As String
Set WSH = CreateObject("WScript.Shell") FNfm = "C:\00_置換後ファイル\test" FNfm = FNfm & "\" WSH.Run "%ComSpec% /c subst z: " & FNfm, 0, True
End Sub
Sub test2()
Dim WSH Dim FNfm As String
Set WSH = CreateObject("WScript.Shell") FNfm = "C:\00_置換後ファイル\test" FNfm = """" & FNfm & "\""" WSH.Run "%ComSpec% /c subst z: " & FNfm, 0, True
End Sub
’-------------------------------------------------
>バッチファイルのsubstなら実行できますか。
>割当.batファイルに
>subst Z: C:\00_置換後ファイル\test
>と記述しておき
>WSH.Run "C:\00_置換後ファイル\割当.bat", 0, True
>割当.batを書きかえては実行を繰り返す
subst Z: C:\00_置換後ファイル\test と記載した
割当.batをC:\00_置換後ファイル\割当.batに配置して
以下のtest3()マクロを実施しましたが
同じ箇所で同じエラーが出ます。
試しに割当.batをC:割当.batに配置して
test4()マクロでも同じ結果です。
Sub test3()
Dim WSH
Set WSH = CreateObject("WScript.Shell") WSH.Run "C:\00_置換後ファイル\割当.bat", 0, True End Sub
Sub test4()
Dim WSH
Set WSH = CreateObject("WScript.Shell") WSH.Run "C:割当.bat", 0, True End Sub
(武藤) 2022/04/07(木) 13:04
(ax) 2022/04/07(木) 14:08
(あとだし) 2022/04/07(木) 14:19
>Windows10標準のWindows Defenderやその他のセキュリティソフトを
>使用しているのであればそれを、一時的に停止させてみてはいかがでしょう。
Defenderのみ利用中です。
Windowsセキュリティのリアルタイム保護をオフにすると
エラー無く処理されるのを確認しました。
これでロングパスの制限を抜け出して目的が達成できそうです
(武藤) 2022/04/07(木) 15:26
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.