how to avoid file re-bulid message in Excel VBA

Dear Expert,

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
LVL 13
duncanb7Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Zack BarresseCEOCommented:
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
Option Explicit

Sub 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 Sub

Public Function ISWBOPEN(wbName As String) As Boolean
'Originally found written by Jake Marx
    On Error Resume Next
    ISWBOPEN = Len(Workbooks(wbName).Name)
End Function

Public 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 With
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
duncanb7Author Commented:
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
Zack BarresseCEOCommented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

duncanb7Author Commented:
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.

duncanb7Author Commented:
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
Zack BarresseCEOCommented:
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
clarkscottCommented:
SECURITY WARNING:
Click TOOLS - MACRO - SECURITY - SELECT LOW.

Scott C
Zack BarresseCEOCommented:
?  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
duncanb7Author Commented:
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
Zack BarresseCEOCommented:
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
duncanb7Author Commented:
Finally is solved and I don't know
why the problem is gone today
suddenly
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Outlook

From novice to tech pro — start learning today.