Public Number1 As Integer
'Public Number2 As Integer
Public vDate As String
Public Month As String
Public Year As String
Public customerNumber As String
Public customerName As String
Public fvNumber As String
Public fvNumber2 As String
Public fvAmount As String
Public Char As String
Public Iter As Integer



Sub Przeksiegowanie()
Set fs = CreateObject("Scripting.FileSystemObject")
Set file = fs.CreateTextFile("C:\xml\Ksiegowanie1.xml", True)
    Makro1
   ' Makro2
    
 
    Year = "2013" 'Rok obrachunkowy
    Number1 = 1 ' Iterator zwiekszajacy licznik dla kolejnych faktur
    Number2 = 1
    vDate = "2013-12-31" ' Zamiana w każdym miesiącu
    Month = "12" ' Zmiana w każdym miesiącu
    Char = "-"
    Iter = 2
    fvNumber2 = Cells(2, 3)
    
    Call WriteBeginXML(file)
        While (Cells(Iter, 1) <> Empty And Cells(Iter, 2) <> Empty And Cells(Iter, 3) <> Empty And Cells(Iter, 4) <> Empty)
                  
            customerNumber = Cells(Iter, 1)
            customerName = Cells(Iter, 2)
            fvNumber = Cells(Iter, 3)
            fvAmount = Str(Cells(Iter, 4))
            
            Call WriteFirstInfoXML(file)
            Number1 = Number1 + 1
            Call WriteSecondInfoXML(file)
            Number1 = Number1 + 1
            Iter = Iter + 1
            'Number2 = Number2 + 1
        
    
        Wend
    Call WriteEndXML(file)
    
    file.Close
    
End Sub

Private Sub WriteBeginXML(ByVal a As Object)

a.writeline ("<?xml version=""1.0"" ?>")
a.writeline ("<eExact xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xsi:noNamespaceSchemaLocation=""eExact-Schema.xsd"">")
a.writeline (" <GLEntries>")
a.writeline (" <GLEntry status=""E"">")
a.writeline ("  <Division code=""888"" />")
a.writeline ("  <Description>przeks.FV  ") & fvNumber2 & ("</Description>") 'Zastosować zmienną numer FV.
a.writeline ("  <Date>") & vDate & ("</Date>") 'Zmienna data zestawienia
a.writeline ("  <DocumentDate>") & vDate & ("</DocumentDate>") 'Zmienna data zestawienia
a.writeline ("  <Journal code=""500"" type=""M"">")
a.writeline ("   <Description>Polecenie ksi&#281;gowania</Description>")
a.writeline ("   <GLAccount code=""  9900003"" type=""B"" subtype=""N"" side=""D"">")
a.writeline ("    <Description>Konto Techniczne dz. 500</Description>")
a.writeline ("   </GLAccount>")
a.writeline ("  </Journal>")
a.writeline ("  <Amount>")
a.writeline ("   <Currency code=""PLN""/>")
a.writeline ("   <Value>0</Value>")
a.writeline ("  </Amount>")
a.writeline ("  <ForeignAmount>")
a.writeline ("   <Currency code=""PLN""/>")
a.writeline ("   <Value>0</Value>")
a.writeline ("  </ForeignAmount>")


End Sub

