VBA - Exporting to Excel - Error Handling

[mulch17]

Got a quick question about error handling in VBA, specifically pertaining to a module that exports results from Access queries, into an Excel file. My current module is working fine for the most part, but during negative testing, I've found a few specific places where the customer would receive an error message.

When the user clicks the Export button, they are prompted to choose a file name and file path, and then click Save (it's just the default File -> Save page within Microsoft, nothing I've made myself).

The first error case is when the user clicks my Export button, and then clicks "Cancel" on the save menu. I receive a run-time error that says "The OutputTo action was cancelled". Pretty self-explanatory, but I'm hoping to make a message box that is more user-friendly than the default run-time error box.

The second error case is when the user tries to save the Excel file, and there already exists a file with the same name in the same path AND it's already open. If it's not open, you're just asked if you want to overwrite it like normal. But if it's open, you get a different run-time error. Through searching, I've found solutions for this, but they are only checking static hard-coded file names/paths. The challenge for me is that the customer would choose this information, and it would be different each time.

In my head, I'm envisioning two basic if statements:

If *user clicks Cancel* Then
MsgBox "Export cancelled."
Exit Sub
End If

If *file name already exists* And *that file is open* Then
MsgBox "There is already an Excel file with this name. Please close that Excel file before continuing."
Exit Sub
End If

The problem is that I don't know the actual code for those 3 pseudo-code statements I put in asterisks. Is there a simple statement I could put in each of those places? I'm hoping this doesn't involve dozens and dozens of lines of code. I'm also hoping to avoid On Error statements if possible, because I still haven't quite wrapped my head around them. Using that approach, I've gotten message boxes to display, but it's just one generic catch-all error message. I would be nervous that another run-time error would occur during my testing, and I wouldn't know about it and which specific error it was.

If you have any suggestions for me, I would greatly appreciate them! Thanks so much everyone!!!!

 

[pbaldy]

If you use an error trap:

http://www.baldyweb.com/ErrorTrap.htm

It would be a matter of accounting for the error numbers and handling appropriately in each Case. You can add the error number to the Else message so you know what number to handle.

Another alternative, depending on you code, is testing before proceeding (and thus avoiding the errors). I'm not sure how to test for a specific file being open offhand, but there may be a way.

 

[Frothingslosh]

If you use an error trap:

http://www.baldyweb.com/ErrorTrap.htm

It would be a matter of accounting for the error numbers and handling appropriately in each Case. You can add the error number to the Else message so you know what number to handle.

Another alternative, depending on you code, is testing before proceeding (and thus avoiding the errors). I'm not sure how to test for a specific file being open offhand, but there may be a way.

I don't know about OPEN, but this will determine if a file is LOCKED. In the OP's case, this will do the trick.

I commented out the message box in my repository file because I always do the notifications for locked files myself rather than just kicking up a default error message.

Note that strFileName needs to be a full path, not just the name.

Public Function FileLocked(strFileName As String) As Boolean
   On Error Resume Next
   ' If the file is already opened by another process,
   ' and the specified type of access is not allowed,
   ' the Open operation fails and an error occurs.
   'Copied from Microsoft at http://support.microsoft.com/kb/209189
   Open strFileName For Binary Access Read Write Lock Read Write As #1
   Close #1
   ' If an error occurs, the document is currently open.
   If Err.Number <> 0 Then
      ' Display the error number and description.
'      MsgBox "Error #" & Str(Err.Number) & " - " & Err.Description
      FileLocked = True
      Err.Clear
   End If
End Function

 

[smig]

On error resume next is not a way of trapping the errors. It will simply move to the next line of code.

 

[Frothingslosh]

On error resume next is not a way of trapping the errors. It will simply move to the next line of code.

I didn't write it (it came from Microsoft's website), but in this case, you're not actually trapping the code. You're simply checking to see if the attempt to open the file for R/W access generated an error. It's actually shorter (by a line or two, anyway) to use the Resume Next than to actually trap a generated error. The user should actually check for the existence of the file before using the procedure I quoted.

I was simply providing some code to determine if a spreadsheet is currently open and in use, not providing an example of error trapping.

 

[mulch17]

If you use an error trap:

***** the URL pbaldy referenced - looks like I can't quote this link, since I have less than 10 posts *****

It would be a matter of accounting for the error numbers and handling appropriately in each Case. You can add the error number to the Else message so you know what number to handle.

