比如说一个返回的记录集一样:
id name chengji minfci
0101 iii 98 1
0110 jjj 96 2我要插入到一个EXECL的表格里面去,
怎么写这个语句。
1:全部记录集放在EXECL
2:我只是把‘0101’放在EXECL的(4,6)单元格上。
id name chengji minfci
0101 iii 98 1
0110 jjj 96 2我要插入到一个EXECL的表格里面去,
怎么写这个语句。
1:全部记录集放在EXECL
2:我只是把‘0101’放在EXECL的(4,6)单元格上。
Dim rs As DAO.Recordset
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
' Open the recordset.
Set db = DBEngine.Workspaces(0).OpenDatabase("D:\db1.mdb")
Set rs = db.OpenRecordset("SELECT * FROM MyTable")' Open the destination Excel workbook.
Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks. _
Open("D:\Book1.xls")' This is all it takes to copy the contents
' of the recordset into the first worksheet
' of Book1.xls.xlBook.Worksheets(1).Range("A1").CopyFromRecordset rs '粘贴整个记录集'否则,如问题2
'xlBook.Worksheets(1).Range("F4").Value = rs!id' Clean up everything.
xlBook.Save
xlBook.Close False
xlApp.Quit
rs.Close
db.Close
Set xlBook = Nothing
Set xlApp = Nothing
Set rs = Nothing
Set db = Nothing
其他不变即可,这是总斑竹给VB斑竹然后到我手里的,非常好使。
Public Function ExporToExcel()'*********************************************************
'* 名称:ExporToExcel
'* 功能:导出数据到EXCEL
'* 用法:ExporToExcel(sql查询字符串)
'*********************************************************
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 ss
If .State = adStateOpen Then
.Close
End If
.ActiveConnection = Cn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockReadOnly
.Source = strOpen
.Open End With
With ss
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(ss, 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)).Font.Name = "黑体"
'设标题为黑体字
.Range(.Cells(1, 1), .Cells(1, Icolcount)).Font.Bold = True
'标题字体加粗
.Range(.Cells(1, 1), .Cells(Irowcount + 1, Icolcount)).Borders.LineStyle = 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 = NothingEnd Function
Set xl = New Excel.Application
Set xl = CreateObject("excel.application")
xl.Visible = True
xl.SheetsInNewWorkbook = 1
Set xlb = xl.Workbooks.Add
一、将‘0101’写入多个单元格内
xl.Range("A1:D1").Select
xl.ActiveCell.FormulaR1C1 = "0101"
二、将‘0101’写入单个单元格内
xl.Cells(i,j) = "0101"
一、将‘0101’写入多个单元格内
xl.Range("A1:D1").Select xl.Selection.Merge '合并单元格 xl.ActiveCell.FormulaR1C1 = "0101"