Ask Question Forum:
Model Library:2025-02-08:A.I. model is online auto reply
C
O
M
P
U
T
E
R
2
8
Show
#
ASK
RECENT
←
- Underline
- Bold
- Italic
- Indent
- Step
- Bullet
- Quote
- Cut
- Copy
- Paste
- Table
- Spelling
- Find & Replace
- Undo
- Redo
- Link
- Attach
- Clear
- Code
Below area will not be traslated by Google,you can input code or other languages
Hint:If find spelling error, You need to correct it,1 by 1 or ignore it (code area won't be checked).
X-position of the mouse cursor
Y-position of the mouse cursor
Y-position of the mouse cursor
Testcursor
caretPos
Attachment:===
Asked by duncanb7
at 2024-03-21 04:01:42
ModifyPoint:500 Replies:21 POST_ID:828446USER_ID:11059
Topic:
Hypertext Markup Language (HTML);Visual Basic Programming;Microsoft Office Suite
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
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:
Author: duncanb7 replied at 2024-07-27 10:44:56
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
'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
Author: duncanb7 replied at 2024-07-26 09:54:29
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
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
Author: duncanb7 replied at 2024-03-23 07:40:15
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.
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.
Author: duncanb7 replied at 2024-03-23 07:39:19
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.
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.
Author: duncanb7 replied at 2024-03-23 07:36:44
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
'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
Author: duncanb7 replied at 2024-03-21 21:08:26
I tried that before but it fail
Expert: leonstryker replied at 2024-03-21 14:14:10
Try this:
Dim wkb As Workbook
Set wkb= Workbooks.Open(filename:=wkdir & openworkbookname, ReadOnly:=False)
wkb.Activate
Dim wkb As Workbook
Set wkb= Workbooks.Open(filename:=wkdir & openworkbookname, ReadOnly:=False)
wkb.Activate
Author: duncanb7 replied at 2024-03-21 10:54:17
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 ?
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 ?
Author: duncanb7 replied at 2024-03-21 10:23:15
it is htm file format, why we can use opentext command ? and how to use it ?
Expert: leonstryker replied at 2024-03-21 10:18:10
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?
Author: duncanb7 replied at 2024-03-21 09:39:43
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
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
Expert: leonstryker replied at 2024-03-21 09:03:48
Can you upload a sample file (33.html for example). I would like to run some sample code and see what happens.
Author: duncanb7 replied at 2024-03-21 08:40:11
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
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
Expert: leonstryker replied at 2024-03-21 08:19:46
No, I mean does the error occur with other files as well?
Author: duncanb7 replied at 2024-03-21 08:05:27
All files is okay,yes
Expert: leonstryker replied at 2024-03-21 08:00:07
Is it just with that file or others as well?
Author: duncanb7 replied at 2024-03-21 07:19:53
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
Inside of 33.htm, just some data and one chart only
Author: duncanb7 replied at 2024-03-21 07:13:46
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.
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.
Author: duncanb7 replied at 2024-03-21 07:08:17
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
Microsoft, and then popup a menu about doing rever on the 33.htm file ...and then everything needs
to restart again by myself
Author: duncanb7 replied at 2024-03-21 07:05:48
The debug.print error message
"Open method ('workbooks", fail at "C:33.htm") "
Sometimes, 33.htm is working, sometimes are not
"Open method ('workbooks", fail at "C:33.htm") "
Sometimes, 33.htm is working, sometimes are not
Accepted Solution
Expert: leonstryker replied at 2024-03-21 06:40:05
500 points GOOD
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
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
