VBA儲存與關閉excel問題

georgectw

unread,

Jul 27, 2005, 9:45:01 PM7/27/05

to

Sub restart()

Application.DisplayAlerts = False
Call by_model
Call summary
Call Upload
ActiveWorkbook.Save
Application.Quit

End Sub

剛開始在每日排定的工作中執行都ok
但最近只要執行完save後, 就hang住不動, 需要強制關閉....
請問各位先進, 是否有解決的方法.謝謝!

chijanzen

unread,

Jul 28, 2005, 12:36:03 AM7/28/05

to

你好:

從你的程式碼上看來有一些問題，從這些問題上解決應該可以改善

1.ActiveWorkbook.Save 檔案存檔後未關閉該檔案而是直接關閉Excel應用程式，這樣的作法不對
因為如果檔案很大儲存的時間可能要3~4秒鐘，會造成檔案尚未儲存完成而程序已經要關閉Excel應用程式

2.Application.DisplayAlerts = False 關閉警告和訊息後，一定要在程序結束前恢復

請試試改成以下程式碼:

Application.DisplayAlerts = False
Call by_model

Call Summary
Call Upload
ActiveWorkbook.Close savechanges:=True
Application.DisplayAlerts = True
'等待1秒鐘後關閉Excel 應用程式
Application.Wait (Now + TimeValue("0:00:01"))
Application.Quit

因為沒有你的檔案所以只能假設是以上的問題，如有錯誤請見諒

--
天行健君子以自強不息

http://www.vba.com.tw/plog/

"georgectw" 來函：

georgectw

unread,

Jul 28, 2005, 4:46:02 AM7/28/05

to

chijanzen您好:

我有照您提供的方式改了, 但是結果還是一樣.
"我懷疑是我的upload那隻程式
因為我會將撈出的資料利用excel轉成html上拋到web server"

只要我把不call upload, 就可以正常作動, 也可以關掉excel.
先謝謝您, 還有解嗎....^^

"chijanzen" 來函：

chijanzen

unread,

Jul 28, 2005, 5:11:05 AM7/28/05

to

你好:

可能要看看 upload 程式怎麼寫了?

或許可以加上一句 DoEvents 試試

Application.DisplayAlerts = False
Call by_model
Call Summary

DoEvents

Call Upload
ActiveWorkbook.Close savechanges:=True
Application.DisplayAlerts = True
'等待1秒鐘後關閉Excel 應用程式
Application.Wait (Now + TimeValue("0:00:01"))
Application.Quit

真不行的話就要看看你的upload 程式了

georgectw

unread,

Jul 28, 2005, 6:12:01 AM7/28/05

to

upload來了^^

Sub Upload()

Sheets("Chart").Select

ActiveSheet.ChartObjects("圖表 2").Activate
ActiveChart.ChartArea.Select
ActiveSheet.ChartObjects("圖表 2").Activate
ActiveWorkbook.PublishObjects.Add(xlSourceChart, "H:\Cycle
time(All).htm", _
"Chart", "圖表 2", xlHtmlStatic, "AM0750 -cycle_time_george_4582",
"").Publish ( _
True)

ActiveSheet.ChartObjects("圖表 3").Activate
With ActiveWorkbook.PublishObjects("cycle_time_george_6091")
.HtmlType = xlHtmlStatic
.Filename = "H:\Cycle time(8A).htm"
.Publish (False)
End With
ChDir "H:\"

ActiveSheet.ChartObjects("圖表 4").Activate
ActiveWorkbook.PublishObjects.Add(xlSourceChart, "H:\Cycle
time(8B).htm", _
"Chart", "圖表 4", xlHtmlStatic, "AM0750 -cycle_time_george_31886",
"").Publish ( _
True)
ChDir "H:\"

ActiveSheet.ChartObjects("圖表 5").Activate
ActiveWorkbook.PublishObjects.Add(xlSourceChart, "H:\Cycle
time(G4).htm", _
"Chart", "圖表 5", xlHtmlStatic, "AM0750 -cycle_time_george_2346",
"").Publish ( _
True)

Sheets("CT_by_Model").Select
ActiveWorkbook.PublishObjects.Add(xlSourceSheet, "H:\CT By Model.htm", _
"CT_by_Model", "", xlHtmlStatic, "AM0750 -cycle_time_george_11023",
"").Publish _
(True)

End Sub

"chijanzen" 來函：

chijanzen

unread,

Jul 28, 2005, 6:47:04 AM7/28/05

to

你好

看了一下程式碼沒發現有什麼問題，也沒能幫什麼忙

或許可以一段一段的測試問題在哪裡?每一句加 Stop

Sheets("Chart").Select
ActiveSheet.ChartObjects("圖表 2").Activate
ActiveChart.ChartArea.Select
ActiveSheet.ChartObjects("圖表 2").Activate
ActiveWorkbook.PublishObjects.Add(xlSourceChart, "H:\Cycle

time(8A).htm", _
"Chart", "圖表 2", xlHtmlStatic, "cycle_time_george_6091",
"").Publish ( _
True)
Stop

ActiveSheet.ChartObjects("圖表 3").Activate
With ActiveWorkbook.PublishObjects("cycle_time_george_6091")
.HtmlType = xlHtmlStatic
.Filename = "H:\Cycle time(8A).htm"
.Publish (False)
End With
ChDir "H:\"

Stop
ActiveSheet.ChartObjects("圖表 4").Activate
ActiveWorkbook.PublishObjects.Add(xlSourceChart, "H:\Cycletime(8B).htm", _

"Chart", "圖表 4", xlHtmlStatic, "AM0750
-cycle_time_george_31886", "").Publish ( _
True)
ChDir "H:\"

georgectw

unread,

Jul 28, 2005, 9:39:55 PM7/28/05

to

雖然有點小失望, 但是還是謝謝你.^^

"georgectw" 來函：

CANDAN

unread,

Aug 25, 2005, 4:04:08 AM8/25/05

to

可能是 memory 問題

Dim proc As System.Diagnostics.Process
For Each proc In System.Diagnostics.Process.GetProcesses
If proc.ProcessName = "EXCEL" Then proc.Kill()
Next
--
CANDAN

"georgectw" 來函：