Option ExplicitPrivate Const mdl_def_strFileName = "408_针车部计件工资表.xls"Public Function ExportReport408() As Boolean
Dim intCurrentRow As Integer
Dim connHRMall As ADODB.Connection
Dim rsReportData As ADODB.Recordset
Dim rsDetailData As ADODB.Recordset
Set connHRMall = New ADODB.Connection
Set rsReportData = New ADODB.Recordset
Set rsDetailData = New ADODB.Recordset
Call ConnectToDatabase("HRMall", connHRMall)
Call OpenWorksheet(mdl_def_strFileName)
With app_xlsApplication.Workbooks(mdl_def_strFileName).Worksheets(1)
rsReportData.Open "SELECT PMonth FROM ReportTemp", connHRMall, adOpenForwardOnly, adLockReadOnly, adCmdText
If rsReportData.RecordCount > 0 Then
rsReportData.MoveFirst
If Not IsNull(rsReportData("PMonth").Value) Then
.Range("F1").Value = rsReportData("PMonth").Value
.Range("H1").Value = "针车部计件工资表"
End If
End If
rsReportData.Close
intCurrentRow = 1
rsReportData.Open "SELECT ID,Team FROM ReportTemp GROUP BY ID,Team ORDER BY ID,Team", connHRMall, adOpenForwardOnly, adLockReadOnly, adCmdText If rsReportData.RecordCount >= 0 Then
rsReportData.MoveFirst
Do Until rsReportData.EOF .Range("A" & Trim(Str(intCurrentRow + 1))).Value = "组别:"
.Range("B" & Trim(Str(intCurrentRow + 1))).Value = rsReportData("Team").Value
.Range("A" & Trim(Str(intCurrentRow + 1)) & ":B" & Trim(Str(intCurrentRow + 1))).Font.Size = 12
.Range("A" & Trim(Str(intCurrentRow + 2))).Value = "工号"
.Range("B" & Trim(Str(intCurrentRow + 2))).Value = "姓名"
.Range("C" & Trim(Str(intCurrentRow + 2))).Value = "工作天数"
... .Range("A" & Trim(Str(intCurrentRow + 2)) & ":P" & Trim(Str(intCurrentRow + 2))).Font.Size = 10 intCurrentRow = intCurrentRow + 3
rsDetailData.Open "SELECT EmployeeCode,EmployeeName,WorkDay,... FROM ReportTemp WHERE Team=N'" & rsReportData("Team").Value & "' ORDER BY EmployeeCode", connHRMall, adOpenForwardOnly, adLockReadOnly, adCmdText
.Range("A" & Trim(Str(intCurrentRow))).CopyFromRecordset rsDetailData
.Range("C" & Trim(Str(intCurrentRow)) & ":O" & Trim(Str(intCurrentRow + rsDetailData.RecordCount))).NumberFormat = ("0.00")
.Range("A" & Trim(Str(intCurrentRow)) & ":P" & Trim(Str(intCurrentRow + rsDetailData.RecordCount))).Font.Size = 10
intCurrentRow = intCurrentRow + rsDetailData.RecordCount + 1
rsDetailData.Close
intCurrentRow = intCurrentRow + 1
rsReportData.MoveNext
Loop
End If
rsReportData.Close
Set rsReportData = Nothing
End With
Call SaveWorksheet(mdl_def_strFileName)
connHRMall.Close
Set connHRMall = Nothing
ExportReport408 = True
End Function
问题:我想用
.Range("C" & Trim(Str(intCurrentRow))) = Format(rsDetailData("WorkDay"), "0.00")
代替上面的 .Range("C" & Trim(Str(intCurrentRow)) & ":O" & Trim(Str(intCurrentRow + rsDetailData.RecordCount))).NumberFormat = ("0.00")
这样就可以达到Excel中编辑栏中的数字跟表格的数字位数一至,
.Range("C" & Trim(Str(intCurrentRow))) = Format(rsDetailData("WorkDay"), "0.00")这句只对一个表格有用,但每个字段记录不只一个,请问该如何向下循环``请各位大虾帮帮忙``先谢了``
Dim intCurrentRow As Integer
Dim connHRMall As ADODB.Connection
Dim rsReportData As ADODB.Recordset
Dim rsDetailData As ADODB.Recordset
Set connHRMall = New ADODB.Connection
Set rsReportData = New ADODB.Recordset
Set rsDetailData = New ADODB.Recordset
Call ConnectToDatabase("HRMall", connHRMall)
Call OpenWorksheet(mdl_def_strFileName)
With app_xlsApplication.Workbooks(mdl_def_strFileName).Worksheets(1)
rsReportData.Open "SELECT PMonth FROM ReportTemp", connHRMall, adOpenForwardOnly, adLockReadOnly, adCmdText
If rsReportData.RecordCount > 0 Then
rsReportData.MoveFirst
If Not IsNull(rsReportData("PMonth").Value) Then
.Range("F1").Value = rsReportData("PMonth").Value
.Range("H1").Value = "针车部计件工资表"
End If
End If
rsReportData.Close
intCurrentRow = 1
rsReportData.Open "SELECT ID,Team FROM ReportTemp GROUP BY ID,Team ORDER BY ID,Team", connHRMall, adOpenForwardOnly, adLockReadOnly, adCmdText If rsReportData.RecordCount >= 0 Then
rsReportData.MoveFirst
Do Until rsReportData.EOF .Range("A" & Trim(Str(intCurrentRow + 1))).Value = "组别:"
.Range("B" & Trim(Str(intCurrentRow + 1))).Value = rsReportData("Team").Value
.Range("A" & Trim(Str(intCurrentRow + 1)) & ":B" & Trim(Str(intCurrentRow + 1))).Font.Size = 12
.Range("A" & Trim(Str(intCurrentRow + 2))).Value = "工号"
.Range("B" & Trim(Str(intCurrentRow + 2))).Value = "姓名"
.Range("C" & Trim(Str(intCurrentRow + 2))).Value = "工作天数"
... .Range("A" & Trim(Str(intCurrentRow + 2)) & ":P" & Trim(Str(intCurrentRow + 2))).Font.Size = 10 intCurrentRow = intCurrentRow + 3
rsDetailData.Open "SELECT EmployeeCode,EmployeeName,WorkDay,... FROM ReportTemp WHERE Team=N'" & rsReportData("Team").Value & "' ORDER BY EmployeeCode", connHRMall, adOpenForwardOnly, adLockReadOnly, adCmdText
.Range("A" & Trim(Str(intCurrentRow))).CopyFromRecordset rsDetailData
.Range("C" & Trim(Str(intCurrentRow)) & ":O" & Trim(Str(intCurrentRow + rsDetailData.RecordCount))).NumberFormat = ("0.00")
.Range("A" & Trim(Str(intCurrentRow)) & ":P" & Trim(Str(intCurrentRow + rsDetailData.RecordCount))).Font.Size = 10
intCurrentRow = intCurrentRow + rsDetailData.RecordCount + 1
rsDetailData.Close
intCurrentRow = intCurrentRow + 1
rsReportData.MoveNext
Loop
End If
rsReportData.Close
Set rsReportData = Nothing
End With
Call SaveWorksheet(mdl_def_strFileName)
connHRMall.Close
Set connHRMall = Nothing
ExportReport408 = True
End Function
问题:我想用
.Range("C" & Trim(Str(intCurrentRow))) = Format(rsDetailData("WorkDay"), "0.00")
代替上面的 .Range("C" & Trim(Str(intCurrentRow)) & ":O" & Trim(Str(intCurrentRow + rsDetailData.RecordCount))).NumberFormat = ("0.00")
这样就可以达到Excel中编辑栏中的数字跟表格的数字位数一至,
.Range("C" & Trim(Str(intCurrentRow))) = Format(rsDetailData("WorkDay"), "0.00")这句只对一个表格有用,但每个字段记录不只一个,请问该如何向下循环``请各位大虾帮帮忙``先谢了``
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货