VBA sub reads file differently than file explorer

60 views Asked by At

I am building an Access form that should allow users to input a street address in the form of Address (number), Street, Town, County, State. Once this is done, they can click a button to generate a KML file defining a placemark at that address and open it in Google Earth. However, when the file is opened, Google Earth gives an error, stating "No results - empty KML file". If the file is then opened manually through File Explorer, the placemark generates (nearly) as expected. The method code and generated KML for the input address = 3, street = "Todd Lane", Town = "Old Tappan", County = Bergen, State = "New Jersey" are shown below.

Private Sub ExportToKMLButton_Click()
    Dim FName As String
    FName = "C:\Users\Public\QueryOutput.KML"
    Close #1 'Make sure nothing is already open as #1.
    Open FName For Output As #1 'Open the file defined by FName.

    Dim outputtext As New Collection
    outputtext.Add "<?xml version=""1.0"" encoding=""UTF-8""?>" 'Denotes the language to follow.
    outputtext.Add Item:="<kml xmlns=""http://earth.google.com/kml/2.0"">"
    outputtext.Add Item:="<Document>"
        outputtext.Add Item:="<Placemark>"
            outputtext.Add Item:="<name>Active Location</name>"
            outputtext.Add Item:="<lookat>Active Location</name>"
            outputtext.Add Item:="<address>" & Address.Value & " " & Street.Value & _
            ", " & Town.Value & ", " & County.Value & " County, " & State.Value & "</address>"
        outputtext.Add Item:="</Placemark>"
    outputtext.Add Item:="</Document>"
    outputtext.Add Item:="</kml>"

    Dim output As String
    Dim i As Long
    For i = 1 To outputtext.Count
        output = output & outputtext.Item(i)
    Next i

    output = Replace(output, "&", "and") 'Remove any ampersands from the output to avoid errors.
    Print #1, output 'Print to file.

    Dim wsh As Object
    Set wsh = VBA.CreateObject("WScript.Shell")
    wsh.Run FName, 1, True
    On Error GoTo EndOfExport 'If the user runs this function twice consecutively, without closing Google Earth, both instances of the function _
                               will resume simultaneously when Earth is closed. One will delete the single extant copy of the KML file, and the _
                               other will attempt to do the same. When the file is not found, an error will occur. This error does no harm, so _
                               we simply skip it and end the function.
    'Kill FName 'Deletes the generated file after opening it.
    GoTo EndOfExport
ErrorFound:
    MsgBox ("An error has occurred. Your export may be incomplete.")
EndOfExport:
    Close #1
End Sub

And the generated KML (edited for readability; no line breaks in original):

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Document>
<Placemark>
<name>Active Location</name>
<address>3 Todd Lane, Old Tappan, Bergen County, New Jersey</address> 
</Placemark>
</Document>
</kml>
1

There are 1 answers

0
Passage On

It seems that the problem lay in the fact that the file was still open for editing when the shell script to run it was called. The problem stopped occurring after moving Close #1 between Print #1, output and Dim wsh As Object.

Print #1, output 'Print to file.
Close #1
Dim wsh As Object