Option Explicit
Sub Main()
Dim toolWb As Workbook
Dim intMaxRow As Integer
Dim intCount As Integer
Call checkBlankCell
If strBlankMsg <> "" Then
MsgBox "以下のセルの空欄を入力してから再実行してください" & vbCrLf & strBlankMsg
Exit Sub
End If
Set toolWb = ThisWorkbook
With Sheets(strMasterSheetName)
strParentFolderPath = .Range(strParentFolderCell)
Debug.Print "親フォルダパス:" & strParentFolderPath
strRingiFolderPath = .Range(strRingiFolderCell)
Debug.Print "稟議フォルダパス:" & strRingiFolderPath
End With
If fncIsExistsDir(strParentFolderPath) = False Then
MsgBox "派遣会社フォルダパスに指定されたフォルダは存在しません。" & vbCrLf & _
"正しいパスを" & strParentFolderCell & "セルに入力しなおしてから再実行してください" & _
vbCrLf & vbCrLf & strParentFolderPath
Exit Sub
End If
If fncIsExistsDir(strRingiFolderPath) = False Then
MsgBox "稟議フォルダパスに指定されたフォルダは存在しません。" & vbCrLf & _
"正しいパスを" & strRingiFolderCell & "セルに入力しなおしてから再実行してください" & _
vbCrLf & vbCrLf & strRingiFolderPath
Exit Sub
End If
Call 稟議ファイル有無結果クリア
Call 一覧表をクリア
strPassWord = InputBox("パスワード入力", "エクセルファイルのパスワードを入力してください。")
If strPassWord = "" Then
MsgBox "キャンセルします"
Exit Sub
End If
Call checkRingiFile
Call GetSubfolder(strParentFolderPath)
With toolWb.Sheets(strToolSheetName)
intMaxRow = .Cells(Rows.Count, intSubFolderColumn).End(xlUp).Row
Debug.Print "最終行:" & intMaxRow
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For intCount = intSubFolderStartRow To intMaxRow
Call 初期化
strSubFolderName = .Cells(intCount, intSubFolderColumn)
Debug.Print vbCrLf & "フォルダ名" + strSubFolderName
strSeikyuNamePattern = Sheets(strConfigSheetName).Range(strSeikyuPdfNameCell)
strMeisaiNamePattern = Sheets(strConfigSheetName).Range(strMeisaiPdfNameCell)
strSeikyuNamePattern = ".*" & strSeikyuNamePattern & ".*" & "\.pdf"
strMeisaiNamePattern = ".*" & strMeisaiNamePattern & ".*" & "\.pdf"
blSeikyuPdfExists = fncCheckPdfFileName(strSubFolderName, strSeikyuNamePattern)
blMeisaiPdfExists = fncCheckPdfFileName(strSubFolderName, strMeisaiNamePattern)
Debug.Print "請求書pdf存在:" & blSeikyuPdfExists
Debug.Print "請求明細書pdf存在:" & blMeisaiPdfExists
If blSeikyuPdfExists = False Then
strErrMsg = "請求書PDFがありません"
blErrFlag = True
End If
strMeisaiNamePattern = .Range(strMeisaiFileNameCell)
If blMeisaiPdfExists = False Then
blMeisaiExcelExists = fncCheckExcelFileName(strSubFolderName, strMeisaiNamePattern)
End If
If blMeisaiExcelExists = True Then
blCheckBookOpened = IsBookOpened(strParentFolderPath & "\" & strSubFolderName & "\" & strMeisaiExcelPath)
Debug.Print "明細エクセル開かれているか?:" & blCheckBookOpened
End If
If blMeisaiPdfExists = False And blCheckBookOpened = True Then
Call MakeErrMsg("請求明細PDFがありません/請求明細エクセルが開かれているため処理できませんでした")
blErrFlag = True
End If
If blMeisaiPdfExists = False And blMeisaiExcelExists = False Then
Call MakeErrMsg("請求明細PDFがありません/請求明細エクセルがありません")
blErrFlag = True
End If
Debug.Print "請求明細xlsx存在:" & blMeisaiExcelExists
strMeisaiNamePattern = Sheets(strConfigSheetName).Range(strMeisaiSheetNameCell)
If blMeisaiPdfExists = False And blMeisaiExcelExists And blCheckBookOpened = False Then
Debug.Print "請求明細書エクセルパス:" & strMeisaiExcelPath
blMeisaiSheetExists = fncSheetExsists(strMeisaiExcelPath, strMeisaiNamePattern)
Debug.Print "請求書明細書シート存在有無:" & blMeisaiSheetExists
End If
If blMeisaiPdfExists = False And blMeisaiExcelExists And blMeisaiSheetExists = False And blCheckBookOpened = False Then
Call MakeErrMsg("請求明細書PDFがありません" & vbCrLf & _
strMeisaiExcelPath & "に" & strMeisaiNamePattern & "を含むシート名が存在しません")
blErrFlag = True
End If
strMeisaiNamePattern = Sheets(strConfigSheetName).Range(strMeisaiPdfNameCell)
If blMeisaiPdfExists = False And blMeisaiSheetExists = True Then
strMeisaiPdfPath = strParentFolderPath & "\" & strSubFolderName & "\【請求明細書】_" & strMeisaiNamePattern & "_" & strSubFolderName & ".pdf"
strMeisaiExcelPath = strParentFolderPath & "\" & strSubFolderName & "\" & strMeisaiExcelPath
Call outputPDF(strMeisaiExcelPath, strMeisaiPdfPath, strMeisaiSheetName)
End If
If blErrFlag Then
.Cells(intCount, intOutputResultColumn) = "NG"
.Cells(intCount, intCauseErrrColumn) = strErrMsg
Else
.Cells(intCount, intOutputResultColumn) = "OK"
End If
Next intCount
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End With
MsgBox "作業完了しました"
End Sub
Sub outputPDF(strExcelPath As String, strPdfPath As String, strTargetSheetName As String)
Dim wb As Workbook
Dim strSheetName As String
Debug.Print "変換前エクセルパス:" & strExcelPath
Debug.Print "変換後PDFパス:" & strPdfPath
Set wb = Workbooks.Open(strExcelPath, Password:=strPassWord, WriteResPassword:=strPassWord)
With wb.Sheets(strTargetSheetName).PageSetup
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
wb.Sheets(strTargetSheetName).ExportAsFixedFormat Type:=xlTypePDF, Filename:=strPdfPath
wb.Close
Set wb = Nothing
End Sub
Sub checkBlankCell()
strBlankMsg = ""
Erase arrayBlankCell
Sheets(strMasterSheetName).Select
If Sheets(strMasterSheetName).Range(strParentFolderCell) = "" Then
arrayBlankCell(0) = strParentFolderCell & ":派遣会社フォルダパス"
End If
With Sheets(strConfigSheetName)
If .Range(strSeikyuPdfNameCell) = "" Then
arrayBlankCell(1) = strSeikyuPdfNameCell & ":請求書PDFファイル名に含まれる文言"
End If
If .Range(strMeisaiPdfNameCell) = "" Then
arrayBlankCell(2) = strMeisaiPdfNameCell & ":明細書PDFファイル名に含まれる文言"
End If
If .Range(strMeisaiFileNameCell) = "" Then
arrayBlankCell(3) = strMeisaiFileNameCell & ":明細書Excelファイル名に含まれる文言"
End If
If .Range(strMeisaiSheetNameCell) = "" Then
arrayBlankCell(4) = strMeisaiSheetNameCell & ":明細書シート名に含まれる文言"
End If
End With
Dim i As Integer
For i = LBound(arrayBlankCell) To UBound(arrayBlankCell)
If arrayBlankCell(i) <> "" Then
strBlankMsg = strBlankMsg & arrayBlankCell(i) & vbCrLf
End If
Next
End Sub
Function fncIsExistsDir(targetPath As String) As Boolean
With CreateObject("Scripting.FileSystemObject")
If .FolderExists(targetPath) Then
fncIsExistsDir = True
Else
fncIsExistsDir = False
End If
End With
End Function
Sub GetSubfolder(strParentFolderPath As String)
Dim f As Object
Dim i As Integer
i = intSubFolderStartRow
With CreateObject("Scripting.FileSystemObject")
For Each f In .GetFolder(strParentFolderPath).SubFolders
Sheets(strToolSheetName).Cells(i, 3) = f.Name
Debug.Print f.Name
i = i + 1
Next f
End With
End Sub
Sub 初期化()
strSeikyuPdfPath = ""
strMeisaiPdfPath = ""
strMeisaiExcelPath = ""
strSubFolderPath = ""
strSubFolderName = ""
blCreatePdf = False
blSeikyuExcelExists = False
blSeikyuPdfExists = False
blSeikyuSheetExists = False
blMeisaiPdfExists = False
blMeisaiExcelExists = False
blMeisaiSheetExists = False
blErrFlag = False
blCheckBookOpened = False
strErrMsg = ""
End Sub
Function fncCheckPdfFileName(strFolderName As String, strPattern As String) As Boolean
Dim blResult As Boolean
Dim Filename As String
Dim objReg As Object
Set objReg = CreateObject("VBScript.RegExp")
objReg.Global = True
objReg.Pattern = strPattern
Filename = Dir(strParentFolderPath & "\" & strFolderName & "\" & "*.pdf")
Do While Filename <> ""
fncCheckPdfFileName = objReg.test(Filename)
If fncCheckPdfFileName Then
Exit Do
End If
Filename = Dir()
Loop
Set objReg = Nothing
End Function
Function fncCheckExcelFileName(strFolderName As String, strPattern As String) As Boolean
Dim blResult As Boolean
Dim Filename As String
Dim objReg As Object
Filename = Dir(strParentFolderPath & "\" & strFolderName & "\" & "*.xls*")
Do While Filename <> ""
If InStr(Filename, strPattern) > 0 Then
strMeisaiExcelPath = Filename
fncCheckExcelFileName = True
Exit Do
End If
Filename = Dir()
Loop
Set objReg = Nothing
End Function
Sub selectFolderPath()
Dim targetPath As String
Dim objDialog As Object
Set objDialog = Application.FileDialog(msoFileDialogFolderPicker)
If objDialog.Show Then
targetPath = objDialog.SelectedItems(1)
Sheets("Sheet1").Range("C3") = targetPath
Else
MsgBox "フォルダ選択キャンセル"
End If
End Sub
Function fncSheetExsists(strpath As String, strPattern As String) As Boolean
fncSheetExsists = False
Dim wb As Workbook
Dim shCount As Integer
Dim sh As Variant
Dim objReg As Object
Dim strShName As String
Dim strFileType As String
Debug.Print "対象ブックパス:" & strParentFolderPath & "\" & strSubFolderName & "\" & strpath
Debug.Print "フラグ :" & blCheckBookOpened
Set wb = Workbooks.Open(strParentFolderPath & "\" & strSubFolderName & "\" & strpath, _
, Password:=strPassWord, WriteResPassword:=strPassWord)
shCount = wb.Sheets.Count
For Each sh In wb.Sheets
If InStr(sh.Name, strPattern) > 0 Then
strMeisaiSheetName = sh.Name
fncSheetExsists = True
Exit For
End If
Next
wb.Close
Set wb = Nothing
Set objReg = Nothing
End Function
Public Function IsBookOpened(ByVal FilePath As String) As Boolean
On Error Resume Next
Open FilePath For Append As #1
Close #1
If Err.Number > 0 Then
IsBookOpened = True
End If
End Function
Sub MakeErrMsg(addMsg As String)
Debug.Print "strErrMsg:" & strErrMsg
Debug.Print "addMsg :" & addMsg
If strErrMsg = "" Then
strErrMsg = addMsg
Else
strErrMsg = strErrMsg & vbCrLf & addMsg
End If
Debug.Print "strErrMsg:" & strErrMsg
End Sub
Sub checkRingiFile()
Dim intCount As Integer
Dim intLastRow As Integer
Dim arrayIndex As Integer
Dim i As Integer
Dim f As Object
Dim blRingiFileExists As Boolean
Dim strRingiErrMsg As String
With Sheets(strConfigSheetName)
intLastRow = .Cells(Rows.Count, intRingiNoColumn).End(xlUp).Row
If intLastRow = intRingiNoStartRow - 1 Then
Call MakeErrMsg(strConfigSheetName & "に稟議番号の記載がないため判定不可")
Exit Sub
End If
arrayIndex = 0
For i = intRingiNoStartRow To intLastRow
ReDim Preserve arrayRingiNo(arrayIndex)
arrayRingiNo(arrayIndex) = .Cells(i, intRingiNoColumn)
arrayIndex = arrayIndex + 1
Next
With CreateObject("Scripting.FileSystemObject")
For i = LBound(arrayRingiNo) To UBound(arrayRingiNo)
blRingiFileExists = False
For Each f In .GetFolder(strRingiFolderPath).Files
If InStr(f.Name, arrayRingiNo(i)) > 0 Then
blRingiFileExists = True
Exit For
End If
Next f
If blRingiFileExists = False Then
If strRingiErrMsg = "" Then
strRingiErrMsg = "稟議番号:" & arrayRingiNo(i) & "のPDFファイルがありません"
Else
strRingiErrMsg = strRingiErrMsg & vbCrLf & "稟議番号:" & arrayRingiNo(i) & "のPDFファイルがありません"
End If
End If
Debug.Print "稟議番号" & arrayRingiNo(i)
Debug.Print "稟議有無フラグ" & blRingiFileExists
Debug.Print "稟議エラーメッセージ:"; strRingiErrMsg
Next i
End With
End With
With Sheets(strToolSheetName)
If strRingiErrMsg <> "" Then
.Range(strRingiNoReultCell) = "NG"
.Range(strRingiNoCauseErrCell) = strRingiErrMsg
Else
.Range(strRingiNoReultCell) = "OK"
End If
End With
End Sub