Public Sub XMLtoTXT()
Dim xmlDoc As MSXML2.DOMDocument
Dim objNode As IXMLDOMNode
Dim SIC, OQ, OUGP As IXMLDOMNodeList
Dim db As ADODB.Connection
Dim rec As ADODB.Recordset
Dim txtSIC, AtxtSIC, xmlFileName, txtFileName As String
Dim i As Integer
Dim last As Date
Dim DBConn As ADODB.Connection
Dim objFSO As FileSystemObject
Dim objFolder As Folder
Dim objFiles As Files
Dim objFile As File
Set xmlDoc = New DOMDocument
Set db = New ADODB.Connection
Set rec = New ADODB.Recordset
Set DBConn = New ADODB.Connection
Set objFSO = New FileSystemObject
Set objFolder = objFSO.GetFolder("D:\trash\test\")
Set objFiles = objFolder.Files
txtFileName = "D:\trash\test.txt"
date_ord = Cells(2, 1)
date_ord = sd(date_ord)
xmlFileName = "ORDER_" + date_ord
last = "01.01.1900"
For Each objFile In objFiles
If CDate(objFSO.GetFileName(objFile.DateCreated)) > last Then
If Left(CStr(objFSO.GetFileName(objFile.Name)), _
Len(CStr(objFSO.GetFileName(objFile.Name))) - 13) = xmlFileName Then
xmlFileName = CStr(objFSO.GetAbsolutePathName(objFolder.Path)) + "\" _
+ CStr(objFSO.GetFileName(objFile.Name))
End If
End If
last = CDate(objFSO.GetFileName(objFile.DateCreated))
Next
AtxtSIC = ""
txtSIC = ""
'-------------------Parsing XML file and fiend SIC,OQ and OUGP------------------
xmlDoc.async = False
xmlDoc.validateOnParse = False
xmlDoc.Load (xmlFileName)
xmlDoc.setProperty "SelectionLanguage", "XPath"
Set SIC = xmlDoc.selectNodes("//SupplierItemCode")
Set OQ = xmlDoc.selectNodes("//OrderedQuantity")
Set OUGP = xmlDoc.selectNodes("//OrderedUnitGrossPrice")
For Each txtSIC In SIC
AtxtSIC = AtxtSIC + txtSIC.Text + ","
Next
AtxtSIC = Left(AtxtSIC, Len(AtxtSIC) - 1)
'-----------------Request Suplier Item Code from IS Pro ------------------------
Call db.Open("Provider='sqloledb';Data Source='Server';Initial Catalog='Pro_Sklad'", "sa", "")
sqlq = "select skln_cd, NmEi_QtOsn, skln_statrep, nmEi_QtOsn from skln" + _
" left join sklnomei on skln_rcd=nmei_rcdnom" + _
" where nmei_cd=3 and skln_rcdgrp in (257,259,260,261,275)" + _
" and skln_statrep in(" + AtxtSIC + ")"
rec.Open sqlq, db
Call DBConn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='D:\trash\'; Extended Properties='DBASE IV'")
DBConn.Execute ("create table REPORT (LCODE varchar, CODE integer, [SECTION] integer, KOL integer, [SUM] double, PRTSH integer)")
'Open txtFileName For Output As #1
'-----------------Write in file -----------------------
i = 0
While Not rec.EOF And SIC.Length - 1
DBConn.Execute ("Insert into REPORT Values(" + rec!skln_cd + ",'',''," + CInt(OQ(i).Text) * CInt(rec!nmEi_QtOsn) + "," + OUGP(i).Text + ",'')")
'Print #1, rec!skln_cd & ";" & CInt(OQ(i).Text) * CInt(rec!nmEi_QtOsn) & ";" & OUGP(i).Text
i = i + 1
rec.MoveNext
Wend
'Close #1
rec.Close
Set rec = Nothing
db.Close
DBConn.Close
Set DBConn = Nothing
Set db = Nothing
Set objFSO = Nothing
Set objFolder = Nothing
Set objFiles = Nothing
Set objFile = Nothing
End Sub
|
