frmcrkdrhz.frm

固定资产台帐管理系统

            .ColWidth(7) = 1200
            .ColWidth(8) = 1200
            .ColWidth(9) = 800
            .ColWidth(10) = 900
            .ColWidth(11) = 3500
     End With
  
 End Sub
Private Sub MyWindowsBT()
Dim bbRq1 As Date, bbRq2 As Date, CkorRk As String, Km As String
  bbRq1 = dtpBegin.Value
  bbRq2 = dtpEnd.Value
  
  Km = ComSyDw.Text
  If dtpBegin.Value = dtpEnd.Value Then
   Label1.Caption = Km & Format(bbRq1, "m月d") & "日报表"
  Else
   Label1.Caption = Km & Format(bbRq1, "m月d") & "日至" & Format(bbRq2, "m月d") & "日报表"
  End If
End Sub
Private Sub DataAdd()
  Dim bbRq1 As Date, bbRq2 As Date, tzdh As String, Pm As String, Gg As String, fk As String, Ywy As String
  Dim strSQLkm As String
  Dim strSQL As String
  Dim Sl(18) As Currency, i%, m%, n%, Jl As Long, ii%, j%
  Dim ChangSe As Boolean
  bbRq1 = dtpBegin.Value
  bbRq2 = dtpEnd.Value
  Grdlistini
  With Grdlist
   .Cols = 12
   .Rows = 2
  End With
  
  If dtpBegin.Value > dtpEnd.Value Then
    MsgBox "起始日期不能大于终止日期。", 48, "系统提示"
    Exit Sub
  End If
   
    strSQLkm = "select * from 资产台帐 where ([录入日期] between #" & Format(bbRq1, "m-d-yy") & "# and #" & Format(bbRq2, "m-d-yy") & "#) "
    If ComSyDw <> "" Then strSQLkm = strSQLkm & "and ([现使用单位] = '" & ComSyDw.Text & "')"
    If SbMc.Text <> "" Then strSQLkm = strSQLkm & "and ([设备名称] like '*" & SbMc.Text & "*')"
    strSQLkm = strSQLkm + " order by  val([厂编号])"
    
  Data9.RecordSource = strSQLkm
  Data9.Refresh
            If Data9.Recordset.EOF Then
             Grdlist.Rows = 1
              Exit Sub
            End If
'填写表格
 
 For i = 0 To 10000
   n = 0
   
    If i = 0 Then
      Data9.Recordset.MoveFirst
    Else
      Data9.Recordset.MoveNext
    End If
    
    If Data9.Recordset.EOF Then Exit For
        If (i + 2) / 2 > (i + 2) \ 2 Then ChangSe = True Else ChangSe = False '判断奇偶行
            With Grdlist
              .TextMatrix(.Rows - 1, 0) = Data9.Recordset.Fields("记录编号").Value & ""
              .TextMatrix(.Rows - 1, 1) = Data9.Recordset.Fields("厂编号").Value & ""
              
              .TextMatrix(.Rows - 1, 2) = Data9.Recordset.Fields("现使用单位").Value & ""
              .TextMatrix(.Rows - 1, 3) = Data9.Recordset.Fields("设备名称").Value & ""
              .TextMatrix(.Rows - 1, 4) = Data9.Recordset.Fields("型号").Value & ""
              .TextMatrix(.Rows - 1, 5) = Data9.Recordset.Fields("制造厂名").Value & ""
              .TextMatrix(.Rows - 1, 6) = Data9.Recordset.Fields("出厂编号").Value & ""
              .TextMatrix(.Rows - 1, 7) = Data9.Recordset.Fields("安装年月").Value & ""
              .TextMatrix(.Rows - 1, 8) = Data9.Recordset.Fields("出厂年月").Value & ""
              .TextMatrix(.Rows - 1, 9) = Data9.Recordset.Fields("单位").Value & ""
              .TextMatrix(.Rows - 1, 10) = Data9.Recordset.Fields("安装数量").Value & ""
              .TextMatrix(.Rows - 1, 11) = Data9.Recordset.Fields("备注").Value & ""
             '表格颜色
              
              
                For ii = 0 To 11
                 .Row = .Rows - 1
                 .Col = ii
                    If ChangSe = True Then
                     .CellBackColor = RGB(255, 255, 255)
                    Else
                     .CellBackColor = RGB(230, 240, 255)
                    End If
                 Next ii
                 .Rows = .Rows + 1
                End With
        'WWWWWWWWWWWWWWWWWW
         
        ' End If
         n = n + 1
     
     Next i
      
