0

I am not sure if there is an answer for that, although this looks like a REALLY simple question.

I am using a vba code to open a file in a Sharepoint, however, as I cannot use the Len(Dir()) to check if the file really exists, I used a code which I found in a forum but this code does not work the way I thought it would.

What I needed was a code to look for several files in the Sharepoint and each file need to have the current date (For example: If we are in July 2014, the file must be named as Name_July2014, not Name_June2014 or May_2014). Since the file with the current date might not exist, then it would be entered the message "Not Updated" in a specific cell in the workbook I am using and the macro would continue to run. The code works well, but it stops every time to show a message that the file does not exists. This message appears everytime the macro tries to open a file that does not exist. I needed to skip this warning and let the code run. I thought this code would skip the warning (using On Error GoTo) and this would be the way to show that a file exists, but it's not working. Can anyone take a look?

Can I avoid somehow the message "The internet address ... is not valid"?

Really sorry if I wasn't clear enough because of my English and sorry if this question has already been answered in another post.

This is what the code looks like:

'Open the file and checks if the name of the file opened is different from the      
'previous file opened
On Error Resume Next
Application.Workbooks.Open (fileName)
newBook = ActiveWorkbook.Name
If originalBook = newBook Then
    [the file does not exist, do something]
Else
    [the file exists, do something else]
End If
On Error Goto
tshepang
  • 12,111
  • 21
  • 91
  • 136
dekio
  • 810
  • 3
  • 16
  • 33

1 Answers1

2

On Error Resume Next makes it so if excel encounters an error, it ignores it and moves on to the next line of code. On Error GoTo 0 turns normal error handling back on. On Error GoTo is incorrect syntax and will not run, perhaps that is where your error is. See here for further explanation:

Why would you ever use "On Error Goto 0"?

Community
  • 1
  • 1
DyRuss
  • 492
  • 4
  • 12
  • I got two problems, first the message that the internet file address is not valid (and I didn't want the message to pop up) and second, if I put On Error Go To xxxx I got the Run Time error 1004 Microsoft Excel cannot access the file) – dekio Sep 17 '14 at 18:48
  • @trder if you don't want any errors to pop up, have `On Error Resume Next` at the top of the script, and delete the `On Error GoTo 0` in your code. From what you've posted I can't tell why you are receiving those errors – DyRuss Sep 17 '14 at 18:56
  • Now I get it... sorry! But can I avoid the message "The internet address xxxxx is not valid"???? – dekio Sep 17 '14 at 19:32
  • @trder there must be other error handling code in your script causing the message to appear, do you have any statements like `If Err <> 0 Then` or other `On Error GoTo 0` statements? If so delete those and try to run it again – DyRuss Sep 17 '14 at 19:51
  • Yeah, I tried. Still receiving the message "the internet address ____ is not valid" whenever I have a file that does not exist. – dekio Sep 17 '14 at 19:55
  • 1
    @trder search for lines containing MsgBox(something), and comment them out. Try putting `Application.DisplayAlerts = False` at the top of the script – DyRuss Sep 17 '14 at 20:05