Private Sub WriteFirstInfoXML(ByVal a As Object)

 a.writeline ("  <FinEntryLine number=""") & Number1 & (""" type=""N"" subtype=""N"">")
 a.writeline ("   <Date>") & vDate & ("</Date>") 'Zmienna data zestawienia
 a.writeline ("   <FinYear number=""") & Year & ("""/>") 'Zmienna rok
 a.writeline ("  <FinPeriod number=""") & Month & ("""/>") 'Zmienna miesiac
 a.writeline ("   <GLAccount code=""  8450300"" type=""B"" subtype=""J"" side=""D"">")
 a.writeline ("    <Description>Zaliczki od odbiorc&#243;w krajowych</Description>")
 a.writeline ("   </GLAccount>")
 a.writeline ("    <Description>przeks.FV  ") & fvNumber & ("</Description>") 'Zastosować zmienną numer FV.
 a.writeline ("  <Debtor code=""                 " & customerNumber & """") & (" number=""   " & customerNumber & """") & (" type=""C"">") 'Zmienna numer klienta
 a.writeline ("   <Name>") & customerName & ("</Name>") ' Zmienna nazwa klienta
 a.writeline ("  </Debtor>")
 a.writeline ("  <Resource number=""0""/>")
 a.writeline ("  <Warehouse code=""WG"">")
 a.writeline ("   <Description>Magazyn Wyrob&#243;w Gotowych</Description>")
 a.writeline ("  </Warehouse>")
 a.writeline ("  <Quantity>0</Quantity>")
 a.writeline ("  <Amount>")
 a.writeline ("   <Currency code=""PLN""/>")
 a.writeline ("    <Debit>") & fvAmount & ("</Debit>") ' Zmienna kwota z faktury
 a.writeline ("    <Credit>0</Credit>")
 a.writeline ("      <VAT code=""0"" type=""B"" vattype=""N"">")
 a.writeline ("       <Description>nie dotyczy VAT</Description>")
 a.writeline ("       <MultiDescriptions>")
 a.writeline ("            <MultiDescription number=""1"">VAT 0%</MultiDescription>")
 a.writeline ("            <MultiDescription number=""2"">VAT 0%</MultiDescription>")
 a.writeline ("            <MultiDescription number=""3"">VAT 0%</MultiDescription>")
 a.writeline ("            <MultiDescription number=""4"">VAT 0%</MultiDescription>")
 a.writeline ("       </MultiDescriptions>")
 a.writeline ("       <Percentage>0</Percentage>")
 a.writeline ("       <Charged>0</Charged>")
 a.writeline ("       <VATExemption>0</VATExemption>")
 a.writeline ("       <ExtraDutyPercentage>0</ExtraDutyPercentage>")
 a.writeline ("       <GLToPay code=""  2200302"" side=""C"" type=""B"" subtype=""C"">")
 a.writeline ("        <Description>Vat nale&#380;ny</Description>")
 a.writeline ("       </GLToPay>")
 a.writeline ("       <GLToClaim code=""  2200301"" side=""D"" type=""B"" subtype=""C"">")
 a.writeline ("        <Description>Vat naliczony</Description>")
 a.writeline ("       </GLToClaim>")
 a.writeline ("       <Creditor code=""              999999"" number=""999999"" type=""S"">")
 a.writeline ("        <Name>Urz&#261;d Skarbowy</Name>")
 a.writeline ("       </Creditor>")
 a.writeline ("       <PaymentPeriod>M</PaymentPeriod>")
 a.writeline ("       <VATListing>G</VATListing>")
 a.writeline ("      </VAT>")
 a.writeline ("  </Amount>")
 a.writeline ("  <VATTransaction code=""0"">")
 a.writeline ("   <VATAmount>0</VATAmount>")
 a.writeline ("   <VATBaseAmount>") & fvAmount & ("</VATBaseAmount>") ' Zmienna wartości faktury
 a.writeline ("  </VATTransaction>")
 a.writeline ("  <Payment>")
 a.writeline ("   <PaymentMethod code=""K""/>")
 a.writeline ("   <CSSDDate1>") & vDate & ("</CSSDDate1>") 'Zmienna data zestawienia
 a.writeline ("   <CSSDDate2>") & vDate & ("</CSSDDate2>") 'Zmienna data zestawienia
 a.writeline ("   <CSSDYesNo>K</CSSDYesNo>")
 a.writeline ("   <CSSDAmount1>0</CSSDAmount1>")
 a.writeline ("   <CSSDAmount2>0</CSSDAmount2>")
 a.writeline ("   <InvoiceNumber>5529</InvoiceNumber>")
 a.writeline ("   <BankAccount code=""74106000760000321000217589"" />")
 a.writeline ("  </Payment>")
 a.writeline ("  <Delivery>")
 a.writeline ("   <Date>") & vDate & ("</Date>") 'Zmienna data zestawienia
 a.writeline ("  </Delivery>")
 a.writeline ("  <FinReferences TransactionOrigin=""N"">")
 a.writeline ("   <UniquePostingNumber>0</UniquePostingNumber>")
 a.writeline ("   <YourRef>przeks.FV  ") & fvNumber & ("</YourRef>") ' Zmienna numer faktury
 a.writeline ("   <DocumentDate>") & vDate & ("</DocumentDate>") 'Zmienna data zestawienia
 a.writeline ("  </FinReferences>")
 a.writeline ("  <Discount>")
 a.writeline ("   <Percentage>0</Percentage>")
 a.writeline ("  </Discount>")
 a.writeline (" </FinEntryLine>")
  
   
   
End Sub

Private Sub WriteSecondInfoXML(ByVal a As Object)

 a.writeline ("  <FinEntryLine number=""") & Number1 & (""" type=""N"" subtype=""N"">")
 a.writeline ("   <Date>") & vDate & ("</Date>") 'Zmienna data zestawienia
 a.writeline ("   <FinYear number=""") & Year & ("""/>") 'Zmienna rok
 a.writeline ("  <FinPeriod number=""") & Month & ("""/>") 'Zmienna miesiąc
 a.writeline ("   <GLAccount code=""  7001000"" type=""W"" subtype=""J"" side=""C"">")
 a.writeline ("    <Description>PCV - Sprzeda&#380; wyrob&#243;w</Description>")
 a.writeline ("   </GLAccount>")
 a.writeline ("    <Description>przeks.FV  ") & fvNumber & ("</Description>") 'Zastosować zmienną numer FV.
 a.writeline ("  <Debtor code=""                 " & customerNumber & """") & (" number=""   " & customerNumber & """") & (" type=""C"">") 'Zmienna numer klienta
 a.writeline ("   <Name>") & customerName & ("</Name>") ' Zmienna nazwa klienta
 a.writeline ("  </Debtor>")
 a.writeline ("  <Resource number=""0""/>")
 a.writeline ("  <Warehouse code=""WG"">")
 a.writeline ("   <Description>Magazyn Wyrob&#243;w Gotowych</Description>")
 a.writeline ("  </Warehouse>")
 a.writeline ("  <Quantity>0</Quantity>")
 a.writeline ("  <Amount>")
 a.writeline ("   <Currency code=""PLN""/>")
 a.writeline ("    <Debit> 0 </Debit>")
 a.writeline ("    <Credit>") & fvAmount & ("</Credit>") ' Zmienna kwota z faktury
 a.writeline ("      <VAT code=""0"" type=""B"" vattype=""N"">")
 a.writeline ("       <Description>nie dotyczy VAT</Description>")
 a.writeline ("       <MultiDescriptions>")
 a.writeline ("            <MultiDescription number=""1"">VAT 0%</MultiDescription>")
 a.writeline ("            <MultiDescription number=""2"">VAT 0%</MultiDescription>")
 a.writeline ("            <MultiDescription number=""3"">VAT 0%</MultiDescription>")
 a.writeline ("            <MultiDescription number=""4"">VAT 0%</MultiDescription>")
 a.writeline ("       </MultiDescriptions>")
 a.writeline ("       <Percentage>0</Percentage>")
 a.writeline ("       <Charged>0</Charged>")
 a.writeline ("       <VATExemption>0</VATExemption>")
 a.writeline ("       <ExtraDutyPercentage>0</ExtraDutyPercentage>")
 a.writeline ("       <GLToPay code=""  2200302"" side=""C"" type=""B"" subtype=""C"">")
 a.writeline ("        <Description>Vat nale&#380;ny</Description>")
 a.writeline ("       </GLToPay>")
 a.writeline ("       <GLToClaim code=""  2200301"" side=""D"" type=""B"" subtype=""C"">")
 a.writeline ("        <Description>Vat naliczony</Description>")
 a.writeline ("       </GLToClaim>")
 a.writeline ("       <Creditor code=""              999999"" number=""999999"" type=""S"">")
 a.writeline ("        <Name>Urz&#261;d Skarbowy</Name>")
 a.writeline ("       </Creditor>")
 a.writeline ("       <PaymentPeriod>M</PaymentPeriod>")
 a.writeline ("       <VATListing>G</VATListing>")
 a.writeline ("      </VAT>")
 a.writeline ("  </Amount>")
 a.writeline ("  <VATTransaction code=""0"">")
 a.writeline ("   <VATAmount>0</VATAmount>")
 a.writeline ("   <VATBaseAmount>") & Char & fvAmount & ("</VATBaseAmount>") ' Zmienna wartości faktury zawsze z (-)
 a.writeline ("  </VATTransaction>")
 a.writeline ("  <Payment>")
 a.writeline ("   <PaymentMethod code=""K""/>")
 a.writeline ("   <CSSDDate1>") & vDate & ("</CSSDDate1>") 'Zmienna data zestawienia
 a.writeline ("   <CSSDDate2>") & vDate & ("</CSSDDate2>") 'Zmienna data zestawienia
 a.writeline ("   <CSSDYesNo>K</CSSDYesNo>")
 a.writeline ("   <CSSDAmount1>0</CSSDAmount1>")
 a.writeline ("   <CSSDAmount2>0</CSSDAmount2>")
 a.writeline ("   <InvoiceNumber>5529</InvoiceNumber>")
 a.writeline ("   <BankAccount code=""74106000760000321000217589"" />")
 a.writeline ("  </Payment>")
 a.writeline ("  <Delivery>")
 a.writeline ("   <Date>") & vDate & ("</Date>") 'Zmienna data zestawienia
 a.writeline ("  </Delivery>")
 a.writeline ("  <FinReferences TransactionOrigin=""N"">")
 a.writeline ("   <UniquePostingNumber>0</UniquePostingNumber>")
 a.writeline ("   <YourRef>przeks.FV  ") & fvNumber & ("</YourRef>") ' Zmienna numer faktury
 a.writeline ("   <DocumentDate>") & vDate & ("</DocumentDate>") 'Zmienna data zestawienia
 a.writeline ("  </FinReferences>")
 a.writeline ("  <Discount>")
 a.writeline ("   <Percentage>0</Percentage>")
 a.writeline ("  </Discount>")
 a.writeline (" </FinEntryLine>")
  
   
   
End Sub



Private Sub WriteEndXML(ByVal a As Object)
a.writeline (" </GLEntry>")
a.writeline ("</GLEntries>")
a.writeline ("</eExact>")

End Sub

Sub Makro1()
'
' Makro1 Makro
'

'
    Columns("B:B").Select
    
Selection.Replace What:="Ą", Replacement:="&#260;", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
Selection.Replace What:="Ę", Replacement:="&#280;", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
Selection.Replace What:="Ł", Replacement:="&#321;", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
Selection.Replace What:="Ó", Replacement:="&#211;", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
Selection.Replace What:="Ż", Replacement:="&#379;", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
Selection.Replace What:="Ź", Replacement:="&#377;", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
Selection.Replace What:="Ć", Replacement:="&#262;", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
Selection.Replace What:="Ś", Replacement:="&#346;", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
Selection.Replace What:="Ń", Replacement:="&#323;", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
Selection.Replace What:="®", Replacement:="&#174;", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
       
Selection.Replace What:="Ü", Replacement:="&#220;", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
        
Selection.Replace What:="&", Replacement:="&amp;", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
       
Selection.Replace What:="ä", Replacement:="&#228;", LookAt:=xlPart, _
       SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
       ReplaceFormat:=False
 
        
'Selection.Replace What:=".", Replacement:="&#8901;", LookAt:=xlPart, _
    '    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
     '   ReplaceFormat:=False
        
        
Selection.Replace What:="""", Replacement:="&quot;", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
End Sub






Sub Makro2()
'
' Makro1 Makro
'

'
    Columns("D:D").Select
    
Selection.Replace What:="&sdot;", Replacement:=".", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        

        
End Sub