jc_gx.frm

适合于中小型企业管理

      _ExtentX        =   847
      _ExtentY        =   847
      _Version        =   393216
   End
   Begin MSDataGridLib.DataGrid DataGrid1 
      Bindings        =   "Jc_GX.frx":1EC6
      Height          =   7035
      Left            =   120
      TabIndex        =   14
      Top             =   120
      Width           =   11445
      _ExtentX        =   20188
      _ExtentY        =   12409
      _Version        =   393216
      AllowUpdate     =   0   'False
      AllowArrows     =   0   'False
      DefColWidth     =   57
      HeadLines       =   1
      RowHeight       =   15
      TabAction       =   2
      BeginProperty HeadFont {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
         Name            =   "宋体"
         Size            =   9
         Charset         =   134
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
         Name            =   "宋体"
         Size            =   9
         Charset         =   134
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ColumnCount     =   2
      BeginProperty Column00 
         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 Column01 
         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     =   1635.024
         EndProperty
         BeginProperty Column01 
            ColumnWidth     =   1635.024
         EndProperty
      EndProperty
   End
   Begin vsElasticLightLibCtl.vsElasticLight vsElasticLight1 
      Left            =   10440
      OleObjectBlob   =   "Jc_GX.frx":1EDB
      Top             =   7920
   End
End
Attribute VB_Name = "Jc_GX"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim Rstmp As ADODB.Recordset
Dim TxtSql As String

Private Sub Command1_Click()
    Dim CmdExe As ADODB.Command
     Set CmdExe = New ADODB.Command
     CmdExe.ActiveConnection = Cw_DataEnvi.DataConnect
     CmdExe.CommandTimeout = 0
    If Option1.Value = True Then
        If txt01.ListIndex > -1 And Txt04.ListIndex > -1 Then
            CmdExe.CommandText = "execute Kg_仓库工序 '" & txt01.Text & "','" & Txt04.Text & "','" & txt02.Value & "','" & txt03.Value & "'"
            CmdExe.Execute
        Else
            MsgBox "请选择仓库和工序名称!"
            Exit Sub
        End If
        
        TxtSql = "select 仓库名称,工序名称,Bs_产品图号.图号,Bs_产品图号.品名,Bs_产品图号.规格,Bs_产品图号.硝材,sum(领一级) as 领一级,sum(领二级) as 领二级,sum(一级品) as 一级品,sum(二级品) as 二级品,sum(留用数) as 留用数,sum(料质数) as 料质数,sum(废品数) as 废品数 from Kg_仓库工序结存表,Bs_产品图号 where Kg_仓库工序结存表.图号=Bs_产品图号.图号  group by 仓库名称,工序名称,Bs_产品图号.图号,Bs_产品图号.品名,Bs_产品图号.规格,Bs_产品图号.硝材"
        OutTxt.Text = "c:\" & txt01.Text & "_" & Txt04.Text & ".xls"
        Set Rstmp = New ADODB.Recordset
        Rstmp.Open TxtSql, Cw_DataEnvi.DataConnect, adOpenStatic, adLockReadOnly, adCmdText
        Set DataGrid1.DataSource = Rstmp
        Else
        If txt01.ListIndex > -1 And Txt04.ListIndex > -1 Then
            CmdExe.CommandText = "execute Kg_仓库工序DM '" & txt01.Text & "','" & txt02.Value & "','" & txt03.Value & "'"
            CmdExe.Execute
        Else
            MsgBox "请选择仓库和工序名称!"
            Exit Sub
        End If
        TxtSql = "select 仓库名称,Bs_产品图号.图号,Bs_产品图号.品名,Bs_产品图号.规格,Bs_产品图号.硝材,sum(领一级) as 领一级,sum(领二级) as 领二级,sum(一级品) as 一级品,sum(留用数) as 留用数,sum(站二级) as 站二级,sum(站报废) as 站报废,sum(擦二级) as 擦二级,sum(擦报废) as 擦报废 from Kg_仓库工序结存表dm,Bs_产品图号 where Kg_仓库工序结存表dm.图号=Bs_产品图号.图号 group by 仓库名称,Bs_产品图号.图号,Bs_产品图号.品名,Bs_产品图号.规格,Bs_产品图号.硝材"
        OutTxt.Text = "c:\" & txt01.Text & "_" & Txt04.Text & ".xls"
        Set Rstmp = New ADODB.Recordset
        Rstmp.Open TxtSql, Cw_DataEnvi.DataConnect, adOpenStatic, adLockReadOnly, adCmdText
        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 Form_Load()
    Dim Rsbj As ADODB.Recordset
    Set Rsbj = New ADODB.Recordset
    Rsbj.Open "select 仓库名称 from Bs_仓库列表 order by 仓库名称", Cw_DataEnvi.DataConnect, adOpenStatic, adLockReadOnly, adCmdText
    Do While Not Rsbj.EOF
        txt01.AddItem Rsbj!仓库名称
        Rsbj.MoveNext
    Loop
    Rsbj.Close
    Set Rsbj = New ADODB.Recordset
    Rsbj.Open "select 工序名称 from Bs_生产流程 order by ID", Cw_DataEnvi.DataConnect, adOpenStatic, adLockReadOnly, adCmdText
    Do While Not Rsbj.EOF
        Txt04.AddItem Rsbj!工序名称
        Rsbj.MoveNext
    Loop
    Rsbj.Close
    txt01.Text = "请选择"
    Txt04.Text = "请选择"
    txt02.Value = Gsdate
    txt03.Value = Gsdate
End Sub

'纪录导出到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