Ask Question Forum:
Model Library:2025-02-08 Updated:A.I. model is online for auto reply question page
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-29 08:27:43
ModifyPoint:500 Replies:11 POST_ID:828459USER_ID:11059
Topic:
Outlook Groupware Software;Visual Basic Programming;Microsoft Excel Spreadsheet Software
I have many files to open and close in Excel 2003 VBA, sometimes I experience
run-tim error and try to re-do the job by error resume next or goto , but
sometimes, Excel will re-bulid the file failed at error of "the open method of file failed"
so we need to pass the dialog window of "Microsoft Office Excel" to click yes
button and the code is hang up even you using error resume next.
The case is similar for avoid the security warning. Any website or suggestion for
download the add-in or com add-on for Excel 2003 VBA to avoid such warning window comes out
so I can re-start the code
Please advise
Duncan
run-tim error and try to re-do the job by error resume next or goto , but
sometimes, Excel will re-bulid the file failed at error of "the open method of file failed"
so we need to pass the dialog window of "Microsoft Office Excel" to click yes
button and the code is hang up even you using error resume next.
The case is similar for avoid the security warning. Any website or suggestion for
download the add-in or com add-on for Excel 2003 VBA to avoid such warning window comes out
so I can re-start the code
Please advise
Duncan
Author: duncanb7 replied at 2024-03-30 06:28:45
Can we see your full code? Again, we can't do much without that, except give you a patch for your test code, which probably won't help you much in the end.
Zack
Zack
Expert: Zack Barresse replied at 2024-03-29 10:29:37
Can we see your full code? Again, we can't do much without that, except give you a patch for your test code, which probably won't help you much in the end.
Zack
Zack
Author: duncanb7 replied at 2024-03-29 10:28:29
Click TOOLS - MACRO - SECURITY - SELECT LOW. that is
same as before, doesn't help at all
After I check, if the file size is small that is better,the open file lopp is passed
my each file is around 200K in which it
has chart object
same as before, doesn't help at all
After I check, if the file size is small that is better,the open file lopp is passed
my each file is around 200K in which it
has chart object
Expert: Zack Barresse replied at 2024-03-29 10:03:22
? If the OP us running a macro, why would they need to lower their security settings from Medium to Low? Asumming it's at Medium. Don't really see the logic here, Scott. Can you fill me in?
Zack
Zack
Expert: clarkscott replied at 2024-03-29 10:01:50
SECURITY WARNING:
Click TOOLS - MACRO - SECURITY - SELECT LOW.
Scott C
Click TOOLS - MACRO - SECURITY - SELECT LOW.
Scott C
Expert: Zack Barresse replied at 2024-03-29 09:37:01
Memory doesn't quite work like that. While running a sub routine, even opening and closing workbooks can take up memory. It shouldn't be a whole lot of process memory though, but we're really only worried about the memory the VBA is using.
Can you post the code you're actually having problems with? I don't want to post code to help you in your test, then find out you need more for your "actual" project. It makes it twice as much work for us.
Zack
Can you post the code you're actually having problems with? I don't want to post code to help you in your test, then find out you need more for your "actual" project. It makes it twice as much work for us.
Zack
Author: duncanb7 replied at 2024-03-29 09:33:45
I try to use dim obj as workbook and set obj-workbooks.open()
and set obj=nothing that doesn't help at all
Yes, it is many file opening but it is runing one by one, one open and close and next.
So memory should be no problem at all, I use'Debug.Print "Microsoft Excel is currently using " & _
Application.MemoryUsed & " bytes" to check Excel memory usage that is only 160k byte
Sub opentest()
Dim k As Integer
dim obj as workbook
k = 0
Do Until k = 50
On Error Resume Next
set obj=Workbooks.Open (filename:="D:myexcel" & K & ".htm", ReadOnly:=False)
If Err.Number <> 0 Then
Debug.Print Err.Description
Err.Clear
Exit Sub
End If
Err.Clear
ActiveWorkbook.Activate
'''''''''''''do some calcution here
ActiveWorkbook.Save
ActiveWorkbook.Close False
k = k + 1
set obj=nothing
Loop
End Sub
and set obj=nothing that doesn't help at all
Yes, it is many file opening but it is runing one by one, one open and close and next.
So memory should be no problem at all, I use'Debug.Print "Microsoft Excel is currently using " & _
Application.MemoryUsed & " bytes" to check Excel memory usage that is only 160k byte
Sub opentest()
Dim k As Integer
dim obj as workbook
k = 0
Do Until k = 50
On Error Resume Next
set obj=Workbooks.Open (filename:="D:myexcel" & K & ".htm", ReadOnly:=False)
If Err.Number <> 0 Then
Debug.Print Err.Description
Err.Clear
Exit Sub
End If
Err.Clear
ActiveWorkbook.Activate
'''''''''''''do some calcution here
ActiveWorkbook.Save
ActiveWorkbook.Close False
k = k + 1
set obj=nothing
Loop
End Sub
Author: duncanb7 replied at 2024-03-29 09:26:31
it is product files, daily I work for it around and save it and sent it to
my domian site so I pick to use htm format. The opentest()
is for debugging in small code but similar error happen in my
original code that is huge vba code.
my domian site so I pick to use htm format. The opentest()
is for debugging in small code but similar error happen in my
original code that is huge vba code.
Expert: Zack Barresse replied at 2024-03-29 09:20:07
Opening 50 workbooks could consume a large amount of memory. And they're not Excel files, they're browser files (.htm). Can you give us a little more scope on what it is you are trying to do? You are referring to this as a test. A test for what? Details, give us lots of details. Without it we won't know the true scope of this and will have a much more difficult time in helping you with a solution.
Zack
Zack
Author: duncanb7 replied at 2024-03-29 09:17:27
Dear,
The problem is sometimes, the marco is passed but sometimes are not.
it is randomly to have open method of fail object file error at 1.htm"
sometimes, error is happen at openfile at 10.htm , sometimes at 29 htm,
When it fail, microsoft office excel title window will comes and ask you
whether need to report it ot MS , select yes to report and no for not report.
And then it will rebuild the fail file and quit my VBA program.
So feel it is annoying, after I check whether it is re-building the error file that
is no much different. I mean actually the error file is no problem at all for my application
My code is attach,I would like to avoid those MS warning or ask yes or no button dialog box
application.displayalert= false is not wokring, any suggestion ?
Sub opentest()
Dim k As Integer
k = 0
Do Until k = 50
On Error Resume Next
Application.Workbooks.Open filename:="D:myexcel" & K & ".htm", ReadOnly:=False
'SourceWorkbook.Activate
If Err.Number <> 0 Then
Debug.Print Err.Description
Err.Clear
Exit Sub
End If
Err.Clear
'Do Until ActiveWorkbook.Name = k & ".htm"
'Loop
'ActiveWorkbook.Activate
'
ActiveWorkbook.Activate
ActiveWorkbook.Save
ActiveWorkbook.Close False
k = k + 1
Loop
End Sub
The problem is sometimes, the marco is passed but sometimes are not.
it is randomly to have open method of fail object file error at 1.htm"
sometimes, error is happen at openfile at 10.htm , sometimes at 29 htm,
When it fail, microsoft office excel title window will comes and ask you
whether need to report it ot MS , select yes to report and no for not report.
And then it will rebuild the fail file and quit my VBA program.
So feel it is annoying, after I check whether it is re-building the error file that
is no much different. I mean actually the error file is no problem at all for my application
My code is attach,I would like to avoid those MS warning or ask yes or no button dialog box
application.displayalert= false is not wokring, any suggestion ?
Sub opentest()
Dim k As Integer
k = 0
Do Until k = 50
On Error Resume Next
Application.Workbooks.Open filename:="D:myexcel" & K & ".htm", ReadOnly:=False
'SourceWorkbook.Activate
If Err.Number <> 0 Then
Debug.Print Err.Description
Err.Clear
Exit Sub
End If
Err.Clear
'Do Until ActiveWorkbook.Name = k & ".htm"
'Loop
'ActiveWorkbook.Activate
'
ActiveWorkbook.Activate
ActiveWorkbook.Save
ActiveWorkbook.Close False
k = k + 1
Loop
End Sub
Accepted Solution
Expert: Zack Barresse replied at 2024-03-29 08:59:07
500 points GOOD
Hi there,
Care to post your code? And if you could tell us what line is erroring out, that would help too, along with the error message details (i.e. number, general description).
When it comes to opening files, there are really two methods. You know the file and location already, or you don't now the file or location yet. If you don't know, you use the GetOpenFilename() method (there are other methods, albeit, but that is probably the simplest one). You should also check if the file is open or not already. Here is some generic code to open a couple of files where you know the location is, and set them to different variables. It also closes them when done if they were closed when you started. Since we don't know any more specifics, this is kind of a generic guess...
Regards,
Zack
Care to post your code? And if you could tell us what line is erroring out, that would help too, along with the error message details (i.e. number, general description).
When it comes to opening files, there are really two methods. You know the file and location already, or you don't now the file or location yet. If you don't know, you use the GetOpenFilename() method (there are other methods, albeit, but that is probably the simplest one). You should also check if the file is open or not already. Here is some generic code to open a couple of files where you know the location is, and set them to different variables. It also closes them when done if they were closed when you started. Since we don't know any more specifics, this is kind of a generic guess...
Regards,
Zack
Option ExplicitSub OpenWorkbooks() Dim aWBs(1 To 3, 1 To 2) As String, i As Long, WB As Workbook, bWBOpen As Boolean aWBs(1, 1) = "C:YOURFILEPATH1_HERE" aWBs(1, 2) = "YOURFILENAME1_HERE" aWBs(2, 1) = "C:YOURFILEPATH2_HERE" aWBs(2, 2) = "YOURFILENAME2_HERE" aWBs(3, 1) = "C:YOURFILEPATH3_HERE" aWBs(3, 2) = "YOURFILENAME3_HERE" Call TOGGLEEVENTS(False) For i = LBound(aWBs) To UBound(aWBs) Set WB = Nothing If ISWBOPEN(aWBs(i, 2)) = True Then Set WB = Workbooks(aWBs(i, 2)) bWBOpen = True Else Set WB = Workbooks.Open(aWBs(i, 1) & aWBs(i, 2)) bWBOpen = False End If '///////////////////////// 'DO STUFF TO WORKBOOK HERE '///////////////////////// If bWBOpen = False Then WB.Close False 'False for not saving, change to True to save on close End If Next i Call TOGGLEEVENTS(True)End SubPublic Function ISWBOPEN(wbName As String) As Boolean'Originally found written by Jake Marx On Error Resume Next ISWBOPEN = Len(Workbooks(wbName).Name)End FunctionPublic Sub TOGGLEEVENTS(blnState As Boolean)'Originally written by Zack Barresse With Application .DisplayAlerts = blnState .EnableEvents = blnState .ScreenUpdating = blnState If blnState Then .CutCopyMode = False If blnState Then .StatusBar = False End WithEnd Sub 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:42:43:44:45:46:47:48:49:50:51:52:53:54:55:56:57:
