jc_lf.frm

适合于中小型企业管理

            FirstWeekOfYear =   0
            LCID            =   2052
            SubFormatType   =   0
         EndProperty
      EndProperty
      BeginProperty Column02 
         DataField       =   "品名"
         Caption         =   "   品   名"
         BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} 
            Type            =   0
            Format          =   ""
            HaveTrueFalseNull=   0
            FirstDayOfWeek  =   0
            FirstWeekOfYear =   0
            LCID            =   2052
            SubFormatType   =   0
         EndProperty
      EndProperty
      BeginProperty Column03 
         DataField       =   "规格"
         Caption         =   "        规     格"
         BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} 
            Type            =   0
            Format          =   ""
            HaveTrueFalseNull=   0
            FirstDayOfWeek  =   0
            FirstWeekOfYear =   0
            LCID            =   2052
            SubFormatType   =   0
         EndProperty
      EndProperty
      BeginProperty Column04 
         DataField       =   "硝材"
         Caption         =   "  硝 材"
         BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} 
            Type            =   0
            Format          =   ""
            HaveTrueFalseNull=   0
            FirstDayOfWeek  =   0
            FirstWeekOfYear =   0
            LCID            =   2052
            SubFormatType   =   0
         EndProperty
      EndProperty
      BeginProperty Column05 
         DataField       =   "料质总数"
         Caption         =   "  料质总数"
         BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} 
            Type            =   0
            Format          =   ""
            HaveTrueFalseNull=   0
            FirstDayOfWeek  =   0
            FirstWeekOfYear =   0
            LCID            =   2052
            SubFormatType   =   0
         EndProperty
      EndProperty
      BeginProperty Column06 
         DataField       =   "废品总数"
         Caption         =   "  废品总数"
         BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} 
            Type            =   0
            Format          =   ""
            HaveTrueFalseNull=   0
            FirstDayOfWeek  =   0
            FirstWeekOfYear =   0
            LCID            =   2052
            SubFormatType   =   0
         EndProperty
      EndProperty
      BeginProperty Column07 
         DataField       =   "料废总数"
         Caption         =   "  料废总数"
         BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} 
            Type            =   0
            Format          =   ""
            HaveTrueFalseNull=   0
            FirstDayOfWeek  =   0
            FirstWeekOfYear =   0
            LCID            =   2052
            SubFormatType   =   0
         EndProperty
      EndProperty
      SplitCount      =   1
      BeginProperty Split0 
         BeginProperty Column00 
            ColumnWidth     =   1035.213
         EndProperty
         BeginProperty Column01 
            ColumnWidth     =   1200.189
         EndProperty
         BeginProperty Column02 
         EndProperty
         BeginProperty Column03 
            ColumnWidth     =   2489.953
         EndProperty
         BeginProperty Column04 
            ColumnWidth     =   854.929
         EndProperty
         BeginProperty Column05 
            ColumnWidth     =   1140.095
         EndProperty
         BeginProperty Column06 
            ColumnWidth     =   1200.189
         EndProperty
         BeginProperty Column07 
            ColumnWidth     =   1200.189
         EndProperty
      EndProperty
   End
   Begin vsElasticLightLibCtl.vsElasticLight vsElasticLight1 
      Left            =   9720
      OleObjectBlob   =   "Jc_LF.frx":1EDB
      Top             =   8040
   End
End
Attribute VB_Name = "Jc_LF"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim TxtSql As String
Dim Rstmp As ADODB.Recordset


'纪录导出到Execl
Public Sub RecordsetToExcel(Rs As ADODB.Recordset, excel_sheet As Excel.Worksheet)
    Dim i As Long, j As Long
    Dim excel_range As Excel.Range
    Dim col_count As Long

    If Rs.RecordCount = 0 Then
        Exit Sub
    End If

    Set excel_range = excel_sheet.Cells
    col_count = Rs.Fields.Count
                
    For i = 0 To col_count - 1
        excel_sheet.Cells(1, i + 1).Value = Rs.Fields(i).Name
    Next
    excel_sheet.Range(excel_sheet.Cells(1, 1), _
                      excel_sheet.Cells(1, col_count)).Font.Bold = True
        
    excel_sheet.Range("A2").CopyFromRecordset Rs
            
