VB problem; updateanje worksheeta iz xml filea

Malo je komplicirano, ali hajde da probam što bolje pojasnit, jer mi je pomoć dobrodošla, bilo kakva...

Dakle zadatak je iz workbooka1 worksheet1 exportati automatski u xml datoteku prilikom zatvaranja workbooka1. 

Export se vrši automatski po zatvaranju programa (updatea se postojeća ili se kreira nova datoteka u slučaju da ne postoji). Koristi se event BeforeClose.

Kako je ovo samo dio projekta koji radim, idem dio po dio, pa vas neću sa ostatkom zamarati. Ne želim da vam mozak eksplodira kad čujete ostatak.

Dio koji za sada imam riješen, je dio zaslužan za vršenje eksporta, međutim problem mi je postao kada taj programski kod treba pokrenuti BeforeClose event, tako da se funkcija (za eksportiranje) izvrši. Isti program uspješno funkcionira u jednoj manjoj xls datoteci koja nema drugih modula i formi osim tih zaslužnih za izvršenje eksporta. Kada isti kod pokušavam primjenit na drugu xls datoteku (ovu koja sadrži još neke dodatne module i forme) dolazi do greške.

Odnosno, ništa se točno ne desi na kraju samog procesa (nakon zatvaranja woorkbook1 datoteke).

Tužno za reći, ali još se učim i smatram se još početnikom. Pošto prikupljam informacije na netu i slažem riješenje u skladu sa primjerima. 

Svaka pomoć dobrodošla!

EDIT: Kasnije će se ovaj xml file koristiti za updateanje odvojenog workbooka, u worksheet1 tog workbooka(2)

Sada me počeo mučiti i eksport dio programa.

Izbacuje mi Compile Error sa opisom "Can't find project or library". Zažuti mi "Sub MakeXML_New()" proceduru i zaplavi ovaj prvi Chr kraj broja (10).

Evo prog. koda koji koristim:

Option Explicit

Sub MakeXML_New()

' create an XML file from an Excel table

Dim MyRow As Integer, MyCol As Integer, Temp As String, YesNo As Variant, DefFolder As String

Dim XMLFileName As String, XMLRecSetName As String, MyLF As String, RTC1 As Integer

Dim RangeOne As String, RangeTwo As String, Tt As String, FldName(99) As String

MyLF = Chr(10) & Chr(13)   ' a line feed command

DefFolder = "C:\"   'change this to the location of saved XML files

XMLFileName = FillSpaces(InputBox("1. Enter the name of the XML file:", "MakeXML CiM", "xl_xml_data"))

If Right(XMLFileName, 4) <> ".xml" Then

 XMLFileName = XMLFileName & ".xml"

End If

XMLRecSetName = FillSpaces(InputBox("2. Enter an identifying name of a record:", "MakeXML CiM", "Tool"))

RangeOne = "A1:AL1"

MyRow = MyRng(RangeOne, 1)

For MyCol = MyRng(RangeOne, 3) To MyRng(RangeOne, 4)

 If Len(Cells(MyRow, MyCol).Value) = 0 Then

  MsgBox "Error: names range contains blank cell" & MyLF & "Procedure STOPPED", vbOKOnly + vbCritical, "MakeXML CiM"

  Exit Sub

 End If

 FldName(MyCol - MyRng(RangeOne, 3)) = FillSpaces(Cells(MyRow, MyCol).Value)

Next MyCol

RangeTwo = InputBox("4. Enter the range of cells containing the data table:", "MakeXML CiM", "A2:AL21")

If MyRng(RangeOne, 4) - MyRng(RangeOne, 3) <> MyRng(RangeTwo, 4) - MyRng(RangeTwo, 3) Then

  MsgBox "Error: number of field names <> data columns" & MyLF & "Procedure STOPPED", vbOKOnly + vbCritical, "MakeXML CiM"

  Exit Sub

End If

RTC1 = MyRng(RangeTwo, 3)

If InStr(1, XMLFileName, ":\") = 0 Then

 XMLFileName = DefFolder & XMLFileName

End If

Open XMLFileName For Output As #1

Print #1, "<?xml version=" & Chr(34) & "1.0" & Chr(34) & " encoding=" & Chr(34) & "ISO-8859-1" & Chr(34) & "?>"

Print #1, "<toollib_karlovac>"

For MyRow = MyRng(RangeTwo, 1) To MyRng(RangeTwo, 2)

Print #1, "<" & XMLRecSetName & ">"

  For MyCol = RTC1 To MyRng(RangeTwo, 4)

  ' the next line uses the FormChk function to format dates and numbers

   Print #1, "<" & FldName(MyCol - RTC1) & ">" & RemoveAmpersands(FormChk(MyRow, MyCol)) & "</" & FldName(MyCol - RTC1) & ">"

  ' the next line does not apply any formatting

  '  Print #1, "<" & FldName(MyCol - RTC1) & ">" & RemoveAmpersands(Cells(MyRow, MyCol).Value) & "</" & FldName(MyCol - RTC1) & ">"

   Next MyCol

 Print #1, "</" & XMLRecSetName & ">"

Next MyRow

Print #1, "</toollib_karlovac>"

Close #1

MsgBox XMLFileName & " created." & MyLF & "Process finished", vbOKOnly + vbInformation, "MakeXML CiM"

Debug.Print XMLFileName & " saved"

End Sub

Function MyRng(MyRangeAsText As String, MyItem As Integer) As Integer

' analyse a range, where MyItem represents 1=TR, 2=BR, 3=LHC, 4=RHC

Dim UserRange As Range

Set UserRange = Range(MyRangeAsText)

Select Case MyItem

 Case 1

 MyRng = UserRange.Row

 Case 2

 MyRng = UserRange.Row + UserRange.Rows.Count - 1

 Case 3

 MyRng = UserRange.Column

 Case 4

 MyRng = UserRange.Columns(UserRange.Columns.Count).Column

End Select

Exit Function

End Function

Function FillSpaces(AnyStr As String) As String

' remove any spaces and replace with underscore character

Dim MyPos As Integer

MyPos = InStr(1, AnyStr, " ")

Do While MyPos > 0

 Mid(AnyStr, MyPos, 1) = "_"

 MyPos = InStr(1, AnyStr, " ")

Loop

FillSpaces = LCase(AnyStr)

End Function

Function FormChk(RowNum As Integer, ColNum As Integer) As String

' formats numeric and date cell values to comma 000's and DD MMM YY

FormChk = Cells(RowNum, ColNum).Value

'i f IsNumeric(Cells(RowNum, ColNum).Value) Then

 'FormChk = Format(Cells(RowNum, ColNum).Value, "#,##0 ;(#,##0)")

'End If

'If IsDate(Cells(RowNum, ColNum).Value) Then

 'FormChk = Format(Cells(RowNum, ColNum).Value, "dd mmm yy")

'End If

End Function

Function RemoveAmpersands(AnyStr As String) As String

Dim MyPos As Integer

' replace Ampersands (&) with plus symbols (+)

MyPos = InStr(1, AnyStr, "&")

Do While MyPos > 0

 Mid(AnyStr, MyPos, 1) = "+"

 MyPos = InStr(1, AnyStr, "&")

Loop

RemoveAmpersands = AnyStr

End Function