Dim Es As Object Set Es = CreateObject("Excel.Sheet") Es.Application.cells(1, 1) = "AAA" Es.Application.cells(1, 2) = "BBB" Es.Application.cells(2, 1) = "CCC" Es.Application.Visible = True
'先引用excel *.0 object library Public ex As Excel.Application Public wb As Excel.Workbook Public sh As Excel.WorksheetPrivate Sub Command3_Click() Dim i As Long Set ex = CreateObject("Excel.Application") Set wb = ex.Workbooks.Add '新建excel Set sh = wb.Sheets(1) '第一个工作表
For i = 0 To 20 '在第一列写入20个数 sh.Cells(i + 1, 1) = i Next i
ex.Visible = True Set ex = Nothing Set wb = Nothing Set sh = Nothing End Sub
Private Sub Command1_Click() 'On Error GoTo myErr Dim excel_app As Object Dim Filename1 As String, FilePath As String '设置EXCEL文件名 Filename1 = "temp" '建立 Excel 应用程序 Set excel_app = CreateObject("Excel.Application") '显示Excel应用程序 excel_app.Visible = False '添加新工作簿: excel_app.WorkBooks.Add '检测Excel版本 If Val(excel_app.Application.Version) >= 12 Then FilePath = Filename1 & ".xlsx" Else FilePath = Filename1 & ".xls" End If '设置一个鼠标形状 Screen.MousePointer = vbHourglass '设置第1个工作表为活动工作表: excel_app.Sheets("sheet1").Select '设置指定列的宽度(单位:字符个数) excel_app.ActiveSheet.Columns(1).ColumnWidth = 4 excel_app.ActiveSheet.Columns(2).ColumnWidth = 6 '初始化列对齐方式 Dim iiCol As Integer For iiCol = 1 To 2 With excel_app.ActiveSheet '4右对齐,3居中 .Columns(iiCol).HorizontalAlignment = 4 End With Next iiCol Dim a(29, 1) As Integer '你的数组 Dim i As Integer, j As Integer For i = 0 To 29 '初始化你的数据 For j = 0 To 1 a(i, j) = i + 1 Next j Next i '写入EXCEL For i = 1 To 30 For j = 1 To 2 excel_app.cells(i, j) = a(i - 1, j - 1) Next j DoEvents Next i '工作表另存为: If Not excel_app.ActiveWorkBook.Saved Then excel_app.ActiveWorkBook.SaveAs FileName:=FilePath End If '关闭Excel: excel_app.Quit Set excel_app = Nothing '还原鼠标形状: Screen.MousePointer = vbDefault MsgBox "导出了" & UBound(a) + 1 & "条记录", , "导出成功" Exit Sub myErr: Screen.MousePointer = vbDefault If Err.Number = 429 Then Screen.MousePointer = vbDefault MsgBox "请先安装EXCEL!", , "导出错误" Exit Sub End If excel_app.DisplayAlerts = False '关闭时不提示保存 excel_app.Quit '关闭EXCEL excel_app.DisplayAlerts = True '关闭时提示保存 Set excel_app = Nothing MsgBox " 导出数据到 Excel 时出错! ", , "导出错误" End Sub
Dim Es As Object
Set Es = CreateObject("Excel.Sheet")
Es.Application.cells(1, 1) = "AAA"
Es.Application.cells(1, 2) = "BBB"
Es.Application.cells(2, 1) = "CCC"
Es.Application.Visible = True
Public ex As Excel.Application
Public wb As Excel.Workbook
Public sh As Excel.WorksheetPrivate Sub Command3_Click()
Dim i As Long
Set ex = CreateObject("Excel.Application")
Set wb = ex.Workbooks.Add '新建excel
Set sh = wb.Sheets(1) '第一个工作表
For i = 0 To 20 '在第一列写入20个数
sh.Cells(i + 1, 1) = i
Next i
ex.Visible = True
Set ex = Nothing
Set wb = Nothing
Set sh = Nothing
End Sub
'On Error GoTo myErr
Dim excel_app As Object
Dim Filename1 As String, FilePath As String
'设置EXCEL文件名
Filename1 = "temp"
'建立 Excel 应用程序
Set excel_app = CreateObject("Excel.Application")
'显示Excel应用程序
excel_app.Visible = False
'添加新工作簿:
excel_app.WorkBooks.Add
'检测Excel版本
If Val(excel_app.Application.Version) >= 12 Then
FilePath = Filename1 & ".xlsx"
Else
FilePath = Filename1 & ".xls"
End If
'设置一个鼠标形状
Screen.MousePointer = vbHourglass
'设置第1个工作表为活动工作表:
excel_app.Sheets("sheet1").Select
'设置指定列的宽度(单位:字符个数)
excel_app.ActiveSheet.Columns(1).ColumnWidth = 4
excel_app.ActiveSheet.Columns(2).ColumnWidth = 6
'初始化列对齐方式
Dim iiCol As Integer
For iiCol = 1 To 2
With excel_app.ActiveSheet
'4右对齐,3居中
.Columns(iiCol).HorizontalAlignment = 4
End With
Next iiCol
Dim a(29, 1) As Integer '你的数组
Dim i As Integer, j As Integer
For i = 0 To 29 '初始化你的数据
For j = 0 To 1
a(i, j) = i + 1
Next j
Next i
'写入EXCEL
For i = 1 To 30
For j = 1 To 2
excel_app.cells(i, j) = a(i - 1, j - 1)
Next j
DoEvents
Next i
'工作表另存为:
If Not excel_app.ActiveWorkBook.Saved Then
excel_app.ActiveWorkBook.SaveAs FileName:=FilePath
End If
'关闭Excel:
excel_app.Quit
Set excel_app = Nothing
'还原鼠标形状:
Screen.MousePointer = vbDefault
MsgBox "导出了" & UBound(a) + 1 & "条记录", , "导出成功"
Exit Sub
myErr:
Screen.MousePointer = vbDefault
If Err.Number = 429 Then
Screen.MousePointer = vbDefault
MsgBox "请先安装EXCEL!", , "导出错误"
Exit Sub
End If
excel_app.DisplayAlerts = False '关闭时不提示保存
excel_app.Quit '关闭EXCEL
excel_app.DisplayAlerts = True '关闭时提示保存
Set excel_app = Nothing
MsgBox " 导出数据到 Excel 时出错! ", , "导出错误"
End Sub