新聞財經娛樂♬資訊 News Economy

Recently, I have switched to my system Window Vista back to Window XP.
And re-run my VBA program in Window XP Excel 2003, there is a lot of problem which is never happen before in Window Vista running 2003 Excel. Now A lot problem is solved besides workbooks.open command
in vba, I need to loop and open 100 files and write data and then save and close file and open next file
until 100 files is done,
During file open loop, the error is came out randomly during file opening (I mean not at an specified file name). I want to
know there is any syntax error on my coding. The error is always at "excel.Application.workbooks.open",and
the Excel ask me do I need to report to the bug to microsoft ? and then try to do recovery on the file expected to be
opened with fail. But I found no any file is damaged by VBA program when open it in other standalone Excel program

I try to put delay after complete file ready checking function and before Open command but it is still in problem
and also put Application.EnableEvents = False before  Workbooks.open comand, it is also fail,
and finally I try clear up the clipboard before any file open  that also fail.

The error is not stable but always report at the same workbooks.open location, it happens sometimes  after 10 file is opened, sometimes, 30 file is opened, and sometimes,sometimes  it will happen when third opening

Any Excel 2007 system I need to do before I pass the error. Why the performance is big difference in workbooks.open
between Windows Vista and XP's 2003 Excel VBA ?

Please advise

Duncan

Sub openfile()Dim wkdir as string, openworkbookname as stringwkdir="C:"k=0Do until k=100openfileworkname= k&".htm" If CheckFileAvailable(wkdir & openworkbookname) = "Fileinfree" Then'Sleep(100)'Application.EnableEvents = False'ClearClipboardExcel.Application.Workbooks.Open filename:=wkdir & openworkbookname, ReadOnly:=FalseDoevent or ...'Application.EnableEvents = TrueEndifk=k+1LoopEnd SubFunction CheckFileAvailable(sFilePath)    On Error Resume Next    Dim oFS    Dim oFile    Set oFS = CreateObject("Scripting.FileSystemObject")    Set oFile = oFS.OpenTextFile(sFilePath, 8, False)    If Err.Number <> 0 Then        CheckFileAvailable = "Fileinuse"      ' Debug.Print "FileinUse"    Else        CheckFileAvailable = "Fileisfree"       Debug.Print "FileisFree"    End IfEnd FunctionPublic Function ClearClipboard()    OpenClipboard (0&)    EmptyClipboard    CloseClipboardEnd Function                                  

1:2:3:4:5:6:7:8:9:10:11:12:13:14:15:16:17:18:19:20:21:22:23:24:25:26:27:28:29:30:31:32:33:34:35:36:37:38:39:40:41:

3- The copy and paste between workbooks activate  and using range need to modify in other ways
'Workbooks(openworkbookname).Sheets(sheetname).Range("d" & i).Copy
'Workbooks(dataworkbookname).Sheets(dataworksheetname).Activate
' Workbooks(dataworkbookname).Sheets(dataworksheetname).Range("j" & temprow).PasteSpecial
'Workbooks(openworkbookname).Sheets(sheetname).Activate
' Workbooks(openworkbookname).Sheets(sheetname).Range(Range("o" & i), Range("t" & i)).Copy
' Workbooks(dataworkbookname).Sheets(dataworksheetname).Activate
' Workbooks(dataworkbookname).Sheets(dataworksheetname).Range(Range("k" & temprow), Range("p" & temprow)).PasteSpecial
' Workbooks(openworkbookname).Sheets(sheetname).Activate

Try this:

On Error Resume Next
Workbooks.Open filename:=wkdir & openworkbookname, ReadOnly:=False
If Err.Number <> 0 Then
    Debug.Print wkdir & openworkbookname
    Debug.Print Err.Description
    Err.Clear
End if

What this should do is allow to process the rest of the files while noting the files which generate the error, and the error itself. Lets see how random those errors are.

Leon