Another alternative, depending on you code, is testing before proceeding (and thus avoiding the errors). I'm not sure how to test for a specific file being open offhand, but there may be a way.

Thank you for sending me this. This was 100 times easier than I was expecting. I'm not quite sure why I anticipated this On Error stuff being so difficult. This has taken care of the issue perfectly. The cancel button error was even coded for me already in that example.

For the second error, with the file already being open, I simply added another case to that example:

Public Function CreateExcelChart(QueryNameInput As String)
On Error GoTo ErrorHandler:

'My function

ExitHandler:
  Exit Function
ErrorHandler:
  Select Case Err   'specific Case statements for errors we can anticipate, the "Else" catches any others
    Case 2501       'user clicked Cancel button on the save menu
      MsgBox "Export cancelled"
      DoCmd.Hourglass False
      Resume ExitHandler
    Case 2302       'Excel file was already open with the same name, when the user clicked OK
      MsgBox "There is already an Excel file open with this name. Please close that Excel file before continuing."
      DoCmd.Hourglass False
      Resume ExitHandler
    Case Else
      MsgBox Err.Description
      DoCmd.Hourglass False
      Resume ExitHandler
  End Select

End Function

It turns out the error number for that situation I described is #2302. The only problem with this, is if there would ever be a time when the customer could get Run-Time Error #2302 for a different reason. The description is "Microsoft Access can't save the output data to the file you've selected", which is obviously pretty generic.

Do you think it's okay to leave what I have, or do you think there could be other cases where 2302 errors could occur that I have not considered?

I don't know about OPEN, but this will determine if a file is LOCKED. In the OP's case, this will do the trick.

I commented out the message box in my repository file because I always do the notifications for locked files myself rather than just kicking up a default error message.

Note that strFileName needs to be a full path, not just the name.

Public Function FileLocked(strFileName As String) As Boolean
   On Error Resume Next
   ' If the file is already opened by another process,
   ' and the specified type of access is not allowed,
   ' the Open operation fails and an error occurs.
   'Copied from Microsoft at ***** the URL Frothingslosh referenced - looks like I can't quote this link, since I have less than 10 posts :( *****
   Open strFileName For Binary Access Read Write Lock Read Write As #1
   Close #1
   ' If an error occurs, the document is currently open.
   If Err.Number <> 0 Then
      ' Display the error number and description.
'      MsgBox "Error #" & Str(Err.Number) & " - " & Err.Description
      FileLocked = True
      Err.Clear
   End If
End Function

This looks helpful, and I would like to use this in place of the error case 2302 I have in my code above (since we know that this code here only checks for the file being locked, and not any other possible instances of run-time error #2302).

However, this has the same challenge I described in my original post. I don't know what the explicit full file path is, because it will change every time, since the user can pick their name/path. So I wouldn't be able to enter StrFileName.

Is there a way to programmatically determine the name and path of the Excel file after they click "Save"? Even if I may not need it for this specific function, I'm curious to know this for future reference. I'm hoping there's an easy way to do this.

Thanks for both of your replies, pbaldy and Frothingslosh! My issue is now resolved, thanks for the helpful and lightning fast replies!!!!

 

[pbaldy]

Regarding "Do you think it's okay to leave what I have, or do you think there could be other cases where 2302 errors could occur that I have not considered?", I'd probably leave it. The 2302 could come up doing something else, but in this particular routine it's likely only the one you're anticipating.

By the way, you can leave the error message out of a particular case if you want. For example, the cancel one. The user clicked cancel, so the message box may be superfluous. Some users would think "I know the export was cancelled, I cancelled it!". Some of my users get aggravated when they have to make an extra click or keystroke.

 

[mulch17]

By the way, you can leave the error message out of a particular case if you want. For example, the cancel one. The user clicked cancel, so the message box may be superfluous. Some users would think "I know the export was cancelled, I cancelled it!". Some of my users get aggravated when they have to make an extra click or keystroke.

......not sure why I didn't figure that out I agree 100%, and I've deleted that line from my code. Thanks for helping again, and being lightning fast on the replies again!

For me personally, my issue is taken care of now, so I've marked the thread as solved. Frothingslosh, if you (or anyone else) have any suggestions or information (mainly the way to programmatically determine the name/path of the file), I'll still be reading and I welcome any replies, for both myself and any future searchers.

 

[pbaldy]

Happy to help!