to bigbigfans你可以说得详细一点吗?我现在用的是调用Excel,然后往里面 增加列,再根据查询结果增加数据,但是不知道你说得方法是什么? 是否可以指点一下?谢谢
'编了一个函数,拿出来献丑了 '将数据导入到Excel文件中 '参数1为记录集 '参数2为excel模板文件名,默认路径为应用程序当前目录 '参数3为从第几行开始写数据 '函数返回0表示导入成功,1为失败 Public Function Data2Excel(rs As ADODB.Recordset, strExcelFile As String, _ iBeginRow As String) As Integer
Dim i As Integer, j As Integer, Handle As Long, bOpen As Boolean Dim iFieldsCount As Integer
Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet
On Error GoTo TransError
' 将鼠标指针改变为沙漏标。 Screen.MousePointer = vbHourglass
Set xlApp = New Excel.Application
'激活Excel应用程序 Set xlApp = CreateObject("Excel.Application") '显示/隐藏Excel应用程序 xlApp.Visible = True '判断Excel模板文件是否存在 If Dir(App.Path & "\Excels\" & strExcelFile) = "" Then Data2Excel = 1 Exit Function End If
'将模板文件拷贝到临时文件,防止模板文件被修改 '临时文件保存在 app.path & "\Excels" 目录下,格式为 "Temp" & 数字 & ".xls" '查看临时文件是否被打开,如果是,则把模板文件拷贝到下一个临时文件 '若否,则覆盖第一个没打开的临时文件 bOpen = True: i = 0 Do Until bOpen = False i = i + 1 Handle = FindWindow("XLMAIN", "Microsoft Excel - Temp" & CStr(i)) If Handle = 0 Then bOpen = False Loop FileCopy App.Path & "\Excels\" & strExcelFile, App.Path & "\Excels\Temp" & CStr(i) & ".xls"
'打开工作簿和工作表 Set xlBook = xlApp.Workbooks.Open(App.Path & "\Excels\Temp" & CStr(i) & ".xls") Set xlSheet = xlBook.Worksheets(1)
'写字段名 i = iBeginRow iFieldsCount = rs.Fields.Count 'For j = 0 To iFieldsCount - 1 ' xlSheet.Cells(i, j + 1) = rs.Fields(j).Name 'Next '写数据 i=i+1 rs.MoveFirst Do While Not rs.EOF For j = 0 To iFieldsCount - 1 xlSheet.Cells(i, j + 1) = rs.Fields(j).Value Next i = i + 1 rs.MoveNext Loop Data2Excel = 0 GoTo CloseObject TransError: Data2Excel = 1 CloseObject: xlApp.Save xlApp.Quit ' 返回鼠标指针到正常状态。 Screen.MousePointer = vbDefault End Function
sub 宏1
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.748031496062992)
.RightMargin = Application.InchesToPoints(0.748031496062992)
.TopMargin = Application.InchesToPoints(0.590551181102362)
.BottomMargin = Application.InchesToPoints(0.984251968503937)
.HeaderMargin = Application.InchesToPoints(0.511811023622047)
.FooterMargin = Application.InchesToPoints(0.511811023622047)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 360
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
End With
end sub
增加列,再根据查询结果增加数据,但是不知道你说得方法是什么?
是否可以指点一下?谢谢
'将数据导入到Excel文件中
'参数1为记录集
'参数2为excel模板文件名,默认路径为应用程序当前目录
'参数3为从第几行开始写数据
'函数返回0表示导入成功,1为失败
Public Function Data2Excel(rs As ADODB.Recordset, strExcelFile As String, _
iBeginRow As String) As Integer
Dim i As Integer, j As Integer, Handle As Long, bOpen As Boolean
Dim iFieldsCount As Integer
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
On Error GoTo TransError
' 将鼠标指针改变为沙漏标。
Screen.MousePointer = vbHourglass
Set xlApp = New Excel.Application
'激活Excel应用程序
Set xlApp = CreateObject("Excel.Application")
'显示/隐藏Excel应用程序
xlApp.Visible = True
'判断Excel模板文件是否存在
If Dir(App.Path & "\Excels\" & strExcelFile) = "" Then
Data2Excel = 1
Exit Function
End If
'将模板文件拷贝到临时文件,防止模板文件被修改
'临时文件保存在 app.path & "\Excels" 目录下,格式为 "Temp" & 数字 & ".xls"
'查看临时文件是否被打开,如果是,则把模板文件拷贝到下一个临时文件
'若否,则覆盖第一个没打开的临时文件
bOpen = True: i = 0
Do Until bOpen = False
i = i + 1
Handle = FindWindow("XLMAIN", "Microsoft Excel - Temp" & CStr(i))
If Handle = 0 Then bOpen = False
Loop
FileCopy App.Path & "\Excels\" & strExcelFile, App.Path & "\Excels\Temp" & CStr(i) & ".xls"
'打开工作簿和工作表
Set xlBook = xlApp.Workbooks.Open(App.Path & "\Excels\Temp" & CStr(i) & ".xls")
Set xlSheet = xlBook.Worksheets(1)
'写字段名
i = iBeginRow
iFieldsCount = rs.Fields.Count
'For j = 0 To iFieldsCount - 1
' xlSheet.Cells(i, j + 1) = rs.Fields(j).Name
'Next
'写数据
i=i+1
rs.MoveFirst
Do While Not rs.EOF
For j = 0 To iFieldsCount - 1
xlSheet.Cells(i, j + 1) = rs.Fields(j).Value
Next
i = i + 1
rs.MoveNext
Loop
Data2Excel = 0
GoTo CloseObject
TransError:
Data2Excel = 1
CloseObject:
xlApp.Save
xlApp.Quit
' 返回鼠标指针到正常状态。
Screen.MousePointer = vbDefault
End Function