End Sub

Private Sub Command1_Click()
    Dim CmdExe As ADODB.Command
    Set CmdExe = New ADODB.Command
    CmdExe.ActiveConnection = Cw_DataEnvi.DataConnect
        CmdExe.CommandText = "execute Kg_料废结存 '" & DtpKsrq.Value & "','" & DtpJsrq.Value & "'"
        CmdExe.Execute
    CmdExe.CommandTimeout = 0
    If Check1.Value = 0 Then
            TxtSql = "select Bs_产品图号.图号,Bs_产品图号.品名,Bs_产品图号.规格,Bs_产品图号.硝材,sum(料质总数) as 料质总数,sum(废品总数) as 废品总数,sum(料质总数)+sum(废品总数) as 料废总数 from Kg_仓库料废总表,Bs_产品图号 where  Kg_仓库料废总表.图号=Bs_产品图号.图号 group by Bs_产品图号.图号,Bs_产品图号.品名,Bs_产品图号.规格,Bs_产品图号.硝材 order by Bs_产品图号.图号"
        Set Rstmp = New ADODB.Recordset
        Rstmp.Open TxtSql, Cw_DataEnvi.DataConnect, adOpenStatic, adLockReadOnly
          Set DataGrid1.DataSource = Rstmp
        Else
            TxtSql = "select 部门名称,Bs_产品图号.图号,Bs_产品图号.品名,Bs_产品图号.规格,Bs_产品图号.硝材,sum(料质总数) as 料质总数,sum(废品总数) as 废品总数,sum(料质总数)+sum(废品总数) as 料废总数 from Kg_仓库料废总表,Bs_产品图号 where  Kg_仓库料废总表.图号=Bs_产品图号.图号 group by 部门名称,Bs_产品图号.图号,Bs_产品图号.品名,Bs_产品图号.规格,Bs_产品图号.硝材 order by Bs_产品图号.图号,部门名称"
        Set Rstmp = New ADODB.Recordset
        Rstmp.Open TxtSql, Cw_DataEnvi.DataConnect, adOpenStatic, adLockReadOnly
          Set DataGrid1.DataSource = Rstmp
    End If
End Sub

Private Sub Command2_Click()
On Error GoTo errs
    Dim Rs As ADODB.Recordset
    Dim ExcelApp As Excel.Application
    Dim ExcelBook As Excel.Workbook
    Dim ExcelSheet As Excel.Worksheet

    Set ExcelApp = New Excel.Application
    ExcelApp.Visible = False
    Set ExcelBook = ExcelApp.Workbooks.Add
    Set ExcelSheet = ExcelBook.Worksheets.Item(1)
    
    Set Rs = New ADODB.Recordset
     
    Rs.Open TxtSql, Cw_DataEnvi.DataConnect, , adLockPessimistic, adCmdText
    RecordsetToExcel Rs, ExcelSheet
    If OutTxt.Text = "" Then
      MsgBox "请指定输出文件位置和文件名！", 16, "严重错误"
      Exit Sub
    End If
    On Error GoTo ErrSave
     ExcelBook.Close True, OutTxt.Text
     MsgBox "输出成功！文件位于" & OutTxt.Text
     Rs.Close

Exit Sub
errs:
    MsgBox "Select 语句错误！", 16, "严重错误"
    ExcelBook.Close False
     Exit Sub
ErrSave:
    MsgBox "输出错误！", 16, "严重错误"
End Sub

Private Sub Command3_Click()
    Cdlg.DialogTitle = "另存为Excel文件："
    Cdlg.Filter = "Excel文件|*.Xls|所有文件|*.*"
    Cdlg.ShowSave
    If Cdlg.FileName = "" Then Exit Sub
    OutTxt.Text = Cdlg.FileName
End Sub

Private Sub Command4_Click()

End Sub

Private Sub Form_Load()
    DtpKsrq.Value = Gsdate
    DtpJsrq.Value = Gsdate
End Sub