VBA – ページ 6 – テックメモ

2017-05-18

VBA 生産高集計

以前作った生産高を集計するファイルにそのまま機能を追加。

エクセルの工程管理でガントチャートっぽく運用している場合、
罫線で表を作って、色で進捗を管理する組み合わせは面倒になりやすい。

罫線をオンオフできると管理が楽になる。

Sub 罫線トグル()

Application.ScreenUpdating = False

asn = ActiveSheet.Name

Dim sc As Integer ' シート数

sc = ActiveWorkbook.Sheets.Count

ReDim sn(sc, 2) As String 'シート名,フォーム数

For i = 1 To sc Step 1
  If Sheets(i).Name <> "集計" Then
    sn(i, 1) = Sheets(i).Name
    sn(i, 2) = fcount(Sheets(i).Name)
    If sn(i, 2) = -1 Then
      MsgBox sn(i, 1) & "シートが適切な工程表ではないため罫線の描画ができません。"
      Exit Sub
    End If
  End If
Next i

If Sheets(1).Range("H5").Borders(xlEdgeRight).LineStyle = -4142 Then '罫線無し
  borderState = False
ElseIf Sheets(1).Range("H5").Borders(xlEdgeRight).LineStyle = -4118 Then '罫線有り
  borderState = True
End If

For i = 1 To sc Step 1
  Call BorderToggle(sn(i, 1), sn(i, 2), borderState) 'シート名,フォーム数
Next i

Sheets(asn).Activate

Application.ScreenUpdating = True
MsgBox "終了しました。"

End Sub

Sub BorderToggle(sheetName, formQty, borderState)

If sheetName = "" Then Exit Sub

With Sheets(sheetName)

If borderState = True Then '罫線有り

  rowTop = 5
  rowBtn = 24

  For f = 1 To formQty
  
    .Range(.Cells(rowTop, 8), .Cells(rowBtn, 100)).Borders.LineStyle = xlLineStyleNone
    '斜線は個別に処理しないと消えなかった。
    .Range(.Cells(rowTop, 8), .Cells(rowBtn, 100)).Borders(xlDiagonalDown).LineStyle = -4142
    .Range(.Cells(rowTop, 8), .Cells(rowBtn, 100)).Borders(xlDiagonalUp).LineStyle = -4142
    
    .Range(.Cells(rowTop, 8), .Cells(rowBtn, 100)).Borders(xlEdgeTop).LineStyle = 1
    .Range(.Cells(rowTop, 8), .Cells(rowBtn, 100)).Borders(xlEdgeRight).LineStyle = 1
    .Range(.Cells(rowTop, 8), .Cells(rowBtn, 100)).Borders(xlEdgeBottom).LineStyle = 1
    .Range(.Cells(rowTop, 8), .Cells(rowBtn, 100)).Borders(xlEdgeLeft).LineStyle = 1
    
    rowTop = rowTop + 25
    rowBtn = rowBtn + 25
  
  Next f

End If

If borderState = False Then '罫線無し

  rowTop = 5
  rowBtn = 24

  For f = 1 To formQty
  
    .Range(.Cells(rowTop, 8), .Cells(rowBtn, 100)).Borders(xlInsideVertical).LineStyle = -4118
    .Range(.Cells(rowTop, 8), .Cells(rowBtn, 100)).Borders(xlInsideHorizontal).LineStyle = 1
        
    '縦のWeightが違う線の処理
    For col = 10 To 97 Step 3
    
      .Range(.Cells(rowTop, col), .Cells(rowBtn, col)).Borders(xlEdgeRight).LineStyle = 1
      
      Select Case col
      Case 22, 40, 55, 70, 85
        .Range(.Cells(rowTop, col), .Cells(rowBtn, col)).Borders(xlEdgeRight).Weight = -4138
      End Select
       
    Next col

    For col = 8 To 38 Step 3
        
    '月末部の斜線処理
    If .Cells(4, col).Value = "" Then
      .Range(.Cells(rowTop, col), .Cells(rowBtn, col + 2)).Borders(xlDiagonalUp).LineStyle = 1
    End If
       
    Next col
    
    rowTop = rowTop + 25
    rowBtn = rowBtn + 25
    
  Next f
  
End If

End With

End Sub

Sub 生産高集計()

'[集計]というシートがあると削除
'個別シートのフォーム数はA列で判断
'（24なら1つ49なら2つというように探す、フォームが崩れているとエラーを返す）
'B列が記入されていて、尚且つ[数量]と[単価]が記入されている行のみ処理
'日付はG列に記入があれば、そちらを使用、なければF列を使用

Application.ScreenUpdating = False

For Each ws In Worksheets
    If ws.Name = "集計" Then
        If vbYes = MsgBox("集計シートが存在します。削除しますか？", vbYesNo) Then
            Worksheets("集計").Delete
        Else
            Exit Sub
        End If
    End If
Next ws

Dim sc As Integer ' シート数

sc = ActiveWorkbook.Sheets.Count

ReDim sn(sc, 2) As String 'シート名,フォーム数

For i = 1 To sc Step 1
    
    sn(i, 1) = Sheets(i).Name
    sn(i, 2) = fcount(sn(i, 1)) ' fcount call
    
    If sn(i, 2) = -1 Then
        MsgBox sn(i, 1) & "シートが適切な工程表ではないため集計できません。"
        Exit Sub
    End If
    
Next i

Sheets.Add after:=ActiveSheet
ActiveSheet.Name = "集計"

If 納期集計(sn(1, 1)) = -1 Then Exit Sub '納期集計 call

For i = 1 To sc Step 1
  Call main(sn(i, 1), sn(i, 2), i) 'シート名,フォーム数,カウンタ
  Call main2(sn(i, 1), sn(i, 2), i) 'シート名,フォーム数,カウンタ
Next i

With Sheets("集計")
.Range("J1").Value = "班名"
.Range("K1").Value = "生産額"
.Range("L1").Value = "目盛"
.Range("M1").Value = "生産日数"
.Range("N1").Value = "生産額/日"
.Range("P1").Value = "目盛"
.Range("Q1").Value = "未生産日数"
.Columns("A:Q").AutoFit
.Activate
End With

Application.ScreenUpdating = True
MsgBox "終了しました。"

End Sub

Sub main(ByVal sn As String, ByVal fc As Integer, ByVal c As Integer) 'シート名,フォーム数,カウンタ

Worksheets("集計").Cells(c, 1).Value = sn

Worksheets("集計").Cells(c + 1, 10).Value = sn

Worksheets(sn).Activate

buf = 0

For r = (fc * 25 - 1) To 5 Step -1 '-

    If ActiveSheet.Range("B" & r & "").Value <> "" Then  '型式が記入されている '--
    
     'ActiveSheet.Range("B" & r & "").Select
     
        If IsNumeric(ActiveSheet.Range("E" & r & "").Value) And IsNumeric(ActiveSheet.Range("CZ" & r & "").Value) Then '数量と単価が数値 '---
        
            buf = buf + (ActiveSheet.Range("E" & r & "").Value * ActiveSheet.Range("CZ" & r & "").Value)
            
            'ここから顧客別金額 -------------------------------------------------
            
            If ActiveSheet.Range("CX" & r & "").Value = "" Then
                        
                cus = "空白"
                        
            Else

                cus = 文字列調整(ActiveSheet.Range("CX" & r & "").Value)
            
            End If
            
            pri = ActiveSheet.Range("E" & r & "").Value * ActiveSheet.Range("CZ" & r & "").Value
            
            i = 1
            
            Do While Worksheets("集計").Range("D" & i & "").Value <> ""
            
                If Worksheets("集計").Range("D" & i & "").Value = cus Then
                
                    Worksheets("集計").Range("E" & i & "").NumberFormatLocal = "#,##0"
                
                    Worksheets("集計").Range("E" & i & "").Value = Worksheets("集計").Range("E" & i & "").Value + pri
                    
                    pri = 0
                
                End If
                
                i = i + 1
            
            Loop
            
            If pri <> 0 Then
            
                Worksheets("集計").Range("D" & i & "").Value = cus
                
                Worksheets("集計").Range("E" & i & "").NumberFormatLocal = "#,##0"
                
                Worksheets("集計").Range("E" & i & "").Value = pri
            
            End If
            
            'ここまで顧客別金額  ここから納期別金額 -------------------------------------------------
            
            lt = ActiveSheet.Range("F" & r & "").Value

            If ActiveSheet.Range("G" & r & "").Value <> "" Then lt = ActiveSheet.Range("G" & r & "").Value
 
            If IsDate(lt) = False Then
            
                Worksheets("集計").Range("H13").NumberFormatLocal = "#,##0"
                
                Worksheets("集計").Range("H13").Value = Worksheets("集計").Range("H13").Value + (ActiveSheet.Range("E" & r & "").Value * ActiveSheet.Range("CZ" & r & "").Value)
            
            End If
            
            If IsDate(lt) Then
            
                m = Month(lt)
                    
                d = Day(lt)
                
                Select Case d
                
                    Case 21 To 31
                    
                        m = m + 1
                        
                        If m = 13 Then m = 1
                    
                    Case 1 To 20
                
                End Select
                
                For i = 1 To 12
                
                    If m = Worksheets("集計").Range("G" & i & "").Value Then
                    
                        Worksheets("集計").Range("H" & i & "").NumberFormatLocal = "#,##0"
                    
                        Worksheets("集計").Range("H" & i & "").Value = Worksheets("集計").Range("H" & i & "").Value + (ActiveSheet.Range("E" & r & "").Value * ActiveSheet.Range("CZ" & r & "").Value)
                    
                    End If
                
                Next i
                        
            End If
            
            'ここまで納期別金額 -------------------------------------------------
                    
        End If '---
    
    End If '--
    
    Select Case r
    
        Case 105, 80, 55, 30
    
        r = r - 5
    
    End Select
        
Next r ' -

Worksheets("集計").Cells(c, 2).NumberFormatLocal = "#,##0"
Worksheets("集計").Cells(c, 2).Value = buf
Worksheets("集計").Cells(c + 1, 11).NumberFormatLocal = "#,##0"
Worksheets("集計").Cells(c + 1, 11).Value = buf

End Sub

Function fcount(ByVal sn As String) As Integer

Dim rc As Integer '行数
Dim fc As Integer 'フォーム数

Sheets(sn).Activate

rc = ActiveSheet.UsedRange.Rows.Count

For i = ActiveSheet.UsedRange.Rows.Count To 1 Step -1 'フォーム数を確認(A列で判断)

    If Range("A" & i & "").Value <> "" Then Exit For

Next i

Select Case i

    Case 24
            
        fc = 1
    
    Case 49
        
        fc = 2
    
    Case 74
        
        fc = 3
    
    Case 99
        
        fc = 4
    
    Case 124
        
        fc = 5

    Case Else

        fc = -1
        
End Select

fcount = fc
    
End Function

Function 文字列調整(ByVal cus_str As String)

    cus_str = Replace(cus_str, vbLf, "")

    cus_str = StrConv(cus_str, vbWide)
    
    cus_str = Replace(cus_str, "　　　", "　") '空白4つまで1つに置換する
    
    cus_str = Replace(cus_str, "　　", "　")
    
    cus_str = Trim(cus_str)
    
    cus_str = Replace(cus_str, "㈱", "")
        
    cus_str = Replace(cus_str, "㈲", "")
    
    文字列調整 = cus_str

End Function

Function 納期集計(ByVal sn As String)

mth = Replace(Sheets(sn).Range("H2").Value, "月", "")

Select Case mth

    Case 1 To 12
    
        For i = 1 To 12
    
            Sheets("集計").Range("G" & i & "").Value = mth + i
            
            If Sheets("集計").Range("G" & i & "").Value > 12 Then
            
                Sheets("集計").Range("G" & i & "").Value = Sheets("集計").Range("G" & i & "").Value - 12
            
            End If
        
        Next i
    
    Case Else
    
        MsgBox "月が正しく記入されていません。"
    
        mth = -1

End Select

Sheets("集計").Range("G13").Value = "未定"

納期集計 = mth

End Function

Sub main2(ByVal sn As String, ByVal fc As Integer, ByVal c As Integer) 'シート名,フォーム数,カウンタ

Worksheets(sn).Activate

buf1 = 0

buf2 = 0

For cc = 8 To 100

    For r = 5 To (fc * 25 - 1)
    
        If ActiveSheet.Cells(4, cc).Interior.Color = RGB(255, 255, 0) Then Exit For
        
        If ActiveSheet.Cells(r, cc).Interior.Color = RGB(0, 0, 0) Then
        
            buf1 = buf1 + 1 '黒塗りつぶし
        
            Exit For
            
        End If
    
        'ActiveSheet.Cells(r, cc).Value = r & "," & cc
        
        If r = (fc * 25 - 1) Then buf2 = buf2 + 1 '塗りつぶしなし
    
        Select Case r
        
            Case 99, 74, 49, 24
        
            r = r + 5
        
        End Select
        
    Next r

Next cc

Worksheets("集計").Cells(c + 1, 12).Value = buf1 '目盛

Worksheets("集計").Cells(c + 1, 13).NumberFormatLocal = "0.0" '

Worksheets("集計").Cells(c + 1, 13).Value = buf1 / 3 '生産日数

Worksheets("集計").Cells(c + 1, 14).NumberFormatLocal = "#,##0"

If Worksheets("集計").Cells(c + 1, 13) <> 0 Then

    Worksheets("集計").Cells(c + 1, 14).Value = (Worksheets("集計").Cells(c + 1, 11)) / (Worksheets("集計").Cells(c + 1, 13)) '生産額/日

End If

Worksheets("集計").Cells(c + 1, 16).Value = buf2

Worksheets("集計").Cells(c + 1, 17).NumberFormatLocal = "0.0" '

Worksheets("集計").Cells(c + 1, 17).Value = buf2 / 3  '未生産日数

End Sub

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

Sub 罫線トグル()

Application.ScreenUpdating = False

asn = ActiveSheet.Name

Dim sc As Integer ' シート数

sc = ActiveWorkbook.Sheets.Count

ReDim sn(sc, 2) As String 'シート名,フォーム数

For i = 1 To sc Step 1

If Sheets(i).Name <> "集計" Then

sn(i, 1) = Sheets(i).Name

sn(i, 2) = fcount(Sheets(i).Name)

If sn(i, 2) = -1 Then

MsgBox sn(i, 1) & "シートが適切な工程表ではないため罫線の描画ができません。"

Exit Sub

End If

Next i

If Sheets(1).Range("H5").Borders(xlEdgeRight).LineStyle = -4142 Then '罫線無し

borderState = False

ElseIf Sheets(1).Range("H5").Borders(xlEdgeRight).LineStyle = -4118 Then '罫線有り

borderState = True

End If

For i = 1 To sc Step 1

Call BorderToggle(sn(i, 1), sn(i, 2), borderState) 'シート名,フォーム数

Next i

Sheets(asn).Activate

Application.ScreenUpdating = True

MsgBox "終了しました。"

End Sub

Sub BorderToggle(sheetName, formQty, borderState)

If sheetName = "" Then Exit Sub

With Sheets(sheetName)

If borderState = True Then '罫線有り

rowTop = 5

rowBtn = 24

For f = 1 To formQty

.Range(.Cells(rowTop, 8), .Cells(rowBtn, 100)).Borders.LineStyle = xlLineStyleNone

'斜線は個別に処理しないと消えなかった。

.Range(.Cells(rowTop, 8), .Cells(rowBtn, 100)).Borders(xlDiagonalDown).LineStyle = -4142

.Range(.Cells(rowTop, 8), .Cells(rowBtn, 100)).Borders(xlDiagonalUp).LineStyle = -4142

.Range(.Cells(rowTop, 8), .Cells(rowBtn, 100)).Borders(xlEdgeTop).LineStyle = 1

.Range(.Cells(rowTop, 8), .Cells(rowBtn, 100)).Borders(xlEdgeRight).LineStyle = 1

.Range(.Cells(rowTop, 8), .Cells(rowBtn, 100)).Borders(xlEdgeBottom).LineStyle = 1

.Range(.Cells(rowTop, 8), .Cells(rowBtn, 100)).Borders(xlEdgeLeft).LineStyle = 1

rowTop = rowTop + 25

rowBtn = rowBtn + 25

Next f

End If

If borderState = False Then '罫線無し

rowTop = 5

rowBtn = 24

For f = 1 To formQty

.Range(.Cells(rowTop, 8), .Cells(rowBtn, 100)).Borders(xlInsideVertical).LineStyle = -4118

.Range(.Cells(rowTop, 8), .Cells(rowBtn, 100)).Borders(xlInsideHorizontal).LineStyle = 1

'縦のWeightが違う線の処理

For col = 10 To 97 Step 3

.Range(.Cells(rowTop, col), .Cells(rowBtn, col)).Borders(xlEdgeRight).LineStyle = 1

Select Case col

Case 22, 40, 55, 70, 85

.Range(.Cells(rowTop, col), .Cells(rowBtn, col)).Borders(xlEdgeRight).Weight = -4138

End Select

Next col

For col = 8 To 38 Step 3

'月末部の斜線処理

If .Cells(4, col).Value = "" Then

.Range(.Cells(rowTop, col), .Cells(rowBtn, col + 2)).Borders(xlDiagonalUp).LineStyle = 1

End If

Next col

rowTop = rowTop + 25

rowBtn = rowBtn + 25

Next f

End If

End With

End Sub

Sub 生産高集計()

'[集計]というシートがあると削除

'個別シートのフォーム数はA列で判断

'（24なら1つ49なら2つというように探す、フォームが崩れているとエラーを返す）

'B列が記入されていて、尚且つ[数量]と[単価]が記入されている行のみ処理

'日付はG列に記入があれば、そちらを使用、なければF列を使用

Application.ScreenUpdating = False

For Each ws In Worksheets

If ws.Name = "集計" Then

If vbYes = MsgBox("集計シートが存在します。削除しますか？", vbYesNo) Then

Worksheets("集計").Delete

Else

Exit Sub

End If

Next ws

Dim sc As Integer ' シート数

sc = ActiveWorkbook.Sheets.Count

ReDim sn(sc, 2) As String 'シート名,フォーム数

For i = 1 To sc Step 1

sn(i, 1) = Sheets(i).Name

sn(i, 2) = fcount(sn(i, 1)) ' fcount call

If sn(i, 2) = -1 Then

MsgBox sn(i, 1) & "シートが適切な工程表ではないため集計できません。"

Exit Sub

End If

Next i

Sheets.Add after:=ActiveSheet

ActiveSheet.Name = "集計"

If 納期集計(sn(1, 1)) = -1 Then Exit Sub '納期集計 call

For i = 1 To sc Step 1

Call main(sn(i, 1), sn(i, 2), i) 'シート名,フォーム数,カウンタ

Call main2(sn(i, 1), sn(i, 2), i) 'シート名,フォーム数,カウンタ

Next i

With Sheets("集計")

.Range("J1").Value = "班名"

.Range("K1").Value = "生産額"

.Range("L1").Value = "目盛"

.Range("M1").Value = "生産日数"

.Range("N1").Value = "生産額/日"

.Range("P1").Value = "目盛"

.Range("Q1").Value = "未生産日数"

.Columns("A:Q").AutoFit

.Activate

End With

Application.ScreenUpdating = True

MsgBox "終了しました。"

End Sub

Sub main(ByVal sn As String, ByVal fc As Integer, ByVal c As Integer) 'シート名,フォーム数,カウンタ

Worksheets("集計").Cells(c, 1).Value = sn

Worksheets("集計").Cells(c + 1, 10).Value = sn

Worksheets(sn).Activate

buf = 0

For r = (fc * 25 - 1) To 5 Step -1 '-

If ActiveSheet.Range("B" & r & "").Value <> "" Then '型式が記入されている '--

'ActiveSheet.Range("B" & r & "").Select

If IsNumeric(ActiveSheet.Range("E" & r & "").Value) And IsNumeric(ActiveSheet.Range("CZ" & r & "").Value) Then '数量と単価が数値 '---

buf = buf + (ActiveSheet.Range("E" & r & "").Value * ActiveSheet.Range("CZ" & r & "").Value)

'ここから顧客別金額 -------------------------------------------------

If ActiveSheet.Range("CX" & r & "").Value = "" Then

cus = "空白"

Else

cus = 文字列調整(ActiveSheet.Range("CX" & r & "").Value)

End If

pri = ActiveSheet.Range("E" & r & "").Value * ActiveSheet.Range("CZ" & r & "").Value

i = 1

Do While Worksheets("集計").Range("D" & i & "").Value <> ""

If Worksheets("集計").Range("D" & i & "").Value = cus Then

Worksheets("集計").Range("E" & i & "").NumberFormatLocal = "#,##0"

Worksheets("集計").Range("E" & i & "").Value = Worksheets("集計").Range("E" & i & "").Value + pri

pri = 0

End If

i = i + 1

Loop

If pri <> 0 Then

Worksheets("集計").Range("D" & i & "").Value = cus

Worksheets("集計").Range("E" & i & "").NumberFormatLocal = "#,##0"

Worksheets("集計").Range("E" & i & "").Value = pri

End If

'ここまで顧客別金額ここから納期別金額 -------------------------------------------------

lt = ActiveSheet.Range("F" & r & "").Value

If ActiveSheet.Range("G" & r & "").Value <> "" Then lt = ActiveSheet.Range("G" & r & "").Value

If IsDate(lt) = False Then

Worksheets("集計").Range("H13").NumberFormatLocal = "#,##0"

Worksheets("集計").Range("H13").Value = Worksheets("集計").Range("H13").Value + (ActiveSheet.Range("E" & r & "").Value * ActiveSheet.Range("CZ" & r & "").Value)

End If

If IsDate(lt) Then

m = Month(lt)

d = Day(lt)

Select Case d

Case 21 To 31

m = m + 1

If m = 13 Then m = 1

Case 1 To 20

End Select

For i = 1 To 12

If m = Worksheets("集計").Range("G" & i & "").Value Then

Worksheets("集計").Range("H" & i & "").NumberFormatLocal = "#,##0"

Worksheets("集計").Range("H" & i & "").Value = Worksheets("集計").Range("H" & i & "").Value + (ActiveSheet.Range("E" & r & "").Value * ActiveSheet.Range("CZ" & r & "").Value)

End If

Next i

End If

'ここまで納期別金額 -------------------------------------------------

End If '---

End If '--

Select Case r

Case 105, 80, 55, 30

r = r - 5

End Select

Next r ' -

Worksheets("集計").Cells(c, 2).NumberFormatLocal = "#,##0"

Worksheets("集計").Cells(c, 2).Value = buf

Worksheets("集計").Cells(c + 1, 11).NumberFormatLocal = "#,##0"

Worksheets("集計").Cells(c + 1, 11).Value = buf

End Sub

Function fcount(ByVal sn As String) As Integer

Dim rc As Integer '行数

Dim fc As Integer 'フォーム数

Sheets(sn).Activate

rc = ActiveSheet.UsedRange.Rows.Count

For i = ActiveSheet.UsedRange.Rows.Count To 1 Step -1 'フォーム数を確認(A列で判断)

If Range("A" & i & "").Value <> "" Then Exit For

Next i

Select Case i

Case 24

fc = 1

Case 49

fc = 2

Case 74

fc = 3

Case 99

fc = 4

Case 124

fc = 5

Case Else

fc = -1

End Select

fcount = fc

End Function

Function 文字列調整(ByVal cus_str As String)

cus_str = Replace(cus_str, vbLf, "")

cus_str = StrConv(cus_str, vbWide)

cus_str = Replace(cus_str, "　　　", "　") '空白4つまで1つに置換する

cus_str = Replace(cus_str, "　　", "　")

cus_str = Trim(cus_str)

cus_str = Replace(cus_str, "㈱", "")

cus_str = Replace(cus_str, "㈲", "")

文字列調整 = cus_str

End Function

Function 納期集計(ByVal sn As String)

mth = Replace(Sheets(sn).Range("H2").Value, "月", "")

Select Case mth

Case 1 To 12

For i = 1 To 12

Sheets("集計").Range("G" & i & "").Value = mth + i

If Sheets("集計").Range("G" & i & "").Value > 12 Then

Sheets("集計").Range("G" & i & "").Value = Sheets("集計").Range("G" & i & "").Value - 12

End If

Next i

Case Else

MsgBox "月が正しく記入されていません。"

mth = -1

End Select

Sheets("集計").Range("G13").Value = "未定"

納期集計 = mth

End Function

Sub main2(ByVal sn As String, ByVal fc As Integer, ByVal c As Integer) 'シート名,フォーム数,カウンタ

Worksheets(sn).Activate

buf1 = 0

buf2 = 0

For cc = 8 To 100

For r = 5 To (fc * 25 - 1)

If ActiveSheet.Cells(4, cc).Interior.Color = RGB(255, 255, 0) Then Exit For

If ActiveSheet.Cells(r, cc).Interior.Color = RGB(0, 0, 0) Then

buf1 = buf1 + 1 '黒塗りつぶし

Exit For

End If

'ActiveSheet.Cells(r, cc).Value = r & "," & cc

If r = (fc * 25 - 1) Then buf2 = buf2 + 1 '塗りつぶしなし

Select Case r

Case 99, 74, 49, 24

r = r + 5

End Select

Next r

Next cc

Worksheets("集計").Cells(c + 1, 12).Value = buf1 '目盛

Worksheets("集計").Cells(c + 1, 13).NumberFormatLocal = "0.0" '

Worksheets("集計").Cells(c + 1, 13).Value = buf1 / 3 '生産日数

Worksheets("集計").Cells(c + 1, 14).NumberFormatLocal = "#,##0"

If Worksheets("集計").Cells(c + 1, 13) <> 0 Then

Worksheets("集計").Cells(c + 1, 14).Value = (Worksheets("集計").Cells(c + 1, 11)) / (Worksheets("集計").Cells(c + 1, 13)) '生産額/日

End If

Worksheets("集計").Cells(c + 1, 16).Value = buf2

Worksheets("集計").Cells(c + 1, 17).NumberFormatLocal = "0.0" '

Worksheets("集計").Cells(c + 1, 17).Value = buf2 / 3 '未生産日数

End Sub

2017-05-092017-05-09

VBA 即席会員数保存

入力シートと保存シートに分け、保存シートを関数で参照する。
登録時や起動時に常に関数の参照範囲を変更する。

Sub reset()

Sheets("新規").Rows("2:1000").Delete
Sheets("更新").Rows("2:1000").Delete

End Sub

Sub style()

