Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.Add Set xlSheet = xlBook.Worksheets(1)
On Error Resume Next Set oExcel = GetObject(, "Excel.Application") If Err.Number <> 0 Then Set oExcel = CreateObject("Excel.Application") End If Err.Clear oExcel.Visible = False oExcel.ScreenUpdating = False Set oBook = Application.Workbooks.Open(App.Path + "\WeekReportModel.xls") Set oSheet = oBook.Worksheets(1)
给你一个类模块,我自己写的,使用时必须针对自己的需求改改Option ExplicitPublic m_xlApp As Excel.Application Public m_xlBook As Excel.Workbook Public m_xlSheet As Excel.WorksheetPublic m_strFileName As StringPrivate Sub Class_Initialize() m_strFileName = "Books1.xls" Set m_xlApp = New Excel.Application Set m_xlBook = m_xlApp.Workbooks.Add Set m_xlSheet = m_xlBook.Sheets(1)
m_xlApp.Visible = False End SubPrivate Sub Class_Terminate()
m_xlBook.Close False m_xlApp.quit
Set m_xlApp = Nothing Set m_xlBook = Nothing Set m_xlSheet = Nothing End SubPublic Function OpenSaveAsFileName(strFileNameDefault As String) As Variant On Error Resume Next
Dim strFileName As Variant strFileName = m_xlApp.GetSaveAsFilename(strFileNameDefault, "Microsoft Excel 工作薄(*.xls),*.xls") If strFileName <> False Then Me.m_strFileName = CStr(strFileName) End If OpenSaveAsFileName = strFileName
If Err Then Err.Clear End If End FunctionPublic Function OpenOpenFileName() As Variant On Error Resume Next
Dim strFileName As Variant strFileName = m_xlApp.GetOpenFilename("Microsoft Excel 工作薄(*.xls),*.xls") OpenOpenFileName = strFileName
If Err Then Err.Clear End If End FunctionPublic Sub SaveCurrExportedExcel() On Error Resume Next
m_xlBook.SaveAs m_strFileName
If Err Then Err.Clear End If End SubPublic Function AdodcExport(ByRef ctrADO As Adodc, ByVal nSheetStartRow As Long, ByVal nSheetStartCol As Long) As Long '''------------------------------------------------------ ''' For Test Only: Dim nRow As Long Dim nCol As Long
On Error Resume Next
ctrADO.Recordset.MoveFirst
If Err Then Err.Clear AdodcExport = 0 Exit Function End If
nRow = nSheetStartRow nCol = 0
nRow = nRow + 1 For nCol = 1 To ctrADO.Recordset.Fields.Count Step 1 m_xlSheet.Cells(nRow, nCol + nSheetStartCol).value = Trim(ctrADO.Recordset.Fields(nCol - 1).Name) Next nCol
Do While (ctrADO.Recordset.EOF = False And ctrADO.Recordset.BOF = False) nRow = nRow + 1 For nCol = 1 To ctrADO.Recordset.Fields.Count Step 1 m_xlSheet.Cells(nRow, nCol + nSheetStartCol).value = Trim(ctrADO.Recordset.Fields(nCol - 1).value) Next nCol ctrADO.Recordset.MoveNext If Err Then Err.Clear End If Loop
AdodcExport = nRow End FunctionPublic Function ResExport(ByRef rsADO As ADODB.Recordset, ByVal nSheetStartRow As Long, ByVal nSheetStartCol As Long) As Long '''------------------------------------------------------ ''' For Test Only: Dim nRow As Long Dim nCol As Long
On Error Resume Next
If rsADO.EOF Or rsADO.BOF Then Exit Function End If
rsADO.MoveFirst If Err Then Err.Clear ResExport = 0 Exit Function End If
nRow = nSheetStartRow + 1 nCol = 0
Do While (rsADO.EOF = False And rsADO.BOF = False)
nRow = nRow + 1 For nCol = 1 To rsADO.Fields.Count Step 1 m_xlSheet.Cells(nRow, nCol + nSheetStartCol).value = Trim(rsADO.Fields(nCol - 1).value) Next nCol
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
Set oExcel = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set oExcel = CreateObject("Excel.Application")
End If
Err.Clear
oExcel.Visible = False
oExcel.ScreenUpdating = False
Set oBook = Application.Workbooks.Open(App.Path + "\WeekReportModel.xls")
Set oSheet = oBook.Worksheets(1)
Public m_xlBook As Excel.Workbook
Public m_xlSheet As Excel.WorksheetPublic m_strFileName As StringPrivate Sub Class_Initialize()
m_strFileName = "Books1.xls"
Set m_xlApp = New Excel.Application
Set m_xlBook = m_xlApp.Workbooks.Add
Set m_xlSheet = m_xlBook.Sheets(1)
m_xlApp.Visible = False
End SubPrivate Sub Class_Terminate()
m_xlBook.Close False
m_xlApp.quit
Set m_xlApp = Nothing
Set m_xlBook = Nothing
Set m_xlSheet = Nothing
End SubPublic Function OpenSaveAsFileName(strFileNameDefault As String) As Variant
On Error Resume Next
Dim strFileName As Variant
strFileName = m_xlApp.GetSaveAsFilename(strFileNameDefault, "Microsoft Excel 工作薄(*.xls),*.xls")
If strFileName <> False Then
Me.m_strFileName = CStr(strFileName)
End If
OpenSaveAsFileName = strFileName
If Err Then
Err.Clear
End If
End FunctionPublic Function OpenOpenFileName() As Variant
On Error Resume Next
Dim strFileName As Variant
strFileName = m_xlApp.GetOpenFilename("Microsoft Excel 工作薄(*.xls),*.xls")
OpenOpenFileName = strFileName
If Err Then
Err.Clear
End If
End FunctionPublic Sub SaveCurrExportedExcel()
On Error Resume Next
m_xlBook.SaveAs m_strFileName
If Err Then
Err.Clear
End If
End SubPublic Function AdodcExport(ByRef ctrADO As Adodc, ByVal nSheetStartRow As Long, ByVal nSheetStartCol As Long) As Long
'''------------------------------------------------------
''' For Test Only:
Dim nRow As Long
Dim nCol As Long
On Error Resume Next
ctrADO.Recordset.MoveFirst
If Err Then
Err.Clear
AdodcExport = 0
Exit Function
End If
nRow = nSheetStartRow
nCol = 0
nRow = nRow + 1
For nCol = 1 To ctrADO.Recordset.Fields.Count Step 1
m_xlSheet.Cells(nRow, nCol + nSheetStartCol).value = Trim(ctrADO.Recordset.Fields(nCol - 1).Name)
Next nCol
Do While (ctrADO.Recordset.EOF = False And ctrADO.Recordset.BOF = False)
nRow = nRow + 1
For nCol = 1 To ctrADO.Recordset.Fields.Count Step 1
m_xlSheet.Cells(nRow, nCol + nSheetStartCol).value = Trim(ctrADO.Recordset.Fields(nCol - 1).value)
Next nCol
ctrADO.Recordset.MoveNext
If Err Then
Err.Clear
End If
Loop
AdodcExport = nRow
End FunctionPublic Function ResExport(ByRef rsADO As ADODB.Recordset, ByVal nSheetStartRow As Long, ByVal nSheetStartCol As Long) As Long
'''------------------------------------------------------
''' For Test Only:
Dim nRow As Long
Dim nCol As Long
On Error Resume Next
If rsADO.EOF Or rsADO.BOF Then
Exit Function
End If
rsADO.MoveFirst
If Err Then
Err.Clear
ResExport = 0
Exit Function
End If
nRow = nSheetStartRow + 1
nCol = 0
Do While (rsADO.EOF = False And rsADO.BOF = False)
nRow = nRow + 1
For nCol = 1 To rsADO.Fields.Count Step 1
m_xlSheet.Cells(nRow, nCol + nSheetStartCol).value = Trim(rsADO.Fields(nCol - 1).value)
Next nCol
rsADO.MoveNext
If Err Then
Err.Clear
End If
Loop
m_xlSheet.Cells(1, 1).value = nRow & ";" & rsADO.Fields.Count
ResExport = nRow
End Function