Public Sub ExportToExcel(ctlMshg As MSHFlexGrid, rstRecord As ADODB.Recordset, _
strReportCaption As String, strReportHead As String, _
strReportTail As String, strFileName As String, blnCount As Boolean)
Dim xlApp As Excel.Application '定义Excel应用对象
Dim xlBook As Excel.Workbook '定义Excel工作簿对象
Dim xlSheet As Excel.Worksheet '定义Excel工作表对象
Dim i As Long, j As Long, k As Long, l As Long
Dim strTemp As String
On Error GoTo ErrHandler
If FileExists(strFileName) Then
Kill strFileName
End If
Set xlApp = New Excel.Application
xlApp.SheetsInNewWorkbook = 1
xlApp.Visible = False
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.ActiveSheet
k = rstRecord.AbsolutePage '保存记录集的页位置
rstRecord.MoveFirst
l = ctlMshg.Cols
strTemp = "A1:" & Chr(65 + l - 1) & "1"
xlSheet.Range(strTemp).MergeCells = True
xlSheet.Range(strTemp).Value = strReportCaption
strTemp = "A2:" & Chr(65 + l - 1) & "2"
xlSheet.Range(strTemp).MergeCells = True
xlSheet.Range(strTemp).Value = strReportHead
For i = 0 To l - 1
xlSheet.Cells(3, i + 1).Value = ctlMshg.TextMatrix(0, i)
Next i
i = 4
With rstRecord
Do Until .EOF
For j = 0 To l - 1
strTemp = rstRecord.Fields(j).Value
xlSheet.Cells(i, j + 1).Value = IIf(blnCount, strTemp, "'" & strTemp)
Next
DoEvents
i = i + 1
.MoveNext
Loop
End With
If blnCount Then
xlSheet.Cells(i, 1).Value = "合计"
For j = 1 To l - 1
strTemp = "=SUM(" & Chr(65 + j) & 4 & ":" & Chr(65 + j) & (i - 1) & ")"
xlSheet.Cells(i, j + 1).Value = strTemp
Next j
i = i + 1
End If
strTemp = "A" & i & ":" & Chr(65 + l - 1) & i
xlSheet.Range(strTemp).MergeCells = True
xlSheet.Range(strTemp).Value = strReportTail
xlSheet.SaveAs strFileName '保存导出的Excel文件
xlApp.DisplayAlerts = False
xlBook.Close
xlApp.Quit '退出EXCEL
xlApp.DisplayAlerts = True
Set xlApp = Nothing
If k > 0 Then
rstRecord.AbsolutePage = k '恢复记录集的页位置
End If
Exit Sub
ErrHandler:
If k > 0 Then
rstRecord.AbsolutePage = k
End If
MsgBox "错误:" & Err.Description & "!", vbExclamation, "系统提示"
End Sub
strReportCaption As String, strReportHead As String, _
strReportTail As String, strFileName As String, blnCount As Boolean)
Dim xlApp As Excel.Application '定义Excel应用对象
Dim xlBook As Excel.Workbook '定义Excel工作簿对象
Dim xlSheet As Excel.Worksheet '定义Excel工作表对象
Dim i As Long, j As Long, k As Long, l As Long
Dim strTemp As String
On Error GoTo ErrHandler
If FileExists(strFileName) Then
Kill strFileName
End If
Set xlApp = New Excel.Application
xlApp.SheetsInNewWorkbook = 1
xlApp.Visible = False
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.ActiveSheet
k = rstRecord.AbsolutePage '保存记录集的页位置
rstRecord.MoveFirst
l = ctlMshg.Cols
strTemp = "A1:" & Chr(65 + l - 1) & "1"
xlSheet.Range(strTemp).MergeCells = True
xlSheet.Range(strTemp).Value = strReportCaption
strTemp = "A2:" & Chr(65 + l - 1) & "2"
xlSheet.Range(strTemp).MergeCells = True
xlSheet.Range(strTemp).Value = strReportHead
For i = 0 To l - 1
xlSheet.Cells(3, i + 1).Value = ctlMshg.TextMatrix(0, i)
Next i
i = 4
With rstRecord
Do Until .EOF
For j = 0 To l - 1
strTemp = rstRecord.Fields(j).Value
xlSheet.Cells(i, j + 1).Value = IIf(blnCount, strTemp, "'" & strTemp)
Next
DoEvents
i = i + 1
.MoveNext
Loop
End With
If blnCount Then
xlSheet.Cells(i, 1).Value = "合计"
For j = 1 To l - 1
strTemp = "=SUM(" & Chr(65 + j) & 4 & ":" & Chr(65 + j) & (i - 1) & ")"
xlSheet.Cells(i, j + 1).Value = strTemp
Next j
i = i + 1
End If
strTemp = "A" & i & ":" & Chr(65 + l - 1) & i
xlSheet.Range(strTemp).MergeCells = True
xlSheet.Range(strTemp).Value = strReportTail
xlSheet.SaveAs strFileName '保存导出的Excel文件
xlApp.DisplayAlerts = False
xlBook.Close
xlApp.Quit '退出EXCEL
xlApp.DisplayAlerts = True
Set xlApp = Nothing
If k > 0 Then
rstRecord.AbsolutePage = k '恢复记录集的页位置
End If
Exit Sub
ErrHandler:
If k > 0 Then
rstRecord.AbsolutePage = k
End If
MsgBox "错误:" & Err.Description & "!", vbExclamation, "系统提示"
End Sub
解决方案 »
- 求助,关于MDi的缩放问题,谢谢
- 关于listview的keydown事件
- 在几台电脑上安装成功并正常使用的程序,现在出了新的安装问题。请高手进来看看。
- 帮我做几个vb的题吧!!!
- 大家给看一段代码,数据集导出到mdb(参加讨论就给分)
- 用VB如何实现条形码的打印?
- 那位能帮我 vb与word 的问题
- vb,水晶8.0 用ODBC做数据源,做好的报表安装到其他的计算机上,安装路经改变了,就出ODBC PATH错误.详细情进
- 急急急!!各位帮忙啊!100分!将两个数据表的内容纳入到一个datagrid中去????
- 我已经没分送了,为什么还没人问答我的问题呀!!是不是都太差劲了
- 报表问题?急
- 怎么查询没有记录呀(在线等待......)
Private Sub s_OutToExcel()
Screen.MousePointer = vbHourglass
Dim oXl As Excel.Application
Dim oWb As Workbook
Dim oWs As Excel.Worksheet
Dim iA, iB, iC, iD
Dim bExcelRunning 'Excel是否已运行
Dim flg As MSFlexGrid
Dim sStr
On Error GoTo Morn
bExcelRunning = True '同首先用的GetObject一致:假设Excel已运行
Set oXl = GetObject("", "Excel.Application")
Set oWb = oXl.Workbooks.Add
Set oWs = oWb.Worksheets(1)
Set flg = mOform.flg
With oWs
For iB = 1 To flg.Cols
.Cells(1, iB).Value = flg.TextMatrix(0, iB - 1)
Next
For iC = 1 To flg.Rows - 1
For iB = 1 To flg.Cols
sStr = Trim(flg.TextMatrix(iC, iB - 1))
If IsNumeric(sStr) Then
.Cells(iC + 1, iB).Value = sStr
Else
'截取尾部的数字
If IsNumeric(Right(sStr, 1)) Then sStr = Left(sStr, Len(sStr) - 1)
If IsNumeric(Right(sStr, 1)) Then sStr = Left(sStr, Len(sStr) - 1)
.Cells(iC + 1, iB).Value = sStr
End If
Next
Next
End With
oWs.Parent.Names.Add "CostRange", "=" & "A1:B39"
sStr = App.Path & "\" & mOform.Caption & ".xls"
oWs.SaveAs sStr
Screen.MousePointer = vbDefault
If MsgBox("已将数据输出到Excel文件中! 现在打开该文件?", vbQuestion + vbYesNo, "已完成") = vbNo Then
oXl.Quit
Else
oXl.Visible = True
End If
Set oXl = Nothing
Set oWs = Nothing
Set oWb = NothingExit Sub
Morn:
Select Case Err.number
Case 429
Set oXl = GetObject("", "Excel.Application")
bExcelRunning = False
Resume Next
Case 1004
Screen.MousePointer = 0
iA = MsgBox("发生了错误" & Err.number & ": " & Err.Description, vbExclamation + vbAbortRetryIgnore + vbDefaultButton3, "错误")
If iA = vbAbort Then Exit Sub
If iA = vbRetry Then
Resume
Else
Resume Next
End If
Case Else
MornSubs.sub_ErrCenter False
End Select
End Sub
说我用户定义类型未定义,
请问如何定义?
如:Dim xlApp as object
这样再继续执行就可以了。因为我刚刚用过,如你还有不明白的再问我,
而将来程序运行的环境下也必须安装有excell,