excel-vba问题：第一次运行时没有问题，第二次运行时报错，提示变量未设置或with变量未设置！！在线等！！

[wandavb]

Private Sub PrintYou(ByVal YourName As String)
Dim freefil As Integer
Kill (App.Path + "\硫化报表.xls")
freefil = 1
Open App.Path + "\硫化报表.xls" For Output As #freefil
Close #freefil
Dim xlApp As Excel.Application '定义EXCEL类
Dim xlBook As Excel.Workbook '定义工件簿类
Dim xlsheet As Excel.Worksheet '定义工作表类 sql = ""
Set xlApp = CreateObject("Excel.Application") '创建EXCEL应用类
xlApp.Visible = True '设置EXCEL可见
Set xlBook = xlApp.Workbooks.Open(App.Path + "\硫化报表.xls") '打开EXCEL工作簿
Set xlsheet = xlBook.Worksheets(1) '打开EXCEL工作表
xlsheet.Activate '激活工作表

Dim j As Integer
Dim rst As ADODB.Recordset

xlsheet.Cells(1, 1) = comCJLH.Text & "报表" & " " & Combo1.Text & " " & DTPicker1.Value

xlsheet.Cells(2, 1) = "产品名称"
xlsheet.Cells(2, 2) = "包装"
xlsheet.Cells(2, 3) = "商标"
xlsheet.Cells(2, 4) = "咀子"
xlsheet.Cells(2, 5) = "颜色"
xlsheet.Cells(2, 6) = "模具数"
xlsheet.Cells(2, 7) = "班产"
xlsheet.Cells(2, 8) = "实际完成数"
xlsheet.Cells(2, 9) = "盈亏"
xlsheet.Cells(2, 10) = "擦模工时"
xlsheet.Cells(2, 11) = "擦模模具数"
xlsheet.Cells(2, 12) = "擦模产量"
xlsheet.Cells(2, 13) = "换模工时"
xlsheet.Cells(2, 14) = "换模模具数"
xlsheet.Cells(2, 15) = "换模产量"
xlsheet.Cells(2, 16) = "红胶(公斤)"
xlsheet.Cells(2, 17) = "黑胶(公斤)"
xlsheet.Cells(2, 18) = "金万程(公斤)"
xlsheet.Cells(2, 19) = "红金万(公斤)"
xlsheet.Cells(2, 20) = "天桥(公斤)"
xlsheet.Cells(2, 21) = "芳香胶(公斤)"
xlsheet.Cells(2, 22) = "机台号"
xlsheet.Cells(2, 23) = "计划产量"

j = 3
Dim i As Integer

'/////////////////////////////////
sql = "select * from " + YourName & " order by 类别,颜色,产品名称"

Set rst = New ADODB.Recordset
rst.CursorType = adOpenDynamic
rst.LockType = adLockPessimistic
rst.Open sql, dbWanda

If rst.BOF = False Then
rst.MoveFirst
Do While Not rst.EOF

xlsheet.Cells(j, 1) = rst.Fields(2).Value
xlsheet.Cells(j, 2) = rst.Fields(3).Value
xlsheet.Cells(j, 3) = rst.Fields(4).Value
xlsheet.Cells(j, 4) = rst.Fields(5).Value
xlsheet.Cells(j, 5) = rst.Fields(6).Value
xlsheet.Cells(j, 6) = rst.Fields(1).Value
xlsheet.Cells(j, 7) = Int(rst.Fields(13).Value)
xlsheet.Cells(j, 10) = Int(rst.Fields(7).Value)
xlsheet.Cells(j, 11) = Int(rst.Fields(8).Value)
xlsheet.Cells(j, 12) = Int(rst.Fields(9).Value)
xlsheet.Cells(j, 13) = Int(rst.Fields(10).Value)
xlsheet.Cells(j, 14) = Int(rst.Fields(11).Value)
xlsheet.Cells(j, 15) = Int(rst.Fields(12).Value)
If rst.Fields(16).Value = "红胶" Then
xlsheet.Cells(j, 16) = Int(rst.Fields(14).Value) '红胶
ElseIf rst.Fields(16).Value = "黑胶" Then
xlsheet.Cells(j, 17) = Int(rst.Fields(14).Value) '黑胶
ElseIf rst.Fields(16).Value = "金万程" Then
xlsheet.Cells(j, 18) = Int(rst.Fields(14).Value) '金万程
ElseIf rst.Fields(16).Value = "红金万" Then
xlsheet.Cells(j, 19) = Int(rst.Fields(14).Value) '红金万
ElseIf rst.Fields(16).Value = "天桥" Then
xlsheet.Cells(j, 20) = Int(rst.Fields(14).Value) '天桥
ElseIf rst.Fields(16).Value = "芳香胶" Then
xlsheet.Cells(j, 21) = Int(rst.Fields(14).Value) '芳香胶
End If
xlsheet.Cells(j, 22) = rst.Fields(0).Value
xlsheet.Cells(j, 23) = rst.Fields(17).Value
j = j + 1
rst.MoveNext
Loop
End If
Set rst = Nothing


'求总和

Dim a As Integer
Dim mjh As Integer
Dim bch As Single
Dim smgs As Long
Dim smmjs As Long
Dim smcl As Single
Dim hmgs As Long
Dim hmmjs As Long
Dim hmcl As Single

