- 帖子
- 4901
- 主題
- 44
- 精華
- 24
- 積分
- 4916
- 點名
- 182
- 作業系統
- Windows 7
- 軟體版本
- Office 20xx
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台北
- 註冊時間
- 2010-4-30
- 最後登錄
- 2025-7-27
               
|
18#
發表於 2012-8-10 21:34
| 只看該作者
回復 17# jsleee
是某些固定檔案會發生這樣的情形嗎?
如果是,我猜可能是檔案開啟時間較長
結果程式先跑到檢視程式碼,然後才完成檔案開啟
試試用sleep停頓數秒後再繼續往下執行試試看是否有改善- Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
- Sub 開啟檔案()
- Dim CurrentPath As String '儲存目前檔案目錄
- Dim OpenFN As String '讀取到的檔案名稱
- Dim FNExt As String '檔案副檔名
- Dim MyBook As Workbook
- FN = ActiveWorkbook.Name
- CurrentPath = Range("B1") '如果有設定以設定為主
- FNExt = Range("b2") '查詢檔案類型
- If Trim(CurrentPath) = "" Then
- CurrentPath = Excel.ActiveWorkbook.Path
- End If
-
- n = 0
- Sheets("trans").Cells.Delete '將之前的結果清除
- If Right(CurrentPath, 1) = "\" Then
- OpenFN = Dir(CurrentPath & FNExt, vbDirectory)
- OpenFNTime = CurrentPath
- Else
- OpenFN = Dir(CurrentPath & "\" & FNExt, vbDirectory)
- OpenFNTime = CurrentPath & "\"
- End If
-
- While OpenFN <> ""
- If OpenFN <> ActiveWorkbook.Name Then '這個檔案不要顯示
- If OpenFN <> "." And OpenFN <> ".." Then
- n = n + 1
- fs = OpenFNTime & OpenFN
- Workbooks(FN).Sheets("trans").Cells(n, 7).Value = fs
- Workbooks.Open(Filename:=OpenFNTime & OpenFN _
- , Password:="msign").RunAutoMacros Which:=xlAutoOpen
- Sleep 200 '暫停2秒
- Pwd = "1234" '自行修改密碼
- CId = IIf(Application.Version = 11, 16, 42)
- Set vbProj = ActiveWorkbook.VBProject
- If vbProj.Protection <> 1 Then GoTo 10
- SendKeys Pwd & "~", True
- Application.CommandBars(CId).FindControl(ID:=1561, recursive:=True).Execute
- End If
- End If
- 10
- OpenFN = Dir() '讀取下一個檔案
- Wend
- SendKeys "%{F11}" '離開VBE
- ' Workbooks(FN).Close savechanges:=False
- End Sub
複製代碼 |
|