昨日、苦労して「仕分くん」のバッチファイルを完成させたが、チェックが十二分に出来ないことがわかったので、日付が変わるころに起きだし、Excel2010で VBA化した。まだ改善の余地があるが、検証した結果、目的どおり動いたのでひとまず完成とした。
取扱説明書は大胆な挑戦をした人に作ってもらうことにしたい...
マクロ仕分くんのソース
- Option Explicit
- Const ShiwakeExe = "D:\Download\仕分ちゃん\shiwake.exe"
- '引用元:http://www.moug.net/tech/exvba/0150034.html
- '--- Win32 API 関数の宣言 ---
- Declare Function WaitForSingleObject Lib "kernel32" (ByVal hHandle As Long, _
- ByVal dwMilliseconds As Long) As Long
- Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, _
- ByVal bInheritHandle As Long, _
- ByVal dwProcessId As Long) As Long
- Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
- '--- Win32 API 定数の宣言 ---
- Public Const PROCESS_ALL_ACCESS As Long = &H1F0FFF
- Public Const INFINITE As Long = &HFFFF
- ' INIファイル文字列情報取得関数(API)の定義
- Public Declare Function GetPrivateProfileString Lib "kernel32" _
- Alias "GetPrivateProfileStringA" _
- (ByVal lpApplicationName As String, _
- ByVal lpKeyName As Any, _
- ByVal lpDefault As String, _
- ByVal lpReturnedString As String, _
- ByVal nSize As Long, _
- ByVal lpFileName As String) As Long
- Dim STRAns As String
- Dim VARAns As Variant
- Sub Auto_Open()
- Dim mbTitle As String
- Dim i As Long
- Dim shiwakeFolder As String
- Dim shiwakeSaki As String
- mbTitle = "Auto_Open/" & ThisWorkbook.Name
- shiwakeFolder = getFolderName()
- If shiwakeFolder = "" Then Exit Sub
- i = InStrRev(shiwakeFolder, "\")
- If i = 0 Then GoTo Exit_AutoOpen
- STRAns = Mid(shiwakeFolder, i + 1)
- If STRAns <> "DCIM" Then
- MsgBox "このフォルダーは仕分けることはできません。" & vbCrLf & vbCrLf _
- & "フォルダー名=" & shiwakeFolder, vbCritical, mbTitle
- GoTo Exit_AutoOpen
- End If
- Call runShiwake(shiwakeFolder)
- shiwakeSaki = getShiwakeSaki()
- Call runExplorer(shiwakeSaki)
- Exit_AutoOpen:
- Application.Quit
- ThisWorkbook.Close
- End Sub
- ' INIファイルから文字列情報を取得する関数
- ' 返り値:取得データ
- ' 引き数:FName - .iniファイル名
- ' SName - セクション名
- ' KName - キー名
- ' Default - 取得に失敗したときや該当する項目が無かった時の戻り値
- Public Function ReadIni(ByVal FName As String, ByVal SName As String, _
- ByVal KName As String, ByVal default As String) As String
- Dim RtnCD As Long
- Dim RtnStr As String
- ' GetPrivateProfileString APIを利用し、INIファイルから情報取得
- RtnStr = Space$(256)
- RtnCD = GetPrivateProfileString(SName, KName, default, RtnStr, 255, _
- FName)
- ' 戻り値設定
- If RtnCD > 0 Then
- If InStr(RtnStr, Chr$(0)) > 0 Then
- ReadIni = Left$(RtnStr, InStr(RtnStr, Chr$(0)) - 1)
- Else
- ReadIni = ""
- End If
- Else
- ReadIni = default
- End If
- End Function
- Function getShiwakeSaki() As String
- Const iniName = "settings.ini" ' INIファイル名
- Const secName = "settings" ' セクション名
- Const keyName = "001" ' キー名
- Const default = "" ' デフォルト値
- Dim i As String
- Dim iniFileName As String
- iniFileName = ThisWorkbook.Path & "\files\" & iniName
- STRAns = ReadIni(iniFileName, secName, keyName, "")
- i = InStrRev(STRAns, "\")
- getShiwakeSaki = Left(STRAns, i - 1)
- End Function
- '引用:http://www.moug.net/tech/exvba/0150086.html
- Sub runExplorer(argFolder As String)
- Dim environmentString As String
- Dim i As Long
- i = 1
- Do
- environmentString = Environ(i)
- If Left(UCase(environmentString), 7) = "WINDIR=" Then
- STRAns = Mid(environmentString, 8, Len(environmentString))
- Exit Do
- End If
- i = i + 1
- Loop Until Environ(i) = ""
- VARAns = Shell(STRAns & "\Explorer.exe /e, " & argFolder, vbNormalFocus)
- End Sub
- Sub runShiwake(argPath As String)
- Dim TaskId As Long 'タスクID
- Dim hProc As Long 'プロセスハンドル
- ' 外部プログラムの実行
- TaskId = Shell(ShiwakeExe & " " & argPath, vbNormalFocus)
- ' プロセスハンドルの取得
- hProc = OpenProcess(PROCESS_ALL_ACCESS, 0, TaskId)
- ' プロセスハンドルが返されたかを判定
- If hProc <> 0 Then
- ' プロセスのシグナル待ち
- Call WaitForSingleObject(hProc, INFINITE)
- 'プロセスクローズ
- CloseHandle hProc
- End If
- End Sub
- Function getFolderName() As String
- With Application.FileDialog(msoFileDialogFolderPicker)
- If .Show = True Then
- getFolderName = .SelectedItems(1)
- End If
- End With
- End Function