我有一段代码,是把ACCES数据库内的数据输出到EXCEL里的,运行都正常。
但我用EXCEL录制了一段宏用于设置EXCEL页面及格式,程序运行后只能正常输出到EXCEL里一次,第二次再点击输出按钮,不是没有反应就是说服务器忙,只好重新启动程序才能正常输出一次,请问各位这是什么原因造成的啊,怎么解决啊?是不是录制的宏还需要什么特殊的设置啊。
但我用EXCEL录制了一段宏用于设置EXCEL页面及格式,程序运行后只能正常输出到EXCEL里一次,第二次再点击输出按钮,不是没有反应就是说服务器忙,只好重新启动程序才能正常输出一次,请问各位这是什么原因造成的啊,怎么解决啊?是不是录制的宏还需要什么特殊的设置啊。
解决方案 »
- 如何更改编译设置
- VB控件数组批量设置属性问题,超难
- 那位高人给个钩子程序源码,要求能执行
- 初级问题:vb6.0 如何添加winsocket控件?
- 问一个问题,VB怎么接收来自服务端的二进制流数据,分不够再加
- 连接SQL数据库问题,急,在线
- 谁会用微软的 Agent 控件? 高分相赠
- vb文件应用实例
- 困惑,送100分,来者有分,为何我在网站简体版中输入繁体中文在简体版中能正常显示,但在繁体版环境下却乱麻?也就是说我想网站简体版和繁
- 为什么调用WINAPI函数中的READFILE总是会引起一些.DLL文件的无效模块错误?谁能告诉我?
- 垃圾问题请教:用控件如何循环播放
- [送分]推荐一个可以换皮肤的窗体控件源码
Dim oBook As Object
Dim oSheet As Object
Dim oo As Integer
CommonDialog1.CancelError = True CommonDialog1.Flags = cdlOFNHideReadOnly
CommonDialog1.Filter = "All Files (*.*)|*.*|Excel Files" & _
"(*.xls)|*.xls"
CommonDialog1.FilterIndex = 2
CommonDialog1.FileName = "统计表"
CommonDialog1.ShowSave
strFilepath = CommonDialog1.FileName
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
Set oSheet = oExcel.Application.Workbooks(1).Worksheets("Sheet1")
With oSheet.PageSetup
.PrintTitleRows = "$1:$2"
.PrintTitleColumns = ""
End With
oSheet.PageSetup.PrintArea = ""
With oSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "第 &P 页,共 &N 页"
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.393700787401575)
.RightMargin = Application.InchesToPoints(0.393700787401575)
.TopMargin = Application.InchesToPoints(0.393700787401575)
.BottomMargin = Application.InchesToPoints(0.393700787401575)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0.196850393700787)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 300
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With
Cells.Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A1:V1").Select
ActiveCell.FormulaR1C1 = ""
Range("A1:V1").Select
Columns("B:B").ColumnWidth = 2.38
Columns("C:C").ColumnWidth = 6.38
Columns("D:D").ColumnWidth = 4.88
Columns("E:E").ColumnWidth = 2.63
Columns("F:F").ColumnWidth = 4.25
Columns("H:H").ColumnWidth = 4.13
Columns("J:J").ColumnWidth = 4.25
Columns("K:K").ColumnWidth = 4
Columns("N:N").ColumnWidth = 4.25
Columns("O:O").ColumnWidth = 6.25
Columns("P:P").ColumnWidth = 4.5
Columns("Q:Q").ColumnWidth = 10.75
Columns("L:L").ColumnWidth = 10.38
Columns("L:L").ColumnWidth = 10.75
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
Columns("Q:Q").ColumnWidth = 10.88
Columns("Q:Q").ColumnWidth = 10.75
Columns("T:T").ColumnWidth = 2.88
Columns("U:U").ColumnWidth = 3
Columns("V:V").ColumnWidth = 15.63
Range("A1:V1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Selection.UnMerge
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Selection.Font.Bold = False
Selection.Font.Bold = True
With Selection.Font
.Name = "宋体"
.Size = 20
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Columns("L:L").Select
Range("L2").Activate
ActiveWindow.SmallScroll ToRight:=0
Range("L:L,Q:Q").Select
Range("Q2").Activate
Selection.NumberFormatLocal = "yyyy-m-d"
Range("N11").Select
Columns("A:A").ColumnWidth = 3.38
Columns("A:A").ColumnWidth = 3.63
Columns("G:G").ColumnWidth = 4.88
Columns("G:G").ColumnWidth = 4.38
Columns("I:I").ColumnWidth = 2.75
Columns("I:I").ColumnWidth = 2.25
Columns("M:M").ColumnWidth = 7.75
Columns("M:M").ColumnWidth = 6.75
Cells.Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
Columns("S:U").Select
Range("U2").Activate
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("S:U,A:A").Select
Range("A2").Activate
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
Range("U3").Select
Columns("S:U").Select
Range("U2").Activate
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("S:U,A:A").Select
Range("A2").Activate
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
Range("S3:T667").Select
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 639
ActiveWindow.ScrollRow = 638
ActiveWindow.ScrollRow = 635
ActiveWindow.ScrollRow = 632
ActiveWindow.ScrollRow = 625
ActiveWindow.ScrollRow = 617
ActiveWindow.ScrollRow = 604
ActiveWindow.ScrollRow = 596
ActiveWindow.ScrollRow = 586
ActiveWindow.ScrollRow = 573
ActiveWindow.ScrollRow = 559
ActiveWindow.ScrollRow = 544
ActiveWindow.ScrollRow = 529
ActiveWindow.ScrollRow = 509
ActiveWindow.ScrollRow = 495
ActiveWindow.ScrollRow = 473
ActiveWindow.ScrollRow = 453
ActiveWindow.ScrollRow = 438
ActiveWindow.ScrollRow = 416
ActiveWindow.ScrollRow = 396
ActiveWindow.ScrollRow = 379
ActiveWindow.ScrollRow = 364
ActiveWindow.ScrollRow = 355
ActiveWindow.ScrollRow = 342
ActiveWindow.ScrollRow = 336
ActiveWindow.ScrollRow = 327
ActiveWindow.ScrollRow = 325
ActiveWindow.ScrollRow = 322
ActiveWindow.ScrollRow = 321
ActiveWindow.ScrollRow = 317
ActiveWindow.ScrollRow = 305
ActiveWindow.ScrollRow = 295
ActiveWindow.ScrollRow = 284
ActiveWindow.ScrollRow = 271
ActiveWindow.ScrollRow = 252
ActiveWindow.ScrollRow = 232
ActiveWindow.ScrollRow = 208
ActiveWindow.ScrollRow = 187
ActiveWindow.ScrollRow = 165
ActiveWindow.ScrollRow = 143
ActiveWindow.ScrollRow = 125
ActiveWindow.ScrollRow = 108
ActiveWindow.ScrollRow = 91
ActiveWindow.ScrollRow = 78
ActiveWindow.ScrollRow = 67
ActiveWindow.ScrollRow = 60
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 1
Range("S3:T667,A3:A666").Select
Range("A3").Activate
Selection.NumberFormatLocal = "G/通用格式"
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
Range("U3:U347").Select
Selection.NumberFormatLocal = "G/通用格式"
Columns("R:R").ColumnWidth = 4.75
Columns("R:R").ColumnWidth = 4.25
Columns("S:S").ColumnWidth = 4.75
Columns("S:S").ColumnWidth = 4.13
Columns("T:T").ColumnWidth = 3.5
Columns("T:T").ColumnWidth = 2.75
Columns("T:T").ColumnWidth = 2.5
Columns("U:U").ColumnWidth = 3.63
Columns("U:U").ColumnWidth = 2.75
Range("R8").Select
ActiveWindow.Zoom = 90 '90%
ActiveWindow.ScrollColumn = 1 '最左
Range("P2").Activate
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Set oSheet = oExcel.Application.Workbooks(1).Worksheets("Sheet1")
oSheet.Cells(1, 1).Value = "统计表"
oSheet.Cells(2, 1).Value = "编号"
oSheet.Cells(2, 2).Value = "类型"
Adodc1.Recordset.MoveFirst
For oo = 3 To Adodc1.Recordset.RecordCount + 2
oSheet.Cells(oo, 1).Value = Adodc1.Recordset(1)
oSheet.Cells(oo, 2).Value = Adodc1.Recordset(2)
oSheet.Cells(oo, 3).Value = Adodc1.Recordset(3)
oSheet.Cells(oo, 4).Value = Adodc1.Recordset(4)
oSheet.Cells(oo, 5).Value = Adodc1.Recordset(5)
oSheet.Cells(oo, 6).Value = Adodc1.Recordset(6)
oSheet.Cells(oo, 7).Value = Adodc1.Recordset(7)
oSheet.Cells(oo, 8).Value = Adodc1.Recordset(8)
oSheet.Cells(oo, 9).Value = Adodc1.Recordset(9)
oSheet.Cells(oo, 10).Value = Adodc1.Recordset(10)
oSheet.Cells(oo, 11).Value = Adodc1.Recordset(11)
oSheet.Cells(oo, 12).Value = Adodc1.Recordset(12)
oSheet.Cells(oo, 13).Value = Adodc1.Recordset(13)
oSheet.Cells(oo, 14).Value = Adodc1.Recordset(14)
oSheet.Cells(oo, 15).Value = Adodc1.Recordset(15)
oSheet.Cells(oo, 16).Value = Adodc1.Recordset(16)
oSheet.Cells(oo, 17).Value = Adodc1.Recordset(17)
oSheet.Cells(oo, 18).Value = Adodc1.Recordset(18)
oSheet.Cells(oo, 19).Value = Adodc1.Recordset(19)
oSheet.Cells(oo, 20).Value = Adodc1.Recordset(20)
oSheet.Cells(oo, 21).Value = Adodc1.Recordset(21)
oSheet.Cells(oo, 22).Value = Adodc1.Recordset(22)
Adodc1.Recordset.MoveNext
Next oo
With oSheet
.Range(.Cells(2, 1), .Cells(Adodc1.Recordset.RecordCount + 2, 22)).Borders.LineStyle = xlContinuous
End With
oBook.SaveAs strFilepath
oExcel.Visible = True
'oExcel.Quit
Set oExcel = Nothing
Set oBook = Nothing
Set oSheet = Nothing
Exit SubEnd Sub
主要应该是退出这个部分,应该有什么没有释放,如果不用宏这代码是没有问题的谢谢了。
为什么要屏蔽??
应该要退出的呀。
不然启动多个excel进程,很耗内存的。
另外,检查marco里是不是有指定了绝对路径之类的命令。
帮楼主顶一下。
我想输出后把EXCEL打开着,路径有关系吗?
谢谢朋友.
With oExcel.Selections
oExcel.ActiveWindow.ScrollColumn = 1
试试看,对不对
我以前也遇到过
不过我哪个只要求一次性写入
完成后unload了就可以再次运行
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
不过建议你引用EXCEL对象,如下声明:
Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet不知是否正确,你试试
oExcel.Workbooks(1).Close
Set oExcel = Nothing.
……