Public Function getLatestLehman(playerName As String)
    i = 2
    Do
        Name = Workbooks("LEHMAN.xls").Worksheets("Sheet3").Cells(i, 1)
        If Name = playerName Then
            getLatestLehman = Workbooks("LEHMAN.xls").Worksheets("Sheet3").Cells(i, 2)
            Exit Function
        End If
        i = i + 1
    Loop While (Name <> 0)
End Function
Sub createVugraphXmlFile()
    Sheets("Vugraph").Select
    Dim filepath, filename, extention
    Dim eventName, eventDate
    filepath = Cells(1, 2).Text
    filename = Cells(2, 2).Text
    extention = Cells(3, 2).Text
    eventName = Cells(4, 2).Text
    numTables = Cells(5, 2).Value
    eventDate = Cells(6, 2).Text
    firstBoard = Cells(8, 2).Value
    lastBoard = Cells(9, 2).Value
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.CreateTextFile(filepath & filename & "." & extention, True)
    a.writeline ("<?xml version=""1.0"" encoding=""ISO-8859-9""?>")
    a.writeline ("<!-- Lehman Results in XML format for vugraph.com (C)opyright 2006 Odeva Internet Teknolojileri -->")
    a.writeline ("<lehmanResult xmlns=""http://vugraph.com/vugraph"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xsi:schemaLocation=""http://vugraph.com/vugraph http://vugraph.com/vugraph.xsd"">")
    a.writeline ("<name>" & eventName & "</name>")
    a.writeline ("<date>" & eventDate & "</date>")
    a.writeline ("<tables>")
    For t = 1 To numTables
        a.writeline ("  <table no=""" & t & """>")
        a.writeline ("    <players>")
        a.writeline ("      <player direction=""north"">")
        a.writeline ("        <name>" & Worksheets("detay").Cells(5 * t - 3, 2) & "</name>")
        
        a.writeline ("        <lehman>" & Replace(getLatestLehman(Worksheets("detay").Cells(5 * t - 3, 2)), ",", ".") & "</lehman>")
        a.writeline ("      </player>")
        a.writeline ("      <player direction=""south"">")
        a.writeline ("        <name>" & Worksheets("detay").Cells(5 * t - 2, 2) & "</name>")
        a.writeline ("        <lehman>" & Replace(getLatestLehman(Worksheets("detay").Cells(5 * t - 2, 2)), ",", ".") & "</lehman>")
        a.writeline ("      </player>")
        a.writeline ("      <player direction=""east"">")
        a.writeline ("        <name>" & Worksheets("detay").Cells(5 * t - 1, 2) & "</name>")
        a.writeline ("        <lehman>" & Replace(getLatestLehman(Worksheets("detay").Cells(5 * t - 1, 2)), ",", ".") & "</lehman>")
        a.writeline ("      </player>")
        a.writeline ("      <player direction=""west"">")
        a.writeline ("        <name>" & Worksheets("detay").Cells(5 * t, 2) & "</name>")
        a.writeline ("        <lehman>" & Replace(getLatestLehman(Worksheets("detay").Cells(5 * t, 2)), ",", ".") & "</lehman>")
        a.writeline ("      </player>")
        a.writeline ("    </players>")
        a.writeline ("    <impNS>" & Worksheets("detay").Cells(5 * t - 3, 19) & "</impNS>")
        a.writeline ("    <impEW>" & Worksheets("detay").Cells(5 * t - 1, 19) & "</impEW>")
        a.writeline ("    <boards>")
        For b = firstBoard To lastBoard
            a.writeline ("      <board no=""" & b & """>")
            a.writeline ("        <score>" & Worksheets("detay").Cells(5 * t - 3, b - firstBoard + 3).Text & "</score>")
            a.writeline ("        <imp>" & Replace(Worksheets("detay").Cells(5 * t - 2, b - firstBoard + 3).Text, ",", ".") & "</imp>")
            a.writeline ("      </board>")
        Next b
        a.writeline ("    </boards>")
        a.writeline ("  </table>")
    Next t
    a.writeline ("</tables>")
    a.writeline ("</lehmanResult>")
    a.Close
End Sub