'制表
    With Grdlist
      If .Rows < 2 Then .Rows = 2
            .RowHeight(0) = 500
    
                For j = 0 To .Cols - 1
                  .FixedAlignment(j) = 4
                Next j
               
          '计算单价,金额
         
    End With
End Sub
Private Sub ComToExcl_Click()
Dim bbRq1 As Date, bbRq2 As Date, CkorRk As String, Km As String
  bbRq1 = dtpBegin.Value
  bbRq2 = dtpEnd.Value
 
  Km = ComSyDw.Text
  Label1.Caption = Km & Format(bbRq1, "m月d") & "日至" & Format(bbRq2, "m月d") & "日报表"
 
 OutDataToExcel Grdlist
End Sub


'导出Excle
Public Sub OutDataToExcel(Flex As Object)
       Dim s   As String
       Dim i   As Integer
       Dim j   As Integer
       Dim k   As Integer
       Dim x   As Integer
       Dim Hang   As String
       Hang = "i"
       On Error GoTo Ert
       Dim Excelapp  As Excel.Application
       Set Excelapp = New Excel.Application
       On Error Resume Next
       DoEvents
       Bar1.Visible = True
       Bar1.Value = 1
       Excelapp.SheetsInNewWorkbook = 1
       Excelapp.Workbooks.Add
       Excelapp.ActiveSheet.Cells(1, 3) = s
      
       Excelapp.ActiveSheet.Cells(1, 3) = Label1.Caption
          Excelapp.ActiveSheet.Cells(1, 3).Font.FontStyle = "Bold"
       Excelapp.Range("D1:D8").Width = 10000
       Excelapp.Selection.Font.FontStyle = "Bold"
       Excelapp.Selection.FontSize = 6
       Select Case Flex.Cols
                     Case 1:
                               Hang = "A"
                     Case 2:
                               Hang = "B"
                   Case 3:
                               Hang = "C"
                   Case 4:
                               Hang = "D"
                   Case 5:
                               Hang = "E"
                   Case 6:
                               Hang = "F"
                   Case 7:
                               Hang = "G"
                   Case 8:
                               Hang = "H"
                   Case 9:
                               Hang = "I"
                   Case 10:
                               Hang = "J"
                   Case 11:
                               Hang = "K"
                   Case 12:
                               Hang = "L"
                 Case 13:
                               Hang = "M"
                 Case 14:
                               Hang = "N"
                 Case 15:
                               Hang = "O"
                 Case 16:
                               Hang = "P"
                 Case 17:
                               Hang = "Q"
                 Case 18:
                               Hang = "R"
                 Case 19:
                               Hang = "S"
                 Case 20:
                               Hang = "U"
                             
             End Select
       With Flex
               k = .Rows
                 With Excelapp.ActiveSheet.Range("a3:" & Hang & .Rows + 2).Borders           '边框设置

                       .LineStyle = 0              'xlBorderLineStyleContinuous

                       .Weight = xlThin

                       .ColorIndex = 0

                       End With

         Excelapp.ActiveSheet.Range("a3:" & Hang & .Rows + 2).Font.Size = 9            'xlBorderLineStyleContinuous
         For i = 0 To k - 1
                         For j = 0 To .Cols - 1
                         Bar1.Value = Bar1.Value + 100 / (.Cols * .Rows)
                           If Bar1.Value = 100 Then
                              Bar1.Value = 100
                               End If
                             DoEvents
                             Excelapp.ActiveSheet.Cells(3 + i, j + 1) = .TextMatrix(i, j)
                       Next j
               Next i
       End With
       Bar1.Visible = False
       Excelapp.Visible = True
     '  Excelapp.Sheets.PrintPreview
    
       Exit Sub
