Question

vb操作excel第二个文件报错

实现读取一个文件夹下的xls文件，然后逐个做数据处理最后保存成txt文件，执行到第二个文件后就报错

功能代码：
Private Sub dealMethod(filePath, fileName)
Dim newFileName As String
Dim fileType As String
fileType = Right(fileName, 4)
newFileName = Left(fileName, Len(fileName) - 4)
newFileName = newFileName + "_1.txt"

Dim xlapp As Excel.Application
Dim wkBook As Excel.Workbook
Dim wkSheet As Excel.Worksheet
Set xlapp = CreateObject("excel.application")
xlapp.Application.EnableEvents = False
xlapp.DisplayAlerts = False
Set wkBook = xlapp.Workbooks.Open(filePath & "\" & fileName)
Set wkSheet = wkBook.Worksheets("CivilReport")
Rows("1:4").Select
Selection.Delete Shift:=xlUp
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Columns("C:C").Select
wkSheet.Sort.SortFields.Clear
wkSheet.Sort.SortFields.Add Key:=Range("C1") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With wkSheet.Sort
.SetRange Range("A1:C599")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("B:B").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("E2").Select
'ChDir "filePath"
ActiveWorkbook.SaveAs fileName:=filePath & "\" & newFileName, _
FileFormat:=xlCSV, CreateBackup:=False
List2.AddItem (filePath & "\" & newFileName)
wkBook.Application.Quit
Set wkBook = Nothing
Set wkSheet = Nothing
xlapp.Workbooks.Close
xlapp.Quit
Set xlapp = Nothing
End Sub

Answer 1

Dim xlapp As Excel.Application

Dim wkBook As Excel.Workbook

Dim wkSheet As Excel.Worksheet

通过上面三个代码可以看出，对Excel的运行环境，已经做了前期绑定。

可是下面为什么又要用后期绑定？利用CreateObject创建Excel环境呢？

直接Set xlapp = Excel.Application   不就可以了么？

Set xlapp = CreateObject("excel.application")   '所以，这句代码略显多余，

 

看你出现的问题，估计应该是后面的代码出错，改为如下：

... ...
wkBook.SaveAs fileName:=filePath & "\" & newFileName, _        FileFormat:=xlCSV, CreateBackup:=False
List2.AddItem (filePath & "\" & newFileName)
wkBook.Close True   '关闭当前调用的工作簿，并保存修改
xlApp.Quit   '退出Excel运用环境
Set wkBook = Nothing   '释放
Set wkSheet = Nothing  '释放
Set xlapp = Nothing    '释放
End Sub

 

不知你后面还有什么疑问，如果要继续追问，请先采纳！然后1346Q7914Q联系！因为我看你的代码都是录制的，可能要做大批量修改。附件传来传去麻烦，不如QQ解决！！