Solved

Workbooks.open in Excel 2003 VBA in Window XP

Posted on 2011-03-21
21
989 Views
Last Modified: 2012-05-11
Dear Experts,
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 string
wkdir="C:\"
k=0
Do until k=100
openfileworkname= k&".htm"

 If CheckFileAvailable(wkdir & openworkbookname) = "Fileinfree" Then
'Sleep(100)
'Application.EnableEvents = False
'ClearClipboard
Excel.Application.Workbooks.Open filename:=wkdir & openworkbookname, ReadOnly:=False
Doevent or ...
'Application.EnableEvents = True 
Endif

k=k+1

Loop
End Sub


Function 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 If
End Function
Public Function ClearClipboard()
    OpenClipboard (0&)
    EmptyClipboard
    CloseClipboard
End Function

Open in new window

0
Comment
Question by:duncanb7
  • 15
  • 6
21 Comments
 
LVL 29

Accepted Solution

by:
leonstryker earned 500 total points
ID: 35180613
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
0
 
LVL 13

Author Comment

by:duncanb7
ID: 35180806
The debug.print error message
"Open method ('workbooks", fail at "C:\33.htm") "

Sometimes, 33.htm is working, sometimes are not

0
 
LVL 13

Author Comment

by:duncanb7
ID: 35180826
After the error message, Excel will stop the rest of VBA program and ask me to report the error to
Microsoft, and then popup a menu about doing rever on the 33.htm file ...and then everything needs
to restart again by myself
0
 
LVL 13

Author Comment

by:duncanb7
ID: 35180864
After the error message, Excel will stop the rest of VBA program and ask me to report the error to
Microsoft, and then popup a window bar about  "Microsoft office Excel now is doing recover  your document, 33.htm file ...and then ask you to reopen the VBA program file.
0
 
LVL 13

Author Comment

by:duncanb7
ID: 35180914
And I try to open 33.htm file in Excel individually, and the file is normal, nothing is corrupted

Inside of 33.htm, just some data and one chart only
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 35181301
Is it just with that file or others as well?
0
 
LVL 13

Author Comment

by:duncanb7
ID: 35181347
All files is okay,yes
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 35181485
No, I mean does the error occur with other files as well?
0
 
LVL 13

Author Comment

by:duncanb7
ID: 35181705
yes, it is random, sometimes on 33.htm, sometimes on 87.htm.
but sometimes 33.htm and 87.htm is no problem.
I am doing it on 2007 instead of 2003 Excel, it is passed on Excel 2007 but the speed is really slow
So I insist to solve the prolbem in Excel 2003
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 35181991
Can you upload a sample file (33.html for example). I would like to run some sample code and see what happens.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 13

Author Comment

by:duncanb7
ID: 35182343
I found something when I am doing this it is passed for 100 files just open and close without other code
like
  If CheckFileAvailable(wkdir & openworkbookname) = "Fileinfree" Then
Excel.Application.Workbooks.Open filename:=wkdir & openworkbookname, ReadOnly:=False
''''other code here is deleted
Endif

it seems ohter code is affecting the next file opening

0
 
LVL 29

Expert Comment

by:leonstryker
ID: 35182655
Perhaps, try adding DoEvents to give it time to process things. BTW, do these files need to be html, and have you tried opeing them with OpenText method?
0
 
LVL 13

Author Comment

by:duncanb7
ID: 35182689
it is htm file format, why we can use opentext command ? and how to use it ?
0
 
LVL 13

Author Comment

by:duncanb7
ID: 35183011
I try it that is also fail with opentext. When I add activeworkbook.activate after open command, it is solved the issue
but it could run third times of 100 files opening it means when reset the VBA editor and F8 press to run for three
times, the same problem of open method object error is came out again as before. So I quefore,it excel application and
then do the same as before, the error is happen again when third times  sub of openfile() looping from reset and run


Now I believe the problem is related to different the memory usage  in Excel bewteen under XP and Vista
since I try the looping.

Sub openfile()
Do until k=100
openfileworkname= k&".htm"
If CheckFileAvailable(wkdir & openworkbookname) = "Fileinfree" Then
Excel.Application.Workbooks.Open filename:=wkdir & openworkbookname, ReadOnly:=False
activeworkbook.activate
''''other code here is deleted
Endif

k=k+1
loop
End Sub

Anyway suggestion to clear memory as start a new Excel application ?
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 35184556
Try this:

Dim wkb As Workbook

Set wkb= Workbooks.Open(filename:=wkdir & openworkbookname, ReadOnly:=False)

wkb.Activate


0
 
LVL 13

Author Comment

by:duncanb7
ID: 35186499
I tried that before but it fail
0
 
LVL 13

Author Closing Comment

by:duncanb7
ID: 35199014
Thanks for your reply and fianlly
it works after I put
activeworkbook.activate for
some copy and paste code
Without activate, Excel VBA doesn't
what is content to be pasted to or where, So make it hidden unreport
error and then open next file will
have issue.
0
 
LVL 13

Author Comment

by:duncanb7
ID: 35199042
In other words, open ommand is issue , just copy and paste between two workbooks is not
well done without activate function help so make the system error udring nect file opening.
Now it can open many files such as 1000 files without any issue.
0
 
LVL 13

Author Comment

by:duncanb7
ID: 35199049
In other words, open command is "NOT" issue , just copy and paste between two workbooks is not
well done without activate function help so make the system error during next  file opening.
Now it can open many files such as 1000 files without any issue.
0
 
LVL 13

Author Comment

by:duncanb7
ID: 36256809
For memo only:

The final solution to this thread is:

Conclusion:
1- The main roots cause of the thread it is because using integer for part of file name
and we can check by debug.print len(k) that will be 2 if k=1
and so the filename will  be " 1.htm", (the length of filename is 6) instead of  expected "1.htm" (the length of
filename is 5). and the " 1.htm" will NOt be open since no such file
So we had better to CStr(k) if k integer is used.

2- Because when using
activeworkbook.save
activeworkbook.close false
The file close action might NOT be completed if the large size file is saving
We need put a loop for checking like this as follows in order to avoid unstable runtime error

ActiveWorkbook.Save
Do Until CheckFileAvailable(wkdir & openworkbookname) = "Fileisfree"
ActiveWorkbook.Close False
Loop
Do Until ActiveWorkbook.Name <> openworkbookname
ActiveWorkbook.Close False
Loop

Duncan

0
 
LVL 13

Author Comment

by:duncanb7
ID: 36264433
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
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now