求助,使用VB导出数据到Excel,在Excel内鼠标点击报错
Dim i, j, k, n As Long
On Error GoTo ErrHnd
Set oApp = CreateObject("Excel.Application")
Set oBook = oApp.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
Set oSheet = oBook.ActiveSheet
oApp.Visible = True
oApp.Cursor = 2
oSheet.Visible = -1
oSheet.Columns("A:Z").ColumnWidth = 12 '导出数据部分 oApp.Cursor = -4143
Set oApp = Nothing
Set oBook = Nothing
Set oSheet = Nothing
MousePointer = 0
Exit Sub
ErrHnd:
MousePointer = 0
Set oApp = Nothing
Set oBook = Nothing
Set oSheet = Nothing
Dim i, j, k, n As Long
On Error GoTo ErrHnd
Set oApp = CreateObject("Excel.Application")
Set oBook = oApp.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
Set oSheet = oBook.ActiveSheet
oApp.Visible = True
oApp.Cursor = 2
oSheet.Visible = -1
oSheet.Columns("A:Z").ColumnWidth = 12 '导出数据部分 oApp.Cursor = -4143
Set oApp = Nothing
Set oBook = Nothing
Set oSheet = Nothing
MousePointer = 0
Exit Sub
ErrHnd:
MousePointer = 0
Set oApp = Nothing
Set oBook = Nothing
Set oSheet = Nothing
代码和下面差不多,谢谢
Dim i, j, k, n As Long
On Error GoTo ErrHnd
Set oApp = CreateObject("Excel.Application")
Set oBook = oApp.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
Set oSheet = oBook.ActiveSheet
oApp.Visible = True
oApp.Cursor = 2
oSheet.Visible = -1
oSheet.Columns("A:Z").ColumnWidth = 12
With lvw
For i = 0 To .ListItems.Count - 1
oSheet.Cells(n + 1, 1) = .ListItems.Item(i + 1).Text
'VBA.DoEvents
Next
End With
oApp.Cursor = -4143
Set oApp = Nothing
Set oBook = Nothing
Set oSheet = Nothing
MousePointer = 0
Exit Sub
ErrHnd:
MousePointer = 0
Set oApp = Nothing
Set oBook = Nothing
Set oSheet = Nothing
不同鼠标活动影响 Excel 操作通常是在代码中需要访问当前工作表或当前选择单元之类,而鼠标活动恰恰改变了这些当前对象,才会导致程序错误。请仔细检查。
谢谢,您的提示让我发现了错误的原因,求其次的解决方式,不允许进行选择了,如下: oSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, _
UserInterfaceOnly:=True, AllowFormattingCells:=True, _
AllowFormattingColumns:=False, AllowFormattingRows:=False, _
AllowInsertingColumns:=False, AllowInsertingRows:=False, _
AllowInsertingHyperlinks:=False, AllowDeletingColumns:=False, _
AllowDeletingRows:=False, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True
oSheet.EnableSelection = Excel.XlEnableSelection.xlNoSelection
oSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, _
UserInterfaceOnly:=True, AllowFormattingCells:=True, _
AllowFormattingColumns:=True, AllowFormattingRows:=True, _
AllowInsertingColumns:=True, AllowInsertingRows:=True, _
AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True
oSheet.EnableSelection = xlNoRestrictions
因为导出时间有点儿长,用进度条感觉也不太良好,所以显示出来了 上面的代码还是有点毛病,滚动条还是不可用
可以oApp.DisplayScrollBars = FALSE
但是却导致了鼠标不可用,滚动条不显示,还是很不人性化 呵呵或许就该不显示出来,呵呵
长时间导出的 Excel,可见状态需要消耗更多的时间进行界面刷新,不划算。