'开始填充数据
CurrentHUOHAO = ""
CurrentSEHAO = ""
CurrentExcelRow = 2
For I = 1 To VS7.Rows - 2
If VS7.TextMatrix(I, 2) = CurrentHUOHAO And VS7.TextMatrix(I, 3) = CurrentSEHAO Then
'如果还是这个款式,只是规格变化,那么就填充到当前行
With exSheet
Select Case Right(VS7.TextMatrix(I, 4), Len(VS7.TextMatrix(I, 4)) - 2)
Case "XS"
.Cells(CurrentExcelRow, 3) = VS7.TextMatrix(I, 5)
.Cells(CurrentExcelRow, 13) = VS7.TextMatrix(I, 6)
Case "S"
.Cells(CurrentExcelRow, 4) = VS7.TextMatrix(I, 5)
.Cells(CurrentExcelRow, 14) = VS7.TextMatrix(I, 6)
Case "M"
.Cells(CurrentExcelRow, 5) = VS7.TextMatrix(I, 5)
.Cells(CurrentExcelRow, 15) = VS7.TextMatrix(I, 6)
Case "L"
.Cells(CurrentExcelRow, 6) = VS7.TextMatrix(I, 5)
.Cells(CurrentExcelRow, 16) = VS7.TextMatrix(I, 6)
Case "XL"
.Cells(CurrentExcelRow, 7) = VS7.TextMatrix(I, 5)
.Cells(CurrentExcelRow, 17) = VS7.TextMatrix(I, 6)
Case "2XL"
.Cells(CurrentExcelRow, 8) = VS7.TextMatrix(I, 5)
.Cells(CurrentExcelRow, 18) = VS7.TextMatrix(I, 6)
Case "3XL"
.Cells(CurrentExcelRow, 9) = VS7.TextMatrix(I, 5)
.Cells(CurrentExcelRow, 19) = VS7.TextMatrix(I, 6)
Case "4XL"
.Cells(CurrentExcelRow, 10) = VS7.TextMatrix(I, 5)
.Cells(CurrentExcelRow, 20) = VS7.TextMatrix(I, 6)
Case "5XL"
.Cells(CurrentExcelRow, 11) = VS7.TextMatrix(I, 5)
.Cells(CurrentExcelRow, 21) = VS7.TextMatrix(I, 6)
Case Else
End Select
End With
Else
'如果款式发生变化,则填充到下一行
With exSheet
CurrentExcelRow = CurrentExcelRow + 1
.Cells(CurrentExcelRow, 1) = VS7.TextMatrix(I, 2)
.Cells(CurrentExcelRow, 2) = VS7.TextMatrix(I, 3)
Select Case Right(VS7.TextMatrix(I, 4), Len(VS7.TextMatrix(I, 4)) - 2)
Case "XS"
.Cells(CurrentExcelRow, 3) = VS7.TextMatrix(I, 5)
.Cells(CurrentExcelRow, 13) = VS7.TextMatrix(I, 6)
Case "S"
.Cells(CurrentExcelRow, 4) = VS7.TextMatrix(I, 5)
.Cells(CurrentExcelRow, 14) = VS7.TextMatrix(I, 6)
Case "M"
.Cells(CurrentExcelRow, 5) = VS7.TextMatrix(I, 5)
.Cells(CurrentExcelRow, 15) = VS7.TextMatrix(I, 6)
Case "L"
.Cells(CurrentExcelRow, 6) = VS7.TextMatrix(I, 5)
.Cells(CurrentExcelRow, 16) = VS7.TextMatrix(I, 6)
Case "XL"
.Cells(CurrentExcelRow, 7) = VS7.TextMatrix(I, 5)
.Cells(CurrentExcelRow, 17) = VS7.TextMatrix(I, 6)
Case "2XL"
.Cells(CurrentExcelRow, 8) = VS7.TextMatrix(I, 5)
.Cells(CurrentExcelRow, 18) = VS7.TextMatrix(I, 6)
Case "3XL"
.Cells(CurrentExcelRow, 9) = VS7.TextMatrix(I, 5)
.Cells(CurrentExcelRow, 19) = VS7.TextMatrix(I, 6)
Case "4XL"
.Cells(CurrentExcelRow, 10) = VS7.TextMatrix(I, 5)
.Cells(CurrentExcelRow, 20) = VS7.TextMatrix(I, 6)
Case "5XL"
.Cells(CurrentExcelRow, 11) = VS7.TextMatrix(I, 5)
.Cells(CurrentExcelRow, 21) = VS7.TextMatrix(I, 6)
Case Else
End Select
End With
End If
Next I
With exSheet
For I = 3 To CurrentExcelRow
For J = 3 To 22
If .Cells(I, J) = "" Then
.Cells(I, J) = 0
End If
Next J
Next I
End With
'接下来进行数量合计,包括横向合计
With exSheet
'入库区
'横向合计
Range("L3").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-9]:RC[-1])"
Range("L3").Select
Selection.AutoFill Destination:=Range("L3:L" & CurrentExcelRow), Type:=xlFillDefault
Range("L3:L" & CurrentExcelRow).Select
'纵向合计
For I = 0 To 9
Range("C3:L" & (CurrentExcelRow + 1)).Select
Range(Chr(Asc("C") + I) & (CurrentExcelRow + 1)).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[-7]C:R[-1]C)"
Next I
'设置右对齐
Range("C3:L" & (CurrentExcelRow + 1)).Select
ActiveWindow.SmallScroll ToRight:=4
'现存区
'横向合计
Range("V3").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-9]:RC[-1])"
Range("V3").Select
Selection.AutoFill Destination:=Range("V3:V" & CurrentExcelRow), Type:=xlFillDefault
Range("V3:V" & CurrentExcelRow).Select
'纵向合计
For I = 0 To 9
Range("M3:V" & (CurrentExcelRow + 1)).Select
Range(Chr(Asc("M") + I) & (CurrentExcelRow + 1)).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[-7]C:R[-1]C)"
Next I
'设置右对齐
Range("M3:V" & (CurrentExcelRow + 1)).Select
ActiveWindow.SmallScroll ToRight:=4
'最后,向合计行的第一个单元格输入“合计”二字
.Cells(CurrentExcelRow + 1, 1) = "合计"
End With
'现在开始处理表头和表尾
'******************************************
'使用服务器日期和时间
mysql = "SELECT GETDATE() AS DATEANDTIME"
Set myrs = OpenRecordset(mysql)
If Not (myrs.EOF And myrs.BOF) Then
'获得当前日期
MYDATE = Format(myrs!DATEANDTIME, "YYYY-MM-DD")
MYTIME = Format(myrs!DATEANDTIME, "HH:MM:SS")
End If
myrs.Close
Set myrs = Nothing
'**************************************** '表头处理 exSheet.PageSetup.CenterHeader = "&B&20成品入库汇总" & Chr(13) & Chr(10) & Chr(13) & Chr(10) exSheet.PageSetup.RightHeader = Chr(13) & Chr(13) & Chr(10) & "汇总日期:" & MYDATE & " " & MYTIME & Chr(13) & Chr(10)
If chkTime.Value = 1 Then
exSheet.PageSetup.LeftHeader = Chr(13) & Chr(13) & Chr(10) & "汇总范围:" & dtpRQStart.Value & "——>" & dtpRQEnd.Value Else
exSheet.PageSetup.LeftHeader = Chr(13) & Chr(13) & Chr(10) & "汇总范围:" & dtpRQStart.Value & "——>" & dtpRQEnd.Value & " " & dtpTimeStart.Value & "——>" & dtpTimeEnd.Value
End If
'表尾处理
exSheet.PageSetup.LeftFooter = "生产部门:" & cblBM.Text
exSheet.PageSetup.CenterFooter = "入库仓库:" & cblCK.Text
exSheet.PageSetup.RightFooter = "第&P页 共&N页" '打印缩放
exSheet.PageSetup.Zoom = 110 '打印处理 Screen.MousePointer = vbDefault If A = vbYes Then exWbook.Save
exApp.Visible = True
exWbook.PrintOut
Else
exWbook.Save
exApp.Visible = True
exSheet.PrintPreview End If
exApp.Visible = False exWbook.Close True
exApp.Quit
Set exSheet = Nothing
Set exWbook = Nothing
Set exApp = Nothing
Exit Sub
errhandle: Call MyErrHandle
If Not (exWbook Is Nothing) Then
exWbook.Close True
exApp.Quit
Set exSheet = Nothing
Set exWbook = Nothing
Set exApp = Nothing
End If
大家请看,以上是一段打印过程,其中调用的错误处理过程只不过是使用MSGBOX显示错误号和描述而已,多一句代码都没有。我所不能理解的是,我其它部分的程序使用正常,可以正常杀掉EXCEL进程,唯独这个过程不行,而且发生错误时反倒可以杀掉,正常运行释放资源却不能杀掉EXCEL进程,导致二次打印错误,真是奇怪啊。请各位帮忙查查看,出出主意。分数还会陆续加的。
http://www.csdn.net/Expert/TopicView1.asp?id=948562
If Err.Number <> 0 Then '没有
bolExcelWasNotRunning = True
Set oExcel = CreateObject("Excel.Application") 'run it
End If
Err.Clear 'Clear Err object in case error occurred.
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
可以解决你的问题;
我试过所有的方法君不能杀掉EXCEL进程,所以只好采用上面的方法望对你有帮助。
和Set oExcel = GetObject(, "Excel.Application")
exWbook.RunAutoMacros xlAutoClose
hhjjhjhj(大头)兄,你的方法我试过了,无效,依旧是1004错误
if err.number<>0 then
goto
关注!!!
各位继续讨论,如果我们找到了答案,相信会帮助一大批朋友,加油!
coolsky(天心) 兄,我知道可以不用SELECTION,我只想知道,为什么使用了就不行?如果不解决这个问题,可能以后应用宏的时候还会碰到这种问题的,毕竟不能总是用笨办法吧。
各位大侠,UPUP!
Range("L3").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-9]:RC[-1])"
Range("L3").Select
Selection.AutoFill Destination:=Range("L3:L" & CurrentExcelRow), Type:=xlFillDefault
Range("L3:L" & CurrentExcelRow).Select其中的第二段,如果不用SELECTION,怎么使用AUTOFILL呢?我需要这个公式应用于合计列的每一行