1Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
UserForm1
lang
1'''
2''' キャンセルフラグ
3'''
4Private IsCancel As Boolean
56'''
7''' フォームロード
8'''
9Private Sub UserForm_Initialize()
10 Dim CellText As New Collection
11 Dim MaxRowNumber As Integer
12 Dim i As Integer
13 If Worksheets(2).Range("A1").Text <> "" Then
14 MaxRowNumber = Worksheets(2).Cells(Rows.Count, 1).End(xlUp).Row
15 For i = 0 To MaxRowNumber - 1
16 ListBox1.AddItem Worksheets(2).Cells(i + 1, 1).Value
17 Next i
18 SetListData
19 End If
20End Sub
2122'''
23''' 印刷ボタン
24'''
25Private Sub CommandButton1_Click()
26 Const cnsDIR = "*.xls" ' 対象ファイル名(ワイルドカード)
27 Dim strPathName As String ' ディレクトリPath
28 Dim strFullPath As String ' フルPath
29 Dim rc As Integer
30 Dim xlApp As Excel.Application
31 Dim Fso As Object
32 Dim ado As Object
3334On Error GoTo ErrorHandler
35 IsCancel = False
36 With Application.FileDialog(msoFileDialogFolderPicker)
37 If .Show Then strPathName = .SelectedItems(1)
38 End With
39 rc = MsgBox("印刷ジョブに登録しますか?", vbYesNo + vbQuestion, "確認")
40 If rc = vbNo Then
41 MsgBox "処理をキャンセルしました。", vbInformation, "処理終了"
42 Exit Sub
43 End If
4445 Set xlApp = CreateObject("Excel.Application")
46 Set ado = CreateObject("ADODB.Recordset")
47 Set Fso = CreateObject("Scripting.FileSystemObject")
4849 ado.Fields.Append "FILENUMBER", 200, 300, 32 ' 番号
50 ado.Fields.Append "FILENAME", 200, 300, 32 ' ファイル名
51 ado.Open
5253 ' GetFolderでパスを取得しAdoに格納
54 Dim file As Object
55 For Each file In Fso.GetFolder(strPathName).Files
56 ' 登録ファイルチェック
57 If IsOutPutFile(file) Then
58 ' ファイル名は以下の形式
59 ' (番号)_(○○○)_(×××).xls
60 ado.AddNew
61 ado.Fields(0) = padZero(CInt(Split(Dir(file), "_")(0)), 4) ' 番号(0埋)
62 ado.Fields(1) = file ' フルPATH
63 ado.Update
64 End If
65 Next
6667 ado.Sort = "FILENUMBER ASC" ' FILENUMBERでソート
68 ado.MoveFirst
6970 Do Until ado.EOF
71 xlApp.Workbooks.Open FileName:=CStr(ado.Fields(1)), UpdateLinks:=0 ' Excelを開く
72 DoEvents
73 If IsCancel Then
74 If IsOpenBook(CStr(ado.Fields(1))) And Not xlApp Is Nothing Then
75 xlApp.DisplayAlerts = False
76 xlApp.Workbooks.Close ' Excelを閉じる
77 xlApp.DisplayAlerts = True
78 End If
79 If Not Fso Is Nothing Then Set Fso = Nothing
80 If Not ado Is Nothing Then Set ado = Nothing
81 If Not xlApp Is Nothing Then Set xlApp = Nothing
82 MsgBox "処理を中断しました。", vbInformation, "処理中断"
83 Exit Sub
84 End If
85 xlApp.Visible = False ' 非表示
86 xlApp.ActiveWorkbook.PrintOut ' 印刷
87 xlApp.DisplayAlerts = False
88 xlApp.Workbooks.Close ' Excelを閉じる
89 xlApp.DisplayAlerts = True
90 Sleep 1 ' CPU使用率考慮
91 ado.MoveNext
92 Loop
9394 ado.Close
95 Set xlApp = Nothing
96 Set ado = Nothing
97 Set Fso = Nothing
9899 MsgBox "印刷ジョブに登録しました。", vbInformation, "処理終了"
100 Exit Sub
101ErrorHandler:
102 If Not Fso Is Nothing Then Set Fso = Nothing
103 If Not ado Is Nothing Then Set ado = Nothing
104 If Not xlApp Is Nothing Then Set xlApp = Nothing
105 MsgBox Err.Number & ":" & Err.Description, vbCritical & vbOKOnly, "例外発生"
106End Sub
107108'''
109''' キャンセルボタン
110'''
111Private Sub CommandButton4_Click()
112 IsCancel = True
113End Sub
114115'''
116''' 閉じるボタン
117'''
118Private Sub CommandButton2_Click()
119 Unload UserForm1
120End Sub
121122'''
123''' 追加ボタン
124'''
125Private Sub CommandButton3_Click()
126 Dim i As Integer
127 For i = 0 To ListBox1.ListCount - 1
128 If ListBox1.List(i) = TextBox1.Text Then
129 MsgBox "既に登録済みです。", vbCritical & vbOKOnly, "重複エラー"
130 Exit Sub
131 End If
132 Next
133 ListBox1.AddItem TextBox1.Text
134 TextBox1.Text = ""
135 SetListData
136End Sub
137138'''
139''' 削除ボタン
140'''
141Private Sub CommandButton5_Click()
142 Dim i As Integer
143 For i = 0 To ListBox1.ListCount - 1
144 If ListBox1.Selected(i) Then
145 ListBox1.RemoveItem (i)
146 Exit For
147 End If
148 Next
149 SetListData
150End Sub
151152'''
153''' ゼロ埋め処理
154'''
155Private Function padZero(n As Integer, keta As Integer)
156 padZero = Right(n + 10 ^ keta, keta)
157End Function
158159'''
160''' Bookが開かれているか判別
161'''
162Private Function IsOpenBook(strFullPath As String) As Boolean
163 On Error Resume Next
164 Open strFullPath For Append As #1
165 Close #1
166 IsOpenBook = Err.Number > 0
167End Function
168169'''
170''' 除外ファイル名登録
171'''
172Private Sub SetListData()
173 Worksheets(2).Columns("A").Delete
174 For i = 0 To ListBox1.ListCount - 1
175 Worksheets(2).Cells(i + 1, 1).Value = ListBox1.List(i)
176 Next
177 ThisWorkbook.Save
178End Sub
179180'''
181''' 登録ファイルチェック
182'''
183Private Function IsOutPutFile(file As Object) As Boolean
184 IsOutPutFile = True
185 For i = 0 To ListBox1.ListCount - 1
186 If Dir(file) Like ("*" & ListBox1.List(i) & "*") Then
187 IsOutPutFile = False ' 除外ファイル名が含まれていた場合は登録フラグOFF
188 Exit For
189 End If
190 Next
191End Function
192