'新規
Sheets("新規").Columns("A").NumberFormatLocal = "yyyy/mm/dd"
Sheets("新規").Columns("B").NumberFormatLocal = "0"
Sheets("新規").Columns("C").NumberFormatLocal = "@"

Sheets("新規").Cells(1, 1).Value = "日付"
Sheets("新規").Cells(1, 2).Value = "人数"
Sheets("新規").Cells(1, 3).Value = "カード番号"

Sheets("新規").Columns("A:C").AutoFit

'更新
Sheets("更新").Columns("A").NumberFormatLocal = "yyyy/mm/dd"
Sheets("更新").Columns("B").NumberFormatLocal = "0"

Sheets("更新").Cells(1, 1).Value = "日付"
Sheets("更新").Cells(1, 2).Value = "人数"

Sheets("更新").Columns("A:B").AutoFit

'表紙
Sheets("表紙").Range("B6:B15").NumberFormatLocal = "@"
Sheets("表紙").Cells(3, 2).NumberFormatLocal = "yyyy/mm/dd"
Sheets("表紙").Cells(3, 4).NumberFormatLocal = "0"

Sheets("表紙").Cells(6, 4).Value = Sheets("新規").UsedRange.Rows.Count
Sheets("表紙").Cells(7, 4).Value = Sheets("更新").UsedRange.Rows.Count

Dim btm(2)
btm(0) = Sheets("表紙").Cells(6, 4).Value
btm(1) = Sheets("表紙").Cells(7, 4).Value
If btm(0) = 1 Then btm(0) = 2
If btm(1) = 1 Then btm(1) = 2
For r = 3 To 33
  Sheets("表紙").Cells(r, 9).Value = "=SUMIFS(新規!B2:B" & btm(0) & ",新規!A2:A" & btm(0) & ",表紙!G" & r & ")"
  Sheets("表紙").Cells(r, 10).Value = "=SUMIFS(更新!B2:B" & btm(1) & ",更新!A2:A" & btm(1) & ",表紙!G" & r & ")"
Next r

End Sub

Sub auto_open()

Call style

Sheets("表紙").Cells(3, 2).Value = Date

End Sub

Sub main()

Dim btm(2)
btm(0) = Sheets("表紙").Cells(6, 4).Value + 1
btm(1) = Sheets("表紙").Cells(7, 4).Value + 1

'新規
Dim tmp As String
For r = 6 To 15
  tmp = Sheets("表紙").Cells(r, 2).Value
  If tmp <> "" Then
    Sheets("新規").Cells(btm(0), 1).Value = Sheets("表紙").Cells(3, 2).Value
    Sheets("新規").Cells(btm(0), 2).Value = 1
    tmp = "27" & Right("000000000000000000" & tmp, 18)
    Sheets("新規").Cells(btm(0), 3).Value = tmp
    btm(0) = btm(0) + 1
  End If
Next r

Sheets("表紙").Range("B6:B15").Value = ""

'更新
If Sheets("表紙").Cells(3, 4).Value <> "" And IsNumeric(Sheets("表紙").Cells(3, 4).Value) Then
  Sheets("更新").Cells(btm(1), 1).Value = Sheets("表紙").Cells(3, 2).Value
  Sheets("更新").Cells(btm(1), 2).Value = Sheets("表紙").Cells(3, 4).Value
End If

Sheets("表紙").Cells(3, 4).Value = ""

Call style

MsgBox "終了しました。"

End Sub

Sub reset()

Sheets("新規").Rows("2:1000").Delete

Sheets("更新").Rows("2:1000").Delete

End Sub

Sub style()

'新規

Sheets("新規").Columns("A").NumberFormatLocal = "yyyy/mm/dd"

Sheets("新規").Columns("B").NumberFormatLocal = "0"

Sheets("新規").Columns("C").NumberFormatLocal = "@"

Sheets("新規").Cells(1, 1).Value = "日付"

Sheets("新規").Cells(1, 2).Value = "人数"

Sheets("新規").Cells(1, 3).Value = "カード番号"

Sheets("新規").Columns("A:C").AutoFit

'更新

Sheets("更新").Columns("A").NumberFormatLocal = "yyyy/mm/dd"

Sheets("更新").Columns("B").NumberFormatLocal = "0"

Sheets("更新").Cells(1, 1).Value = "日付"

Sheets("更新").Cells(1, 2).Value = "人数"

Sheets("更新").Columns("A:B").AutoFit

'表紙

Sheets("表紙").Range("B6:B15").NumberFormatLocal = "@"

Sheets("表紙").Cells(3, 2).NumberFormatLocal = "yyyy/mm/dd"

Sheets("表紙").Cells(3, 4).NumberFormatLocal = "0"

Sheets("表紙").Cells(6, 4).Value = Sheets("新規").UsedRange.Rows.Count

Sheets("表紙").Cells(7, 4).Value = Sheets("更新").UsedRange.Rows.Count

Dim btm(2)

btm(0) = Sheets("表紙").Cells(6, 4).Value

btm(1) = Sheets("表紙").Cells(7, 4).Value

If btm(0) = 1 Then btm(0) = 2

If btm(1) = 1 Then btm(1) = 2

For r = 3 To 33

Sheets("表紙").Cells(r, 9).Value = "=SUMIFS(新規!B2:B" & btm(0) & ",新規!A2:A" & btm(0) & ",表紙!G" & r & ")"

Sheets("表紙").Cells(r, 10).Value = "=SUMIFS(更新!B2:B" & btm(1) & ",更新!A2:A" & btm(1) & ",表紙!G" & r & ")"

Next r

End Sub

Sub auto_open()

Call style

Sheets("表紙").Cells(3, 2).Value = Date

End Sub

Sub main()

Dim btm(2)

btm(0) = Sheets("表紙").Cells(6, 4).Value + 1

btm(1) = Sheets("表紙").Cells(7, 4).Value + 1

'新規

Dim tmp As String

For r = 6 To 15

tmp = Sheets("表紙").Cells(r, 2).Value

If tmp <> "" Then

Sheets("新規").Cells(btm(0), 1).Value = Sheets("表紙").Cells(3, 2).Value

Sheets("新規").Cells(btm(0), 2).Value = 1

tmp = "27" & Right("000000000000000000" & tmp, 18)

Sheets("新規").Cells(btm(0), 3).Value = tmp

btm(0) = btm(0) + 1

End If

Next r

Sheets("表紙").Range("B6:B15").Value = ""

'更新

If Sheets("表紙").Cells(3, 4).Value <> "" And IsNumeric(Sheets("表紙").Cells(3, 4).Value) Then

Sheets("更新").Cells(btm(1), 1).Value = Sheets("表紙").Cells(3, 2).Value

Sheets("更新").Cells(btm(1), 2).Value = Sheets("表紙").Cells(3, 4).Value

End If

Sheets("表紙").Cells(3, 4).Value = ""

Call style

MsgBox "終了しました。"

End Sub

2017-05-082017-05-08

C# VBA 年月日フォルダ自動生成(会員数保存) 作りかけ

少し試作を作り始めたが、方向性が変わったため。

Form1.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace 会員数保存
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();

            this.comboBox1.Items.Add("2017");
            this.comboBox1.Items.Add("2018");

            for (int i = 1; i <= 12; i++ )
            {
                this.comboBox2.Items.Add(
                    string.Format("{0:00}",i)
                    );
            }

            this.comboBox1.Text = DateTime.Today.ToString("yyyy");
            this.comboBox2.Text = DateTime.Today.ToString("MM");

            this.button1.Click += new EventHandler(MakeFolder);

        }

        private void MakeFolder(object sender, EventArgs e)
        {
            try
            {
                DateTime.Parse(comboBox1.Text + "/" + comboBox2.Text + "/01");

                if(System.IO.File.Exists(Application.StartupPath + @"\dat.txt"))
                {
                    MessageBox.Show("既にdat.txtが存在します。中止します。");
                    return;
                }

                string[] directories = System.IO.Directory.GetDirectories(Application.StartupPath);

                if (directories.Length > 0)
                {
                    MessageBox.Show("既に同一階層にフォルダが存在します。中止します。");
                    return;
                }

                string targetPath = Application.StartupPath + "/" + comboBox1.Text + comboBox2.Text;
                System.IO.Directory.CreateDirectory(targetPath);

                DateTime FromDate = DateTime.Parse(comboBox1.Text + "/" + comboBox2.Text + "/01");
                DateTime ToDate = FromDate.AddMonths(1);
                ToDate = ToDate.AddDays(-1);

                while(FromDate <= ToDate)
                {
                    System.IO.Directory.CreateDirectory(targetPath + "/" + FromDate.ToString("dd"));
                    FromDate = FromDate.AddDays(1);
                }

                using(System.IO.StreamWriter sw = new System.IO.StreamWriter(
                    Application.StartupPath + @"\dat.txt",false,System.Text.Encoding.GetEncoding("Shift_Jis")))
                {
                    sw.Write(comboBox1.Text + "\r\n" + comboBox2.Text + "\r\n" + ToDate.ToString("dd"));
                    sw.Close();
                }

            }
            catch (Exception exp)
            {
                MessageBox.Show(exp.Message);
                return;
            }

            MessageBox.Show("フォルダを作成しました。");

        }
    }
}

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

namespace 会員数保存

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

this.comboBox1.Items.Add("2017");

this.comboBox1.Items.Add("2018");

for (int i = 1; i <= 12; i++ )

{

this.comboBox2.Items.Add(

string.Format("{0:00}",i)

);

}

this.comboBox1.Text = DateTime.Today.ToString("yyyy");

this.comboBox2.Text = DateTime.Today.ToString("MM");

this.button1.Click += new EventHandler(MakeFolder);

}

private void MakeFolder(object sender, EventArgs e)

