On Error GoTo err
'数据导出到EXCEL
Dim Total As Range
Dim Col1 As Integer
Dim RT As Integer
Dim FileSO
Set FileSO = CreateObject("Scripting.FileSystemObject")
Dim File1
CommonDialog1.InitDir = App.Path
CommonDialog1.Filter = "*.xls(Excel文件格式)|*.xls|*.*(所有文件)|*.*"
CommonDialog1.ShowSaveIf CommonDialog1.FileName <> "" Then
On Error Resume Next
Set File1 = FileSO.GetFile(CommonDialog1.FileName)
'判段是否文件名重名
If File1 <> "" Then
RT = MsgBox("文件已存在或未保存,是否保存?", vbOKCancel, "保存文件")
If RT = 1 Then
'File1.Close
File1.Delete
CrystalReport1.Destination = crptToFile '保存到文件
CrystalReport1.PrintFileName = CommonDialog1.FileName '文件名
CrystalReport1.PrintFileType = crptDIF
CrystalReport1.Action = 1 '保存
Else
Command7.Enabled = False
Exit Sub
End If
Else
CrystalReport1.Destination = crptToFile
CrystalReport1.PrintFileName = CommonDialog1.FileName
CrystalReport1.PrintFileType = crptDIF
CrystalReport1.Action = 1
End If
'对表头进行修改Set VBExcel = CreateObject("excel.application")'根据操作人员是否需要见到Excel此处可设为TRUE 或FALSE
'VBExcel.Visible = TrueSet xlbook = VBExcel.Workbooks.Open(CrystalReport1.PrintFileName)
'打开已存在的Excel模板文件 (包括路径)Set xlsheet = xlbook.Worksheets(1)xlsheet.ActivateIf Combo1.Text = "故障分析报表" Then
If Check1.Value = 0 Then
xh = 1
Row = 9 '从第9行开始是数据,1-8 行为表头
SumRow = Row 'SumRow用来存放合计的起始行
'xlsheet.Cells(2, 11).ColumnWidth = LenB(xlsheet.Cells(2, 11).Value) * 2
'xlsheet.Cells(2, 11).HorizontalAlignment = xlLeft
'xlsheet.Cells(2, 11).ShrinkToFit = True
For Col1 = 1 To 16
xlsheet.Cells(4, Col1).Value = ""
xlsheet.Cells(5, Col1).Value = ""
xlsheet.Cells(6, Col1).Value = ""
xlsheet.Cells(7, Col1).Value = ""
xlsheet.Cells(8, Col1).Value = ""
Next xlsheet.Cells(5, 2).Value = "线路状态"
xlsheet.Cells(5, 4).Value = "读卡器"
xlsheet.Cells(5, 6).Value = "流水打印机"
xlsheet.Cells(5, 8).Value = "凭条打印机"
xlsheet.Cells(5, 10).Value = "加密模块"
xlsheet.Cells(5, 12).Value = "出钞模块"
xlsheet.Cells(5, 14).Value = "钞箱"
xlsheet.Cells(5, 16).Value = "未知时间"
xlsheet.Cells(6, 1).Value = "银行"
xlsheet.Cells(8, 1).Value = "ATM号"
xlsheet.Cells(7, 2).Value = "时间"
xlsheet.Cells(8, 2).Value = "(小时)"
xlsheet.Cells(7, 3).Value = "故障"
xlsheet.Cells(8, 3).Value = "次数"
xlsheet.Cells(7, 4).Value = "时间"
xlsheet.Cells(8, 4).Value = "(小时)"
xlsheet.Cells(7, 5).Value = "故障"
xlsheet.Cells(8, 5).Value = "次数"
xlsheet.Cells(7, 6).Value = "时间"
xlsheet.Cells(8, 6).Value = "(小时)"
xlsheet.Cells(7, 7).Value = "故障"
xlsheet.Cells(8, 7).Value = "次数"
xlsheet.Cells(7, 8).Value = "时间"
xlsheet.Cells(8, 8).Value = "(小时)"
xlsheet.Cells(7, 9).Value = "故障"
xlsheet.Cells(8, 9).Value = "次数"
xlsheet.Cells(7, 10).Value = "时间"
xlsheet.Cells(8, 10).Value = "(小时)"
xlsheet.Cells(7, 11).Value = "故障"
xlsheet.Cells(8, 11).Value = "次数"
xlsheet.Cells(7, 12).Value = "时间"
xlsheet.Cells(8, 12).Value = "(小时)"
xlsheet.Cells(7, 13).Value = "故障"
xlsheet.Cells(8, 13).Value = "次数"
xlsheet.Cells(7, 14).Value = "时间"
xlsheet.Cells(8, 14).Value = "(小时)"
xlsheet.Cells(7, 15).Value = "故障"
xlsheet.Cells(8, 15).Value = "次数"
xlsheet.Cells(6, 16).Value = "时间"
xlsheet.Cells(7, 16).Value = "(小时)"
Else
xh = 1
Row = 9 '从第9行开始是数据,1-8 行为表头
SumRow = Row 'SumRow用来存放合计的起始行
For Col1 = 1 To 16
xlsheet.Cells(8, Col1).Value = ""
xlsheet.Cells(9, Col1).Value = ""
xlsheet.Cells(10, Col1).Value = ""
xlsheet.Cells(11, Col1).Value = ""
Next
xlsheet.Cells(8, 2).Value = "线路状态"
xlsheet.Cells(8, 4).Value = "读卡器"
xlsheet.Cells(8, 6).Value = "流水打印机"
xlsheet.Cells(8, 8).Value = "凭条打印机"
xlsheet.Cells(8, 10).Value = "加密模块"
xlsheet.Cells(8, 12).Value = "出钞模块"
xlsheet.Cells(8, 14).Value = "钞箱"
xlsheet.Cells(8, 16).Value = "未知时间"
xlsheet.Cells(9, 1).Value = "银行"
xlsheet.Cells(11, 1).Value = "ATM号"
xlsheet.Cells(10, 2).Value = "时间"
xlsheet.Cells(11, 2).Value = "(小时)"
xlsheet.Cells(10, 3).Value = "故障"
xlsheet.Cells(11, 3).Value = "次数"
xlsheet.Cells(10, 4).Value = "时间"
xlsheet.Cells(11, 4).Value = "(小时)"
xlsheet.Cells(10, 5).Value = "故障"
xlsheet.Cells(11, 5).Value = "次数"
xlsheet.Cells(10, 6).Value = "时间"
xlsheet.Cells(11, 6).Value = "(小时)"
xlsheet.Cells(10, 7).Value = "故障"
xlsheet.Cells(11, 7).Value = "次数"
xlsheet.Cells(10, 8).Value = "时间"
xlsheet.Cells(11, 8).Value = "(小时)"
xlsheet.Cells(10, 9).Value = "故障"
xlsheet.Cells(11, 9).Value = "次数"
xlsheet.Cells(10, 10).Value = "时间"
xlsheet.Cells(11, 10).Value = "(小时)"
xlsheet.Cells(10, 11).Value = "故障"
xlsheet.Cells(11, 11).Value = "次数"
xlsheet.Cells(10, 12).Value = "时间"
xlsheet.Cells(11, 12).Value = "(小时)"
xlsheet.Cells(10, 13).Value = "故障"
xlsheet.Cells(11, 13).Value = "次数"
xlsheet.Cells(10, 14).Value = "时间"
xlsheet.Cells(11, 14).Value = "(小时)"
xlsheet.Cells(10, 15).Value = "故障"
xlsheet.Cells(11, 15).Value = "次数"
xlsheet.Cells(9, 16).Value = "时间"
xlsheet.Cells(10, 16).Value = "(小时)"
End If
End If
xlbook.Close False
xlbook.Save
Set xlsheet = Nothing
Set VBExcel = NothingEnd If
'数据导出到EXCEL
Dim Total As Range
Dim Col1 As Integer
Dim RT As Integer
Dim FileSO
Set FileSO = CreateObject("Scripting.FileSystemObject")
Dim File1
CommonDialog1.InitDir = App.Path
CommonDialog1.Filter = "*.xls(Excel文件格式)|*.xls|*.*(所有文件)|*.*"
CommonDialog1.ShowSaveIf CommonDialog1.FileName <> "" Then
On Error Resume Next
Set File1 = FileSO.GetFile(CommonDialog1.FileName)
'判段是否文件名重名
If File1 <> "" Then
RT = MsgBox("文件已存在或未保存,是否保存?", vbOKCancel, "保存文件")
If RT = 1 Then
'File1.Close
File1.Delete
CrystalReport1.Destination = crptToFile '保存到文件
CrystalReport1.PrintFileName = CommonDialog1.FileName '文件名
CrystalReport1.PrintFileType = crptDIF
CrystalReport1.Action = 1 '保存
Else
Command7.Enabled = False
Exit Sub
End If
Else
CrystalReport1.Destination = crptToFile
CrystalReport1.PrintFileName = CommonDialog1.FileName
CrystalReport1.PrintFileType = crptDIF
CrystalReport1.Action = 1
End If
'对表头进行修改Set VBExcel = CreateObject("excel.application")'根据操作人员是否需要见到Excel此处可设为TRUE 或FALSE
'VBExcel.Visible = TrueSet xlbook = VBExcel.Workbooks.Open(CrystalReport1.PrintFileName)
'打开已存在的Excel模板文件 (包括路径)Set xlsheet = xlbook.Worksheets(1)xlsheet.ActivateIf Combo1.Text = "故障分析报表" Then
If Check1.Value = 0 Then
xh = 1
Row = 9 '从第9行开始是数据,1-8 行为表头
SumRow = Row 'SumRow用来存放合计的起始行
'xlsheet.Cells(2, 11).ColumnWidth = LenB(xlsheet.Cells(2, 11).Value) * 2
'xlsheet.Cells(2, 11).HorizontalAlignment = xlLeft
'xlsheet.Cells(2, 11).ShrinkToFit = True
For Col1 = 1 To 16
xlsheet.Cells(4, Col1).Value = ""
xlsheet.Cells(5, Col1).Value = ""
xlsheet.Cells(6, Col1).Value = ""
xlsheet.Cells(7, Col1).Value = ""
xlsheet.Cells(8, Col1).Value = ""
Next xlsheet.Cells(5, 2).Value = "线路状态"
xlsheet.Cells(5, 4).Value = "读卡器"
xlsheet.Cells(5, 6).Value = "流水打印机"
xlsheet.Cells(5, 8).Value = "凭条打印机"
xlsheet.Cells(5, 10).Value = "加密模块"
xlsheet.Cells(5, 12).Value = "出钞模块"
xlsheet.Cells(5, 14).Value = "钞箱"
xlsheet.Cells(5, 16).Value = "未知时间"
xlsheet.Cells(6, 1).Value = "银行"
xlsheet.Cells(8, 1).Value = "ATM号"
xlsheet.Cells(7, 2).Value = "时间"
xlsheet.Cells(8, 2).Value = "(小时)"
xlsheet.Cells(7, 3).Value = "故障"
xlsheet.Cells(8, 3).Value = "次数"
xlsheet.Cells(7, 4).Value = "时间"
xlsheet.Cells(8, 4).Value = "(小时)"
xlsheet.Cells(7, 5).Value = "故障"
xlsheet.Cells(8, 5).Value = "次数"
xlsheet.Cells(7, 6).Value = "时间"
xlsheet.Cells(8, 6).Value = "(小时)"
xlsheet.Cells(7, 7).Value = "故障"
xlsheet.Cells(8, 7).Value = "次数"
xlsheet.Cells(7, 8).Value = "时间"
xlsheet.Cells(8, 8).Value = "(小时)"
xlsheet.Cells(7, 9).Value = "故障"
xlsheet.Cells(8, 9).Value = "次数"
xlsheet.Cells(7, 10).Value = "时间"
xlsheet.Cells(8, 10).Value = "(小时)"
xlsheet.Cells(7, 11).Value = "故障"
xlsheet.Cells(8, 11).Value = "次数"
xlsheet.Cells(7, 12).Value = "时间"
xlsheet.Cells(8, 12).Value = "(小时)"
xlsheet.Cells(7, 13).Value = "故障"
xlsheet.Cells(8, 13).Value = "次数"
xlsheet.Cells(7, 14).Value = "时间"
xlsheet.Cells(8, 14).Value = "(小时)"
xlsheet.Cells(7, 15).Value = "故障"
xlsheet.Cells(8, 15).Value = "次数"
xlsheet.Cells(6, 16).Value = "时间"
xlsheet.Cells(7, 16).Value = "(小时)"
Else
xh = 1
Row = 9 '从第9行开始是数据,1-8 行为表头
SumRow = Row 'SumRow用来存放合计的起始行
For Col1 = 1 To 16
xlsheet.Cells(8, Col1).Value = ""
xlsheet.Cells(9, Col1).Value = ""
xlsheet.Cells(10, Col1).Value = ""
xlsheet.Cells(11, Col1).Value = ""
Next
xlsheet.Cells(8, 2).Value = "线路状态"
xlsheet.Cells(8, 4).Value = "读卡器"
xlsheet.Cells(8, 6).Value = "流水打印机"
xlsheet.Cells(8, 8).Value = "凭条打印机"
xlsheet.Cells(8, 10).Value = "加密模块"
xlsheet.Cells(8, 12).Value = "出钞模块"
xlsheet.Cells(8, 14).Value = "钞箱"
xlsheet.Cells(8, 16).Value = "未知时间"
xlsheet.Cells(9, 1).Value = "银行"
xlsheet.Cells(11, 1).Value = "ATM号"
xlsheet.Cells(10, 2).Value = "时间"
xlsheet.Cells(11, 2).Value = "(小时)"
xlsheet.Cells(10, 3).Value = "故障"
xlsheet.Cells(11, 3).Value = "次数"
xlsheet.Cells(10, 4).Value = "时间"
xlsheet.Cells(11, 4).Value = "(小时)"
xlsheet.Cells(10, 5).Value = "故障"
xlsheet.Cells(11, 5).Value = "次数"
xlsheet.Cells(10, 6).Value = "时间"
xlsheet.Cells(11, 6).Value = "(小时)"
xlsheet.Cells(10, 7).Value = "故障"
xlsheet.Cells(11, 7).Value = "次数"
xlsheet.Cells(10, 8).Value = "时间"
xlsheet.Cells(11, 8).Value = "(小时)"
xlsheet.Cells(10, 9).Value = "故障"
xlsheet.Cells(11, 9).Value = "次数"
xlsheet.Cells(10, 10).Value = "时间"
xlsheet.Cells(11, 10).Value = "(小时)"
xlsheet.Cells(10, 11).Value = "故障"
xlsheet.Cells(11, 11).Value = "次数"
xlsheet.Cells(10, 12).Value = "时间"
xlsheet.Cells(11, 12).Value = "(小时)"
xlsheet.Cells(10, 13).Value = "故障"
xlsheet.Cells(11, 13).Value = "次数"
xlsheet.Cells(10, 14).Value = "时间"
xlsheet.Cells(11, 14).Value = "(小时)"
xlsheet.Cells(10, 15).Value = "故障"
xlsheet.Cells(11, 15).Value = "次数"
xlsheet.Cells(9, 16).Value = "时间"
xlsheet.Cells(10, 16).Value = "(小时)"
End If
End If
xlbook.Close False
xlbook.Save
Set xlsheet = Nothing
Set VBExcel = NothingEnd If
但我还是觉得要在一个sheet是实现比较好,就是每导一页是都会有一个页头,因为分组的信息在页头是显示。还有更好的方法吗,我一定给分。对第一问有什么好的解决方法吗?能留下QQ或MSN吗,Mail也行