导出数据到EXCEL

Private Sub Command1_Click()

Dim 文件名称As String

文件名称= "C:\Documents and Settings\Administrator\桌面\Hellod.Doc "

Dim uObjDoc As Word.Document

Set uObjWord = New Word.Application

uObjWord.Documents.Add "Normal", IIf(iMode = 1, True, False) '1、根据"Normal"模板添加一个新的文档[1-创建一个模板，0-创建一个文件]

uObjWord.Documents.Save NoPrompt:=True, OriginalFormat:=wdWordDocument '2、设置Word对象为自动保存模式

uObjWord.ActiveDocument.SaveAs 文件名称'3、保存Word文档到文件名称

Set uObjDoc = Nothing

End Sub

EXCEL用法

Public Function ExporToExcel(strOpen As String)

'*********************************************************

'* 名称：ExporToExcel

'* 功能：导出数据到EXCEL

'* 用法：ExporToExcel(sql查询字符串)

'*********************************************************

Dim Rs_Data As New ADODB.Recordset

Dim Irowcount As Integer

Dim Icolcount As Integer

Dim xlApp As New Excel.Application

Dim xlBook As Excel.Workbook

Dim xlSheet As Excel.Worksheet

Dim xlQuery As Excel.QueryTable

With Rs_Data

If .State = adStateOpen Then

.Close

End If

.ActiveConnection = Cn

.CursorLocation = adUseClient

.CursorType = adOpenStatic

.LockType = adLockReadOnly

.Source = strOpen

.Open

End With

' Rs_Data.Open strOpen, Cn, adOpenStatic, adLockReadOnly With Rs_Data

' .MoveFirst

If .RecordCount < 1 Then

MsgBox ( "没有记录! ")

Exit Function

End If

'记录总数

Irowcount = .RecordCount

'字段总数

Icolcount = .Fields.Count

End With

Set xlApp = CreateObject( "Excel.Application ")

Set xlBook = Nothing

Set xlSheet = Nothing

Set xlBook = xlApp.Workbooks().Add

Set xlSheet = xlBook.Worksheets( "sheet1 ")

xlApp.Visible = True

'添加查询语句，导入EXCEL数据

Set xlQuery = xlSheet.QueryTables.Add(Rs_Data, xlSheet.Range( "a1 "))

With xlQuery

.FieldNames = True

.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = True

.RefreshOnFileOpen = False

.BackgroundQuery = True

.RefreshStyle = xlInsertDeleteCells

.SavePassword = True

.SaveData = True

.AdjustColumnWidth = True

.RefreshPeriod = 0

.PreserveColumnInfo = True

End With

xlQuery.FieldNames = True '显示字段名

xlQuery.Refresh

With xlSheet

.Range(.Cells(1, 1), .Cells(1, Icolcount)) = "黑体"

'设标题为黑体字

.Range(.Cells(1, 1), .Cells(1, Icolcount)).Font.Bold = True

'标题字体加粗

.Range(.Cells(1, 1), .Cells(Irowcount + 1, Icolcount)).Borders.LineStyl e = xlContinuous

'设表格边框样式

End With

' With xlSheet.PageSetup

' .LeftHeader = " " & Chr(10) & "& " "楷体_GB2312,常规" "&10公司名称：" ' & Gsmc

' .CenterHeader = "& " "楷体_GB2312,常规" "公司人员情况表& " "宋体,常规" " " & Chr(10) & "& " "楷体_GB2312,常规" "&10日期："

' .RightHeader = " " & Chr(10) & "& " "楷体_GB2312,常规" "&10单位："

' .LeftFooter = "& " "楷体_GB2312,常规" "&10制表人："

' .CenterFooter = "& " "楷体_GB2312,常规" "&10制表日期："

' .RightFooter = "& " "楷体_GB2312,常规" "&10第&P页共&N页"

' End With

xlApp.Application.Visible = True

Set xlApp = Nothing ' "交还控制给Excel

Set xlBook = Nothing

Set xlSheet = Nothing

End Function

在VB 中控制Word

Word 提供了一个Word 对象，通过在“引用” 中的该对象，可以实现对Word 的控制。以下的代码演示了执行WordBasic 语句，该段代码是动态引用对象，无须在工程中引用Word 对象。

Private Sub Command1_Click()

Dim wd As Object

Set wd = CreateObject("Word.Basic ")

wd.FileNewDefault

wd.FontSize 20

wd.Insert "Hello, World "