Ert:
       If Not (Excelapp Is Nothing) Then
               Excelapp.Quit
       End If
End Sub



Private Sub Grdlist_DblClick()
 Dim h As Integer
 h = Grdlist.Row
 If h < 1 Then Exit Sub
 Jlbh = Grdlist.TextMatrix(h, 0)
 If Jlbh = "" Then Exit Sub
 Data9.Recordset.FindFirst "记录编号 = " & Jlbh
 
 FrmTzdj.Show
With FrmTzdj
 .Text38.Text = Jlbh
 .Combo1.Text = Data9.Recordset.Fields(2).Value & ""
 .Text1.Text = Data9.Recordset.Fields(3).Value & ""
 .Text4.Text = Data9.Recordset.Fields(4).Value & ""
 .Text2.Text = Data9.Recordset.Fields(5).Value & ""
.Text12.Text = Data9.Recordset.Fields(6).Value & ""
.Text11.Text = Data9.Recordset.Fields(7).Value & ""
 .Text15.Text = Data9.Recordset.Fields(8).Value & ""
 .Text14.Text = Data9.Recordset.Fields(9).Value & ""
 .Text5.Text = Data9.Recordset.Fields(10).Value & ""
.Text16.Text = Data9.Recordset.Fields(11).Value & ""
 .Text13.Text = Data9.Recordset.Fields(12).Value & ""
.Text117.Text = Data9.Recordset.Fields(13).Value & ""
 .Text8.Text = Data9.Recordset.Fields(14).Value & ""
.Text9.Text = Data9.Recordset.Fields(15).Value & ""
 .Text10.Text = Data9.Recordset.Fields(16).Value & ""
.Text23.Text = Data9.Recordset.Fields(17).Value & ""
.Text3.Text = Data9.Recordset.Fields(18).Value & ""
.Text22.Text = Data9.Recordset.Fields(19).Value & ""
 .Text21.Text = Data9.Recordset.Fields(20).Value & ""
.Text19.Text = Data9.Recordset.Fields(21).Value & ""
.Text56.Text = Data9.Recordset.Fields(22).Value & ""
.Text55.Text = Data9.Recordset.Fields(23).Value & ""
.Text53.Text = Data9.Recordset.Fields(24).Value & ""
.Text67.Text = Data9.Recordset.Fields(25).Value & ""
.Text66.Text = Data9.Recordset.Fields(26).Value & ""
.Text64.Text = Data9.Recordset.Fields(27).Value & ""
.Text34.Text = Data9.Recordset.Fields(28).Value & ""
.Text33.Text = Data9.Recordset.Fields(29).Value & ""
.Text31.Text = Data9.Recordset.Fields(30).Value & ""
.Text100.Text = Data9.Recordset.Fields(31).Value & ""
.Text99.Text = Data9.Recordset.Fields(32).Value & ""
 .Text97.Text = Data9.Recordset.Fields(33).Value & ""
.Text78.Text = Data9.Recordset.Fields(34).Value & ""
 .Text77.Text = Data9.Recordset.Fields(35).Value & ""
.Text75.Text = Data9.Recordset.Fields(36).Value & ""
.Text45.Text = Data9.Recordset.Fields(37).Value & ""
.Text44.Text = Data9.Recordset.Fields(38).Value & ""
.Text42.Text = Data9.Recordset.Fields(39).Value & ""
.Text111.Text = Data9.Recordset.Fields(40).Value & ""
.Text110.Text = Data9.Recordset.Fields(41).Value & ""
.Text108.Text = Data9.Recordset.Fields(42).Value & ""
.Text89.Text = Data9.Recordset.Fields(43).Value & ""
.Text88.Text = Data9.Recordset.Fields(44).Value & ""
.Text86.Text = Data9.Recordset.Fields(45).Value & ""
.Text20.Text = Data9.Recordset.Fields(46).Value & ""
.Text17.Text = Data9.Recordset.Fields(47).Value & ""
.Text18.Text = Data9.Recordset.Fields(48).Value & ""
.Text54.Text = Data9.Recordset.Fields(49).Value & ""
.Text51.Text = Data9.Recordset.Fields(50).Value & ""
.Text52.Text = Data9.Recordset.Fields(51).Value & ""
.Text65.Text = Data9.Recordset.Fields(52).Value & ""
.Text62.Text = Data9.Recordset.Fields(53).Value & ""
.Text63.Text = Data9.Recordset.Fields(54).Value & ""

