使用以下代码可实现打开并重新保存EXCEL文件,从而解决从程序导出的EXCEL文件格式不能被ADO访问的问题,据实验,光是处理一个文件的话需要5秒钟左右,但若批量一起处理的话能缩短到1秒/文件左右.
************下载来的程序源代码*****************************
Dim xlApp As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Private Sub Command1_Click()
Set xlApp = New Excel.Application
xlApp.Visible = True '设为false,不可见
Set xlbook = xlApp.Workbooks.Open("c:/temp/
模板
.xls")
Set xlsheet = xlbook.Worksheets(1)
xlsheet.Range("A1") = "ABC"
xlbook.Save
xlbook.Close
xlApp.Quit
Set xlApp = Nothing
End Sub
******************我的程序******************************************
加入了对中间漏掉程序的处理,若连续丢失10个文件,则认为已经处理完毕,结束处理
Dim xlApp As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Private Sub Command1_Click()
On Error GoTo fix_err
Set xlApp = New Excel.Application
Dim i As Integer
Dim fileName As String
Dim cWrong As Integer '连续错误次数,cWrong>10则结束循环
cWrong = 0
For i = 1 To 20000
fileName = "c:/temp/hq" & i & ".xls"
If Dir(fileName) <> "" Then '文件存在
Set xlbook = xlApp.Workbooks.Open(fileName)
xlbook.Save
cWrong = 0
Else
cWrong = cWrong + 1
If (cWrong > 10) Then
Exit Sub
End If
End If
Label1.Caption = i
Next i
xlbook.Close
xlApp.Quit
Set xlApp = Nothing
MsgBox "ok"
fix_err:
Debug.Print Err.Description + " " + _
Err.Source, vbCritical, "Import"
Err.Clear
End Sub