{

try

{

DateTime.Parse(comboBox1.Text + "/" + comboBox2.Text + "/01");

if(System.IO.File.Exists(Application.StartupPath + @"\dat.txt"))

{

MessageBox.Show("既にdat.txtが存在します。中止します。");

return;

}

string[] directories = System.IO.Directory.GetDirectories(Application.StartupPath);

if (directories.Length > 0)

{

MessageBox.Show("既に同一階層にフォルダが存在します。中止します。");

return;

}

string targetPath = Application.StartupPath + "/" + comboBox1.Text + comboBox2.Text;

System.IO.Directory.CreateDirectory(targetPath);

DateTime FromDate = DateTime.Parse(comboBox1.Text + "/" + comboBox2.Text + "/01");

DateTime ToDate = FromDate.AddMonths(1);

ToDate = ToDate.AddDays(-1);

while(FromDate <= ToDate)

{

System.IO.Directory.CreateDirectory(targetPath + "/" + FromDate.ToString("dd"));

FromDate = FromDate.AddDays(1);

}

using(System.IO.StreamWriter sw = new System.IO.StreamWriter(

Application.StartupPath + @"\dat.txt",false,System.Text.Encoding.GetEncoding("Shift_Jis")))

{

sw.Write(comboBox1.Text + "\r\n" + comboBox2.Text + "\r\n" + ToDate.ToString("dd"));

sw.Close();

}

catch (Exception exp)

{

MessageBox.Show(exp.Message);

return;

}

MessageBox.Show("フォルダを作成しました。");

}

VBA

Sub auto_open()

On Error Resume Next

Application.CommandBars("cell").Controls("会員数をフォルダから取得").Delete

With Application.CommandBars("cell").Controls.Add
.OnAction = "main"
.Caption = "会員数をフォルダから取得"
End With

End Sub

Sub auto_close()

On Error Resume Next

Application.CommandBars("cell").Controls("会員数をフォルダから取得").Delete
Application.CommandBars("cell").Controls("会員数をフォルダから取得").Delete

End Sub

Sub main()

On Error GoTo e

targetPath = ThisWorkbook.Path & "\dat.txt"

If Dir(targetPath) = "" Then

  MsgBox "dat.txtが存在しません。終了します。"
  Exit Sub

End If

Open targetPath For Input As #1
  Line Input #1, y
  Line Input #1, m
  Line Input #1, d
Close #1

For i = 1 To d

  targetPath = ThisWorkbook.Path & "/" & y & m & "/" & Format(i, "00")
  
  If Dir(targetPath) = "" Then
  
    MsgBox targetPath & "が存在しません。終了します。"
    Exit Sub
  
  End If
  
  ActiveSheet.Cells(i + 1, 1).Value = y & "/" & m & "/" & Format(i, "00")
  'ActiveSheet.Cells(i + 1, 1).Font.Color = RGB(255, 0, 0)
  'ActiveSheet.Cells(i + 1, 2).Value = ActiveSheet.Cells(i, 2).Value
  'ActiveSheet.Cells(i + 1, 3).Value = ActiveSheet.Cells(i, 3).Value

Next i

Exit Sub

e:

MsgBox "エラーが発生しています。"

End Sub

Sub initialize()

ActiveSheet.Cells.Delete
ActiveSheet.Columns("A").NumberFormatLocal = "yyyy/mm/dd"
ActiveSheet.Cells(1, 1).Value = "日付"
ActiveSheet.Cells(1, 2).Value = "新規"
ActiveSheet.Cells(1, 3).Value = "更新"

End Sub

Sub finalize()

ActiveSheet.Columns.AutoFit

End Sub

Sub auto_open()

On Error Resume Next

Application.CommandBars("cell").Controls("会員数をフォルダから取得").Delete

With Application.CommandBars("cell").Controls.Add

.OnAction = "main"

.Caption = "会員数をフォルダから取得"

End With

End Sub

Sub auto_close()

On Error Resume Next

Application.CommandBars("cell").Controls("会員数をフォルダから取得").Delete

End Sub

Sub main()

On Error GoTo e

targetPath = ThisWorkbook.Path & "\dat.txt"

If Dir(targetPath) = "" Then

MsgBox "dat.txtが存在しません。終了します。"

Exit Sub

End If

Open targetPath For Input As #1

Line Input #1, y

Line Input #1, m

Line Input #1, d

Close #1

For i = 1 To d

targetPath = ThisWorkbook.Path & "/" & y & m & "/" & Format(i, "00")

If Dir(targetPath) = "" Then

MsgBox targetPath & "が存在しません。終了します。"

Exit Sub

End If

ActiveSheet.Cells(i + 1, 1).Value = y & "/" & m & "/" & Format(i, "00")

'ActiveSheet.Cells(i + 1, 1).Font.Color = RGB(255, 0, 0)

'ActiveSheet.Cells(i + 1, 2).Value = ActiveSheet.Cells(i, 2).Value

'ActiveSheet.Cells(i + 1, 3).Value = ActiveSheet.Cells(i, 3).Value

Next i

Exit Sub

MsgBox "エラーが発生しています。"

End Sub

Sub initialize()

ActiveSheet.Cells.Delete

ActiveSheet.Columns("A").NumberFormatLocal = "yyyy/mm/dd"

ActiveSheet.Cells(1, 1).Value = "日付"

ActiveSheet.Cells(1, 2).Value = "新規"

ActiveSheet.Cells(1, 3).Value = "更新"

End Sub

Sub finalize()

ActiveSheet.Columns.AutoFit

End Sub

2017-03-06

C# VBA 在庫管理

以前作ったプログラムお蔵入り版

Form1.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

using System.Data.OleDb;

namespace 全在庫管理
{
    public partial class Form1 : Form
    {
        DataTable dt;

        public Form1()
        {
            InitializeComponent();

            dataGridView1.MultiSelect = false;
            dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
            dataGridView1.AllowUserToAddRows = false;
            dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;

            comboBox2.Enabled = false;

            textBox1.TextChanged += new EventHandler(init_view);
            textBox2.TextChanged += new EventHandler(init_view);
            textBox3.TextChanged += new EventHandler(init_view);
            textBox5.TextChanged += new EventHandler(init_view);

            contextMenuStrip1.Items.Add("更新");
            contextMenuStrip1.Items.Add("出庫");
            contextMenuStrip1.Items.Add("複写");
            contextMenuStrip1.Items.Add("削除");

            init();
        }

         ~Form1()
        {
            dt.Dispose();
        }

        private void init()
        {
            string q = @"select * from tbl where 出庫日 is null order by ID";
            if (checkBox3.Checked) q = @"select * from tbl where 出庫日 is not null order by ID";
            if (checkBox4.Checked) q = @"select * from tbl where 入庫日 = #" + DateTime.Now.ToString("yyyy/MM/dd") + "# order by ID";
            if (checkBox5.Checked) q = @"select * from tbl where 出庫日 = #" + DateTime.Now.ToString("yyyy/MM/dd") + "# order by ID";

            dt = Util.getDt(q);
            list_add();
        }

        public void list_add()
        {
            comboBox1.Items.Clear();
            comboBox2.Items.Clear();
            using (DataView dv = new DataView(dt))
            {
                using (DataTable tmp = dv.ToTable(true, new string[] { "仕入月" }))
                {
                    DataRow[] dr = tmp.Select("仕入月 is not null");
                    for (int i = 0; i < dr.Length; i++)
                    {
                        comboBox1.Items.Add(DateTime.Parse(dr[i]["仕入月"].ToString()).ToString("yyyy年MM月"));
                        comboBox2.Items.Add(DateTime.Parse(dr[i]["仕入月"].ToString()).ToString("yyyy年MM月"));
                    }
                }
            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            init_view(sender, e);
        }

        public void init_view(object sender, EventArgs e)
        {
            dataGridView1.Rows.Clear();
            string tmp = "";
            bool chk = false;

            if (textBox2.Text != "")
            {
                tmp = "工番 like '%" + textBox2.Text + "%'";
                chk = true;
            }

            if (textBox1.Text != "")
            {
                if (chk) tmp = tmp + " and ";
                tmp = tmp + "製品名称 like '%" + textBox1.Text + "%'";
                chk = true;
            }

            if (textBox3.Text != "")
            {
                if (chk) tmp = tmp + " and ";
                tmp = tmp + " 仕入先 like '%" + textBox3.Text + "%'";
                chk = true;
            }

            if (textBox5.Text != "")
            {
                if (chk) tmp = tmp + " and ";
                tmp = tmp + " 区分 like '%" + textBox5.Text + "%'";
                chk = true;
            }

            if (comboBox1.Text != "" && comboBox2.Enabled == false)
            {
                if (chk) tmp = tmp + " and ";
                tmp = tmp + " 仕入月 = #" + DateTime.Parse(comboBox1.Text).ToString("yyyy/MM/01") + "#";
            }
            else if (comboBox1.Text != "" && comboBox2.Enabled == true && comboBox2.Text != "")
            {
                if (chk) tmp = tmp + " and ";
                tmp = tmp + " 仕入月 >= #" + DateTime.Parse(comboBox1.Text).ToString("yyyy/MM/01")
                    + "# and 仕入月 <= #" + DateTime.Parse(comboBox2.Text).ToString("yyyy/MM/01") + "#";
            }
            else if (comboBox1.Text != "" && comboBox2.Enabled == true && comboBox2.Text == "")
            {
                if (chk) tmp = tmp + " and ";
                tmp = tmp + " 仕入月 >= #" + DateTime.Parse(comboBox1.Text).ToString("yyyy/MM/01") + "#";
            }
            else if (comboBox1.Text == "" && comboBox2.Enabled == true && comboBox2.Text != "")
            {
                if (chk) tmp = tmp + " and ";
                tmp = tmp + " 仕入月 <= #" + DateTime.Parse(comboBox2.Text).ToString("yyyy/MM/01") + "#";
            }

            System.Diagnostics.Debug.Print(tmp.ToString());

            DataRow[] dr = dt.Select(tmp, "ID");
            decimal[,] rc = new decimal[6, 4];

            for (int i = 0; i < dr.Length; i++)
            {
                decimal res00;
                decimal.TryParse(dr[i]["数量"].ToString(), out res00);
                decimal res01;
                decimal.TryParse(dr[i]["計上単価"].ToString(), out res01);
                DateTime res02;
                if (!DateTime.TryParse(dr[i]["仕入月"].ToString(), out res02)) res02 = new DateTime(1970, 1, 1);

                dataGridView1.Rows.Add(
                    dr[i]["ID"].ToString(),
                    dr[i]["工番"].ToString(),
                    dr[i]["製品名称"].ToString(),
                    dr[i]["仕入先"].ToString(),
                    res00.ToString(),
                    string.Format("{0:#,0}", res01),
                    string.Format("{0:#,0}", (res00 * res01)),
                    dr[i]["区分"].ToString(),
                    res02.ToString("yyyy年MM月"),
                    dr[i]["備考"].ToString()
                    );

                switch (dr[i]["区分"].ToString())
                {
                    case "内作材料①":

                        if ((res00 * res01) >= 50000)
                        {
                            rc[1, 2] = rc[1, 2] + (res00 * res01);
                        }
                        else
                        {
                            rc[1, 3] = rc[1, 3] + (res00 * res01);
                        }
                        rc[1, 1] = rc[1, 1] + (res00 * res01);
                        break;

                    case "外作材料①":

                        if ((res00 * res01) >= 50000)
                        {
                            rc[2, 2] = rc[2, 2] + (res00 * res01);
                        }
                        else
                        {
                            rc[2, 3] = rc[2, 3] + (res00 * res01);
                        }
                        rc[2, 1] = rc[2, 1] + (res00 * res01);
                        break;

                    case "内作製品":

                        if ((res00 * res01) >= 50000)
                        {
                            rc[3, 2] = rc[3, 2] + (res00 * res01);
                        }
                        else
                        {
                            rc[3, 3] = rc[3, 3] + (res00 * res01);
                        }
                        rc[3, 1] = rc[3, 1] + (res00 * res01);
                        break;

                    case "外作製品":

                        if ((res00 * res01) >= 50000)
                        {
                            rc[4, 2] = rc[4, 2] + (res00 * res01);
                        }
                        else
                        {
                            rc[4, 3] = rc[4, 3] + (res00 * res01);
                        }
                        rc[4, 1] = rc[4, 1] + (res00 * res01);
                        break;

                    default:

                        if ((res00 * res01) >= 50000)
                        {
                            rc[5, 2] = rc[5, 2] + (res00 * res01);
                        }
                        else
                        {
                            rc[5, 3] = rc[5, 3] + (res00 * res01);
                        }
                        rc[5, 1] = rc[5, 1] + (res00 * res01);
                        break;
                }
            }
            textBox4.Text = string.Format("{0:#,0}", rc[1, 1]);
            textBox8.Text = string.Format("{0:#,0}", rc[2, 1]);
            textBox11.Text = string.Format("{0:#,0}", rc[3, 1]);
            textBox14.Text = string.Format("{0:#,0}", rc[4, 1]);
            textBox17.Text = string.Format("{0:#,0}", rc[5, 1]);
            textBox20.Text = string.Format("{0:#,0}", rc[1, 1] + rc[2, 1] + rc[3, 1] + rc[4, 1] + rc[5, 1]);
            textBox6.Text = string.Format("{0:#,0}", rc[1, 2]);
            textBox9.Text = string.Format("{0:#,0}", rc[2, 2]);
            textBox12.Text = string.Format("{0:#,0}", rc[3, 2]);
            textBox15.Text = string.Format("{0:#,0}", rc[4, 2]);
            textBox18.Text = string.Format("{0:#,0}", rc[5, 2]);
            textBox21.Text = string.Format("{0:#,0}", rc[1, 2] + rc[2, 2] + rc[3, 2] + rc[4, 2] + rc[5, 2]);
            textBox7.Text = string.Format("{0:#,0}", rc[1, 3]);
            textBox10.Text = string.Format("{0:#,0}", rc[2, 3]);
            textBox13.Text = string.Format("{0:#,0}", rc[3, 3]);
            textBox16.Text = string.Format("{0:#,0}", rc[4, 3]);
            textBox19.Text = string.Format("{0:#,0}", rc[5, 3]);
            textBox22.Text = string.Format("{0:#,0}", rc[1, 3] + rc[2, 3] + rc[3, 3] + rc[4, 3] + rc[5, 3]);
            label16.Text = dr.Length.ToString() + "件取得しました";
        }

        private void button1_Click(object sender, EventArgs e)
        {
            textBox1.Text = "";
            textBox2.Text = "";
            textBox3.Text = "";
            textBox5.Text = "";
            comboBox1.Text = "";
            comboBox2.Text = "";
            checkBox1.Checked = false;
            comboBox2.Enabled = false;
            textBox23.Text = "";
            textBox24.Text = "";
            textBox25.Text = "";
            textBox26.Text = "";
            textBox27.Text = "";
            textBox28.Text = "";
            textBox29.Text = "";
            textBox30.Text = "";
            init_view(sender, e);
        }

        private void dataGridView1_CellMouseDown(object sender, DataGridViewCellMouseEventArgs e)
        {
            if (e.Button == MouseButtons.Right)
            {
                dataGridView1.ClearSelection();

                if (e.RowIndex >= 0 && e.ColumnIndex >= 0) dataGridView1.Rows[e.RowIndex].Selected = true;

                foreach (ToolStripMenuItem tsmi in contextMenuStrip1.Items)
                {
                    if (tsmi.Text == "出庫取消")
                    {
                        contextMenuStrip1.Items.Remove(tsmi);
                        break;
                    }
                }

                if (checkBox3.Checked || checkBox5.Checked) contextMenuStrip1.Items.Add("出庫取消");
            }
        }

        private string[] parse_num(string[] tmp)
        {
            string[] res = new string[3];

            decimal out001;
            if (tmp[0] == "" || !decimal.TryParse(tmp[0], out out001))
            {
                res[0] = "0";
            }
            else
            {
                res[0] = out001.ToString();
            }

            decimal out002;
            if (tmp[1] == "" || !decimal.TryParse(tmp[1], out out002))
            {
                res[1] = "0";
            }
            else
            {
                res[1] = out002.ToString();
            }

            DateTime out003;

            if (tmp[2].Length == 6 && tmp[2].Substring(4, 1) != "/")
            {
                tmp[2] = tmp[2].Substring(0, 4) + "/" + tmp[2].Substring(4, 2) + "/1";
            }
            else if (tmp[2].Length == 6 && tmp[2].Substring(4, 1) == "/")
            {
                tmp[2] = tmp[2].Substring(0, 4) + "/" + tmp[2].Substring(5, 1) + "/1";
            }
            else if (tmp[2].Length == 7 && tmp[2].Substring(4, 1) == "/")
            {
                tmp[2] = tmp[2].Substring(0, 4) + "/" + tmp[2].Substring(5, 2) + "/1";
            }

            if (tmp[2] == "" || !DateTime.TryParse(tmp[2], out out003))
            {
                res[2] = "1970/01/01";
            }
            else
            {
                res[2] = out003.ToString("yyyy/MM/01");
            }

            return res;
        }

        private void contextMenuStrip1_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
        {
            if (dataGridView1.Rows.Count == 0) return;
            set_selection(dataGridView1.SelectedRows[0].Index);

            string q = "";

            switch (e.ClickedItem.Text.ToString())
            {
                case "更新":

                    if (dgvr.Cells[4].Value == null || dgvr.Cells[5].Value == null || dgvr.Cells[8].Value == null)
                    {
                        MessageBox.Show("数量、計上単価、仕入月が空白では処理できません。");
                        return;
                    }

                    string[] res = parse_num(new string[] {
                        dgvr.Cells[4].Value.ToString(),
                        dgvr.Cells[5].Value.ToString(),
                        dgvr.Cells[8].Value.ToString()
                    } );

                    q = @"update tbl set " +
                        "工番='" + dgvr.Cells[1].Value.ToString() + "'," +
                        "製品名称='" + dgvr.Cells[2].Value.ToString() + "'," +
                        "仕入先='" + dgvr.Cells[3].Value.ToString() + "'," +
                        "数量=" + res[0] + "," +
                        "計上単価=" + res[1] + "," +
                        "区分='" + dgvr.Cells[7].Value.ToString() + "'," +
                        "仕入月=#" + res[2] + "#," +
                        "備考='" + dgvr.Cells[9].Value.ToString() + "' " +
                        "where ID = " + dgvr.Cells[0].Value.ToString() + "";

                    break;

                case "出庫":

                    q = @"update tbl set " +
                        "出庫日=#" + DateTime.Now.ToString("yyyy/MM/dd") + "# " +
                        "where ID = " + dgvr.Cells[0].Value.ToString() + "";

                    break;

                case "出庫取消":

                    q = @"update tbl set " +
                    "出庫日=NULL " +
                    "where ID = " + dgvr.Cells[0].Value.ToString() + "";

                    break;

                case "複写":

                    q = @"insert into tbl select 工番,製品名称,仕入先,数量,計上単価,区分,仕入月,備考,出庫日,入庫日 from tbl " + 
                        "where ID = " + dgvr.Cells[0].Value.ToString() + "";

                    break;

                case "削除":

                    q = @"delete from tbl " +
                        "where ID = " + dgvr.Cells[0].Value.ToString() + "";
                    break;
            }

            Util.exeQry(q);

            init();
            init_view(sender, e);
            init_selection(e.ClickedItem.Text.ToString());
            MessageBox.Show(e.ClickedItem.Text.ToString() + "しました。");
        }

        private void init_selection(string tmp)
        {
            if (dataGridView1.Rows.Count == 0) return;

            switch (tmp)
            {
                case "更新":
                case "出庫":
                case "削除":

                    if (dgv_rows_count == dataGridView1.Rows.Count)
                    {
                        dataGridView1.Rows[dgv_selected].Selected = true;
                        dataGridView1.FirstDisplayedScrollingRowIndex = dgv_view_index;
                    }
                    else
                    {
                        if (dgv_selected >= 1) dataGridView1.Rows[dgv_selected - 1].Selected = true;
                        dataGridView1.FirstDisplayedScrollingRowIndex = dgv_view_index;
                    }
                    break;

                case "複写":
                case "新規":

                    dataGridView1.Rows[dataGridView1.Rows.Count - 1].Selected = true;
                    dataGridView1.FirstDisplayedScrollingRowIndex = dataGridView1.Rows.Count - 1;
                    break;
            }
        }

        private DataGridViewRow dgvr;
        private int dgv_view_index;
        private int dgv_selected;
        private int dgv_rows_count;

        private void set_selection(int rows_index)
        {
            dgvr = dataGridView1.SelectedRows[0];
            dgv_view_index = dataGridView1.FirstDisplayedScrollingRowIndex;
            dgv_selected = rows_index;
            dgv_rows_count = dataGridView1.Rows.Count;
        }

        private void comboBox1_TextChanged(object sender, EventArgs e)
        {
            DateTime out001;
            if (!DateTime.TryParse(comboBox1.Text, out out001)) comboBox1.Text = "";
            init_view(sender, e);
        }

        private void comboBox2_TextChanged(object sender, EventArgs e)
        {
            DateTime out001;
            if (!DateTime.TryParse(comboBox2.Text, out out001)) comboBox2.Text = "";
            init_view(sender, e);
        }

        private void checkBox1_CheckedChanged(object sender, EventArgs e)
        {
            if(checkBox1.Checked)
            {
                comboBox2.Enabled = true;
            }
            else
            {
                comboBox2.Text = "";
                comboBox2.Enabled = false;
            }
            init_view(sender, e);
        }

        private void button2_Click(object sender, EventArgs e)
        {
            checkBox3.Checked = false;

            string[] res = parse_num(new string[] {
                        textBox26.Text,
                        textBox27.Text,
                        textBox29.Text
                    });

            string q = @"insert into tbl (工番,製品名称,仕入先,数量,計上単価,区分,仕入月,備考,入庫日) values " +
                "('" + textBox23.Text + "','" + textBox24.Text + "','" + textBox25.Text + "'" +
                "," + res[0] + "," + res[1] + ",'" + textBox28.Text + "',#" + res[2] + "#,'" + textBox30.Text + "',#" + DateTime.Now.ToString("yyyy/MM/dd") + "#)";

            Util.exeQry(q);

            init();
            init_view(sender, e);
            init_selection("新規");
            button1.PerformClick();
            MessageBox.Show("新規登録しました。");
        }

        private void btn5678_click(object sender, EventArgs e)
        {
            switch (((Button)sender).Text)
            {
                case "更新":
                    contextMenuStrip1.Items[0].PerformClick();
                    break;
                case "出庫":
                    contextMenuStrip1.Items[1].PerformClick();
                    break;
                case "複写":
                    contextMenuStrip1.Items[2].PerformClick();
                    break;
                case "削除":
                    contextMenuStrip1.Items[3].PerformClick();
                    break;
            }
        }

        private void btn34_click(object sender, EventArgs e)
        {
            DialogResult res = MessageBox.Show("表示されている行が" + ((Button)sender).Text + "されます。実行しますか？","",MessageBoxButtons.OKCancel);
            if (res == DialogResult.Cancel) return;

            for (int r = 0; r < dataGridView1.Rows.Count; r++)
            {
                string q = "";

                if (((Button)sender).Text == "全て出庫")
                {
                    q = @"update tbl set " +
                        "出庫日=#" + DateTime.Now.ToString("yyyy/MM/dd") + "# " +
                        "where ID = " + dataGridView1.Rows[r].Cells[0].Value.ToString() + "";
                }
                else
                {
                    q = @"delete from tbl " +
                        "where ID = " + dataGridView1.Rows[r].Cells[0].Value.ToString() + "";
                }

                Text = r + "件目処理中...";
                Util.exeQry(q);

            }
            
            Text = "全在庫管理 v1.0";

            init();
            init_view(sender, e);
            button1.PerformClick();
            MessageBox.Show(((Button)sender).Text + "しました。");
        }

        private void checkBox3_CheckedChanged(object sender, EventArgs e)
        {
            if (checkBox3.Checked)
            {
                checkBox4.Checked = false;
                checkBox5.Checked = false;
            }
            init();
            init_view(sender, e);
            button1.PerformClick();
        }

        private void checkBox4_CheckedChanged(object sender, EventArgs e)
        {
            if (checkBox4.Checked)
            {
                checkBox3.Checked = false;
                checkBox5.Checked = false;
            }
            init();
            init_view(sender, e);
            button1.PerformClick();
        }

        private void checkBox5_CheckedChanged(object sender, EventArgs e)
        {
            if (checkBox5.Checked)
            {
                checkBox3.Checked = false;
                checkBox4.Checked = false;
            }

            init();
            init_view(sender, e);
            button1.PerformClick();
        }
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

480

481

482

483

484

485

486

487

488

489

490

491

492

493

494

495

496

497

498

499

500

501

502

503

504

505

506

507

508

509

510

511

512

513

514

515

516

517

518

519

520

521

522

523

524

525

526

527

528

529

530

531

532

533

534

535

536

537

538

539

540

541

542

543

544

545

546

547

548

549

550

551

552

553

554

555

556

557

558

559

560

561

562

563

564

565

566

567

568

569

570

571

572

573

574

575

576

577

578

579

580

581

582

583

584

585

586

587

588

589

590

591

592

593

594

595

596

597

598

599

600

601

602

603

604

605

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

using System.Data.OleDb;

namespace 全在庫管理

{

public partial class Form1 : Form

{

DataTable dt;

public Form1()

{

InitializeComponent();

dataGridView1.MultiSelect = false;

dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;

dataGridView1.AllowUserToAddRows = false;

dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;

comboBox2.Enabled = false;

textBox1.TextChanged += new EventHandler(init_view);

textBox2.TextChanged += new EventHandler(init_view);

textBox3.TextChanged += new EventHandler(init_view);

textBox5.TextChanged += new EventHandler(init_view);

contextMenuStrip1.Items.Add("更新");

contextMenuStrip1.Items.Add("出庫");

contextMenuStrip1.Items.Add("複写");

contextMenuStrip1.Items.Add("削除");

init();

}

~Form1()

{

dt.Dispose();

}

private void init()

{

string q = @"select * from tbl where 出庫日 is null order by ID";

if (checkBox3.Checked) q = @"select * from tbl where 出庫日 is not null order by ID";

if (checkBox4.Checked) q = @"select * from tbl where 入庫日 = #" + DateTime.Now.ToString("yyyy/MM/dd") + "# order by ID";

if (checkBox5.Checked) q = @"select * from tbl where 出庫日 = #" + DateTime.Now.ToString("yyyy/MM/dd") + "# order by ID";

dt = Util.getDt(q);

list_add();

}

public void list_add()

{

comboBox1.Items.Clear();

comboBox2.Items.Clear();

using (DataView dv = new DataView(dt))

{

using (DataTable tmp = dv.ToTable(true, new string[] { "仕入月" }))

{

DataRow[] dr = tmp.Select("仕入月 is not null");

for (int i = 0; i < dr.Length; i++)

{

comboBox1.Items.Add(DateTime.Parse(dr[i]["仕入月"].ToString()).ToString("yyyy年MM月"));

comboBox2.Items.Add(DateTime.Parse(dr[i]["仕入月"].ToString()).ToString("yyyy年MM月"));

}

private void Form1_Load(object sender, EventArgs e)

{

init_view(sender, e);

}

public void init_view(object sender, EventArgs e)

{

dataGridView1.Rows.Clear();

string tmp = "";

bool chk = false;

if (textBox2.Text != "")

{

tmp = "工番 like '%" + textBox2.Text + "%'";

chk = true;

}

if (textBox1.Text != "")

{

if (chk) tmp = tmp + " and ";

tmp = tmp + "製品名称 like '%" + textBox1.Text + "%'";

chk = true;

}

if (textBox3.Text != "")

{

if (chk) tmp = tmp + " and ";

tmp = tmp + " 仕入先 like '%" + textBox3.Text + "%'";

chk = true;

}

if (textBox5.Text != "")

{

if (chk) tmp = tmp + " and ";

tmp = tmp + " 区分 like '%" + textBox5.Text + "%'";

chk = true;

}

if (comboBox1.Text != "" && comboBox2.Enabled == false)

{

if (chk) tmp = tmp + " and ";

tmp = tmp + " 仕入月 = #" + DateTime.Parse(comboBox1.Text).ToString("yyyy/MM/01") + "#";

}

else if (comboBox1.Text != "" && comboBox2.Enabled == true && comboBox2.Text != "")

{

if (chk) tmp = tmp + " and ";

tmp = tmp + " 仕入月 >= #" + DateTime.Parse(comboBox1.Text).ToString("yyyy/MM/01")

+ "# and 仕入月 <= #" + DateTime.Parse(comboBox2.Text).ToString("yyyy/MM/01") + "#";

}

else if (comboBox1.Text != "" && comboBox2.Enabled == true && comboBox2.Text == "")

{

if (chk) tmp = tmp + " and ";

tmp = tmp + " 仕入月 >= #" + DateTime.Parse(comboBox1.Text).ToString("yyyy/MM/01") + "#";

}

else if (comboBox1.Text == "" && comboBox2.Enabled == true && comboBox2.Text != "")

{

if (chk) tmp = tmp + " and ";

tmp = tmp + " 仕入月 <= #" + DateTime.Parse(comboBox2.Text).ToString("yyyy/MM/01") + "#";

}

System.Diagnostics.Debug.Print(tmp.ToString());

DataRow[] dr = dt.Select(tmp, "ID");

decimal[,] rc = new decimal[6, 4];

for (int i = 0; i < dr.Length; i++)

{

decimal res00;

decimal.TryParse(dr[i]["数量"].ToString(), out res00);

decimal res01;

decimal.TryParse(dr[i]["計上単価"].ToString(), out res01);

DateTime res02;

if (!DateTime.TryParse(dr[i]["仕入月"].ToString(), out res02)) res02 = new DateTime(1970, 1, 1);

dataGridView1.Rows.Add(

dr[i]["ID"].ToString(),

dr[i]["工番"].ToString(),

dr[i]["製品名称"].ToString(),

dr[i]["仕入先"].ToString(),

res00.ToString(),

string.Format("{0:#,0}", res01),

string.Format("{0:#,0}", (res00 * res01)),

dr[i]["区分"].ToString(),

res02.ToString("yyyy年MM月"),

dr[i]["備考"].ToString()

);

switch (dr[i]["区分"].ToString())

{

case "内作材料①":

if ((res00 * res01) >= 50000)

{

rc[1, 2] = rc[1, 2] + (res00 * res01);

}

else

{

rc[1, 3] = rc[1, 3] + (res00 * res01);

}

rc[1, 1] = rc[1, 1] + (res00 * res01);

break;

case "外作材料①":

if ((res00 * res01) >= 50000)

{

rc[2, 2] = rc[2, 2] + (res00 * res01);

}

else

{

rc[2, 3] = rc[2, 3] + (res00 * res01);

}

rc[2, 1] = rc[2, 1] + (res00 * res01);

break;

case "内作製品":

if ((res00 * res01) >= 50000)

{

rc[3, 2] = rc[3, 2] + (res00 * res01);

}

else

{

rc[3, 3] = rc[3, 3] + (res00 * res01);

}

rc[3, 1] = rc[3, 1] + (res00 * res01);

break;

case "外作製品":

if ((res00 * res01) >= 50000)

{

rc[4, 2] = rc[4, 2] + (res00 * res01);

}

else

{

rc[4, 3] = rc[4, 3] + (res00 * res01);

}

rc[4, 1] = rc[4, 1] + (res00 * res01);

break;

default:

if ((res00 * res01) >= 50000)

{

rc[5, 2] = rc[5, 2] + (res00 * res01);

}

else

{

rc[5, 3] = rc[5, 3] + (res00 * res01);

}

rc[5, 1] = rc[5, 1] + (res00 * res01);

break;

}

textBox4.Text = string.Format("{0:#,0}", rc[1, 1]);

textBox8.Text = string.Format("{0:#,0}", rc[2, 1]);

textBox11.Text = string.Format("{0:#,0}", rc[3, 1]);

textBox14.Text = string.Format("{0:#,0}", rc[4, 1]);

textBox17.Text = string.Format("{0:#,0}", rc[5, 1]);

textBox20.Text = string.Format("{0:#,0}", rc[1, 1] + rc[2, 1] + rc[3, 1] + rc[4, 1] + rc[5, 1]);

textBox6.Text = string.Format("{0:#,0}", rc[1, 2]);

textBox9.Text = string.Format("{0:#,0}", rc[2, 2]);

textBox12.Text = string.Format("{0:#,0}", rc[3, 2]);

textBox15.Text = string.Format("{0:#,0}", rc[4, 2]);

textBox18.Text = string.Format("{0:#,0}", rc[5, 2]);

textBox21.Text = string.Format("{0:#,0}", rc[1, 2] + rc[2, 2] + rc[3, 2] + rc[4, 2] + rc[5, 2]);

textBox7.Text = string.Format("{0:#,0}", rc[1, 3]);

textBox10.Text = string.Format("{0:#,0}", rc[2, 3]);

textBox13.Text = string.Format("{0:#,0}", rc[3, 3]);

textBox16.Text = string.Format("{0:#,0}", rc[4, 3]);

textBox19.Text = string.Format("{0:#,0}", rc[5, 3]);

textBox22.Text = string.Format("{0:#,0}", rc[1, 3] + rc[2, 3] + rc[3, 3] + rc[4, 3] + rc[5, 3]);

label16.Text = dr.Length.ToString() + "件取得しました";

}

private void button1_Click(object sender, EventArgs e)

{

textBox1.Text = "";

textBox2.Text = "";

textBox3.Text = "";

textBox5.Text = "";

comboBox1.Text = "";

comboBox2.Text = "";

checkBox1.Checked = false;

comboBox2.Enabled = false;

textBox23.Text = "";

textBox24.Text = "";

textBox25.Text = "";

textBox26.Text = "";

textBox27.Text = "";

textBox28.Text = "";

textBox29.Text = "";

textBox30.Text = "";

init_view(sender, e);

}

private void dataGridView1_CellMouseDown(object sender, DataGridViewCellMouseEventArgs e)

{

if (e.Button == MouseButtons.Right)

{

dataGridView1.ClearSelection();

if (e.RowIndex >= 0 && e.ColumnIndex >= 0) dataGridView1.Rows[e.RowIndex].Selected = true;

foreach (ToolStripMenuItem tsmi in contextMenuStrip1.Items)

{

if (tsmi.Text == "出庫取消")

{

contextMenuStrip1.Items.Remove(tsmi);

break;

}

if (checkBox3.Checked || checkBox5.Checked) contextMenuStrip1.Items.Add("出庫取消");

}

private string[] parse_num(string[] tmp)

{

string[] res = new string[3];

decimal out001;

if (tmp[0] == "" || !decimal.TryParse(tmp[0], out out001))

{

res[0] = "0";

}

else

{

res[0] = out001.ToString();

}

decimal out002;

if (tmp[1] == "" || !decimal.TryParse(tmp[1], out out002))

{

res[1] = "0";

}

else

{

res[1] = out002.ToString();

}

DateTime out003;

if (tmp[2].Length == 6 && tmp[2].Substring(4, 1) != "/")

{

tmp[2] = tmp[2].Substring(0, 4) + "/" + tmp[2].Substring(4, 2) + "/1";

}

else if (tmp[2].Length == 6 && tmp[2].Substring(4, 1) == "/")

{

tmp[2] = tmp[2].Substring(0, 4) + "/" + tmp[2].Substring(5, 1) + "/1";

}

else if (tmp[2].Length == 7 && tmp[2].Substring(4, 1) == "/")

{

tmp[2] = tmp[2].Substring(0, 4) + "/" + tmp[2].Substring(5, 2) + "/1";

}

if (tmp[2] == "" || !DateTime.TryParse(tmp[2], out out003))

{

res[2] = "1970/01/01";

}

else

{

res[2] = out003.ToString("yyyy/MM/01");

}

return res;

}

private void contextMenuStrip1_ItemClicked(object sender, ToolStripItemClickedEventArgs e)

{

if (dataGridView1.Rows.Count == 0) return;

set_selection(dataGridView1.SelectedRows[0].Index);

string q = "";

switch (e.ClickedItem.Text.ToString())

{

case "更新":

if (dgvr.Cells[4].Value == null || dgvr.Cells[5].Value == null || dgvr.Cells[8].Value == null)

{

MessageBox.Show("数量、計上単価、仕入月が空白では処理できません。");

return;

}

string[] res = parse_num(new string[] {

dgvr.Cells[4].Value.ToString(),

dgvr.Cells[5].Value.ToString(),

dgvr.Cells[8].Value.ToString()

} );

q = @"update tbl set " +

"工番='" + dgvr.Cells[1].Value.ToString() + "'," +

"製品名称='" + dgvr.Cells[2].Value.ToString() + "'," +

"仕入先='" + dgvr.Cells[3].Value.ToString() + "'," +

"数量=" + res[0] + "," +

"計上単価=" + res[1] + "," +

"区分='" + dgvr.Cells[7].Value.ToString() + "'," +

"仕入月=#" + res[2] + "#," +

"備考='" + dgvr.Cells[9].Value.ToString() + "' " +

"where ID = " + dgvr.Cells[0].Value.ToString() + "";

break;

case "出庫":

q = @"update tbl set " +

"出庫日=#" + DateTime.Now.ToString("yyyy/MM/dd") + "# " +

"where ID = " + dgvr.Cells[0].Value.ToString() + "";

break;

case "出庫取消":

q = @"update tbl set " +

"出庫日=NULL " +

"where ID = " + dgvr.Cells[0].Value.ToString() + "";

break;

case "複写":

q = @"insert into tbl select 工番,製品名称,仕入先,数量,計上単価,区分,仕入月,備考,出庫日,入庫日 from tbl " +

"where ID = " + dgvr.Cells[0].Value.ToString() + "";

break;

case "削除":

q = @"delete from tbl " +

"where ID = " + dgvr.Cells[0].Value.ToString() + "";

break;

}

Util.exeQry(q);

init();

init_view(sender, e);

init_selection(e.ClickedItem.Text.ToString());

MessageBox.Show(e.ClickedItem.Text.ToString() + "しました。");

}

private void init_selection(string tmp)

{

if (dataGridView1.Rows.Count == 0) return;

switch (tmp)

{

case "更新":

case "出庫":

case "削除":

if (dgv_rows_count == dataGridView1.Rows.Count)

{

dataGridView1.Rows[dgv_selected].Selected = true;

dataGridView1.FirstDisplayedScrollingRowIndex = dgv_view_index;

}

else

{

if (dgv_selected >= 1) dataGridView1.Rows[dgv_selected - 1].Selected = true;

dataGridView1.FirstDisplayedScrollingRowIndex = dgv_view_index;

}

break;

case "複写":

case "新規":

dataGridView1.Rows[dataGridView1.Rows.Count - 1].Selected = true;

dataGridView1.FirstDisplayedScrollingRowIndex = dataGridView1.Rows.Count - 1;

break;

}

private DataGridViewRow dgvr;

private int dgv_view_index;

private int dgv_selected;

private int dgv_rows_count;

private void set_selection(int rows_index)

{

dgvr = dataGridView1.SelectedRows[0];

dgv_view_index = dataGridView1.FirstDisplayedScrollingRowIndex;

dgv_selected = rows_index;

dgv_rows_count = dataGridView1.Rows.Count;

}

private void comboBox1_TextChanged(object sender, EventArgs e)

{

DateTime out001;

if (!DateTime.TryParse(comboBox1.Text, out out001)) comboBox1.Text = "";

init_view(sender, e);

}

private void comboBox2_TextChanged(object sender, EventArgs e)

{

DateTime out001;

if (!DateTime.TryParse(comboBox2.Text, out out001)) comboBox2.Text = "";

init_view(sender, e);

}

private void checkBox1_CheckedChanged(object sender, EventArgs e)

{

if(checkBox1.Checked)

{

comboBox2.Enabled = true;

}

else

{

comboBox2.Text = "";

comboBox2.Enabled = false;

}

init_view(sender, e);

}

private void button2_Click(object sender, EventArgs e)

{

checkBox3.Checked = false;

string[] res = parse_num(new string[] {

textBox26.Text,

textBox27.Text,

textBox29.Text

});

string q = @"insert into tbl (工番,製品名称,仕入先,数量,計上単価,区分,仕入月,備考,入庫日) values " +

"('" + textBox23.Text + "','" + textBox24.Text + "','" + textBox25.Text + "'" +

"," + res[0] + "," + res[1] + ",'" + textBox28.Text + "',#" + res[2] + "#,'" + textBox30.Text + "',#" + DateTime.Now.ToString("yyyy/MM/dd") + "#)";

Util.exeQry(q);

init();

init_view(sender, e);

init_selection("新規");

button1.PerformClick();

MessageBox.Show("新規登録しました。");

}

private void btn5678_click(object sender, EventArgs e)

{

switch (((Button)sender).Text)

{

case "更新":

contextMenuStrip1.Items[0].PerformClick();

break;

case "出庫":

contextMenuStrip1.Items[1].PerformClick();

break;

case "複写":

contextMenuStrip1.Items[2].PerformClick();

break;

case "削除":

contextMenuStrip1.Items[3].PerformClick();

break;

}

private void btn34_click(object sender, EventArgs e)

{

DialogResult res = MessageBox.Show("表示されている行が" + ((Button)sender).Text + "されます。実行しますか？","",MessageBoxButtons.OKCancel);

if (res == DialogResult.Cancel) return;

for (int r = 0; r < dataGridView1.Rows.Count; r++)

{

string q = "";

if (((Button)sender).Text == "全て出庫")

{

q = @"update tbl set " +

"出庫日=#" + DateTime.Now.ToString("yyyy/MM/dd") + "# " +

"where ID = " + dataGridView1.Rows[r].Cells[0].Value.ToString() + "";

}

else

{

q = @"delete from tbl " +

"where ID = " + dataGridView1.Rows[r].Cells[0].Value.ToString() + "";

}

Text = r + "件目処理中...";

Util.exeQry(q);

}

Text = "全在庫管理 v1.0";

init();

init_view(sender, e);

button1.PerformClick();

MessageBox.Show(((Button)sender).Text + "しました。");

}

private void checkBox3_CheckedChanged(object sender, EventArgs e)

{

if (checkBox3.Checked)

{

checkBox4.Checked = false;

checkBox5.Checked = false;

}

init();

init_view(sender, e);

button1.PerformClick();

}

private void checkBox4_CheckedChanged(object sender, EventArgs e)

{

if (checkBox4.Checked)

{

checkBox3.Checked = false;

checkBox5.Checked = false;

}

init();

init_view(sender, e);

button1.PerformClick();

}

private void checkBox5_CheckedChanged(object sender, EventArgs e)

{

if (checkBox5.Checked)

{

checkBox3.Checked = false;

checkBox4.Checked = false;

}

init();

init_view(sender, e);

button1.PerformClick();

}

Util.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

namespace 全在庫管理
{
    class Util
    {
        public static void exeQry(string q)
        {
            using (OleDbConnection con = new OleDbConnection(@"provider=microsoft.jet.oledb.4.0;data source=" + Application.StartupPath + @"\zen_zaiko.mdb"))
            {
                try
                {
                    con.Open();
                    OleDbCommand cmd = new OleDbCommand(q, con);
                    cmd.ExecuteNonQuery();
                }
                finally
                {
                    con.Close();
                }
            }
        }

        public static DataTable getDt(string q)
        {
            DataTable dt;

            using (OleDbConnection con = new OleDbConnection(@"provider=microsoft.jet.oledb.4.0;data source=" + Application.StartupPath + @"\zen_zaiko.mdb"))
            {
                try
                {
                    con.Open();
                    OleDbCommand cmd = new OleDbCommand(q, con);
                    using (OleDbDataReader dr = cmd.ExecuteReader())
                    {
                        dt = new DataTable();
                        dt.Load(dr);
                        dr.Close();
                    }
                }
                finally
                {
                    con.Close();
                }

            }
            return dt;
        }
    }
}

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Data;

using System.Data.OleDb;

using System.Windows.Forms;

namespace 全在庫管理

{

class Util

{

public static void exeQry(string q)

{

using (OleDbConnection con = new OleDbConnection(@"provider=microsoft.jet.oledb.4.0;data source=" + Application.StartupPath + @"\zen_zaiko.mdb"))

{

try

{

con.Open();

OleDbCommand cmd = new OleDbCommand(q, con);

cmd.ExecuteNonQuery();

}

finally

{

con.Close();

}

public static DataTable getDt(string q)

{

DataTable dt;

using (OleDbConnection con = new OleDbConnection(@"provider=microsoft.jet.oledb.4.0;data source=" + Application.StartupPath + @"\zen_zaiko.mdb"))

{

try

{

con.Open();

OleDbCommand cmd = new OleDbCommand(q, con);

using (OleDbDataReader dr = cmd.ExecuteReader())

{

dt = new DataTable();

dt.Load(dr);

dr.Close();

}

finally

{

con.Close();

}

return dt;

}

アップロードVBA

Public frm_type As String
Public frm_date As String
Public frm_book As String
Public frm_chk As Boolean

Sub upload_zaiko()

On Error GoTo ex

frm_chk = False

UserForm1.Show

If frm_chk = False Then Exit Sub

If frm_type = "" Then

  MsgBox "区分を選択してください。終了します。"
  Exit Sub

End If

If frm_date = "" Then

  MsgBox "仕入月を選択してください。終了します。"
  Exit Sub

End If

If frm_book = "" Then

  MsgBox "対象ブックを選択してください。終了します。"
  Exit Sub

End If

Application.ScreenUpdating = False
Application.DisplayAlerts = False

p = ThisWorkbook.Path & "\zen_zaiko.mdb"

t = Selection(1).Row
b = Selection(Selection.Count).Row

Set con = CreateObject("adodb.connection")
Set res = CreateObject("adodb.recordset")

con.Open "provider=microsoft.jet.oledb.4.0;data source=" & p

Dim buf(9) As String

For r = t To b

  If frm_book = "仕入台帳" Then
  
    buf(1) = h(Cells(r, 10).Text, "str") '工番
    buf(2) = h(Cells(r, 5).Text & " " & Cells(r, 6).Text & " " & Cells(r, 7).Text, "str") & "(" & h(Cells(r, 12).Text, "str") & ")" '製品名称/型式
    buf(3) = h(Cells(r, 3).Text, "str") '仕入先
    buf(4) = h(Cells(r, 15).Text, "int") '数量
    buf(5) = h(Cells(r, 18).Text, "cur") '計上単価
    buf(6) = frm_type '区分
    buf(7) = Format(CDate(frm_date), "yyyy/MM/01") '仕入月
    buf(8) = "" '備考
    buf(9) = Format(Year(Date) & "/" & Month(Date) & "/" & Day(Date), "yyyy/MM/dd") '入庫日
    
  ElseIf frm_book = "工程表" Then
  
    buf(1) = h(Cells(r, 3).Text, "str") '工番
    buf(2) = h(Cells(r, 2).Text, "str") & "(" & h(Cells(r, 102).Text, "str") & ")" '製品名称/客先
    buf(3) = h(ActiveSheet.Name, "str") '班
    buf(4) = h(Cells(r, 5).Text, "int") '数量
    buf(5) = h(Cells(r, 104).Text, "cur") '計上単価
    buf(6) = frm_type '区分
    buf(7) = Format(CDate(frm_date), "yyyy/MM/01") '仕入月
    buf(8) = "" '備考
    buf(9) = Format(Year(Date) & "/" & Month(Date) & "/" & Day(Date), "yyyy/MM/dd") '入庫日
    
  End If
  
  q = "insert into tbl (工番,製品名称,仕入先,数量,計上単価,区分,仕入月,備考,入庫日) values (" & _
  "'" & buf(1) & "'," & _
  "'" & buf(2) & "'," & _
  "'" & buf(3) & "'," & _
  buf(4) & "," & _
  buf(5) & "," & _
  "'" & buf(6) & "'," & _
  "'" & buf(7) & "'," & _
  "'" & buf(8) & "'," & _
  "'" & buf(9) & "')"
  
  con.Execute (q)

Next r

If con.State = 1 Then con.Close
Set con = Nothing

Application.ScreenUpdating = True
Application.DisplayAlerts = True

MsgBox "終了しました。"

Exit Sub

ex:

  If con.State = 1 Then con.Close
  Set con = Nothing

  Application.ScreenUpdating = True
  Application.DisplayAlerts = True

  MsgBox "エラーが発生しました。"

End Sub

Function h(str, chk)

On Error GoTo ex

If (chk = "int" Or chk = "cur") And str = "" Then

  h = 0
  Exit Function

End If

If chk = "int" Then

  If IsNumeric(str) Then
  
    h = CInt(str)
    Exit Function
  
  Else
  
    h = 0
    Exit Function
  
  End If

End If

If chk = "cur" Then

  If IsNumeric(str) Then
  
    h = CCur(str)
    Exit Function
  
  Else
  
    h = 0
    Exit Function
  
  End If

End If

If chk = "str" And str <> "" Then

  str = Replace(str, ",", "，")
  str = Replace(str, "'", "’")
  '全角空白
  str = Replace(str, "　", " ")
  '半角空白
  str = Replace(str, "  ", " ")
  str = Replace(str, "  ", " ")
  str = Replace(str, "  ", " ")
  
  h = str
  
  Exit Function

ElseIf chk = "str" And str = "" Then

  h = ""
  
  Exit Function

End If

Exit Function

ex:

  MsgBox "文字列の変換エラーが発生しました。"
  h = 0

End Function

Sub auto_open()

On Error Resume Next

Application.CommandBars("cell").Controls("全在庫へアップロード").Delete

With Application.CommandBars("cell").Controls.Add

  .Caption = "全在庫へアップロード"
  .OnAction = "upload_zaiko"

End With

End Sub

Sub auto_close()

On Error Resume Next

Application.CommandBars("cell").Controls("全在庫へアップロード").Delete
Application.CommandBars("cell").Controls("全在庫へアップロード").Delete

End Sub

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

Public frm_type As String

Public frm_date As String

Public frm_book As String

Public frm_chk As Boolean

Sub upload_zaiko()

On Error GoTo ex

frm_chk = False

UserForm1.Show

If frm_chk = False Then Exit Sub

If frm_type = "" Then

MsgBox "区分を選択してください。終了します。"

Exit Sub

End If

If frm_date = "" Then

MsgBox "仕入月を選択してください。終了します。"

Exit Sub

End If

If frm_book = "" Then

MsgBox "対象ブックを選択してください。終了します。"

Exit Sub

End If

Application.ScreenUpdating = False

Application.DisplayAlerts = False

p = ThisWorkbook.Path & "\zen_zaiko.mdb"

t = Selection(1).Row

b = Selection(Selection.Count).Row

Set con = CreateObject("adodb.connection")

Set res = CreateObject("adodb.recordset")

con.Open "provider=microsoft.jet.oledb.4.0;data source=" & p

Dim buf(9) As String

For r = t To b

If frm_book = "仕入台帳" Then

buf(1) = h(Cells(r, 10).Text, "str") '工番

buf(2) = h(Cells(r, 5).Text & " " & Cells(r, 6).Text & " " & Cells(r, 7).Text, "str") & "(" & h(Cells(r, 12).Text, "str") & ")" '製品名称/型式

buf(3) = h(Cells(r, 3).Text, "str") '仕入先

buf(4) = h(Cells(r, 15).Text, "int") '数量

buf(5) = h(Cells(r, 18).Text, "cur") '計上単価

buf(6) = frm_type '区分

buf(7) = Format(CDate(frm_date), "yyyy/MM/01") '仕入月

buf(8) = "" '備考

buf(9) = Format(Year(Date) & "/" & Month(Date) & "/" & Day(Date), "yyyy/MM/dd") '入庫日

ElseIf frm_book = "工程表" Then

buf(1) = h(Cells(r, 3).Text, "str") '工番

buf(2) = h(Cells(r, 2).Text, "str") & "(" & h(Cells(r, 102).Text, "str") & ")" '製品名称/客先

buf(3) = h(ActiveSheet.Name, "str") '班

buf(4) = h(Cells(r, 5).Text, "int") '数量

buf(5) = h(Cells(r, 104).Text, "cur") '計上単価

buf(6) = frm_type '区分

buf(7) = Format(CDate(frm_date), "yyyy/MM/01") '仕入月

buf(8) = "" '備考

buf(9) = Format(Year(Date) & "/" & Month(Date) & "/" & Day(Date), "yyyy/MM/dd") '入庫日

End If

q = "insert into tbl (工番,製品名称,仕入先,数量,計上単価,区分,仕入月,備考,入庫日) values (" & _

"'" & buf(1) & "'," & _

"'" & buf(2) & "'," & _

"'" & buf(3) & "'," & _

buf(4) & "," & _

buf(5) & "," & _

"'" & buf(6) & "'," & _

"'" & buf(7) & "'," & _

"'" & buf(8) & "'," & _

"'" & buf(9) & "')"

con.Execute (q)

Next r

If con.State = 1 Then con.Close

Set con = Nothing

Application.ScreenUpdating = True

Application.DisplayAlerts = True

MsgBox "終了しました。"

Exit Sub

ex:

If con.State = 1 Then con.Close

Set con = Nothing

Application.ScreenUpdating = True

Application.DisplayAlerts = True

MsgBox "エラーが発生しました。"

End Sub

Function h(str, chk)

On Error GoTo ex

If (chk = "int" Or chk = "cur") And str = "" Then

h = 0

Exit Function

End If

If chk = "int" Then

If IsNumeric(str) Then

h = CInt(str)

Exit Function

Else

h = 0

Exit Function

End If

If chk = "cur" Then

If IsNumeric(str) Then

h = CCur(str)

Exit Function

Else

h = 0

Exit Function

End If

If chk = "str" And str <> "" Then

str = Replace(str, ",", "，")

str = Replace(str, "'", "’")

'全角空白

str = Replace(str, "　", " ")

'半角空白

str = Replace(str, " ", " ")

h = str

Exit Function

ElseIf chk = "str" And str = "" Then

h = ""

Exit Function

End If

Exit Function

ex:

MsgBox "文字列の変換エラーが発生しました。"

h = 0

End Function

Sub auto_open()

On Error Resume Next

Application.CommandBars("cell").Controls("全在庫へアップロード").Delete

With Application.CommandBars("cell").Controls.Add

.Caption = "全在庫へアップロード"

.OnAction = "upload_zaiko"

End With

End Sub

Sub auto_close()

On Error Resume Next

Application.CommandBars("cell").Controls("全在庫へアップロード").Delete

End Sub

2017-03-062017-03-06

AccessVBA テーブル作成、他

Sub test()

'テーブル作成 --------------------------------------------------------

Set t = CurrentDb.CreateTableDef("ttest")
Set f = t.CreateField("id", dbDate)

t.Fields.Append f
CurrentDb.TableDefs.Append t

'パススルークエリ作成 --------------------------------------------------------
'SQLServerへ接続

queryName = "test"

For i = 0 To CurrentDb.QueryDefs.Count - 1
  If CurrentDb.QueryDefs(i).Name = queryName Then CurrentDb.QueryDefs.Delete queryName
Next i

Set t = CurrentDb.CreateQueryDef(queryName)

With t
.Connect = "ODBC; Driver=SQL Server; Server=ESPRIMO\SQLEXPRESS; Database=my_database"
.ReturnsRecords = True
.SQL = "SELECT * FROM tbl;"
End With

Set t = Nothing

'更新系などのパススルークエリも作成できる --------------------------------------------------------

queryName = "test"

For i = 0 To CurrentDb.QueryDefs.Count - 1
  If CurrentDb.QueryDefs(i).Name = queryName Then CurrentDb.QueryDefs.Delete queryName
Next i

Set t = CurrentDb.CreateQueryDef(queryName)

With t
.Connect = "ODBC; Driver=SQL Server; Server=ESPRIMO\SQLEXPRESS;Database=my_database"
.ReturnsRecords = False
.SQL = "DELETE FROM tbl;"
End With

Set t = Nothing

'リンクテーブル(mdbへ接続) --------------------------------------------------------

Set t = CurrentDb.CreateTableDef("AccessLink")
t.Connect = ";DATABASE=" & Application.CurrentProject.Path & "/tmp.mdb;"
t.SourceTableName = "tbl"
CurrentDb.TableDefs.Append t

'テーブル削除 --------------------------------------------------------

tableName = "test_tbl"

For i = 0 To CurrentDb.TableDefs.Count - 1

  If CurrentDb.TableDefs(i).Name = tableName Then
    CurrentDb.TableDefs.Delete tableName
    Exit For
  End If

Next i

'クエリの実行 --------------------------------------------------------

'new_tableを作成しold_tableのデータをnew_tableへ入れる。
CurrentDb.Execute "select * into new_table from old_table"

'new_tableにold_tableのデータを入れる。
CurrentDb.Execute "insert into new_table select * from old_table"

End Sub

Sub test()

'テーブル作成 --------------------------------------------------------

Set t = CurrentDb.CreateTableDef("ttest")

Set f = t.CreateField("id", dbDate)

t.Fields.Append f

CurrentDb.TableDefs.Append t

'パススルークエリ作成 --------------------------------------------------------

'SQLServerへ接続

queryName = "test"

For i = 0 To CurrentDb.QueryDefs.Count - 1

If CurrentDb.QueryDefs(i).Name = queryName Then CurrentDb.QueryDefs.Delete queryName

Next i

Set t = CurrentDb.CreateQueryDef(queryName)

With t

.Connect = "ODBC; Driver=SQL Server; Server=ESPRIMO\SQLEXPRESS; Database=my_database"

.ReturnsRecords = True

.SQL = "SELECT * FROM tbl;"

End With

Set t = Nothing

'更新系などのパススルークエリも作成できる --------------------------------------------------------

queryName = "test"

For i = 0 To CurrentDb.QueryDefs.Count - 1

If CurrentDb.QueryDefs(i).Name = queryName Then CurrentDb.QueryDefs.Delete queryName

Next i

Set t = CurrentDb.CreateQueryDef(queryName)

With t

.Connect = "ODBC; Driver=SQL Server; Server=ESPRIMO\SQLEXPRESS;Database=my_database"

.ReturnsRecords = False

.SQL = "DELETE FROM tbl;"

End With

Set t = Nothing

'リンクテーブル(mdbへ接続) --------------------------------------------------------

Set t = CurrentDb.CreateTableDef("AccessLink")

t.Connect = ";DATABASE=" & Application.CurrentProject.Path & "/tmp.mdb;"

t.SourceTableName = "tbl"

CurrentDb.TableDefs.Append t

'テーブル削除 --------------------------------------------------------

tableName = "test_tbl"

For i = 0 To CurrentDb.TableDefs.Count - 1

If CurrentDb.TableDefs(i).Name = tableName Then

CurrentDb.TableDefs.Delete tableName

Exit For

End If

Next i

'クエリの実行 --------------------------------------------------------

'new_tableを作成しold_tableのデータをnew_tableへ入れる。

CurrentDb.Execute "select * into new_table from old_table"

'new_tableにold_tableのデータを入れる。

CurrentDb.Execute "insert into new_table select * from old_table"

End Sub

2017-03-06

VBA 農産物売上集計

mdbに登録する予定で作成していたが、途中でエクセルのみにしようと思い中断。

Sub auto_open()

On Error Resume Next

Application.CommandBars("cell").Controls("処理実行").Delete
Application.CommandBars("cell").Controls("シート初期化(貼付前処理)").Delete

With Application.CommandBars("cell").Controls.Add
.FaceId = 18
.Caption = "シート初期化(貼付前処理)"
.OnAction = "SheetsClear"
End With

With Application.CommandBars("cell").Controls.Add
.FaceId = 18
.Caption = "処理実行"
.OnAction = "MainProcedure"
End With

End Sub

Sub auto_close()

On Error Resume Next

Application.CommandBars("cell").Controls("処理実行").Delete
Application.CommandBars("cell").Controls("シート初期化(貼付前処理)").Delete

End Sub

Sub SheetsClear()

Sheets("<実績表集計表>貼付1").Rows("1:1000").Delete
Sheets("<実績表集計表>貼付2").Rows("1:1000").Delete
Sheets("<買取業者別 手数料集計一覧表>貼付").Rows("1:1000").Delete

End Sub

Sub MainProcedure()

On Error GoTo e

YearMonth = InputBox("登録月をYYYY/MMの形式で入力してください。")
YearMonth = StrConv(YearMonth, vbNarrow)
YearMonth = YearMonth & "/01"

If True = IsDate(YearMonth) Then

  yesno = MsgBox(Format(YearMonth, "yyyy年mm月") & "でよろしいですか？", vbYesNo)
  If yesno = vbNo Then Exit Sub

Else

  MsgBox "正しい登録月を入力してください。"
  Exit Sub

End If

If False = DataUpload("<実績表集計表>貼付1", 1, YearMonth) Then GoTo e
If False = DataUpload("<実績表集計表>貼付2", 1, YearMonth) Then GoTo e
If False = DataUpload("<買取業者別 手数料集計一覧表>貼付", 2, YearMonth) Then GoTo e

MsgBox "完了しました。"

Exit Sub

e:
  
  MsgBox "エラーが発生しています。"

End Sub

Function DataUpload(sheetName, sheetType, salesDate)

On Error GoTo e

Set con = CreateObject("adodb.connection")
con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & "/sales.mdb")

Dim w As Worksheet: Set w = Sheets(sheetName)


For r = 1 To w.UsedRange.Rows.Count

  If sheetType = 1 And _
  w.Cells(r, 1).Text <> "" And IsNumeric(w.Cells(r, 1).Text) And _
  w.Cells(r, 8).Text <> "" And IsNumeric(w.Cells(r, 8).Text) Then
  
    partnerCode = w.Cells(r, 1).Text
    partnerName = w.Cells(r, 2).Text
    salesVolume = w.Cells(r, 6).Text
    salesAmount = w.Cells(r, 7).Text
    marginCost = w.Cells(r, 10).Text
    pointCost = w.Cells(r, 18).Text
    overheadCost = w.Cells(r, 19).Text
    
    q = "insert into SalesTable (売上年月日,パートナーID,パートナー名称,売上数量,売上金額,手数料,ポイント負担額,諸経費,登録日時) " & _
    "values (#" & salesDate & "#,'" & partnerCode & "','" & partnerName & "','" & salesVolume & "','" & salesAmount & "','" & marginCost & "','" & pointCost & "','" & overheadCost & "',#" & Now() & "#)"
    
    con.Execute q
  
  End If
  
  If sheetType = 2 And _
  w.Cells(r, 1).Text <> "" And IsNumeric(w.Cells(r, 1).Text) And _
  w.Cells(r, 4).Text <> "" And IsNumeric(w.Cells(r, 4).Text) Then
  
    partnerCode = w.Cells(r, 1).Text
    partnerName = w.Cells(r, 2).Text
    salesVolume = w.Cells(r, 4).Text
    salesAmount = w.Cells(r, 5).Text
    marginCost = w.Cells(r, 7).Text
    pointCost = w.Cells(r, 9).Text
    overheadCost = 0

    q = "insert into SalesTable (売上年月日,パートナーID,パートナー名称,売上数量,売上金額,手数料,ポイント負担額,諸経費,登録日時) " & _
    "values (#" & salesDate & "#,'" & partnerCode & "','" & partnerName & "','" & salesVolume & "','" & salesAmount & "','" & marginCost & "','" & pointCost & "','" & overheadCost & "',#" & Now() & "#)"
    
    con.Execute q
    
  End If

Next r

If con.State = 1 Then con.Close
Set con = Nothing

DataUpload = True

Exit Function

e:

  If con.State = 1 Then con.Close
  Set con = Nothing
  
  DataUpload = False
  
End Function

Sub MasterUpdate()

On Error GoTo e

Set con = CreateObject("adodb.connection")
con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & "/sales.mdb")

q = "delete from MasterTable"
con.Execute q

Dim w As Worksheet: Set w = Sheets("マスタ")
        
For r = 1 To w.UsedRange.Rows.Count

  If w.Cells(r, 1).Text <> "" And w.Cells(r, 2).Text <> "" And w.Cells(r, 3).Text <> "" Then
  
    Select Case w.Cells(r, 3).Text
    
      Case "正契約農産物", "準契約農産物", "委託農産物", "買取農産物", "花卉", "契約加工品", "委託加工品", "買取加工品"
      
        partnerCode = w.Cells(r, 1).Text
        partnerName = w.Cells(r, 2).Text
        partnerType = w.Cells(r, 3).Text
    
        q = "insert into MasterTable (パートナーID,パートナー名称,パートナータイプ,登録日時) " & _
        "values ('" & partnerCode & "','" & partnerName & "','" & partnerType & "',#" & Now() & "#)"
        
        con.Execute q
        
        w.Rows(r).Columns("A:C").Interior.ColorIndex = 0 ' OKの場合
    
      Case Else
      
        w.Cells(r, 3).Interior.Color = RGB(255, 0, 0) 'A:Cは空白ではないが、C列が不正

    End Select
    
  Else
  
    w.Rows(r).Columns("A:C").Interior.Color = RGB(255, 0, 0) 'A:C列の何れか空白
  
  End If

Next r

If con.State = 1 Then con.Close
Set con = Nothing

MsgBox "完了しました。"

Exit Sub

e:

  If con.State = 1 Then con.Close
  Set con = Nothing
  
  MsgBox "エラーが発生しています。"
  
End Sub

Sub DataDelte()

On Error GoTo e

Dim w As Worksheet: Set w = Sheets("表紙")

yesno = MsgBox(w.Cells(6, 2).Text & "の日付で登録したデータは全て削除されます。よろしいですか？", vbYesNo)
If yesno = vbNo Then Exit Sub

Set con = CreateObject("adodb.connection")
con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & "/sales.mdb")

q = "delete from SalesTable where 売上年月日 = #" & w.Cells(6, 2).Text & "#"
con.Execute q

If con.State = 1 Then con.Close
Set con = Nothing

MsgBox "完了しました。"

Exit Sub

e:

  If con.State = 1 Then con.Close
  Set con = Nothing
  
  MsgBox "エラーが発生しています。"
  
End Sub

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

Sub auto_open()

On Error Resume Next

Application.CommandBars("cell").Controls("処理実行").Delete

Application.CommandBars("cell").Controls("シート初期化(貼付前処理)").Delete

With Application.CommandBars("cell").Controls.Add

.FaceId = 18

.Caption = "シート初期化(貼付前処理)"

.OnAction = "SheetsClear"

End With

With Application.CommandBars("cell").Controls.Add

.FaceId = 18

.Caption = "処理実行"

.OnAction = "MainProcedure"

End With

End Sub

Sub auto_close()

On Error Resume Next

Application.CommandBars("cell").Controls("処理実行").Delete

Application.CommandBars("cell").Controls("シート初期化(貼付前処理)").Delete

End Sub

Sub SheetsClear()

Sheets("<実績表集計表>貼付1").Rows("1:1000").Delete

Sheets("<実績表集計表>貼付2").Rows("1:1000").Delete

Sheets("<買取業者別手数料集計一覧表>貼付").Rows("1:1000").Delete

End Sub

Sub MainProcedure()

On Error GoTo e

YearMonth = InputBox("登録月をYYYY/MMの形式で入力してください。")

YearMonth = StrConv(YearMonth, vbNarrow)

YearMonth = YearMonth & "/01"

If True = IsDate(YearMonth) Then

yesno = MsgBox(Format(YearMonth, "yyyy年mm月") & "でよろしいですか？", vbYesNo)

If yesno = vbNo Then Exit Sub

Else

MsgBox "正しい登録月を入力してください。"

Exit Sub

End If

If False = DataUpload("<実績表集計表>貼付1", 1, YearMonth) Then GoTo e

If False = DataUpload("<実績表集計表>貼付2", 1, YearMonth) Then GoTo e

If False = DataUpload("<買取業者別手数料集計一覧表>貼付", 2, YearMonth) Then GoTo e

MsgBox "完了しました。"

Exit Sub

MsgBox "エラーが発生しています。"

End Sub

Function DataUpload(sheetName, sheetType, salesDate)

On Error GoTo e

Set con = CreateObject("adodb.connection")

con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & "/sales.mdb")

Dim w As Worksheet: Set w = Sheets(sheetName)

For r = 1 To w.UsedRange.Rows.Count

If sheetType = 1 And _

w.Cells(r, 1).Text <> "" And IsNumeric(w.Cells(r, 1).Text) And _

w.Cells(r, 8).Text <> "" And IsNumeric(w.Cells(r, 8).Text) Then

partnerCode = w.Cells(r, 1).Text

partnerName = w.Cells(r, 2).Text

salesVolume = w.Cells(r, 6).Text

salesAmount = w.Cells(r, 7).Text

marginCost = w.Cells(r, 10).Text

pointCost = w.Cells(r, 18).Text

overheadCost = w.Cells(r, 19).Text

q = "insert into SalesTable (売上年月日,パートナーID,パートナー名称,売上数量,売上金額,手数料,ポイント負担額,諸経費,登録日時) " & _

"values (#" & salesDate & "#,'" & partnerCode & "','" & partnerName & "','" & salesVolume & "','" & salesAmount & "','" & marginCost & "','" & pointCost & "','" & overheadCost & "',#" & Now() & "#)"

con.Execute q

End If

If sheetType = 2 And _

w.Cells(r, 1).Text <> "" And IsNumeric(w.Cells(r, 1).Text) And _

w.Cells(r, 4).Text <> "" And IsNumeric(w.Cells(r, 4).Text) Then

partnerCode = w.Cells(r, 1).Text

partnerName = w.Cells(r, 2).Text

salesVolume = w.Cells(r, 4).Text

salesAmount = w.Cells(r, 5).Text

marginCost = w.Cells(r, 7).Text

pointCost = w.Cells(r, 9).Text

overheadCost = 0

q = "insert into SalesTable (売上年月日,パートナーID,パートナー名称,売上数量,売上金額,手数料,ポイント負担額,諸経費,登録日時) " & _

"values (#" & salesDate & "#,'" & partnerCode & "','" & partnerName & "','" & salesVolume & "','" & salesAmount & "','" & marginCost & "','" & pointCost & "','" & overheadCost & "',#" & Now() & "#)"

con.Execute q

End If

Next r

If con.State = 1 Then con.Close

Set con = Nothing

DataUpload = True

Exit Function

If con.State = 1 Then con.Close

Set con = Nothing

DataUpload = False

End Function

Sub MasterUpdate()

On Error GoTo e

Set con = CreateObject("adodb.connection")

con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & "/sales.mdb")

q = "delete from MasterTable"

con.Execute q

Dim w As Worksheet: Set w = Sheets("マスタ")

For r = 1 To w.UsedRange.Rows.Count

If w.Cells(r, 1).Text <> "" And w.Cells(r, 2).Text <> "" And w.Cells(r, 3).Text <> "" Then

Select Case w.Cells(r, 3).Text

Case "正契約農産物", "準契約農産物", "委託農産物", "買取農産物", "花卉", "契約加工品", "委託加工品", "買取加工品"

partnerCode = w.Cells(r, 1).Text

partnerName = w.Cells(r, 2).Text

partnerType = w.Cells(r, 3).Text

q = "insert into MasterTable (パートナーID,パートナー名称,パートナータイプ,登録日時) " & _

"values ('" & partnerCode & "','" & partnerName & "','" & partnerType & "',#" & Now() & "#)"

con.Execute q

w.Rows(r).Columns("A:C").Interior.ColorIndex = 0 ' OKの場合

Case Else

w.Cells(r, 3).Interior.Color = RGB(255, 0, 0) 'A:Cは空白ではないが、C列が不正

End Select

Else

w.Rows(r).Columns("A:C").Interior.Color = RGB(255, 0, 0) 'A:C列の何れか空白

End If

Next r

If con.State = 1 Then con.Close

Set con = Nothing

MsgBox "完了しました。"

Exit Sub

If con.State = 1 Then con.Close

Set con = Nothing

MsgBox "エラーが発生しています。"

End Sub

Sub DataDelte()

On Error GoTo e

Dim w As Worksheet: Set w = Sheets("表紙")

yesno = MsgBox(w.Cells(6, 2).Text & "の日付で登録したデータは全て削除されます。よろしいですか？", vbYesNo)

If yesno = vbNo Then Exit Sub

Set con = CreateObject("adodb.connection")

con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & "/sales.mdb")

q = "delete from SalesTable where 売上年月日 = #" & w.Cells(6, 2).Text & "#"

con.Execute q

If con.State = 1 Then con.Close

Set con = Nothing

MsgBox "完了しました。"

Exit Sub

If con.State = 1 Then con.Close

Set con = Nothing

MsgBox "エラーが発生しています。"

End Sub

2017-02-222020-05-10

VBA SQL実験用②(SQL Server)

以前、Accessにテーブルを手軽作成するエクセルVBAがあったので、今回はSQL Server版。クエリの複数行対応と、シート名でテーブル作成へ少し修正している。

Sub auto_open()

Application.CommandBars("cell").Reset

With Application.CommandBars("cell").Controls.Add
.OnAction = "t"
.Caption = "テーブル作成・登録"
End With

With Application.CommandBars("cell").Controls.Add
.OnAction = "tt"
.Caption = "SQL実行"
End With

End Sub

Sub auto_close()

On Error Resume Next

For i = 1 To 2
  Application.CommandBars("cell").Controls("テーブル作成・登録").Delete
  Application.CommandBars("cell").Controls("SQL実行").Delete
Next i

End Sub

Sub t()

Set cn = CreateObject("adodb.connection")
cn.Open "Provider=SQLOLEDB;Data Source=ESPRIMO\SQLEXPRESS;Initial Catalog=my_database;Integrated Security=SSPI;"

tbl = ActiveSheet.Name

'テーブル削除

On Error Resume Next
cn.Execute "drop table " & tbl

'テーブル作成

On Error GoTo 0

fld = ""

For c = Selection(1).Column To Selection(Selection.Count).Column

  Select Case Right(ActiveSheet.Cells(Selection(1).Row, c).Value, 1)
    Case "s"
    tmp = "varchar(255)"
    Case "i"
    tmp = "bigint"
    Case "m"
    tmp = "money"
    Case "d"
    tmp = "date"
  End Select
  
  fld = fld & ActiveSheet.Cells(Selection(1).Row, c).Value & " " & tmp & ","

Next c

fld = Left(fld, Len(fld) - 1)

q = "create table " & tbl & " (id integer identity(1,1) primary key," & fld & ")": Debug.Print q
cn.Execute q

'データ登録

fld = ""

For c = Selection(1).Column To Selection(Selection.Count).Column
  fld = fld & ActiveSheet.Cells(Selection(1).Row, c).Value & ","
Next c

fld = Left(fld, Len(fld) - 1)

For r = (Selection(1).Row) + 1 To Selection(Selection.Count).Row
  For cc = Selection(1).Column To Selection(Selection.Count).Column
    rec = rec & "'" & ActiveSheet.Cells(r, cc).Value & "'" & ","
  Next cc

  rec = Left(rec, Len(rec) - 1)
  
  q = "insert into " & tbl & " (" & fld & ") values (" & rec & ")": Debug.Print q
  cn.Execute q

  rec = ""
Next r

If cn.State = 1 Then cn.Close
Set cn = Nothing

MsgBox "done"

End Sub

Sub tt()

Set cn = CreateObject("adodb.connection")
Set rn = CreateObject("adodb.recordset")

cn.Open "Provider=SQLOLEDB;Data Source=ESPRIMO\SQLEXPRESS;Initial Catalog=my_database;Integrated Security=SSPI;"

If Selection.Rows.Count = 1 Then
  rn.Open ActiveCell.Value, cn
Else
  For r = 1 To Selection.Rows.Count
    q = q & " " & Selection(r)
  Next r
  rn.Open q, cn: Debug.Print q
End If

'フィールド作成

i = 0

For c = Selection(1).Column To (Selection(1).Column + rn.Fields.Count) - 1
  Cells(Selection(Selection.Count).Row + 1, c).Value = rn.Fields(i).Name
  i = i + 1
Next c

'データ読込

Cells(Selection(Selection.Count).Row + 2, Selection(1).Column).CopyFromRecordset rn

If rn.State = 1 Then rn.Close
Set rn = Nothing

If cn.State = 1 Then cn.Close
Set cn = Nothing

End Sub

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

Sub auto_open()

Application.CommandBars("cell").Reset

With Application.CommandBars("cell").Controls.Add

.OnAction = "t"

.Caption = "テーブル作成・登録"

End With

With Application.CommandBars("cell").Controls.Add

.OnAction = "tt"

.Caption = "SQL実行"

End With

End Sub

Sub auto_close()

On Error Resume Next

For i = 1 To 2

Application.CommandBars("cell").Controls("テーブル作成・登録").Delete

Application.CommandBars("cell").Controls("SQL実行").Delete

Next i

End Sub

Sub t()

Set cn = CreateObject("adodb.connection")

cn.Open "Provider=SQLOLEDB;Data Source=ESPRIMO\SQLEXPRESS;Initial Catalog=my_database;Integrated Security=SSPI;"

tbl = ActiveSheet.Name

'テーブル削除

On Error Resume Next

cn.Execute "drop table " & tbl

'テーブル作成

On Error GoTo 0

fld = ""

For c = Selection(1).Column To Selection(Selection.Count).Column

Select Case Right(ActiveSheet.Cells(Selection(1).Row, c).Value, 1)

Case "s"

tmp = "varchar(255)"

Case "i"

tmp = "bigint"

Case "m"

tmp = "money"

Case "d"

tmp = "date"

End Select

fld = fld & ActiveSheet.Cells(Selection(1).Row, c).Value & " " & tmp & ","

Next c

fld = Left(fld, Len(fld) - 1)

q = "create table " & tbl & " (id integer identity(1,1) primary key," & fld & ")": Debug.Print q

cn.Execute q

'データ登録

fld = ""

For c = Selection(1).Column To Selection(Selection.Count).Column

fld = fld & ActiveSheet.Cells(Selection(1).Row, c).Value & ","

Next c

fld = Left(fld, Len(fld) - 1)

For r = (Selection(1).Row) + 1 To Selection(Selection.Count).Row

For cc = Selection(1).Column To Selection(Selection.Count).Column

rec = rec & "'" & ActiveSheet.Cells(r, cc).Value & "'" & ","

Next cc

rec = Left(rec, Len(rec) - 1)

q = "insert into " & tbl & " (" & fld & ") values (" & rec & ")": Debug.Print q

cn.Execute q

rec = ""

Next r

If cn.State = 1 Then cn.Close

Set cn = Nothing

MsgBox "done"

End Sub

Sub tt()

Set cn = CreateObject("adodb.connection")

Set rn = CreateObject("adodb.recordset")

cn.Open "Provider=SQLOLEDB;Data Source=ESPRIMO\SQLEXPRESS;Initial Catalog=my_database;Integrated Security=SSPI;"

If Selection.Rows.Count = 1 Then

rn.Open ActiveCell.Value, cn

Else

For r = 1 To Selection.Rows.Count

q = q & " " & Selection(r)

Next r

rn.Open q, cn: Debug.Print q

End If

'フィールド作成

i = 0

For c = Selection(1).Column To (Selection(1).Column + rn.Fields.Count) - 1

Cells(Selection(Selection.Count).Row + 1, c).Value = rn.Fields(i).Name

i = i + 1

Next c

'データ読込

Cells(Selection(Selection.Count).Row + 2, Selection(1).Column).CopyFromRecordset rn

If rn.State = 1 Then rn.Close

Set rn = Nothing

If cn.State = 1 Then cn.Close

Set cn = Nothing

End Sub

2017-02-222018-03-19

AccessVBA クエリを呼び出す

Sub test()

'Accessにあるクエリを作成するプロシージャを呼ぶ
'クエリを作ってあるmdbなら不要

Set a = CreateObject("Access.Application")

With a
.OpenCurrentDatabase "xxx.mdb"
.Visible = False
.Application.Run "xxx"
.Quit
End With

Set a = Nothing

'ここからはADO

Set c = CreateObject("ADODB.Connection")
Set r = CreateObject("ADODB.Recordset")

c.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=xxx.mdb;"
r.Open "test", c

'クエリで動的なテーブルを開く。
'ちゃんとSQL Serverまで読みにいっている｡Accessの中にキャッシュなどしていないよう

ActiveSheet.Cells(1, 1).CopyFromRecordset r

End Sub

Sub test()

'Accessにあるクエリを作成するプロシージャを呼ぶ

'クエリを作ってあるmdbなら不要

Set a = CreateObject("Access.Application")

With a

.OpenCurrentDatabase "xxx.mdb"

.Visible = False

.Application.Run "xxx"

.Quit

End With

Set a = Nothing

'ここからはADO

Set c = CreateObject("ADODB.Connection")

Set r = CreateObject("ADODB.Recordset")

c.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=xxx.mdb;"

r.Open "test", c

'クエリで動的なテーブルを開く。

'ちゃんとSQL Serverまで読みにいっている｡Accessの中にキャッシュなどしていないよう

ActiveSheet.Cells(1, 1).CopyFromRecordset r

End Sub

2017-01-312017-02-22

C# VBA 積算・原価管理

Form1.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.OleDb;

namespace 積算_原価管理
{
    public partial class Form1 : Form
    {
        Boolean isLoad = false;

        private List GetControls(string pattern)
        {
            List l = new List();
            foreach (Control c in this.Controls)
            {
                if (pattern == "DecimalText")
                {
                    switch (c.Name)
                    {
                        case "textBox14":
                        case "textBox15":
                        case "textBox6":
                        case "textBox8":
                        case "textBox9":
                        case "textBox10":
                        case "textBox11":
                        case "textBox12":
                        case "textBox13":
                            l.Add(c);
                            break;
                    }
                }
                else if(pattern == "ProductInputs")
                {
                    switch (c.Name)
                    {
                        case "textBox1":
                        case "comboBox1":
                        case "comboBox2":
                        case "comboBox3":
                        case "textBox14":
                        case "textBox15":
                        case "textBox4":
                        case "textBox5":
                        case "textBox6":
                        case "textBox7":
                        case "textBox8":
                        case "textBox9":
                        case "textBox10":
                        case "textBox11":
                        case "textBox12":
                        case "textBox13":
                        case "textBox16":
                            l.Add(c);
                            break;
                    }
                }
                else if (pattern == "PartsInput")
                {
                    switch (c.Name)
                    {
                        case "textBox2":
                        case "textBox18":
                        case "textBox19":
                        case "textBox20":
                        case "textBox21":
                        case "textBox3":
                        case "textBox17":
                            l.Add(c);
                            break;
                    }
                }
            }
            return l;
        }

        private string BuildQuery(string pattern)
        {
            if (pattern == "新規")
            {
                return "INSERT INTO 製品 (向先,型式,製作先,販売価格,営業手配分,外注費,輸送費荷受,輸送費納品,試験費,品証費用,品証経費,社内製作分,備考) VALUES (" +
                        "'" + comboBox1.Text + "'," +
                        "'" + comboBox2.Text + "'," +
                        "'" + comboBox3.Text + "'," +
                        "'" + textBox14.Text + "'," +
                        "'" + textBox15.Text + "'," +
                        "'" + textBox6.Text + "'," +
                        "'" + textBox8.Text + "'," +
                        "'" + textBox9.Text + "'," +
                        "'" + textBox10.Text + "'," +
                        "'" + textBox11.Text + "'," +
                        "'" + textBox12.Text + "'," +
                        "'" + textBox13.Text + "'," +
                        "'" + textBox16.Text + "')";
            }
            else if (pattern == "更新")
            {
                return "UPDATE 製品 SET " +
                        "向先 = '" + comboBox1.Text + "'," +
                        "型式 = '" + comboBox2.Text + "'," +
                        "製作先 = '" + comboBox3.Text + "'," +
                        "販売価格 = '" + textBox14.Text + "'," +
                        "営業手配分 = '" + textBox15.Text + "'," +
                        "外注費 = '" + textBox6.Text + "'," +
                        "輸送費荷受 = '" + textBox8.Text + "'," +
                        "輸送費納品 = '" + textBox9.Text + "'," +
                        "試験費 = '" + textBox10.Text + "'," +
                        "品証費用 = '" + textBox11.Text + "'," +
                        "品証経費 = '" + textBox12.Text + "'," +
                        "社内製作分 = '" + textBox13.Text + "'," +
                        "備考 = '" + textBox16.Text + "' " +
                        "WHERE ID = " + textBox1.Text + "";
            }
            else if (pattern =="削除")
            {
                return "DELETE FROM 製品 WHERE ID = " + textBox1.Text + "";
            }
            return "";
        }

        private void InitGridView()
        {
            dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
            dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
            dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;
            dataGridView1.AllowUserToAddRows = false;
            //dataGridView1.ReadOnly = true;

            dataGridView1.Font = new Font("MS UI Gothic", 12);

            dataGridView1.Columns.Add("部品表ID", "部品表ID");
            dataGridView1.Columns.Add("集計ID", "集計ID");
            dataGridView1.Columns.Add("部品ID", "部品ID");
            dataGridView1.Columns.Add("名称", "名称");
            dataGridView1.Columns.Add("材質", "材質");
            dataGridView1.Columns.Add("寸法", "寸法");
            dataGridView1.Columns.Add("単価", "単価");
            dataGridView1.Columns.Add("数量", "数量");

            DataGridViewButtonColumn col = new DataGridViewButtonColumn();
            col.Name = "更新";
            col.UseColumnTextForButtonValue = true;
            col.Text = "更新";
            dataGridView1.Columns.Add(col);

            col = new DataGridViewButtonColumn();
            col.Name = "削除";
            col.UseColumnTextForButtonValue = true;
            col.Text = "削除";
            dataGridView1.Columns.Add(col);

            dataGridView1.Columns["部品表ID"].Visible = false;
        }

        public Form1()
        {
            InitializeComponent();

            LoadMaster();
            InitGridView();

            //読込
            button1.Click += new EventHandler((object sender ,EventArgs e) => { LoadTable("読込"); });
            //検索
            button2.Click += new EventHandler((object sender, EventArgs e) => { LoadTable("検索"); });
            //一覧
            button8.Click += new EventHandler((object sender, EventArgs e) => {
                Form f = new Form3(this);
                f.StartPosition = FormStartPosition.Manual;
                f.Location = new Point(Cursor.Position.X + 50, Cursor.Position.Y);
                f.ShowDialog();
            });

            //新規
            button7.Click += new EventHandler((object sender, EventArgs e) => { DataChanges("新規"); });
            //更新
            button4.Click += new EventHandler((object sender, EventArgs e) => { DataChanges("更新"); });
            //削除
            button6.Click += new EventHandler((object sender, EventArgs e) => { DataChanges("削除"); });

            //部品ID検索
            button5.Click += new EventHandler((object sender, EventArgs e) => {
                Form f = new Form2(this);
                f.StartPosition = FormStartPosition.Manual;
                f.Location = new Point(Cursor.Position.X + 50,Cursor.Position.Y - 550);
                f.ShowDialog();
            });

            //部品追加
            button3.Click += new EventHandler(AddRow);

            dataGridView1.CellClick += new DataGridViewCellEventHandler(dgvClick);

            textBox1.ForeColor = Color.Red;

            textBox1.TextChanged += new EventHandler((object sender, EventArgs e) => {
                isLoad = false;
                textBox1.ForeColor = Color.Red;
                
            });
        }

        private Boolean TryDecimal()
        {
            try
            {
                foreach (Control c in GetControls("DecimalText"))
                {
                    if (c.Text == "") c.Text = "0";
                    Decimal.Parse(c.Text);
                }
                return true;
            }
            catch
            {
                return false;
            }
        }
        
        private Boolean ProductsInputCheck(string sender)
        {
            if (sender == "新規")
            {
                if (textBox1.Text != "")
                {
                    MessageBox.Show("製品IDが入力されています。");
                    return false;
                }
                if (comboBox1.Text == "" || comboBox2.Text == "" || comboBox3.Text == "")
                {
                    MessageBox.Show("向先・型式・製作先が空白です");
                    return false;
                }
                if (!TryDecimal())
                {
                    MessageBox.Show("金額が正しくありません。");
                    return false;
                }
            }
            else if (sender == "更新")
            {
                if (textBox1.Text == "")
                {
                    MessageBox.Show("製品IDが入力されていません。");
                    return false;
                }
                if (comboBox1.Text == "" || comboBox2.Text == "" || comboBox3.Text == "")
                {
                    MessageBox.Show("向先・型式・製作先が空白です");
                    return false;
                }
                if (!isLoad)
                {
                    MessageBox.Show("製品IDが正しくない可能性があります。");
                    return false;
                }
                if (!TryDecimal())
                {
                    MessageBox.Show("金額が正しくありません。");
                    return false;
                }
            }
            else if (sender == "削除")
            {
                if (textBox1.Text == "")
                {
                    MessageBox.Show("製品IDが入力されていません。");
                    return false;
                }
                if (!isLoad)
                {
                    MessageBox.Show("製品IDが正しくない可能性があります。");
                    return false;
                }
            }
            return true;
        }

        private void DataChanges(string sender)
        {
            if (!ProductsInputCheck(sender)) return;

            using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb"))
            {
                try
                {
                    con.Open();
                    OleDbCommand cmd = new OleDbCommand(BuildQuery(sender), con);
                    cmd.ExecuteNonQuery();

                    if (sender == "新規")
                    {
                        cmd = new OleDbCommand("SELECT @@IDENTITY", con);
                        using (OleDbDataReader dr = cmd.ExecuteReader())
                        {
                            if (dr.Read()) textBox1.Text = dr[0].ToString();
                        }
                    }
                }
                catch
                {
                    MessageBox.Show("データベースに接続できませんでした。");
                    return;
                }
                finally
                {
                    con.Close();
                }
            }

            LoadMaster();

            if (sender == "削除")
            {
                MessageBox.Show("削除しました。");
                InputClear();
            }
            else
            {
                LoadTable(sender);
            }
        }

        private void dgvClick(object sender, DataGridViewCellEventArgs e)
        {
            int col = e.ColumnIndex;
            int row = e.RowIndex;

            //削除
            if (col == 9 && row >= 0)
            {
                if (!isLoad)
                {
                    MessageBox.Show("製品IDが正しくない可能性があります。");
                    return;
                }

                string id = dataGridView1.Rows[row].Cells[0].Value.ToString();

                using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb"))
                {
                    try
                    {
                        con.Open();
                        OleDbCommand cmd = new OleDbCommand("DELETE FROM 部品表 WHERE 部品表ID = " + id + "", con);
                        cmd.ExecuteNonQuery();
                    }
                    catch
                    {
                        MessageBox.Show("データベースに接続できませんでした。");
                        return;
                    }
                    finally
                    {
                        con.Close();
                    }
                }
                LoadTable("削除");
            }

            //更新
            if (col == 8 && row >= 0)
            {
                if (!isLoad)
                {
                    MessageBox.Show("製品IDが正しくない可能性があります。");
                    return;
                }

                string id = dataGridView1.Rows[row].Cells[0].Value.ToString();
                string quantity = dataGridView1.Rows[row].Cells[7].Value.ToString();
                string calculateId = dataGridView1.Rows[row].Cells[1].Value.ToString();

                try
                {
                    decimal.Parse(quantity);
                    int i = int.Parse(calculateId);
                    if (i > 5 || i < 1) throw new Exception(); } catch { MessageBox.Show("集計ID・数量が正しくありません。"); return; } using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb")) { try { con.Open(); OleDbCommand cmd = new OleDbCommand( "UPDATE 部品表 SET 集計ID = " + calculateId + ", 数量 = " + quantity + " WHERE 部品表ID = " + id + "", con); cmd.ExecuteNonQuery(); } catch { MessageBox.Show("データベースに接続できませんでした。"); InputClear(); return; } finally { con.Close(); } } LoadTable("更新"); } } private void InputClear() { dataGridView1.Rows.Clear(); foreach (Control c in GetControls("ProductInputs")) { c.Text = ""; } } private void LoadMaster() { comboBox1.Items.Clear(); comboBox2.Items.Clear(); comboBox3.Items.Clear(); using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb")) { try { con.Open(); OleDbCommand cmd; OleDbDataReader dr; foreach (string str in new string[] {"向先","型式","製作先" }) { cmd = new OleDbCommand("SELECT DISTINCT " + str +" FROM 製品", con); dr = cmd.ExecuteReader(); while(dr.Read()) { if(str == "向先") this.comboBox1.Items.Add(dr["向先"].ToString()); if(str == "型式") this.comboBox2.Items.Add(dr["型式"].ToString()); if(str == "製作先") this.comboBox3.Items.Add(dr["製作先"].ToString()); } } } catch { MessageBox.Show("データベースに接続できませんでした。"); return; } finally { con.Close(); } } } private Boolean TryInt() { try { decimal.Parse(textBox17.Text); int i = int.Parse(textBox3.Text); if (i > 5 || i < 1) return false; } catch { return false; } return true; } private Boolean PartsInputCheck() { if (textBox1.Text == "" || textBox2.Text == "" || textBox3.Text == "" || textBox17.Text == "") { MessageBox.Show("製品ID・部品ID・集計ID・数量を入力してください。"); return false; } if (!isLoad) { MessageBox.Show("製品IDが正しくない可能性があります。"); return false; } if (!TryInt()) { MessageBox.Show("集計ID・数量が正しくありません。"); return false; } return true; } private void AddRow(object sender, EventArgs e) { if (!PartsInputCheck()) return; using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb")) { try { con.Open(); OleDbCommand cmd = new OleDbCommand("INSERT INTO 部品表 (製品ID,集計ID,部品ID_,数量) VALUES (" + textBox1.Text + "," + textBox3.Text + "," + textBox2.Text + "," + textBox17.Text + ")", con); cmd.ExecuteNonQuery(); } catch { MessageBox.Show("データベースに接続できませんでした。"); return; } finally { con.Close(); } } foreach (Control c in GetControls("PartsInput")) { c.Text = ""; } LoadTable("追加"); } private void LoadTable(string sender) { dataGridView1.Rows.Clear(); if(sender == "読込") { if (this.textBox1.Text == "") { MessageBox.Show("IDを入力してください。"); InputClear(); return; } } else if (sender == "検索") { if (this.comboBox1.Text == "" || this.comboBox2.Text == "" || this.comboBox3.Text == "") { MessageBox.Show("向先・型式・製作先を入力してください。"); InputClear(); return; } } using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb")) { try { con.Open(); string q = ""; switch (sender) { case "検索": case "新規": q = "SELECT * FROM 製品 WHERE 向先 = '" + comboBox1.Text + "' AND 型式 = '" + comboBox2.Text + "' AND 製作先 = '" + comboBox3.Text + "'"; break; case "読込": case "削除": case "追加": case "更新": q = "SELECT * FROM 製品 WHERE ID = " + this.textBox1.Text + ""; break; } OleDbCommand cmd = new OleDbCommand(q, con); OleDbDataReader dr = cmd.ExecuteReader(); if (!dr.Read()) { MessageBox.Show("データがありませんでした。"); InputClear(); return; } else { int i = 0; do { this.textBox1.Text = dr["ID"].ToString(); this.comboBox1.Text = dr["向先"].ToString(); this.comboBox2.Text = dr["型式"].ToString(); this.comboBox3.Text = dr["製作先"].ToString(); this.textBox14.Text = string.Format("{0:f0}", dr["販売価格"]); this.textBox15.Text = string.Format("{0:f0}", dr["営業手配分"]); this.textBox6.Text = string.Format("{0:f0}", dr["外注費"]); this.textBox8.Text = string.Format("{0:f0}", dr["輸送費荷受"]); this.textBox9.Text = string.Format("{0:f0}", dr["輸送費納品"]); this.textBox10.Text = string.Format("{0:f0}", dr["試験費"]); this.textBox11.Text = string.Format("{0:f0}", dr["品証費用"]); this.textBox12.Text = string.Format("{0:f0}", dr["品証経費"]); this.textBox13.Text = string.Format("{0:f0}", dr["社内製作分"]); this.textBox16.Text = dr["備考"].ToString(); i++; } while (dr.Read()); if (i > 1) MessageBox.Show("向先・型式・製作先の組合わせが重複しているので注意してください。");
                    }

                    cmd = new OleDbCommand("SELECT * FROM 部品表 LEFT OUTER JOIN 部品 ON 部品表.部品ID_ = 部品.部品ID WHERE 製品ID = " + this.textBox1.Text + "" , con);
                    dr = cmd.ExecuteReader();

                    while(dr.Read())
                    {
                        dataGridView1.Rows.Add(
                            dr["部品表ID"].ToString(),
                            dr["集計ID"].ToString(),
                            dr["部品ID"].ToString(),
                            dr["名称"].ToString(),
                            dr["材質"].ToString(),
                            dr["寸法"].ToString(),
                            dr["単価"].ToString(),
                            string.Format("{0:f2}", dr["数量"]), "");
                    }
                }
                catch
                {
                    MessageBox.Show("データベースに接続できませんでした。");
                    InputClear();
                    return;
                }
                finally
                {
                    con.Close();
                }

                try
                {
                    decimal ManHour = 0;
                    decimal PaintingCost = 0;
                    decimal PartsCost1 = 0;
                    decimal PartsCost2 = 0;
                    decimal PartsCost3 = 0;
                    decimal YieldRatio = 0;

                    for (int k = 0; k < dataGridView1.Rows.Count; k++)
                    {
                        decimal UnitPrice = decimal.Parse(dataGridView1.Rows[k].Cells[6].Value.ToString());
                        decimal Quantity = decimal.Parse(dataGridView1.Rows[k].Cells[7].Value.ToString());

                        switch (dataGridView1.Rows[k].Cells[1].Value.ToString())
                        {
                            case "1":
                                ManHour += UnitPrice * Quantity;
                                break;
                            case "2":
                                PaintingCost += UnitPrice * Quantity;
                                break;
                            case "3":
                                PartsCost1 += UnitPrice * Quantity;
                                break;
                            case "4":
                                PartsCost2 += UnitPrice * Quantity;
                                break;
                            case "5":
                                PartsCost3 += UnitPrice * Quantity;
                                break;
                        }
                    }
                    textBox4.Text = string.Format("{0:f0}", ManHour * (decimal)4000);
                    textBox5.Text = string.Format("{0:f0}", PaintingCost);
                    textBox22.Text = string.Format("{0:f0}", PartsCost1);//鋼材(3)
                    textBox23.Text = string.Format("{0:f0}", PartsCost2);//購入品(4)
                    textBox25.Text = string.Format("{0:f0}", PartsCost3);//その他(5)
                    YieldRatio = PartsCost1 * (decimal)0.2;
                    textBox24.Text = string.Format("{0:f0}", YieldRatio);//歩留
                    textBox7.Text = string.Format("{0:f0}", PartsCost1 + PartsCost2 + PartsCost3 + YieldRatio);
                }
                catch
                {
                    MessageBox.Show("部品の単価・数量が不正な値です。修正してください。");
                }
                switch (sender)
                {
                    case "読込":
                    case "検索":
                        MessageBox.Show("取得しました。");
                        break;
                    case "削除":
                        MessageBox.Show("削除しました。");
                        break;
                    case "追加":
                        MessageBox.Show("追加しました。");
                        break;
                    case "新規":
                        MessageBox.Show("作成しました。");
                        break;
                    case "更新":
                        MessageBox.Show("更新しました。");
                        break;
                }

                isLoad = true;

                dataGridView1.Columns[0].ReadOnly = true;
                dataGridView1.Columns[1].ReadOnly = false;
                dataGridView1.Columns[2].ReadOnly = true;
                dataGridView1.Columns[3].ReadOnly = true;
                dataGridView1.Columns[4].ReadOnly = true;
                dataGridView1.Columns[5].ReadOnly = true;
                dataGridView1.Columns[6].ReadOnly = true;
                dataGridView1.Columns[7].ReadOnly = false;
                dataGridView1.Columns[8].ReadOnly = true;
                dataGridView1.Columns[8].ReadOnly = true;

                textBox1.ForeColor = Color.Blue;
            }
        }
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

480

481

482

483

484

485

486

487

488

489

490

491

492

493

494

495

496

497

498

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

using System.Data.OleDb;

namespace 積算_原価管理

{

public partial class Form1 : Form

{

Boolean isLoad = false;

private List GetControls(string pattern)

{

List l = new List();

foreach (Control c in this.Controls)

{

if (pattern == "DecimalText")

{

switch (c.Name)

{

case "textBox14":

case "textBox15":

case "textBox6":

case "textBox8":

case "textBox9":

case "textBox10":

case "textBox11":

case "textBox12":

case "textBox13":

l.Add(c);

break;

}

else if(pattern == "ProductInputs")

{

switch (c.Name)

{

case "textBox1":

case "comboBox1":

case "comboBox2":

case "comboBox3":

case "textBox14":

case "textBox15":

case "textBox4":

case "textBox5":

case "textBox6":

case "textBox7":

case "textBox8":

case "textBox9":

case "textBox10":

case "textBox11":

case "textBox12":

case "textBox13":

case "textBox16":

l.Add(c);

break;

}

else if (pattern == "PartsInput")

{

switch (c.Name)

{

case "textBox2":

case "textBox18":

case "textBox19":

case "textBox20":

case "textBox21":

case "textBox3":

case "textBox17":

l.Add(c);

break;

}

return l;

}

private string BuildQuery(string pattern)

{

if (pattern == "新規")

{

return "INSERT INTO 製品 (向先,型式,製作先,販売価格,営業手配分,外注費,輸送費荷受,輸送費納品,試験費,品証費用,品証経費,社内製作分,備考) VALUES (" +

"'" + comboBox1.Text + "'," +

"'" + comboBox2.Text + "'," +

"'" + comboBox3.Text + "'," +

"'" + textBox14.Text + "'," +

"'" + textBox15.Text + "'," +

"'" + textBox6.Text + "'," +

"'" + textBox8.Text + "'," +

"'" + textBox9.Text + "'," +

"'" + textBox10.Text + "'," +

"'" + textBox11.Text + "'," +

"'" + textBox12.Text + "'," +

"'" + textBox13.Text + "'," +

"'" + textBox16.Text + "')";

}

else if (pattern == "更新")

{

return "UPDATE 製品 SET " +

"向先 = '" + comboBox1.Text + "'," +

"型式 = '" + comboBox2.Text + "'," +

"製作先 = '" + comboBox3.Text + "'," +

"販売価格 = '" + textBox14.Text + "'," +

"営業手配分 = '" + textBox15.Text + "'," +

"外注費 = '" + textBox6.Text + "'," +

"輸送費荷受 = '" + textBox8.Text + "'," +

"輸送費納品 = '" + textBox9.Text + "'," +

"試験費 = '" + textBox10.Text + "'," +

"品証費用 = '" + textBox11.Text + "'," +

"品証経費 = '" + textBox12.Text + "'," +

"社内製作分 = '" + textBox13.Text + "'," +

"備考 = '" + textBox16.Text + "' " +

"WHERE ID = " + textBox1.Text + "";

}

else if (pattern =="削除")

{

return "DELETE FROM 製品 WHERE ID = " + textBox1.Text + "";

}

return "";

}

private void InitGridView()

{

dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;

dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;

dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;

dataGridView1.AllowUserToAddRows = false;

//dataGridView1.ReadOnly = true;

dataGridView1.Font = new Font("MS UI Gothic", 12);

dataGridView1.Columns.Add("部品表ID", "部品表ID");

dataGridView1.Columns.Add("集計ID", "集計ID");

dataGridView1.Columns.Add("部品ID", "部品ID");

dataGridView1.Columns.Add("名称", "名称");

dataGridView1.Columns.Add("材質", "材質");

dataGridView1.Columns.Add("寸法", "寸法");

dataGridView1.Columns.Add("単価", "単価");

dataGridView1.Columns.Add("数量", "数量");

DataGridViewButtonColumn col = new DataGridViewButtonColumn();

col.Name = "更新";

col.UseColumnTextForButtonValue = true;

col.Text = "更新";

dataGridView1.Columns.Add(col);

col = new DataGridViewButtonColumn();

col.Name = "削除";

col.UseColumnTextForButtonValue = true;

col.Text = "削除";

dataGridView1.Columns.Add(col);

dataGridView1.Columns["部品表ID"].Visible = false;

}

public Form1()

{

InitializeComponent();

LoadMaster();

InitGridView();

//読込

button1.Click += new EventHandler((object sender ,EventArgs e) => { LoadTable("読込"); });

//検索

button2.Click += new EventHandler((object sender, EventArgs e) => { LoadTable("検索"); });

//一覧

button8.Click += new EventHandler((object sender, EventArgs e) => {

Form f = new Form3(this);

f.StartPosition = FormStartPosition.Manual;

f.Location = new Point(Cursor.Position.X + 50, Cursor.Position.Y);

f.ShowDialog();

});

//新規

button7.Click += new EventHandler((object sender, EventArgs e) => { DataChanges("新規"); });

//更新

button4.Click += new EventHandler((object sender, EventArgs e) => { DataChanges("更新"); });

//削除

button6.Click += new EventHandler((object sender, EventArgs e) => { DataChanges("削除"); });

//部品ID検索

button5.Click += new EventHandler((object sender, EventArgs e) => {

Form f = new Form2(this);

f.StartPosition = FormStartPosition.Manual;

f.Location = new Point(Cursor.Position.X + 50,Cursor.Position.Y - 550);

f.ShowDialog();

});

//部品追加

button3.Click += new EventHandler(AddRow);

dataGridView1.CellClick += new DataGridViewCellEventHandler(dgvClick);

textBox1.ForeColor = Color.Red;

textBox1.TextChanged += new EventHandler((object sender, EventArgs e) => {

isLoad = false;

textBox1.ForeColor = Color.Red;

});

}

private Boolean TryDecimal()

{

try

{

foreach (Control c in GetControls("DecimalText"))

{

if (c.Text == "") c.Text = "0";

Decimal.Parse(c.Text);

}

return true;

}

catch

{

return false;

}

private Boolean ProductsInputCheck(string sender)

{

if (sender == "新規")

{

if (textBox1.Text != "")

{

MessageBox.Show("製品IDが入力されています。");

return false;

}

if (comboBox1.Text == "" || comboBox2.Text == "" || comboBox3.Text == "")

{

MessageBox.Show("向先・型式・製作先が空白です");

return false;

}

if (!TryDecimal())

{

MessageBox.Show("金額が正しくありません。");

return false;

}

else if (sender == "更新")

{

if (textBox1.Text == "")

{

MessageBox.Show("製品IDが入力されていません。");

return false;

}

if (comboBox1.Text == "" || comboBox2.Text == "" || comboBox3.Text == "")

{

MessageBox.Show("向先・型式・製作先が空白です");

return false;

}

if (!isLoad)

{

MessageBox.Show("製品IDが正しくない可能性があります。");

return false;

}

if (!TryDecimal())

{

MessageBox.Show("金額が正しくありません。");

return false;

}

else if (sender == "削除")

{

if (textBox1.Text == "")

{

MessageBox.Show("製品IDが入力されていません。");

return false;

}

if (!isLoad)

{

MessageBox.Show("製品IDが正しくない可能性があります。");

return false;

}

return true;

}

private void DataChanges(string sender)

{

if (!ProductsInputCheck(sender)) return;

using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb"))

{

try

{

con.Open();

OleDbCommand cmd = new OleDbCommand(BuildQuery(sender), con);

cmd.ExecuteNonQuery();

if (sender == "新規")

{

cmd = new OleDbCommand("SELECT @@IDENTITY", con);

using (OleDbDataReader dr = cmd.ExecuteReader())

{

if (dr.Read()) textBox1.Text = dr[0].ToString();

}

catch

{

MessageBox.Show("データベースに接続できませんでした。");

return;

}

finally

{

con.Close();

}

LoadMaster();

if (sender == "削除")

{

MessageBox.Show("削除しました。");

InputClear();

}

else

{

LoadTable(sender);

}

private void dgvClick(object sender, DataGridViewCellEventArgs e)

{

int col = e.ColumnIndex;

int row = e.RowIndex;

//削除

if (col == 9 && row >= 0)

{

if (!isLoad)

{

MessageBox.Show("製品IDが正しくない可能性があります。");

return;

}

string id = dataGridView1.Rows[row].Cells[0].Value.ToString();

using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb"))

{

try

{

con.Open();

OleDbCommand cmd = new OleDbCommand("DELETE FROM 部品表 WHERE 部品表ID = " + id + "", con);

cmd.ExecuteNonQuery();

}

catch

{

MessageBox.Show("データベースに接続できませんでした。");

return;

}

finally

{

con.Close();

}

LoadTable("削除");

}

//更新

if (col == 8 && row >= 0)

{

if (!isLoad)

{

MessageBox.Show("製品IDが正しくない可能性があります。");

return;

}

string id = dataGridView1.Rows[row].Cells[0].Value.ToString();

string quantity = dataGridView1.Rows[row].Cells[7].Value.ToString();

string calculateId = dataGridView1.Rows[row].Cells[1].Value.ToString();

try

{

decimal.Parse(quantity);

int i = int.Parse(calculateId);

if (i > 5 || i < 1) throw new Exception(); } catch { MessageBox.Show("集計ID・数量が正しくありません。"); return; } using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb")) { try { con.Open(); OleDbCommand cmd = new OleDbCommand( "UPDATE 部品表 SET 集計ID = " + calculateId + ", 数量 = " + quantity + " WHERE 部品表ID = " + id + "", con); cmd.ExecuteNonQuery(); } catch { MessageBox.Show("データベースに接続できませんでした。"); InputClear(); return; } finally { con.Close(); } } LoadTable("更新"); } } private void InputClear() { dataGridView1.Rows.Clear(); foreach (Control c in GetControls("ProductInputs")) { c.Text = ""; } } private void LoadMaster() { comboBox1.Items.Clear(); comboBox2.Items.Clear(); comboBox3.Items.Clear(); using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb")) { try { con.Open(); OleDbCommand cmd; OleDbDataReader dr; foreach (string str in new string[] {"向先","型式","製作先" }) { cmd = new OleDbCommand("SELECT DISTINCT " + str +" FROM 製品", con); dr = cmd.ExecuteReader(); while(dr.Read()) { if(str == "向先") this.comboBox1.Items.Add(dr["向先"].ToString()); if(str == "型式") this.comboBox2.Items.Add(dr["型式"].ToString()); if(str == "製作先") this.comboBox3.Items.Add(dr["製作先"].ToString()); } } } catch { MessageBox.Show("データベースに接続できませんでした。"); return; } finally { con.Close(); } } } private Boolean TryInt() { try { decimal.Parse(textBox17.Text); int i = int.Parse(textBox3.Text); if (i > 5 || i < 1) return false; } catch { return false; } return true; } private Boolean PartsInputCheck() { if (textBox1.Text == "" || textBox2.Text == "" || textBox3.Text == "" || textBox17.Text == "") { MessageBox.Show("製品ID・部品ID・集計ID・数量を入力してください。"); return false; } if (!isLoad) { MessageBox.Show("製品IDが正しくない可能性があります。"); return false; } if (!TryInt()) { MessageBox.Show("集計ID・数量が正しくありません。"); return false; } return true; } private void AddRow(object sender, EventArgs e) { if (!PartsInputCheck()) return; using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb")) { try { con.Open(); OleDbCommand cmd = new OleDbCommand("INSERT INTO 部品表 (製品ID,集計ID,部品ID_,数量) VALUES (" + textBox1.Text + "," + textBox3.Text + "," + textBox2.Text + "," + textBox17.Text + ")", con); cmd.ExecuteNonQuery(); } catch { MessageBox.Show("データベースに接続できませんでした。"); return; } finally { con.Close(); } } foreach (Control c in GetControls("PartsInput")) { c.Text = ""; } LoadTable("追加"); } private void LoadTable(string sender) { dataGridView1.Rows.Clear(); if(sender == "読込") { if (this.textBox1.Text == "") { MessageBox.Show("IDを入力してください。"); InputClear(); return; } } else if (sender == "検索") { if (this.comboBox1.Text == "" || this.comboBox2.Text == "" || this.comboBox3.Text == "") { MessageBox.Show("向先・型式・製作先を入力してください。"); InputClear(); return; } } using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb")) { try { con.Open(); string q = ""; switch (sender) { case "検索": case "新規": q = "SELECT * FROM 製品 WHERE 向先 = '" + comboBox1.Text + "' AND 型式 = '" + comboBox2.Text + "' AND 製作先 = '" + comboBox3.Text + "'"; break; case "読込": case "削除": case "追加": case "更新": q = "SELECT * FROM 製品 WHERE ID = " + this.textBox1.Text + ""; break; } OleDbCommand cmd = new OleDbCommand(q, con); OleDbDataReader dr = cmd.ExecuteReader(); if (!dr.Read()) { MessageBox.Show("データがありませんでした。"); InputClear(); return; } else { int i = 0; do { this.textBox1.Text = dr["ID"].ToString(); this.comboBox1.Text = dr["向先"].ToString(); this.comboBox2.Text = dr["型式"].ToString(); this.comboBox3.Text = dr["製作先"].ToString(); this.textBox14.Text = string.Format("{0:f0}", dr["販売価格"]); this.textBox15.Text = string.Format("{0:f0}", dr["営業手配分"]); this.textBox6.Text = string.Format("{0:f0}", dr["外注費"]); this.textBox8.Text = string.Format("{0:f0}", dr["輸送費荷受"]); this.textBox9.Text = string.Format("{0:f0}", dr["輸送費納品"]); this.textBox10.Text = string.Format("{0:f0}", dr["試験費"]); this.textBox11.Text = string.Format("{0:f0}", dr["品証費用"]); this.textBox12.Text = string.Format("{0:f0}", dr["品証経費"]); this.textBox13.Text = string.Format("{0:f0}", dr["社内製作分"]); this.textBox16.Text = dr["備考"].ToString(); i++; } while (dr.Read()); if (i > 1) MessageBox.Show("向先・型式・製作先の組合わせが重複しているので注意してください。");

}

cmd = new OleDbCommand("SELECT * FROM 部品表 LEFT OUTER JOIN 部品 ON 部品表.部品ID_ = 部品.部品ID WHERE 製品ID = " + this.textBox1.Text + "" , con);

dr = cmd.ExecuteReader();

while(dr.Read())

{

dataGridView1.Rows.Add(

dr["部品表ID"].ToString(),

dr["集計ID"].ToString(),

dr["部品ID"].ToString(),

dr["名称"].ToString(),

dr["材質"].ToString(),

dr["寸法"].ToString(),

dr["単価"].ToString(),

string.Format("{0:f2}", dr["数量"]), "");

}

catch

{

MessageBox.Show("データベースに接続できませんでした。");

InputClear();

return;

}

finally

{

con.Close();

}

try

{

decimal ManHour = 0;

decimal PaintingCost = 0;

decimal PartsCost1 = 0;

decimal PartsCost2 = 0;

decimal PartsCost3 = 0;

decimal YieldRatio = 0;

for (int k = 0; k < dataGridView1.Rows.Count; k++)

{

decimal UnitPrice = decimal.Parse(dataGridView1.Rows[k].Cells[6].Value.ToString());

decimal Quantity = decimal.Parse(dataGridView1.Rows[k].Cells[7].Value.ToString());

switch (dataGridView1.Rows[k].Cells[1].Value.ToString())

{

case "1":

ManHour += UnitPrice * Quantity;

break;

case "2":

PaintingCost += UnitPrice * Quantity;

break;

case "3":

PartsCost1 += UnitPrice * Quantity;

break;

case "4":

PartsCost2 += UnitPrice * Quantity;

break;

case "5":

PartsCost3 += UnitPrice * Quantity;

break;

}

textBox4.Text = string.Format("{0:f0}", ManHour * (decimal)4000);

textBox5.Text = string.Format("{0:f0}", PaintingCost);

textBox22.Text = string.Format("{0:f0}", PartsCost1);//鋼材(3)

textBox23.Text = string.Format("{0:f0}", PartsCost2);//購入品(4)

textBox25.Text = string.Format("{0:f0}", PartsCost3);//その他(5)

YieldRatio = PartsCost1 * (decimal)0.2;

textBox24.Text = string.Format("{0:f0}", YieldRatio);//歩留

textBox7.Text = string.Format("{0:f0}", PartsCost1 + PartsCost2 + PartsCost3 + YieldRatio);

}

catch

{

MessageBox.Show("部品の単価・数量が不正な値です。修正してください。");

}

switch (sender)

{

case "読込":

case "検索":

MessageBox.Show("取得しました。");

break;

case "削除":

MessageBox.Show("削除しました。");

break;

case "追加":

MessageBox.Show("追加しました。");

break;

case "新規":

MessageBox.Show("作成しました。");

break;

case "更新":

MessageBox.Show("更新しました。");

break;

}

isLoad = true;

dataGridView1.Columns[0].ReadOnly = true;

dataGridView1.Columns[1].ReadOnly = false;

dataGridView1.Columns[2].ReadOnly = true;

dataGridView1.Columns[3].ReadOnly = true;

dataGridView1.Columns[4].ReadOnly = true;

dataGridView1.Columns[5].ReadOnly = true;

dataGridView1.Columns[6].ReadOnly = true;

dataGridView1.Columns[7].ReadOnly = false;

dataGridView1.Columns[8].ReadOnly = true;

textBox1.ForeColor = Color.Blue;

}

Form2.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.OleDb;

namespace 積算_原価管理
{
    public partial class Form2 : Form
    {
        Form f1;
        public Form2(Form f)
        {
            InitializeComponent();

            f1 = f;

            button1.Click += new EventHandler(FindRows);
            button3.Click += new EventHandler(addRow);

            this.KeyPreview = true;
            this.KeyDown += new KeyEventHandler((object sender, KeyEventArgs e) => {
                if (e.KeyCode == Keys.Escape) this.Close();
            });

            dataGridView1.CellClick += new DataGridViewCellEventHandler(dgvClick);
            dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
            dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
            dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;
            dataGridView1.AllowUserToAddRows = false;
            dataGridView1.Font = new Font("MS UI Gothic", 12);

            dataGridView1.Columns.Add("部品ID", "部品ID");
            dataGridView1.Columns["部品ID"].ReadOnly = true;

            dataGridView1.Columns.Add("名称", "名称");
            dataGridView1.Columns.Add("材質", "材質");
            dataGridView1.Columns.Add("寸法", "寸法");
            dataGridView1.Columns.Add("単価", "単価");

            DataGridViewButtonColumn col = new DataGridViewButtonColumn();
            col.Name = "更新";
            col.UseColumnTextForButtonValue = true;
            col.Text = "更新";
            dataGridView1.Columns.Add(col);

            col = new DataGridViewButtonColumn();
            col.Name = "削除";
            col.UseColumnTextForButtonValue = true;
            col.Text = "削除";
            dataGridView1.Columns.Add(col);

            LoadMaster();
        }

        private void dgvClick(object sender, DataGridViewCellEventArgs e)
        {
            int col = e.ColumnIndex;
            int row = e.RowIndex;

            if(col == -1 && row >= 0)
            {
                f1.Controls["textBox2"].Text = dataGridView1.Rows[row].Cells[0].Value.ToString();
                f1.Controls["textBox18"].Text = dataGridView1.Rows[row].Cells[1].Value.ToString();
                f1.Controls["textBox19"].Text = dataGridView1.Rows[row].Cells[2].Value.ToString();
                f1.Controls["textBox20"].Text = dataGridView1.Rows[row].Cells[3].Value.ToString();
                f1.Controls["textBox21"].Text = dataGridView1.Rows[row].Cells[4].Value.ToString();
                f1.Controls["textBox3"].Text = "";
                f1.Controls["textBox17"].Text = "";
            }

            if (col == 5 && row >= 0)
            {
                string id = dataGridView1.Rows[row].Cells[0].Value.ToString();
                string name = dataGridView1.Rows[row].Cells[1].Value.ToString();
                string material = dataGridView1.Rows[row].Cells[2].Value.ToString();
                string size = dataGridView1.Rows[row].Cells[3].Value.ToString();
                string price = dataGridView1.Rows[row].Cells[4].Value.ToString();

                using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb"))
                {
                    try
                    {
                        con.Open();

                        OleDbCommand cmd = new OleDbCommand("UPDATE 部品 SET " + 
                            "名称 = '" + name + "', 材質 = '" + material + "', 寸法 = '" + size + "', 単価 = " + price + " WHERE 部品ID = " + id + "", con);
                        cmd.ExecuteNonQuery();
                    }
                    catch
                    {
                        MessageBox.Show("データベースに接続できませんでした。");
                        return;
                    }
                    finally
                    {
                        con.Close();
                    }
                }
                LoadMaster();
                BaseFormClear();
                MessageBox.Show("更新しました。");
            }

            if (col == 6 && row >= 0)
            {
                string id = dataGridView1.Rows[row].Cells[0].Value.ToString();

                using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb"))
                {
                    try
                    {
                        con.Open();

                        OleDbCommand cmd = new OleDbCommand("DELETE FROM 部品 WHERE 部品ID = " + id + "", con);
                        cmd.ExecuteNonQuery();
                    }
                    catch
                    {
                        MessageBox.Show("データベースに接続できませんでした。");
                        return;
                    }
                    finally
                    {
                        con.Close();
                    }
                }
                LoadMaster();
                BaseFormClear();
                MessageBox.Show("削除しました。");
            }
        }
        private void BaseFormClear()
        {
            foreach (Control c in f1.Controls)
            {
                if (c is TextBox) c.Text = "";
                if (c is ComboBox) c.Text = "";
            }
            ((DataGridView)f1.Controls["dataGridView1"]).Rows.Clear();
        }

        private void FindRows(object sender, EventArgs e)
        {
            if (textBox1.Text == "")
            {
                LoadMaster();
            }
            else
            {
                LoadMaster("SELECT * FROM 部品 WHERE 名称 LIKE '%" + textBox1.Text + "%' OR 材質 LIKE '%" + textBox1.Text + "%'");
                textBox1.Text = "";
            }
        }

        private void addRow(object sender, EventArgs e)
        {
            if(textBox18.Text == "" || textBox4.Text == "")
            {
                MessageBox.Show("名称・単価を入力してください。");
                LoadMaster();
                return;
            }

            using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb"))
            {
                try
                {
                    con.Open();

                    OleDbCommand cmd = new OleDbCommand("INSERT INTO 部品 (名称,材質,寸法,単価) VALUES ('" + 
                        textBox18.Text + "','" + textBox2.Text + "','" + textBox3.Text + "','" + textBox4.Text + "')", con);
                    cmd.ExecuteNonQuery();

                }
                catch
                {
                    MessageBox.Show("データベースに接続できませんでした。");
                    return;
                }
                finally
                {
                    con.Close();
                }
            }
            this.textBox18.Text = "";
            this.textBox2.Text = "";
            this.textBox3.Text = "";
            this.textBox4.Text = "";

            LoadMaster();
            MessageBox.Show("追加しました。");            
        }

        private void LoadMaster(string q = "SELECT * FROM 部品")
        {
            f1.Controls["textBox2"].Text = "";
            f1.Controls["textBox18"].Text = "";
            f1.Controls["textBox19"].Text = "";
            f1.Controls["textBox20"].Text = ""; 
            f1.Controls["textBox21"].Text = "";

            dataGridView1.Rows.Clear();

            using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb"))
            {
                try
                {
                    con.Open();

                    OleDbCommand cmd = new OleDbCommand(q, con);
                    OleDbDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        this.dataGridView1.Rows.Add(
                            dr["部品ID"].ToString(),
                            dr["名称"].ToString(),
                            dr["材質"].ToString(),
                            dr["寸法"].ToString(),
                            dr["単価"].ToString(),"","");
                    }
                }
                catch
                {
                    MessageBox.Show("データベースに接続できませんでした。");
                    return;
                }
                finally
                {
                    con.Close();
                }
            }
        }
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

using System.Data.OleDb;

namespace 積算_原価管理

{

public partial class Form2 : Form

{

Form f1;

public Form2(Form f)

{

InitializeComponent();

f1 = f;

button1.Click += new EventHandler(FindRows);

button3.Click += new EventHandler(addRow);

this.KeyPreview = true;

this.KeyDown += new KeyEventHandler((object sender, KeyEventArgs e) => {

if (e.KeyCode == Keys.Escape) this.Close();

});

dataGridView1.CellClick += new DataGridViewCellEventHandler(dgvClick);

dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;

dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;

dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;

dataGridView1.AllowUserToAddRows = false;

dataGridView1.Font = new Font("MS UI Gothic", 12);

dataGridView1.Columns.Add("部品ID", "部品ID");

dataGridView1.Columns["部品ID"].ReadOnly = true;

dataGridView1.Columns.Add("名称", "名称");

dataGridView1.Columns.Add("材質", "材質");

dataGridView1.Columns.Add("寸法", "寸法");

dataGridView1.Columns.Add("単価", "単価");

DataGridViewButtonColumn col = new DataGridViewButtonColumn();

col.Name = "更新";

col.UseColumnTextForButtonValue = true;

col.Text = "更新";

dataGridView1.Columns.Add(col);

col = new DataGridViewButtonColumn();

col.Name = "削除";

col.UseColumnTextForButtonValue = true;

col.Text = "削除";

dataGridView1.Columns.Add(col);

LoadMaster();

}

private void dgvClick(object sender, DataGridViewCellEventArgs e)

{

int col = e.ColumnIndex;

int row = e.RowIndex;

if(col == -1 && row >= 0)

{

f1.Controls["textBox2"].Text = dataGridView1.Rows[row].Cells[0].Value.ToString();

f1.Controls["textBox18"].Text = dataGridView1.Rows[row].Cells[1].Value.ToString();

f1.Controls["textBox19"].Text = dataGridView1.Rows[row].Cells[2].Value.ToString();

f1.Controls["textBox20"].Text = dataGridView1.Rows[row].Cells[3].Value.ToString();

f1.Controls["textBox21"].Text = dataGridView1.Rows[row].Cells[4].Value.ToString();

f1.Controls["textBox3"].Text = "";

f1.Controls["textBox17"].Text = "";

}

if (col == 5 && row >= 0)

{

string id = dataGridView1.Rows[row].Cells[0].Value.ToString();

string name = dataGridView1.Rows[row].Cells[1].Value.ToString();

string material = dataGridView1.Rows[row].Cells[2].Value.ToString();

string size = dataGridView1.Rows[row].Cells[3].Value.ToString();

string price = dataGridView1.Rows[row].Cells[4].Value.ToString();

using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb"))

{

try

{

con.Open();

OleDbCommand cmd = new OleDbCommand("UPDATE 部品 SET " +

"名称 = '" + name + "', 材質 = '" + material + "', 寸法 = '" + size + "', 単価 = " + price + " WHERE 部品ID = " + id + "", con);

cmd.ExecuteNonQuery();

}

catch

{

MessageBox.Show("データベースに接続できませんでした。");

return;

}

finally

{

con.Close();

}

LoadMaster();

BaseFormClear();

MessageBox.Show("更新しました。");

}

if (col == 6 && row >= 0)

{

string id = dataGridView1.Rows[row].Cells[0].Value.ToString();

using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb"))

{

try

{

con.Open();

OleDbCommand cmd = new OleDbCommand("DELETE FROM 部品 WHERE 部品ID = " + id + "", con);

cmd.ExecuteNonQuery();

}

catch

{

MessageBox.Show("データベースに接続できませんでした。");

return;

}

finally

{

con.Close();

}

LoadMaster();

BaseFormClear();

MessageBox.Show("削除しました。");

}

private void BaseFormClear()

{

foreach (Control c in f1.Controls)

{

if (c is TextBox) c.Text = "";

if (c is ComboBox) c.Text = "";

}

((DataGridView)f1.Controls["dataGridView1"]).Rows.Clear();

}

private void FindRows(object sender, EventArgs e)

{

if (textBox1.Text == "")

{

LoadMaster();

}

else

{

LoadMaster("SELECT * FROM 部品 WHERE 名称 LIKE '%" + textBox1.Text + "%' OR 材質 LIKE '%" + textBox1.Text + "%'");

textBox1.Text = "";

}

private void addRow(object sender, EventArgs e)

{

if(textBox18.Text == "" || textBox4.Text == "")

{

MessageBox.Show("名称・単価を入力してください。");

LoadMaster();

return;

}

using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb"))

{

try

{

con.Open();

OleDbCommand cmd = new OleDbCommand("INSERT INTO 部品 (名称,材質,寸法,単価) VALUES ('" +

textBox18.Text + "','" + textBox2.Text + "','" + textBox3.Text + "','" + textBox4.Text + "')", con);

cmd.ExecuteNonQuery();

}

catch

{

MessageBox.Show("データベースに接続できませんでした。");

return;

}

finally

{

con.Close();

}

this.textBox18.Text = "";

this.textBox2.Text = "";

this.textBox3.Text = "";

this.textBox4.Text = "";

LoadMaster();

MessageBox.Show("追加しました。");

}

private void LoadMaster(string q = "SELECT * FROM 部品")

{

f1.Controls["textBox2"].Text = "";

f1.Controls["textBox18"].Text = "";

f1.Controls["textBox19"].Text = "";

f1.Controls["textBox20"].Text = "";

f1.Controls["textBox21"].Text = "";

dataGridView1.Rows.Clear();

using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb"))

{

try

{

con.Open();

OleDbCommand cmd = new OleDbCommand(q, con);

OleDbDataReader dr = cmd.ExecuteReader();

while (dr.Read())

{

this.dataGridView1.Rows.Add(

dr["部品ID"].ToString(),

dr["名称"].ToString(),

dr["材質"].ToString(),

dr["寸法"].ToString(),

dr["単価"].ToString(),"","");

}

catch

{

MessageBox.Show("データベースに接続できませんでした。");

return;

}

finally

{

con.Close();

}

Form3.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

using System.Data.OleDb;

namespace 積算_原価管理
{
    public partial class Form3 : Form
    {
        Form f1;
        public Form3(Form f)
        {
            InitializeComponent();

            f1 = f;

            button1.Click += new EventHandler(FindRows);

            this.KeyPreview = true;
            this.KeyDown += new KeyEventHandler((object sender, KeyEventArgs e) => {
                if (e.KeyCode == Keys.Escape) this.Close();
            });

            dataGridView1.CellClick += new DataGridViewCellEventHandler(dgvClick);

            dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
            dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
            dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;
            dataGridView1.AllowUserToAddRows = false;

            dataGridView1.Font = new Font("MS UI Gothic", 12);
            dataGridView1.ReadOnly = true;

            dataGridView1.Columns.Add("ID", "ID");
            dataGridView1.Columns.Add("向先", "向先");
            dataGridView1.Columns.Add("型式", "型式");
            dataGridView1.Columns.Add("製作先", "製作先");

            LoadMaster();
        }

        private void dgvClick(object sender, DataGridViewCellEventArgs e)
        {
            int col = e.ColumnIndex;
            int row = e.RowIndex;

            if (col == -1 && row >= 0)
            {
                f1.Controls["textBox1"].Text = dataGridView1.Rows[row].Cells[0].Value.ToString();
                f1.Controls["comboBox1"].Text = dataGridView1.Rows[row].Cells[1].Value.ToString();
                f1.Controls["comboBox2"].Text = dataGridView1.Rows[row].Cells[2].Value.ToString();
                f1.Controls["comboBox3"].Text = dataGridView1.Rows[row].Cells[3].Value.ToString();
            }
        }

        private void FindRows(object sender, EventArgs e)
        {
            if (textBox1.Text == "")
            {
                LoadMaster();
            }
            else
            {
                LoadMaster("SELECT ID,向先,型式,製作先 FROM 製品 WHERE 向先 LIKE '%" + textBox1.Text + "%' OR 型式 LIKE '%" + textBox1.Text + "%' OR 製作先 LIKE '%" + textBox1.Text + "%'");
                textBox1.Text = "";
            }
        }

        private void LoadMaster(string q = "SELECT ID,向先,型式,製作先 FROM 製品")
        {
            f1.Controls["textBox1"].Text = "";
            f1.Controls["comboBox1"].Text = "";
            f1.Controls["comboBox2"].Text = "";
            f1.Controls["comboBox3"].Text = "";

            dataGridView1.Rows.Clear();

            using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb"))
            {
                try
                {
                    con.Open();

                    OleDbCommand cmd = new OleDbCommand(q, con);
                    OleDbDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        this.dataGridView1.Rows.Add(dr["ID"].ToString(),dr["向先"].ToString(),dr["型式"].ToString(), dr["製作先"].ToString());
                    }
                }
                catch
                {
                    MessageBox.Show("データベースに接続できませんでした。");
                    return;
                }
                finally
                {
                    con.Close();
                }
            }
        }
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

using System.Data.OleDb;

namespace 積算_原価管理

{

public partial class Form3 : Form

{

Form f1;

public Form3(Form f)

{

InitializeComponent();

f1 = f;

button1.Click += new EventHandler(FindRows);

this.KeyPreview = true;

this.KeyDown += new KeyEventHandler((object sender, KeyEventArgs e) => {

if (e.KeyCode == Keys.Escape) this.Close();

});

dataGridView1.CellClick += new DataGridViewCellEventHandler(dgvClick);

dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;

dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;

dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;

dataGridView1.AllowUserToAddRows = false;

dataGridView1.Font = new Font("MS UI Gothic", 12);

dataGridView1.ReadOnly = true;

dataGridView1.Columns.Add("ID", "ID");

dataGridView1.Columns.Add("向先", "向先");

dataGridView1.Columns.Add("型式", "型式");

dataGridView1.Columns.Add("製作先", "製作先");

LoadMaster();

}

private void dgvClick(object sender, DataGridViewCellEventArgs e)

{

int col = e.ColumnIndex;

int row = e.RowIndex;

if (col == -1 && row >= 0)

{

f1.Controls["textBox1"].Text = dataGridView1.Rows[row].Cells[0].Value.ToString();

f1.Controls["comboBox1"].Text = dataGridView1.Rows[row].Cells[1].Value.ToString();

f1.Controls["comboBox2"].Text = dataGridView1.Rows[row].Cells[2].Value.ToString();

f1.Controls["comboBox3"].Text = dataGridView1.Rows[row].Cells[3].Value.ToString();

}

private void FindRows(object sender, EventArgs e)

{

if (textBox1.Text == "")

{

LoadMaster();

}

else

{

LoadMaster("SELECT ID,向先,型式,製作先 FROM 製品 WHERE 向先 LIKE '%" + textBox1.Text + "%' OR 型式 LIKE '%" + textBox1.Text + "%' OR 製作先 LIKE '%" + textBox1.Text + "%'");

textBox1.Text = "";

}

private void LoadMaster(string q = "SELECT ID,向先,型式,製作先 FROM 製品")

{

f1.Controls["textBox1"].Text = "";

f1.Controls["comboBox1"].Text = "";

f1.Controls["comboBox2"].Text = "";

f1.Controls["comboBox3"].Text = "";

dataGridView1.Rows.Clear();

using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb"))

{

try

{

con.Open();

OleDbCommand cmd = new OleDbCommand(q, con);

OleDbDataReader dr = cmd.ExecuteReader();

while (dr.Read())

{

this.dataGridView1.Rows.Add(dr["ID"].ToString(),dr["向先"].ToString(),dr["型式"].ToString(), dr["製作先"].ToString());

}

catch

{

MessageBox.Show("データベースに接続できませんでした。");

return;

}

finally

{

con.Close();

}

取り出しはVBAにて

Sub func_down()

On Error GoTo e

ym = MsgBox("一覧表で実行する必要があります。" & Chr(13) & "実行しますか？", vbYesNo)
If ym = vbNo Then Exit Sub

Dim cn As Object
Set cn = CreateObject("ADODB.Connection")

Dim rs As Object
Set rs = CreateObject("ADODB.Recordset")

cn.Provider = "Microsoft.JET.OLEDB.4.0"
cn.Properties("Data Source").Value = ThisWorkbook.Path & "\genka_db_2.mdb"
cn.Open
cn.CursorLocation = 3

c = 1

With ActiveSheet

For r = 7 To .UsedRange.Rows.Count

  Customer = .Cells(r, c + 1).Value
  ProductName = .Cells(r, c + 2).Value
  Supplier = .Cells(r, c + 3).Value
  
  If Customer <> "" And ProductName <> "" Then
  
    q = "SELECT ID,向先,型式,販売価格,営業手配分,工数,塗装費,外注費,材料費,輸送費荷受,輸送費納品,試験費,品証費用,品証経費,社内製作分,備考 " & _
    "FROM 製品 WHERE 向先 = '" & Customer & "' AND 型式 = '" & ProductName & "' AND 製作先 = '" & Supplier & "'"
    Debug.Print q
    
    rs.Open q, cn

    If rs.RecordCount = 1 Then

      .Cells(r, c + 4).Value = rs!販売価格
      .Cells(r, c + 5).Value = rs!営業手配分
      
      .Cells(r, c + 11).Value = rs!外注費
      .Cells(r, c + 13).Value = rs!輸送費荷受
      .Cells(r, c + 14).Value = rs!輸送費納品
      .Cells(r, c + 15).Value = rs!試験費
      .Cells(r, c + 21).Value = rs!品証費用
      .Cells(r, c + 22).Value = rs!品証経費
      .Cells(r, c + 23).Value = rs!社内製作分
      .Cells(r, c + 33).Value = rs!備考
      If rs.State = 1 Then rs.Close
      
      '工数
      
      Dim manHour As Currency: manHour = 0

      q = "SELECT " & _
      "部品表.集計ID, " & _
      "Sum(部品表.数量 * 部品.単価) AS 合計 " & _
      "FROM 部品表 LEFT JOIN 部品 " & _
      "ON 部品表.部品ID_ = 部品.部品ID " & _
      "WHERE (((部品表.[製品ID])=(SELECT ID " & _
      "FROM 製品 " & _
      "WHERE 向先 = '" & Customer & "' AND 型式 = '" & ProductName & "' AND 製作先 = '" & Supplier & "' " & _
      "))) AND 集計ID = " & 1 & " " & _
      "GROUP BY 部品表.集計ID;"
      rs.Open q, cn
      If rs.RecordCount = 1 Then manHour = rs!合計
      If rs.State = 1 Then rs.Close
      
      .Cells(r, c + 6).Value = manHour
      
      '塗装費
      
      Dim paintingCost As Currency: paintingCost = 0
      
      q = "SELECT " & _
      "部品表.集計ID, " & _
      "Sum(部品表.数量 * 部品.単価) AS 合計 " & _
      "FROM 部品表 LEFT JOIN 部品 " & _
      "ON 部品表.部品ID_ = 部品.部品ID " & _
      "WHERE (((部品表.[製品ID])=(SELECT ID " & _
      "FROM 製品 " & _
      "WHERE 向先 = '" & Customer & "' AND 型式 = '" & ProductName & "' AND 製作先 = '" & Supplier & "' " & _
      "))) AND 集計ID = " & 2 & " " & _
      "GROUP BY 部品表.集計ID;"
      rs.Open q, cn
      If rs.RecordCount = 1 Then paintingCost = rs!合計
      If rs.State = 1 Then rs.Close
      
      .Cells(r, c + 9).Value = paintingCost
      
      '材料費1
      
      Dim partsCost1 As Currency: partsCost1 = 0
      Dim partsCost2 As Currency: partsCost2 = 0
      Dim partsCost3 As Currency: partsCost3 = 0
      
      q = "SELECT " & _
      "部品表.集計ID, " & _
      "Sum(部品表.数量 * 部品.単価) AS 合計 " & _
      "FROM 部品表 LEFT JOIN 部品 " & _
      "ON 部品表.部品ID_ = 部品.部品ID " & _
      "WHERE (((部品表.[製品ID])=(SELECT ID " & _
      "FROM 製品 " & _
      "WHERE 向先 = '" & Customer & "' AND 型式 = '" & ProductName & "' AND 製作先 = '" & Supplier & "' " & _
      "))) AND 集計ID = " & 3 & " " & _
      "GROUP BY 部品表.集計ID;"
      rs.Open q, cn
      
      If rs.RecordCount = 1 Then partsCost1 = rs!合計
      If rs.State = 1 Then rs.Close
      
      '材料費2
      q = "SELECT " & _
      "部品表.集計ID, " & _
      "Sum(部品表.数量 * 部品.単価) AS 合計 " & _
      "FROM 部品表 LEFT JOIN 部品 " & _
      "ON 部品表.部品ID_ = 部品.部品ID " & _
      "WHERE (((部品表.[製品ID])=(SELECT ID " & _
      "FROM 製品 " & _
      "WHERE 向先 = '" & Customer & "' AND 型式 = '" & ProductName & "' AND 製作先 = '" & Supplier & "' " & _
      "))) AND 集計ID = " & 4 & " " & _
      "GROUP BY 部品表.集計ID;"
      rs.Open q, cn
      
      If rs.RecordCount = 1 Then partsCost2 = rs!合計
      If rs.State = 1 Then rs.Close
      
      '材料費3
      q = "SELECT " & _
      "部品表.集計ID, " & _
      "Sum(部品表.数量 * 部品.単価) AS 合計 " & _
      "FROM 部品表 LEFT JOIN 部品 " & _
      "ON 部品表.部品ID_ = 部品.部品ID " & _
      "WHERE (((部品表.[製品ID])=(SELECT ID " & _
      "FROM 製品 " & _
      "WHERE 向先 = '" & Customer & "' AND 型式 = '" & ProductName & "' AND 製作先 = '" & Supplier & "' " & _
      "))) AND 集計ID = " & 5 & " " & _
      "GROUP BY 部品表.集計ID;"
      rs.Open q, cn
      
      If rs.RecordCount = 1 Then partsCost3 = rs!合計
      If rs.State = 1 Then rs.Close

      .Cells(r, c + 12).Value = partsCost1 + (partsCost1 * 0.2) + partsCost2 + partsCost3
    
    ElseIf rs.RecordCount > 1 Then
    
        MsgBox "向先・型式が重複している可能性があります。" & Chr(13) & r & "行目"
    
    End If
    
    If rs.State = 1 Then rs.Close
    
  End If

Next r

End With

If rs.State = 1 Then rs.Close
Set rs = Nothing

If cn.State = 1 Then cn.Close
Set cn = Nothing

MsgBox "終了しました。"

Exit Sub

e:

  If rs.State = 1 Then rs.Close
  Set rs = Nothing
  
  If cn.State = 1 Then cn.Close
  Set cn = Nothing
  
  MsgBox "不具合が発生しています。管理者へ連絡してください。"

End Sub

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

Sub func_down()

On Error GoTo e

ym = MsgBox("一覧表で実行する必要があります。" & Chr(13) & "実行しますか？", vbYesNo)

If ym = vbNo Then Exit Sub

Dim cn As Object

Set cn = CreateObject("ADODB.Connection")

Dim rs As Object

Set rs = CreateObject("ADODB.Recordset")

cn.Provider = "Microsoft.JET.OLEDB.4.0"

cn.Properties("Data Source").Value = ThisWorkbook.Path & "\genka_db_2.mdb"

cn.Open

cn.CursorLocation = 3

c = 1

With ActiveSheet

For r = 7 To .UsedRange.Rows.Count

Customer = .Cells(r, c + 1).Value

ProductName = .Cells(r, c + 2).Value

Supplier = .Cells(r, c + 3).Value

If Customer <> "" And ProductName <> "" Then

q = "SELECT ID,向先,型式,販売価格,営業手配分,工数,塗装費,外注費,材料費,輸送費荷受,輸送費納品,試験費,品証費用,品証経費,社内製作分,備考 " & _

"FROM 製品 WHERE 向先 = '" & Customer & "' AND 型式 = '" & ProductName & "' AND 製作先 = '" & Supplier & "'"

Debug.Print q

rs.Open q, cn

If rs.RecordCount = 1 Then

.Cells(r, c + 4).Value = rs!販売価格

.Cells(r, c + 5).Value = rs!営業手配分

.Cells(r, c + 11).Value = rs!外注費

.Cells(r, c + 13).Value = rs!輸送費荷受

.Cells(r, c + 14).Value = rs!輸送費納品

.Cells(r, c + 15).Value = rs!試験費

.Cells(r, c + 21).Value = rs!品証費用

.Cells(r, c + 22).Value = rs!品証経費

.Cells(r, c + 23).Value = rs!社内製作分

.Cells(r, c + 33).Value = rs!備考

If rs.State = 1 Then rs.Close

'工数

Dim manHour As Currency: manHour = 0

q = "SELECT " & _

"部品表.集計ID, " & _

"Sum(部品表.数量 * 部品.単価) AS 合計 " & _

"FROM 部品表 LEFT JOIN 部品 " & _

"ON 部品表.部品ID_ = 部品.部品ID " & _

"WHERE (((部品表.[製品ID])=(SELECT ID " & _

"FROM 製品 " & _

"WHERE 向先 = '" & Customer & "' AND 型式 = '" & ProductName & "' AND 製作先 = '" & Supplier & "' " & _

"))) AND 集計ID = " & 1 & " " & _

"GROUP BY 部品表.集計ID;"

rs.Open q, cn

If rs.RecordCount = 1 Then manHour = rs!合計

If rs.State = 1 Then rs.Close

.Cells(r, c + 6).Value = manHour

'塗装費

Dim paintingCost As Currency: paintingCost = 0

q = "SELECT " & _

"部品表.集計ID, " & _

"Sum(部品表.数量 * 部品.単価) AS 合計 " & _

"FROM 部品表 LEFT JOIN 部品 " & _

"ON 部品表.部品ID_ = 部品.部品ID " & _

"WHERE (((部品表.[製品ID])=(SELECT ID " & _

"FROM 製品 " & _

"WHERE 向先 = '" & Customer & "' AND 型式 = '" & ProductName & "' AND 製作先 = '" & Supplier & "' " & _

"))) AND 集計ID = " & 2 & " " & _

"GROUP BY 部品表.集計ID;"

rs.Open q, cn

If rs.RecordCount = 1 Then paintingCost = rs!合計

If rs.State = 1 Then rs.Close

.Cells(r, c + 9).Value = paintingCost

'材料費1

Dim partsCost1 As Currency: partsCost1 = 0

Dim partsCost2 As Currency: partsCost2 = 0

Dim partsCost3 As Currency: partsCost3 = 0

q = "SELECT " & _

"部品表.集計ID, " & _

"Sum(部品表.数量 * 部品.単価) AS 合計 " & _

"FROM 部品表 LEFT JOIN 部品 " & _

"ON 部品表.部品ID_ = 部品.部品ID " & _

"WHERE (((部品表.[製品ID])=(SELECT ID " & _

"FROM 製品 " & _

"WHERE 向先 = '" & Customer & "' AND 型式 = '" & ProductName & "' AND 製作先 = '" & Supplier & "' " & _

"))) AND 集計ID = " & 3 & " " & _

"GROUP BY 部品表.集計ID;"

rs.Open q, cn

If rs.RecordCount = 1 Then partsCost1 = rs!合計

If rs.State = 1 Then rs.Close

'材料費2

q = "SELECT " & _

"部品表.集計ID, " & _

"Sum(部品表.数量 * 部品.単価) AS 合計 " & _

"FROM 部品表 LEFT JOIN 部品 " & _

"ON 部品表.部品ID_ = 部品.部品ID " & _

"WHERE (((部品表.[製品ID])=(SELECT ID " & _

"FROM 製品 " & _

"WHERE 向先 = '" & Customer & "' AND 型式 = '" & ProductName & "' AND 製作先 = '" & Supplier & "' " & _

"))) AND 集計ID = " & 4 & " " & _

"GROUP BY 部品表.集計ID;"

rs.Open q, cn

If rs.RecordCount = 1 Then partsCost2 = rs!合計

If rs.State = 1 Then rs.Close

'材料費3

q = "SELECT " & _

"部品表.集計ID, " & _

"Sum(部品表.数量 * 部品.単価) AS 合計 " & _

"FROM 部品表 LEFT JOIN 部品 " & _

"ON 部品表.部品ID_ = 部品.部品ID " & _

"WHERE (((部品表.[製品ID])=(SELECT ID " & _

"FROM 製品 " & _

"WHERE 向先 = '" & Customer & "' AND 型式 = '" & ProductName & "' AND 製作先 = '" & Supplier & "' " & _

"))) AND 集計ID = " & 5 & " " & _

"GROUP BY 部品表.集計ID;"

rs.Open q, cn

If rs.RecordCount = 1 Then partsCost3 = rs!合計

If rs.State = 1 Then rs.Close

.Cells(r, c + 12).Value = partsCost1 + (partsCost1 * 0.2) + partsCost2 + partsCost3

ElseIf rs.RecordCount > 1 Then

MsgBox "向先・型式が重複している可能性があります。" & Chr(13) & r & "行目"

End If

If rs.State = 1 Then rs.Close

End If

Next r

End With

If rs.State = 1 Then rs.Close

Set rs = Nothing

If cn.State = 1 Then cn.Close

Set cn = Nothing

MsgBox "終了しました。"

Exit Sub

If rs.State = 1 Then rs.Close

Set rs = Nothing

If cn.State = 1 Then cn.Close

Set cn = Nothing

MsgBox "不具合が発生しています。管理者へ連絡してください。"

End Sub

即席登録

Private Sub CommandButton1_Click()

On Error GoTo e

Dim cn As Object
Set cn = CreateObject("ADODB.Connection")

cn.Provider = "Microsoft.JET.OLEDB.4.0"
cn.Properties("Data Source").Value = ThisWorkbook.Path & "\genka_db_2.mdb"
cn.Open

With ActiveSheet

r = 2
Do While .Cells(r, 1) <> ""

  q = "INSERT INTO 部品 (名称,材質,寸法,単価) VALUES ('" & _
  .Cells(r, 1).Value & "','" & .Cells(r, 2).Value & "','" & .Cells(r, 3).Value & "','" & .Cells(r, 4).Value & "')"
  cn.Execute q: Debug.Print q
  
r = r + 1
Loop

End With

If cn.State = 1 Then cn.Close
Set cn = Nothing

MsgBox "終了しました。"

Exit Sub

e:

  If cn.State = 1 Then cn.Close
  Set cn = Nothing

  MsgBox "不具合が発生しています。"
  
End Sub

Private Sub CommandButton1_Click()

On Error GoTo e

Dim cn As Object

Set cn = CreateObject("ADODB.Connection")

cn.Provider = "Microsoft.JET.OLEDB.4.0"

cn.Properties("Data Source").Value = ThisWorkbook.Path & "\genka_db_2.mdb"

cn.Open

With ActiveSheet

r = 2

Do While .Cells(r, 1) <> ""

q = "INSERT INTO 部品 (名称,材質,寸法,単価) VALUES ('" & _

.Cells(r, 1).Value & "','" & .Cells(r, 2).Value & "','" & .Cells(r, 3).Value & "','" & .Cells(r, 4).Value & "')"

cn.Execute q: Debug.Print q

r = r + 1

Loop

End With

If cn.State = 1 Then cn.Close

Set cn = Nothing

MsgBox "終了しました。"

Exit Sub

If cn.State = 1 Then cn.Close

Set cn = Nothing

MsgBox "不具合が発生しています。"

End Sub

2017-01-17

出荷前工程表

ある帳票から工程管理用の帳票を作ろうとしていた模様。
昔のでハッキリ思い出せない。

Dim ymd As Variant
Dim dst As Integer
Dim comma_count As Integer

Sub auto_open()

On Error Resume Next

Application.CommandBars("cell").Controls("出荷前工程表作成(工程表で実行)").Delete
Application.CommandBars("cell").Controls("出荷前工程表DATA_SAVE").Delete
Application.CommandBars("cell").Controls("出荷前工程表DATA_LOAD").Delete

With Application.CommandBars("cell").Controls.Add
.OnAction = "出荷前工程表"
.Caption = "出荷前工程表作成(工程表で実行)"
End With

With Application.CommandBars("cell").Controls.Add
.OnAction = "save_data"
.Caption = "出荷前工程表DATA_SAVE"
End With

With Application.CommandBars("cell").Controls.Add
.OnAction = "load_data"
.Caption = "出荷前工程表DATA_LOAD"
End With

End Sub

Sub auto_close()

On Error Resume Next

Application.CommandBars("cell").Controls("出荷前工程表作成(工程表で実行)").Delete
Application.CommandBars("cell").Controls("出荷前工程表DATA_SAVE").Delete
Application.CommandBars("cell").Controls("出荷前工程表DATA_LOAD").Delete

End Sub

Sub 出荷前工程表()

ymd = ""
dst = 2

For Each ws In Sheets

    If ws.Name = "出荷前工程表" Then
    
        If vbYes = MsgBox("出荷前工程表シートが存在します。削除しますか？", vbYesNo) Then
        
            Worksheets("出荷前工程表").Delete
        
        Else
        
            Exit Sub
        
        End If
    
    End If

Next ws

On Error Resume Next

ymd = Mid(ActiveWorkbook.Name, 1, 4) & "/" & Replace(Sheets(1).Cells(2, 8).Value, "月", "") & "/" & "21"

On Error GoTo 0

If False = IsDate(ymd) Then

  MsgBox "日付が正しく取得できません。" & Chr(13) & "工程表を前面にしていないとエラーになります。"
  Exit Sub
  
End If
    
For Each ws In Sheets

  tmp = Mid(ActiveWorkbook.Name, 1, 4) & "/" & Replace(ws.Cells(2, 8).Value, "月", "") & "/" & "21"
  
  If ymd <> tmp Then
  
    MsgBox "日付が正しく取得できません。" & Chr(13) & "全てのシートが同じ日付でないとエラーになります。"
    Exit Sub
  
  End If

Next ws

sc = ActiveWorkbook.Sheets.Count
ReDim buff(sc, 2) As String

For i = 1 To sc
    
    buff(i, 1) = Sheets(i).Name
    buff(i, 2) = form_count(buff(i, 1))
    
    If buff(i, 2) = -1 Then
    
      MsgBox buff(i, 1) & "シートは工程表のフォーマットが正しくありません。"
      Exit Sub
    
    End If
    
Next i

Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "出荷前工程表"

Sheets("出荷前工程表").Cells(1, 1).Value = "班名"
Sheets("出荷前工程表").Cells(1, 2).Value = "型式"
Sheets("出荷前工程表").Cells(1, 3).Value = "工番"
Sheets("出荷前工程表").Cells(1, 4).Value = "数量"
Sheets("出荷前工程表").Cells(1, 5).Value = "納期"
Sheets("出荷前工程表").Cells(1, 6).Value = "組完"

Sheets("出荷前工程表").Rows(1).HorizontalAlignment = xlHAlignCenter

For i = 1 To sc

    Call main_proc(buff(i, 1), buff(i, 2))

Next i

tmp = ymd
i = 7

Do While tmp <> DateAdd("m", 1, ymd)

  Sheets("出荷前工程表").Cells(1, i).Value = Year(tmp) & "/" & Month(tmp) & "/" & Day(tmp)
  Sheets("出荷前工程表").Cells(1, i).NumberFormatLocal = "m/d"
  
  tmp = DateAdd("d", 1, tmp)
  i = i + 1
  
Loop

Sheets("出荷前工程表").Columns.AutoFit

yn = MsgBox("データを復元しますか？", vbYesNo)
If yn = vbYes Then Call load_data

End Sub

Function form_count(ByVal sn As String) As Integer

For i = Sheets(sn).UsedRange.Rows.Count To 1 Step -1

    If Sheets(sn).Range("A" & i & "").Value <> "" Then Exit For

Next i

Select Case i

    Case 24
        tmp = 1
    
    Case 49
        tmp = 2
    
    Case 74
        tmp = 3
    
    Case 99
        tmp = 4
    
    Case 124
        tmp = 5

    Case Else
        tmp = -1
        
End Select

form_count = tmp
    
End Function

Sub main_proc(ByVal sn As String, ByVal fc As Integer)

For r = 5 To fc * 25 - 1

  r_chk = False

  For c = 100 To 8 Step -1
  
    If Sheets(sn).Cells(r, c).Interior.Color = RGB(0, 0, 0) Then
    
      r_chk = True
      Exit For
    
    End If
  
  Next c

  If r_chk Then
    
    Sheets("出荷前工程表").Cells(dst, 1).Value = sn
    Sheets("出荷前工程表").Cells(dst, 2).Value = rep(Sheets(sn).Cells(r, 2).Value)
    Sheets("出荷前工程表").Cells(dst, 3).Value = rep(Sheets(sn).Cells(r, 3).Value)
    Sheets("出荷前工程表").Cells(dst, 4).Value = Sheets(sn).Cells(r, 5).Value
    Sheets("出荷前工程表").Cells(dst, 5).Value = Sheets(sn).Cells(r, 6).Value
    If Sheets(sn).Cells(r, 7).Value <> "" Then Sheets("出荷前工程表").Cells(dst, 5).Value = Sheets(sn).Cells(r, 7).Value
    
    If c >= 41 Then
      tmp = DateAdd("m", 1, ymd)
    Else
      tmp = ymd
    End If
    
    Sheets("出荷前工程表").Cells(dst, 6).Value = Year(tmp) & "/" & Month(tmp) & "/" & Sheets(sn).Cells(3, c).MergeArea(1, 1).Value
    
    dst = dst + 1
  
  End If

  Select Case r
  
    Case 99, 74, 49, 24
    r = r + 5
    
  End Select

Next r

End Sub

Function rep(tmp As String)
tmp = Replace(tmp, vbLf, " ")
tmp = Replace(tmp, "  ", " ")
tmp = Replace(tmp, "  ", " ")
tmp = Replace(tmp, ",", "，")
rep = tmp
End Function

Sub save_data()

If ActiveSheet.Name <> "出荷前工程表" Then

  MsgBox "出荷前工程表シートで実行してください。"
  Exit Sub
  
End If

yn = MsgBox("サーバー上のデータが上書きされます。実行しますか？", vbYesNo)
If yn = vbNo Then Exit Sub

If chk_col = -1 Then

  MsgBox "最終列が正しく取得できませんでした。"
  Exit Sub
  
End If

If chk_btm() = -1 Then

  MsgBox "最終行が正しく取得できませんでした。"
  Exit Sub

End If

Call u_key_chk

Set cn = CreateObject("adodb.connection")
cn.Open "provider=microsoft.jet.oledb.4.0; data source=" & ThisWorkbook.Path & "\syukka_kotei.mdb;"

On Error GoTo ex

For r = 2 To chk_btm()

  u_key = Sheets("出荷前工程表").Cells(r, 3).Text & " " & Sheets("出荷前工程表").Cells(r, 4).Text & " " & Format(Sheets("出荷前工程表").Cells(1, 7).Text, "yyyymmdd")
  u_day = Format(Sheets("出荷前工程表").Cells(1, 7).Text, "yyyy/mm/dd")

  For c = 7 To chk_col
    
    If c = 7 Then
      u_data = ""
      u_data = rep(Sheets("出荷前工程表").Cells(r, c).Text)
    Else
      u_data = u_data & "," & rep(Sheets("出荷前工程表").Cells(r, c).Text)
    End If
    
  Next c
  
  cn.Execute "delete * from tbl where u_key = '" & u_key & "' and u_day = #" & u_day & "#"
  cn.Execute "insert into tbl(u_key,u_day,u_data) values('" & u_key & "',#" & u_day & "#,'" & u_data & "')"

Next r

If cn.State = 1 Then cn.Close
Set cn = Nothing

MsgBox "done"

Exit Sub

ex:

  MsgBox "正しく保存できませんでした。"
  
  If cn.State = 1 Then cn.Close
  Set cn = Nothing

End Sub

Function chk_col()

c = Sheets("出荷前工程表").Cells(1, 1).CurrentRegion.Columns.Count
u = Sheets("出荷前工程表").UsedRange.Columns.Count

If c <> u Then

  chk_col = -1
  Exit Function

End If

chk_col = c

End Function

Function chk_btm()

c = Sheets("出荷前工程表").Cells(1, 1).CurrentRegion.Rows.Count
u = Sheets("出荷前工程表").UsedRange.Rows.Count

If c <> u Then

  chk_btm = -1
  Exit Function

End If

chk_btm = c

End Function

Sub load_data()

If ActiveSheet.Name <> "出荷前工程表" Then

  MsgBox "出荷前工程表シートで実行してください。"
  Exit Sub
  
End If

yn = MsgBox("シート上のデータが上書きされます。実行しますか？", vbYesNo)
If yn = vbNo Then Exit Sub

If chk_col = -1 Then

  MsgBox "最終列が正しく取得できませんでした。"
  Exit Sub
  
End If

If chk_btm() = -1 Then

  MsgBox "最終行が正しく取得できませんでした。"
  Exit Sub

End If

Call u_key_chk

Set cn = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.recordset")

On Error GoTo ex

For r = 2 To chk_btm()

  u_key = Sheets("出荷前工程表").Cells(r, 3).Text & " " & Sheets("出荷前工程表").Cells(r, 4).Text & " " & Format(Sheets("出荷前工程表").Cells(1, 7).Text, "yyyymmdd")

  cn.Open "provider=microsoft.jet.oledb.4.0; data source=" & ThisWorkbook.Path & "\syukka_kotei.mdb;"
  rs.Open "select u_data from tbl where u_key = '" & u_key & "'", cn
 
  buff = ""
  
  Do Until rs.EOF
    
    If buff <> "" Then
    
      MsgBox "データが重複している可能性があります。注意してください。"
      buff = rs!u_data
    
    Else
    
      buff = rs!u_data
    
    End If
    
    rs.MoveNext
    
  Loop
  
  If buff <> "" Then
  
    comma_count = 0
  
    For c = 7 To chk_col

      Sheets("出荷前工程表").Cells(r, c).Value = split_comma(buff)
    
    Next c
  
  End If
  
  If rs.State = 1 Then rs.Close
  If cn.State = 1 Then cn.Close

Next r

If rs.State = 1 Then rs.Close
Set rs = Nothing

If cn.State = 1 Then cn.Close
Set cn = Nothing
  
MsgBox "done"

Exit Sub

ex:

  MsgBox "正しく取得できませんでした。"
  
  If rs.State = 1 Then rs.Close
  Set rs = Nothing

  If cn.State = 1 Then cn.Close
  Set cn = Nothing

End Sub

Function split_comma(tmp)

buff = ""
comma_count_local = 0

For i = 1 To Len(tmp)

  If Mid(tmp, i, 1) <> "," Then
  
    buff = buff + Mid(tmp, i, 1)
  
  Else

    comma_count_local = comma_count_local + 1
    
    If comma_count >= comma_count_local Then
    
      buff = ""
    
    Else
    
      comma_count = comma_count_local
      Exit For
    
    End If
  
  End If

Next i

split_comma = buff

End Function

Sub u_key_chk()

Sheets("出荷前工程表").UsedRange.Interior.Color = xlNone

For r = 2 To chk_btm()
  
  from_u_key = Sheets("出荷前工程表").Cells(r, 3).Text & " " & Sheets("出荷前工程表").Cells(r, 4).Text & " " & Format(Sheets("出荷前工程表").Cells(1, 7).Text, "yyyymmdd")
  
  For rr = (r + 1) To chk_btm()
  
    to_u_key = Sheets("出荷前工程表").Cells(rr, 3).Text & " " & Sheets("出荷前工程表").Cells(rr, 4).Text & " " & Format(Sheets("出荷前工程表").Cells(1, 7).Text, "yyyymmdd")
    
    If from_u_key = to_u_key Then

      Sheets("出荷前工程表").Cells(r, 1).Interior.Color = RGB(255, 0, 0)
      Sheets("出荷前工程表").Cells(rr, 1).Interior.Color = RGB(255, 0, 0)
    
    End If

  Next rr

Next r

End Sub

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

480

481

482

483

484

485

486

487

488

489

490

491

492

Dim ymd As Variant

Dim dst As Integer

Dim comma_count As Integer

Sub auto_open()

On Error Resume Next

Application.CommandBars("cell").Controls("出荷前工程表作成(工程表で実行)").Delete

Application.CommandBars("cell").Controls("出荷前工程表DATA_SAVE").Delete

Application.CommandBars("cell").Controls("出荷前工程表DATA_LOAD").Delete

With Application.CommandBars("cell").Controls.Add

.OnAction = "出荷前工程表"

.Caption = "出荷前工程表作成(工程表で実行)"

End With

With Application.CommandBars("cell").Controls.Add

.OnAction = "save_data"

.Caption = "出荷前工程表DATA_SAVE"

End With

With Application.CommandBars("cell").Controls.Add

.OnAction = "load_data"

.Caption = "出荷前工程表DATA_LOAD"

End With

End Sub

Sub auto_close()

On Error Resume Next

Application.CommandBars("cell").Controls("出荷前工程表作成(工程表で実行)").Delete

Application.CommandBars("cell").Controls("出荷前工程表DATA_SAVE").Delete

Application.CommandBars("cell").Controls("出荷前工程表DATA_LOAD").Delete

End Sub

Sub 出荷前工程表()

ymd = ""

dst = 2

For Each ws In Sheets

If ws.Name = "出荷前工程表" Then

If vbYes = MsgBox("出荷前工程表シートが存在します。削除しますか？", vbYesNo) Then

Worksheets("出荷前工程表").Delete

Else

Exit Sub

End If

Next ws

On Error Resume Next

ymd = Mid(ActiveWorkbook.Name, 1, 4) & "/" & Replace(Sheets(1).Cells(2, 8).Value, "月", "") & "/" & "21"

On Error GoTo 0

If False = IsDate(ymd) Then

MsgBox "日付が正しく取得できません。" & Chr(13) & "工程表を前面にしていないとエラーになります。"

Exit Sub

End If

For Each ws In Sheets

tmp = Mid(ActiveWorkbook.Name, 1, 4) & "/" & Replace(ws.Cells(2, 8).Value, "月", "") & "/" & "21"

If ymd <> tmp Then

MsgBox "日付が正しく取得できません。" & Chr(13) & "全てのシートが同じ日付でないとエラーになります。"

Exit Sub

End If

Next ws

sc = ActiveWorkbook.Sheets.Count

ReDim buff(sc, 2) As String

For i = 1 To sc

buff(i, 1) = Sheets(i).Name

buff(i, 2) = form_count(buff(i, 1))

If buff(i, 2) = -1 Then

MsgBox buff(i, 1) & "シートは工程表のフォーマットが正しくありません。"

Exit Sub

End If

Next i

Sheets.Add after:=Sheets(Sheets.Count)

ActiveSheet.Name = "出荷前工程表"

Sheets("出荷前工程表").Cells(1, 1).Value = "班名"

Sheets("出荷前工程表").Cells(1, 2).Value = "型式"

Sheets("出荷前工程表").Cells(1, 3).Value = "工番"

Sheets("出荷前工程表").Cells(1, 4).Value = "数量"

Sheets("出荷前工程表").Cells(1, 5).Value = "納期"

Sheets("出荷前工程表").Cells(1, 6).Value = "組完"

Sheets("出荷前工程表").Rows(1).HorizontalAlignment = xlHAlignCenter

For i = 1 To sc

Call main_proc(buff(i, 1), buff(i, 2))

Next i

tmp = ymd

i = 7

Do While tmp <> DateAdd("m", 1, ymd)

Sheets("出荷前工程表").Cells(1, i).Value = Year(tmp) & "/" & Month(tmp) & "/" & Day(tmp)

Sheets("出荷前工程表").Cells(1, i).NumberFormatLocal = "m/d"

tmp = DateAdd("d", 1, tmp)

i = i + 1

Loop

Sheets("出荷前工程表").Columns.AutoFit

yn = MsgBox("データを復元しますか？", vbYesNo)

If yn = vbYes Then Call load_data

End Sub

Function form_count(ByVal sn As String) As Integer

For i = Sheets(sn).UsedRange.Rows.Count To 1 Step -1

If Sheets(sn).Range("A" & i & "").Value <> "" Then Exit For

Next i

Select Case i

Case 24

tmp = 1

Case 49

tmp = 2

Case 74

tmp = 3

Case 99

tmp = 4

Case 124

tmp = 5

Case Else

tmp = -1

End Select

form_count = tmp

End Function

Sub main_proc(ByVal sn As String, ByVal fc As Integer)

For r = 5 To fc * 25 - 1

r_chk = False

For c = 100 To 8 Step -1

If Sheets(sn).Cells(r, c).Interior.Color = RGB(0, 0, 0) Then

r_chk = True

Exit For

End If

Next c

If r_chk Then

Sheets("出荷前工程表").Cells(dst, 1).Value = sn

Sheets("出荷前工程表").Cells(dst, 2).Value = rep(Sheets(sn).Cells(r, 2).Value)

Sheets("出荷前工程表").Cells(dst, 3).Value = rep(Sheets(sn).Cells(r, 3).Value)

Sheets("出荷前工程表").Cells(dst, 4).Value = Sheets(sn).Cells(r, 5).Value

Sheets("出荷前工程表").Cells(dst, 5).Value = Sheets(sn).Cells(r, 6).Value

If Sheets(sn).Cells(r, 7).Value <> "" Then Sheets("出荷前工程表").Cells(dst, 5).Value = Sheets(sn).Cells(r, 7).Value

If c >= 41 Then

tmp = DateAdd("m", 1, ymd)

Else

tmp = ymd

End If

Sheets("出荷前工程表").Cells(dst, 6).Value = Year(tmp) & "/" & Month(tmp) & "/" & Sheets(sn).Cells(3, c).MergeArea(1, 1).Value

dst = dst + 1

End If

Select Case r

Case 99, 74, 49, 24

r = r + 5

End Select

Next r

End Sub

Function rep(tmp As String)

tmp = Replace(tmp, vbLf, " ")

tmp = Replace(tmp, " ", " ")

tmp = Replace(tmp, ",", "，")

rep = tmp

End Function

Sub save_data()

If ActiveSheet.Name <> "出荷前工程表" Then

MsgBox "出荷前工程表シートで実行してください。"

Exit Sub

End If

yn = MsgBox("サーバー上のデータが上書きされます。実行しますか？", vbYesNo)

If yn = vbNo Then Exit Sub

If chk_col = -1 Then

MsgBox "最終列が正しく取得できませんでした。"

Exit Sub

End If

If chk_btm() = -1 Then

MsgBox "最終行が正しく取得できませんでした。"

Exit Sub

End If

Call u_key_chk

Set cn = CreateObject("adodb.connection")

cn.Open "provider=microsoft.jet.oledb.4.0; data source=" & ThisWorkbook.Path & "\syukka_kotei.mdb;"

On Error GoTo ex

For r = 2 To chk_btm()

u_key = Sheets("出荷前工程表").Cells(r, 3).Text & " " & Sheets("出荷前工程表").Cells(r, 4).Text & " " & Format(Sheets("出荷前工程表").Cells(1, 7).Text, "yyyymmdd")

u_day = Format(Sheets("出荷前工程表").Cells(1, 7).Text, "yyyy/mm/dd")

For c = 7 To chk_col

If c = 7 Then

u_data = ""

u_data = rep(Sheets("出荷前工程表").Cells(r, c).Text)

Else

u_data = u_data & "," & rep(Sheets("出荷前工程表").Cells(r, c).Text)

End If

Next c

cn.Execute "delete * from tbl where u_key = '" & u_key & "' and u_day = #" & u_day & "#"

cn.Execute "insert into tbl(u_key,u_day,u_data) values('" & u_key & "',#" & u_day & "#,'" & u_data & "')"

Next r

If cn.State = 1 Then cn.Close

Set cn = Nothing

MsgBox "done"

Exit Sub

ex:

MsgBox "正しく保存できませんでした。"

If cn.State = 1 Then cn.Close

Set cn = Nothing

End Sub

Function chk_col()

c = Sheets("出荷前工程表").Cells(1, 1).CurrentRegion.Columns.Count

u = Sheets("出荷前工程表").UsedRange.Columns.Count

If c <> u Then

chk_col = -1

Exit Function

End If

chk_col = c

End Function

Function chk_btm()

c = Sheets("出荷前工程表").Cells(1, 1).CurrentRegion.Rows.Count

u = Sheets("出荷前工程表").UsedRange.Rows.Count

If c <> u Then

chk_btm = -1

Exit Function

End If

chk_btm = c

End Function

Sub load_data()

If ActiveSheet.Name <> "出荷前工程表" Then

MsgBox "出荷前工程表シートで実行してください。"

Exit Sub

End If

yn = MsgBox("シート上のデータが上書きされます。実行しますか？", vbYesNo)

If yn = vbNo Then Exit Sub

If chk_col = -1 Then

MsgBox "最終列が正しく取得できませんでした。"

Exit Sub

End If

If chk_btm() = -1 Then

MsgBox "最終行が正しく取得できませんでした。"

Exit Sub

End If

Call u_key_chk

Set cn = CreateObject("adodb.connection")

Set rs = CreateObject("adodb.recordset")

On Error GoTo ex

For r = 2 To chk_btm()

u_key = Sheets("出荷前工程表").Cells(r, 3).Text & " " & Sheets("出荷前工程表").Cells(r, 4).Text & " " & Format(Sheets("出荷前工程表").Cells(1, 7).Text, "yyyymmdd")

cn.Open "provider=microsoft.jet.oledb.4.0; data source=" & ThisWorkbook.Path & "\syukka_kotei.mdb;"

rs.Open "select u_data from tbl where u_key = '" & u_key & "'", cn

buff = ""

Do Until rs.EOF

If buff <> "" Then

MsgBox "データが重複している可能性があります。注意してください。"

buff = rs!u_data

Else

buff = rs!u_data

End If

rs.MoveNext

Loop

If buff <> "" Then

comma_count = 0

For c = 7 To chk_col

Sheets("出荷前工程表").Cells(r, c).Value = split_comma(buff)

Next c

End If

If rs.State = 1 Then rs.Close

If cn.State = 1 Then cn.Close

Next r

If rs.State = 1 Then rs.Close

Set rs = Nothing

If cn.State = 1 Then cn.Close

Set cn = Nothing

MsgBox "done"

Exit Sub

ex:

MsgBox "正しく取得できませんでした。"

If rs.State = 1 Then rs.Close

Set rs = Nothing

If cn.State = 1 Then cn.Close

Set cn = Nothing

End Sub

Function split_comma(tmp)

buff = ""

comma_count_local = 0

For i = 1 To Len(tmp)

If Mid(tmp, i, 1) <> "," Then

buff = buff + Mid(tmp, i, 1)

Else

comma_count_local = comma_count_local + 1

If comma_count >= comma_count_local Then

buff = ""

Else

comma_count = comma_count_local

Exit For

End If

Next i

split_comma = buff

End Function

Sub u_key_chk()

Sheets("出荷前工程表").UsedRange.Interior.Color = xlNone

For r = 2 To chk_btm()

from_u_key = Sheets("出荷前工程表").Cells(r, 3).Text & " " & Sheets("出荷前工程表").Cells(r, 4).Text & " " & Format(Sheets("出荷前工程表").Cells(1, 7).Text, "yyyymmdd")

For rr = (r + 1) To chk_btm()

to_u_key = Sheets("出荷前工程表").Cells(rr, 3).Text & " " & Sheets("出荷前工程表").Cells(rr, 4).Text & " " & Format(Sheets("出荷前工程表").Cells(1, 7).Text, "yyyymmdd")

If from_u_key = to_u_key Then

Sheets("出荷前工程表").Cells(r, 1).Interior.Color = RGB(255, 0, 0)

Sheets("出荷前工程表").Cells(rr, 1).Interior.Color = RGB(255, 0, 0)

End If

Next rr

Next r

End Sub