.Text32.Text = Data9.Recordset.Fields(55).Value & ""

.Text29.Text = Data9.Recordset.Fields(56).Value & ""
.Text30.Text = Data9.Recordset.Fields(57).Value & ""
.Text98.Text = Data9.Recordset.Fields(58).Value & ""
.Text95.Text = Data9.Recordset.Fields(59).Value & ""
.Text96.Text = Data9.Recordset.Fields(60).Value & ""
.Text76.Text = Data9.Recordset.Fields(61).Value & ""
.Text73.Text = Data9.Recordset.Fields(62).Value & ""
.Text74.Text = Data9.Recordset.Fields(63).Value & ""
.Text43.Text = Data9.Recordset.Fields(64).Value & ""
.Text40.Text = Data9.Recordset.Fields(65).Value & ""
.Text41.Text = Data9.Recordset.Fields(66).Value & ""
.Text109.Text = Data9.Recordset.Fields(67).Value & ""
.Text106.Text = Data9.Recordset.Fields(68).Value & ""
.Text107.Text = Data9.Recordset.Fields(69).Value & ""
.Text87.Text = Data9.Recordset.Fields(70).Value & ""
.Text84.Text = Data9.Recordset.Fields(71).Value & ""
.Text85.Text = Data9.Recordset.Fields(72).Value & ""
.Text26.Text = Data9.Recordset.Fields(73).Value & ""
.Text24.Text = Data9.Recordset.Fields(74).Value & ""
.Text25.Text = Data9.Recordset.Fields(75).Value & ""
.Text59.Text = Data9.Recordset.Fields(76).Value & ""
.Text57.Text = Data9.Recordset.Fields(77).Value & ""
.Text58.Text = Data9.Recordset.Fields(78).Value & ""
.Text70.Text = Data9.Recordset.Fields(79).Value & ""
.Text68.Text = Data9.Recordset.Fields(80).Value & ""
.Text69.Text = Data9.Recordset.Fields(81).Value & ""
.Text37.Text = Data9.Recordset.Fields(82).Value & ""
.Text35.Text = Data9.Recordset.Fields(83).Value & ""
.Text36.Text = Data9.Recordset.Fields(84).Value & ""
.Text103.Text = Data9.Recordset.Fields(85).Value & ""
.Text101.Text = Data9.Recordset.Fields(86).Value & ""
.Text102.Text = Data9.Recordset.Fields(87).Value & ""
.Text81.Text = Data9.Recordset.Fields(88).Value & ""
.Text79.Text = Data9.Recordset.Fields(89).Value & ""
.Text80.Text = Data9.Recordset.Fields(90).Value & ""
.Text48.Text = Data9.Recordset.Fields(91).Value & ""
.Text46.Text = Data9.Recordset.Fields(92).Value & ""
.Text47.Text = Data9.Recordset.Fields(93).Value & ""
.Text114.Text = Data9.Recordset.Fields(94).Value & ""
.Text112.Text = Data9.Recordset.Fields(95).Value & ""
.Text113.Text = Data9.Recordset.Fields(96).Value & ""
.Text92.Text = Data9.Recordset.Fields(97).Value & ""
.Text90.Text = Data9.Recordset.Fields(98).Value & ""
.Text91.Text = Data9.Recordset.Fields(99).Value & ""
.Text27.Text = Data9.Recordset.Fields(100).Value & ""
.Text60.Text = Data9.Recordset.Fields(101).Value & ""
.Text71.Text = Data9.Recordset.Fields(102).Value & ""
.Text28.Text = Data9.Recordset.Fields(103).Value & ""
.Text61.Text = Data9.Recordset.Fields(104).Value & ""
.Text72.Text = Data9.Recordset.Fields(105).Value & ""
.Text6.Text = Data9.Recordset.Fields(106).Value & ""
 End With
 
End Sub