从"工程"菜单中选择"引用"栏；选择Microsoft Excel 9.0 Object Library（Excel2000），然后选择"确定"。表示在工程中要引用Excel类型库。
Dim xlApp As Excel.Application
Dim xlBook As Excel.WorkBook
Dim xlSheet As Excel.Worksheet
Set xlApp = CreateObject("Excel.Application") '创建Excel对象
Set xlBook = xlApp.Workbooks.Open("文件名") '打开已经存在的Excel工件簿文件
xlApp.Visible = True '设置Excel对象可见（或不可见）
Set xlSheet = xlBook.Worksheets("表名") '设置活动工作表
xlSheet.Cells(row, col) =值 '给单元格（row,col）赋值
xlSheet.PrintOut '打印工作表
xlBook.Close (True) '关闭工作簿
xlApp.Quit '结束Excel对象
Set xlApp = Nothing '释放xlApp对象
xlBook.RunAutoMacros (xlAutoOpen) '运行Excel启动宏
xlBook.RunAutoMacros (xlAutoClose) '运行Excel关闭宏
----------------------------
宏代码：
"bb.xls"中打开Visual Basic编辑器，在工程窗口中点鼠标键选择插入模块，在模块中输入入下程序存盘：
Sub auto_open()
Open "d:\Excel.bz" For Output As #1 '写标志文件
Close #1
End Sub
Sub auto_close()
Kill "d:\Excel.bz" '删除标志文件
End Sub
----------------------
|
|
|
| 60.4.111.* |
4楼
___________________
常用语法
xlsheet.Range("A1:I1").Select '选中A1至I1
xlapp.Selection.Merge '合并选中的
xlapp.Selection.HorizontalAlignment = xlCenter '水平居中
启动 Excel
Dim objExcel As Excel.Application
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True'设置EXCEL对象可见（或不可见）
创建有一个工作表的工作簿
Dim objWorkBook As Excel.WorkBook
objExcel.SheetsInNewWorkbook = 1
Set objWorkbook = objExcel.Workbooks.Add
打开已经存在的 EXCEL 工件簿文件
Set objWorkbook=objExcel.Workbooks.Open("文件名")
设置活动工作表
Dim objSheet As Excel.Worksheet
Set objSheet = objExcel.Worksheets("表名")
给单元格（row,col）赋值
objSheet.Cells(row, col) =值
给合并的单元格赋值，如（A3：A9）
objSheet.Range("A3:A9") =值
运行 EXCEL 宏
objWorkbook.RunAutoMacros ("宏名")
插入一行
objSheet.Rows("1:1").Insert Shift:=xlDown
Range("C8").Select
Selection.EntireRow.Insert '在第8行插入
Range("C9").Select
Selection.EntireRow.Delete '删除第9行
range("a1:c3").copy'复制一块
range("a5").PasteSpecial'在第a5行处粘贴复制的块
xlSheet.Range("C3").Value = "1"
xlSheet.Cells(1, 1) = "test中文" '给单元格（row,col）赋值
' 画边框线
xlSheet.Range(xlSheet.Cells(1, 1), xlSheet.Cells(2, 2)).Borders.LineStyle = xlContinuous
xlSheet.Rows(1).HorizontalAlignment = xlVAlignCenter '左右居中
xlSheet.Rows(1).VerticalAlignment = xlVAlignCenter '上下居中
xlSheet.Cells(iStartRow + idx, 2).HorizontalAlignment = xlVAlignCenter '左右居中
'设置指定列的宽度(单位：字符个数)
xlApp.ActiveSheet.Columns(1).ColumnWidth = 15
'设置指定行的高度(单位：磅)
xlApp.ActiveSheet.Rows(1).RowHeight = 1 / 0.035
'设置字体
'xlApp.ActiveSheet.Cells(1, 1).Font.Name = "黑体"
'设置字体大小
xlApp.ActiveSheet.Cells(1, 1).Font.Size = 25
'设置整列字体为粗体
xlApp.ActiveSheet.Columns(1).Font.Bold = True
'xlBook.SaveAs ("C:\Case1.xls")
'xlSheet.PrintPreview (True)
xlApp.Visible = True '显示文件
'xlSheet.PrintOut '打印工作表
xlSheet.PageSetup.LeftHeader = "&""黑体,加粗""&36页眉"'设为36号字体,黑体，加粗'设置页眉
|
|
|
| 60.4.111.* |
5楼
一个打印课程表的例子
Option Explicit
Dim i, l As Integer
Dim n, k As Integer '表格和内容的起始行
Dim yjhs As Integer '页间间隔行数
Dim weizhi As String
Dim xlApp As Excel.Application '定义Excel类
Dim xlBook As Excel.Workbook '定义工件簿类
Dim xlsheet As Excel.Worksheet '定义工作表类
Dim strSource, strDestination As String
Private Sub Command1_Click() '打开Excel过程
wjcopy
If Dir("d:\Excel.bz") = "" Then '判断Excel是否打开
Set xlApp = CreateObject("Excel.Application") '创建Excel应用类
xlApp.Visible = True '设置Excel可见
Set xlBook = xlApp.Workbooks.Open(weizhi & "\临时文件.xls") '打开Excel工作簿
Set xlsheet = xlBook.Worksheets(1) '打开Excel工作表
xlsheet.Activate '激活工作表
daochu '给单元格赋值
xlBook.Save '保存文件
xlBook.RunAutoMacros (xlAutoOpen) ' 运行Excel中的启动宏
Else
MsgBox ("Excel已打开")
End If
End Sub
Private Sub Command3_Click()
If Dir("d:\Excel.bz") <> "" Then '由VB关闭Excel
xlBook.RunAutoMacros (xlAutoClose) '执行Excel关闭宏
xlBook.Close (True) '关闭Excel工作簿
xlApp.Quit '关闭Excel
End If
Set xlApp = Nothing '释放Excel对象
Unload Me
End
End Sub
Private Sub wjcopy()
On Error GoTo aa
strSource = weizhi & "\课程表.xls"
'RegisterFee.xls就是一个模版文件
strDestination = weizhi & "\临时文件.xls"
'Kill strDestination
FileCopy strSource, strDestination
Exit Sub
aa:
MsgBox "创建临时文件出错，可能是模板文件不存在，也可能是有其它程序占用引起的！"
End Sub
Private Sub daochu() '导入主表数据
If Cg1.FontName <> "" Then
xlApp.ActiveSheet.Cells(1, 1).Font.Name = Cg1.FontName
Else
xlApp.ActiveSheet.Cells(1, 1).Font.Name = "黑体"
End If
xlApp.ActiveSheet.Cells(1, 1).Font.Size = 18
xlsheet.Cells(1, 1) = Text1.Text
n = 0
k = 3
Dim l As Integer
l = 1
For n = 0 To 39
If n = 20 Then
k = k + 1
End If
If (n - 4) Mod 5 = 1 Then
k = k + 1
l = 1
End If
l = l + 1
xlsheet.Cells(k, l) = Combo1(n).Text
Next n
End Sub
Private Sub command2_Click()
wjcopy
If Dir("d:\Excel.bz") = "" Then '判断Excel是否打开
Set xlApp = CreateObject("Excel.Application") '创建Excel应用类
xlApp.Visible = False '设置Excel可见
Set xlBook = xlApp.Workbooks.Open(weizhi & "\临时文件.xls") '打开Excel工作簿
Set xlsheet = xlBook.Worksheets(1) '打开Excel工作表
xlsheet.Activate '激活工作表
daochu '给单元格赋值
xlBook.Save '保存文件
xlsheet.PrintOut '打印表格
xlBook.RunAutoMacros (xlAutoOpen) ' 运行Excel中的启动宏
Else
MsgBox ("Excel已打开,如果现在没有打开，检查D：\excel.bz这个文件是不是存在，删除他。")
End If
End Sub
Private Sub Form_Load()
weizhi = App.Path
'weizhi = "d:\"
Dim i As Integer
For i = 0 To 39
Combo1(i).AddItem ("语文")
Combo1(i).AddItem ("数学")
Combo1(i).AddItem ("英语")
Combo1(i).AddItem ("历史")
Combo1(i).AddItem ("地理")
Combo1(i).AddItem ("生物")
Combo1(i).AddItem ("社会")
Combo1(i).AddItem ("自然")
Combo1(i).AddItem ("政治")
Combo1(i).AddItem ("体育")
Combo1(i).AddItem ("物理")
Combo1(i).AddItem ("美术")
Combo1(i).AddItem ("音乐")
Combo1(i).AddItem ("自习")
Combo1(i).AddItem ("劳动")
Combo1(i).AddItem ("自由")
Combo1(i).AddItem ("活动")
Combo1(i).Text = "语文"
Next i
End Sub
Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
Unload Me
End
End Sub
Private Sub Image1_Click()
On Error GoTo aa:
Cg1.Flags = cdlCFScreenFonts Or cdlCFEffects
Cg1.ShowFont
If Cg1.FontSize > 24 Then Cg1.FontSize = 24
With Text1.Font
.Name = Cg1.FontName
.Size = Cg1.FontSize
.Bold = Cg1.FontBold
.Italic = Cg1.FontItalic
.Strikethrough = Cg1.FontStrikethru
.Underline = Cg1.FontUnderline
End With
xlApp.ActiveSheet.Cells(1, 1).Font.Name = Cg1.FontName
Exit Sub
aa:
End Sub
|
|
|
|