Dim hongjh As Single
Dim heijh As Single
Dim jinwanchengjh As Single
Dim hongjinwanjh As Single
Dim tianqiaojh As Single
Dim fangjh As Single
mjh = 0
bch = 0
smgs = 0
smmjs = 0
smcl = 0
hmgs = 0
hmmjs = 0
hmcl = 0

hongjh = 0
heijh = 0
jinwanchengjh = 0
hongjinwanjh = 0
tianqiaojh = 0
fangjh = 0

For a = 3 To j - 1
mjh = mjh + xlsheet.Cells(a, 6).Value
bch = bch + xlsheet.Cells(a, 7).Value
smgs = smgs + xlsheet.Cells(a, 10).Value
smmjs = smmjs + xlsheet.Cells(a, 11).Value
smcl = smcl + xlsheet.Cells(a, 12).Value
hmgs = hmgs + xlsheet.Cells(a, 13).Value
hmmjs = hmmjs + xlsheet.Cells(a, 14).Value
hmcl = hmcl + xlsheet.Cells(a, 15).Value

hongjh = hongjh + xlsheet.Cells(a, 16).Value
heijh = heijh + xlsheet.Cells(a, 17).Value
jinwanchengjh = jinwanchengjh + xlsheet.Cells(a, 18).Value
hongjinwanjh = hongjinwanjh + xlsheet.Cells(a, 19).Value
tianqiaojh = tianqiaojh + xlsheet.Cells(a, 20).Value
fangjh = fangjh + xlsheet.Cells(a, 21).Value
Next a

j = j + 1
xlsheet.Cells(j, 1) = "合计"
xlsheet.Cells(j, 6) = mjh
xlsheet.Cells(j, 7) = Int(bch)
xlsheet.Cells(j, 10) = smgs
xlsheet.Cells(j, 11) = smmjs
xlsheet.Cells(j, 12) = Int(smcl)
xlsheet.Cells(j, 13) = hmgs
xlsheet.Cells(j, 14) = hmmjs
xlsheet.Cells(j, 15) = Int(hmcl)
xlsheet.Cells(j, 16) = Int(hongjh)
xlsheet.Cells(j, 17) = Int(heijh)
xlsheet.Cells(j, 18) = Int(jinwanchengjh)
xlsheet.Cells(j, 19) = Int(hongjinwanjh)
xlsheet.Cells(j, 20) = Int(tianqiaojh)
xlsheet.Cells(j, 21) = Int(fangjh)

'//////////////////////////////////基本数据完成

'??????????????????????????????????????????????????
'此处第一次运行时没有问题，第二次运行时报错，提示变量未设置
'???????????????????????????????????????????????????
xlsheet.Range("a1:w1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
'??????????????????????????????????????????????????
'此处第一次运行时没有问题，第二次运行时报错，提示变量未设置
'???????????????????????????????????????????????????
xlsheet.Columns.AutoFit
xlApp.Quit
Set xlApp = Nothing



End Sub

[heavensonic]

ctrl+del+alt(你是win2000吧)，系统进程里肯定存在excel应用程序。杀了他，一切ok.
原因:只有一个：程序结束时，没有完全杀了excel对象

[wandavb]

你说的没问题，杀了后还是不行。提示远程服务器不存在或不可用！！不知为什么！！大虾们指点一下小弟吧

[wandavb]

仍在等待！！！

[yinweihong]

excel进程没有杀完全
具体怎么杀，看你怎么初始化了

Dim xlApp As Excel.Application '定义EXCEL类
Dim xlBook As Excel.Workbook '定义工件簿类
Dim xlsheet As Excel.Worksheet '定义工作表类 sql = ""
这样定义
xlapp.quit
set xlsheet=nothing
set xlbook=nothing
set xlapp=nothing
有Set xlApp = CreateObject("Excel.Application") '创建EXCEL应用类
打开的话xlapp.workbooks.open(...)
这样与上面是等价的
xlapp.quit
set xlapp=nothing

[bladewing]

同意 yinweihong(yige)的，Excel的Workbook以及Sheet都需要释放，不能仅仅释放Application

[mxfeng]

搂主！！
你是重新启动应用程序，还是重新启动某个部分？模块？程序段？
好好想想，是不是公共变量已经被释放了
，我想一定是漏掉什么没有被执行！
比如说定义变量，实例化变量等
！

[wxyq]

Dim xlApp As Excel.Application '定义EXCEL类
Dim xlBook As Excel.Workbook '定义工件簿类
Dim xlsheet As Excel.Worksheet '定义工作表类 sql = ""
'Set xlApp = CreateObject("Excel.Application") '创建EXCEL应用类
Set xlApp = new Excel.Application

[zyguyue]

xapp,xlbook,xlsheet都要卸掉
xlbook.Close
xapp.Workbooks.Close
Set xlsheet= Nothing
SET xlbook=NOTHING
set xlapp=nothing

[hhjjhjhj]

Selection
改成
xlApp.Selection

其实不用Selection
With xlsheet.Range("a1:w1")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.Merge
End With

[沐NeMo]

一定是某些变量已经被释放.

[duye]

^_^!

[warlord]

仅仅只有Set xlApp = Nothing肯定是不行的。
楼上几位的思路是完全正确的。

[duye]

严重同意大头的说法。