Error handling for "could not open <filename>"

[DianeA]

I have a macro which I have used the On error handling and executes a specific error routine whenever it obtains Error 1004. The error 1004 routine runs correctly but I get an additional msg "Could not open <filename>"... how do i get rid of this message. Is there a specific code for it as well?

The file I am trying to open is on a website and is posted sometime during a specific day (ie. tuesdays)... so if we try to open it too soon on the tuesday... it may not yet exist.

Thanks
Diane

 

[DianeA]

Maybe i didn't explain this very well:

We access a file on the internet weekly which is posted on tuesdays but not necessarily the same time. So when we run the routine to open the file it may not be there.

In this case, the message "could no open <filename>" appears and doesn't seem to get trapped by my ON ERROR handling. After clicking OK a second error appears .. 1004 basically says it can't open that file and the possible reasons.

The error 1004 does get trapped by the ON ERROR handling.

Code is below:

On Error GoTo ErrorHandler

...code

ErrorHandler:
Select Case Err.Number

Case 91
MsgBox ("Date not found .. resume")
Resume Next
Case 1004
MsgBox ("File " & "is not available. Either confirm the correct retrieval date or check back later")
Resume Next
Case Else
MsgBox (Err.Number & ", " & Err.Description)
End Select

Hope this explains it better

 

[PHV]

What is the code trying to open the file ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DianeA]

Path = Website & Filename

Workbooks.Open Filename:=Path
Workbooks(Filename).Activate

 

[PHV]

What about this ?
Dim wb As Workbook
Set wb = Workbooks.Open(Filename:=Path)
wb.Activate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DianeA]

Same error message "could no open <filename>" appears

 

[DianeA]

I don't know if this would be totally correct to do but using Application.DisplayAlerts = False before the specific statements appears to get rid of the messages. I can see this could be dangerous.

It would be nice to filter out specific alert messages.

 

[VulcanJedi]

Are you supposed to have all those resume nexts after each case?
I was going to suggest using the FileSystemObject but doubt that works pulling from a website, have u used the macro recorder?

Tranpkp

 

[DianeA]

The main body of the routine actually tests for different file naming conventions because we have noticed that the file was named differently a couple of times so I just test for each spelling. The resume next just causes it to test for the next spelling and then if it errors again, test next. May not be totally correct but it seemed to work with the minimal knowledge I have of VBA. I was hoping i could use a wild character and just test for any file that starts with certain characters... but i don't know if this can be done... Haven't seen any examples on the web.

Not sure what u mean by using the macro recorder and how that would help in my instance... please expand.

diane

 

[VulcanJedi]

I was curious if you're searching for the web file (which i'm not too familiar w/) was correct

In Excel it has a TERRIFIC Macro recorder which basically writes exactly what you do in VBA for you!. So I suggest going to tools, macro, record and going to the website to ensure you're getting the right direction/syntax of opening this 'web file'

I really dont think you want/need the extra resume nexts, in fact i believe it would error each time it'd see those. but since you're already ignoring the errors you're not seeing them
To turn off on error resume next you can you on error goto 0
You can't search by wildcards in select case statements in vbscript/vba I blieve.

Tranpkp