Note The path to the XML file will vary based on where you are storing the files that came with this book

4. In the GetXMLData subroutine, change the name of the XML file we're opening to cd.xml. The subroutine should look like Listing 7-8 now.

Listing 7-8. GetXMLData Procedure After Modification

Sub GetXMLData() ActiveWorkbook.XmlImport URL:= *

"C:\Chapter 3\files\cd.xml", ImportMap:= *

Nothing, Overwrite:=True, Destination:=Range("$A$1") End Sub

5. In Excel, make Worksheetl the active sheet.

6. From the Macro dialog box, run the GetXMLData procedure. We get a very ugly error, as shown in Figure 7-38.

Microsoft Visual Basic

Run-time error '-2147217376 (80041020)': The system cannot locate the object specified.

End | | Debug |


Figure 7-38. Anon-intuitive error message is presented to the user. 7. Click the End button.

In cases like this, where it's an issue of resource availability, we don't need to code an actual error handler to assist the user. What we need to do is check for the existence of the file before we try to open it. If the file is not present, we'll tell the user in a friendlier and more understandable manner.

In the VBE, add a standard code module. Add the code from Listing 7-9.

Listing 7-9. The FileExists Function

Function FileExists(FilePathName As String) As Boolean Dim blnReturn As Boolean blnReturn = Len(Dir(FilePathName)) > 0

FileExists = blnReturn End Function

On Standard Modulel, modify the GetXMLData subroutine as in Listing 7-10.

Listing 7-10. GetXMLData Subroutine with Modifications

Sub GetXMLData()

Dim sFileName As String sFileName = "C:\Book\Chapters\Chapter 3\files\cd.xml" If FileExists(sFileName) Then ActiveWorkbook.XmlImport URL:= _ sFileName, ImportMap:= _

Nothing, Overwrite:=True, Destination:=Range("$A$1")


MsgBox "Could not find the requested file", vbOKOnly, "File Not Found" End If End Sub

Let's take a look at what we changed:

• We took the string containing the file name out of the XmlImport method call and assigned it to the variable sFileName.

• We then wrapped our XmlImport method call in an If statement. Based upon the existence of the file, we either import the data or present the user with the friendly message shown in Figure 7-39.

Could not find the requested file

Could not find the requested file

Figure 7-39. Friendly error message: File Not Found

The moral of this story in this case is that the best defense is a good offense. By considering in advance where our code might fail, we can avoid errors and provide users with feedback they can use.

0 0

Post a comment