質問
2018年8月28日火曜日 3:01
初めて投稿させていただきます。よろしくお願いいたします。
VBAで、特定のフォルダを指定し、その下の全Excelファイルからデータを取得する、
というマクロを作っています。
フォルダのフルパスと、dir関数で取得したファイル名で
Set データ取得ファイル = Workbooks.Open(フルパス & ファイル名, ReadOnly:=True, Notify:=False)
という記述でファイルを開いて作業を進めています。
ほぼ問題なく動くのですが、1件だけ不可解なファイルがあって、
ここで動作が止まってしまいます。
同フォルダには「パブリック」という言葉を含むファイルが複数あり、
「〇〇パブリック01」
「××パブリック02□□
「△△パブリック03」
「□□パブリック04〇〇」…のように並んでいるのですが、
不可解なファイルは、エクスプローラー(Win10)で見る限り
「●●パブリック05●●」と表示されているのに、取得されたファイル名は
「●●ハ゜ブリック05●●」となっていて、
マクロ実行時には「ファイルが見つかりませんでした。 移動または削除された可能性があります。」
と表示されて止まります。
エクスプローラから問題のファイル名だけをコピーして、テキストエディタにペーストしてみると
「●●ハ゜ブリック05●●」となり、
エクスプローラ上で表示されている「●●パブリック05●●」にはなりません。
いったいなぜこのような現象が起きるのでしょうか？
dir関数では本当のファイル名を拾えていないということなんでしょうか？
また、この前段でOn Error GoToでファイルが壊れて開かなかった場合のエラー処理はしているのですが、
こういう、ファイルが開かない以前の「見つからない」場合のエラー回避を
どう入れ込んだらいいかについても悩んでおります。
方向性だけでもご教示いただければ幸いです。
すべての返信 (6)
2018年8月28日火曜日 3:58 ✅回答済み
Windowsは内部でUnicodeを使用しています。Unicodeには結合文字という機能があり、「パ」に見える文字は
- U;30D1「パ」
- U;30CF「ハ」U;309A「゜」
の２種類が存在します。ところがVBAは内部でShift-JISを使用しています。前者の文字はShift-JISにも存在しているためVBAで扱うことができますが、後者の文字はShift-JISに存在しないためVBAで扱うことができません。文字列として扱えないため、当然そのような名前の付けられたファイルも扱うことはできません。
解決策としては、上記を認識した上で、問題のファイル名を変更することです。
2018年8月28日火曜日 4:26 ✅回答済み | 1 票
初めっからだったかは知りませんが、少なくともExcel 2007以降に付属するVBAでは、内部でUnicodeを使用しています。
ただDir関数は内部でANSI版のAPIを呼び出すので、Shift_JISに含まれない文字を持ったファイル名を正しく返せません。
FileSystemObjectならUnicodeファイル名も正しく扱えるので、これを使ってファイルを列挙すれば、それをWorkbooks.Openに渡すことができます。
// FileSystemObjectの使い方は検索すれば山ほど見つかるので省略。
2018年8月28日火曜日 4:53
「パブリック」と「パブリック」の違いは、目視では確認できませんね…。
dir関数では本当のファイル名を拾えていないということなんでしょうか？
VBA あるいは「16 bit 版以外の Visual Basic」では、文字列を Unicode 形式で保持しています。
(UCS-2 あるいはサロゲートペアを考慮しない UTF-16 相当)
ただし文字列の入出力時には、OS 既定のコードページ（日本語環境なら Shift_JIS 相当）に ANSI 変換されて扱われます。Dir 関数、Mid 関数、あるいは Excel VBA の Workbooks.Open などもそうです。
他の方の回答にもあるように、FileSystemObject を使えば、正しいファイル名で受け取れます。ただし Excel の Workbooks.Open や、VBA の Open ステートメントなどでは、非 Shift_JIS な文字列を扱えないという制限があります。
※最近のバージョンの Excel だと、Workbooks.Open でも Unicode パスを扱えるようですが。
代替案として、ショートファイルネーム(8;3形式)でアクセスするという方法があります。これならば、古いバージョンの Excel の Workbooks.Open でも扱える可能性があります（ショートファイルネームを無効化してあるボリュームだとロングファイルネームしか得られないので、万能な方法ではありませんが）。
参考までに、指定したフォルダ内のファイルを「正しいロングファイル名」と「ショートファイル名」と、Dir 相当の「文字化けしている可能性のあるファイル名」とで列挙するサンプルを載せておきます。これにより、文字化けしている可能性のファイルをチェックできますので、元のファイル名を Shift_JIS 相当にリネームする際の手助けになるかと。
Sheet1.Range("1:3").Clear
Dim baseDir As String
baseDir = "C:\TEMP\"
Dim FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
Dim row As Integer
row = 0
Dim f As Object
Dim s1 As String, s2 As String, s3 As String
For Each f In FSO.GetFolder(baseDir).Files
If f.Name Like "*.txt" Then
row = row ; 1
'本来のファイル名
s1 = f.Name
'ショートファイル名
s2 = f.ShortName
'Shift_JIS 表現のファイル名
s3 = StrConv(StrConv(s1, vbFromUnicode, 1041), vbUnicode, 1041)
Sheet1.Cells(row, 1) = s1 '本来のファイル名
Sheet1.Cells(row, 2) = s2 '8;3形式(ショートファイル名)
Sheet1.Cells(row, 3) = s3 'Dir で返却される文字列
If s1 <> s3 Then
Debug.Print row '文字化けした行
End If
End If
Next
MsgBox "done"
2018年8月28日火曜日 5:02
ただし Excel の Workbooks.Open や、VBA の Open ステートメントなどでは、非 Shift_JIS な文字列を扱えないという制限があります。
あれ、Excel 2007のVBAで試したところ、ファイル名にU;20BB7（いわゆる土吉）を含んだxlsxファイルをWorkbooks.Openで開くことができましたが……。
2018年8月28日火曜日 8:27
ご回答くださった皆様
まとめてのお;で失;いたします。
ほぼHongliangさまのアドバイスで解決できましたが、
WindowsとVBAの内部処理で文字コードが違うことや、
ショートファイルネームの利用の仕方等、
合わせてとても勉強させていただきました。
当方はExcel2016を使用しています。
問題の「ハ゜」のファイル名をFileSystemObjectで取得し、
そのままWorkbooks.Openに渡してみたところ、
ちゃんと「パ」としてファイルを開いてくれました。
本当にありがとうございました。
2018年8月29日水曜日 1:46 | 1 票
あれ、Excel 2007のVBAで試したところ、ファイル名にU;20BB7（いわゆる土吉）を含んだxlsxファイルをWorkbooks.Openで開くことができましたが……。
Hongliangさん、フォローありがとうございます。
2007 で問題ないということですので、現行バージョンで気にする必要はなさそうですね。
(Office 2010 未満は既にサポート期限が切れているので)
当方環境でも問題なく Workbooks.Open で開けることを確認していたので、
編集追記してみたものの、2 分差ですれ違いになってしまったようです。
古いバージョンだと NG な記憶があったので、一応
Excel 2002 (10.2614.2625) ; Win10 Pro 1803 (17134.112)
な Hyper-V 環境を準備して確認しましたが、こちらも問題なく Workbooks.Open で開けたので、バージョン依存というのは私の勘違いだったかもしれません。
ただし、マクロシートの OPEN 関数で開いてみた場合には、
Excel 2013 問題なく開かれた
Excel 2002 Unicode パスを渡すとクラッシュ
という状態になりましたので、バージョン問題があるとしてもその程度かと思います。
手持ちでは Excel 97 SR-1 (8.0d) な環境もあるのですが、こちらは検;できませんでした。載せている仮想 OS が Win98SE だったので、Unicode パスを準備できなかったという理由です。
Win16 時代の Excel 5.0 はどうだったのでしょうね。ANSI な 8;3 のみだったのかな。