1

Possible Duplicate:
Remove a node from XML file in MS Project VBA

Here is the Pseudocode for the code I'm trying to write:

Open WWID Exclusion List (csv file)
Open XML File

For Each WWID (WorldwideID) search and see if it is in the XML record
    IF EMPLOYEE found delete the EMPLOYEE XML

Save text File

Close text file

I have a XML file with structure similar to that shown below:

<EMPLOYEE>
<WWID><![CDATA[10000001]]></WWID>
<EFFDT><![CDATA[06/02/2009]]></EFFDT>
<ACTION><![CDATA[]]></ACTION>
<ACTION_REASON><![CDATA[]]></ACTION_REASON>
<EMPL_CLASS><![CDATA[E]]></EMPL_CLASS>
<LOCATION><![CDATA[SC2]]></LOCATION>
<FIRST_NAME><![CDATA[FirstName]]></FIRST_NAME>
<LAST_NAME><![CDATA[LastName]]></LAST_NAME>
<MI><![CDATA[S]]></MI>
<NICKNAME><![CDATA[]]></NICKNAME>
<MGR_WWID><![CDATA[10000002]]></MGR_WWID>
<COST_CENTER><![CDATA[55555]]></COST_CENTER>
<EMPL_STATUS><![CDATA[R]]></EMPL_STATUS>
<HIRE_DT><![CDATA[02/09/1987]]></HIRE_DT>
<REHIRE_DT><![CDATA[]]></REHIRE_DT>
<TERMINATION_DT><![CDATA[06/01/2000]]></TERMINATION_DT>
<TITLE><![CDATA[Software Developer]]></TITLE>
<SHIFT><![CDATA[1]]></SHIFT>
<ORGUNIT><![CDATA[11111]]></ORGUNIT>
<FLSA><![CDATA[E]]></FLSA>
</EMPLOYEE>

Coding Progress so Far:

Sub Purge_Old_Records()

Dim sBuf As String
Dim sXMLBuf As String
Dim sTemp As String
Dim sXMLTemp As String
Dim iFileNum As Integer
Dim iXMLFileNum As Integer
Dim sFileName As String 'Name of exclusion file to Open
Dim sXMLFileName As String ' Name of XML file

' Select the XML & Exclusion files  

sXMLFileName = Application.GetOpenFilename(FileFilter:="Data Files (*.xml), *.xml, All Files(*.*), *.*", Title:="Select XML File")

sFileName = Application.GetOpenFilename(FileFilter:="Data Files (*.csv), *.csv, All Files(*.*), *.*", Title:="Select Exclusion File")

'Open & read XML file first
iXMLFileNum = FreeFile
Open sXMLFileName For Input As iXMLFileNum

Do Until EOF(iXMLFileNum)
Line Input #iXMLFileNum, sXMLBuf
sXMLTemp = sXMLTemp & sXMLBuf & vbCrLf
Loop
Close iXMLFileNum

'Open Exclusion List Second
iFileNum = FreeFile
Open sFileName For Input As iFileNum

Do Until EOF(iFileNum)
Line Input #iFileNum, sBuf
sTemp = sTemp & sBuf & vbCrLf
Loop
Close iFileNum

Need Help on how to code to find a WWID and delete the entire EMPLOYEE record, not just blank it out

sTemp = Replace(sTemp, "DIM A", vbCrLf)

'Save Revise file TXT file
iFileNum = FreeFile
sFileName = Application.GetSaveAsFilename(Title:="Select Output XML Filename")
Open sFileName For Output As iFileNum

Print #iFileNum, sTemp

Close iFileNum

End Sub

Thanks very much for the assistance!

Community
  • 1
  • 1
nwdbrown
  • 11
  • 1
  • Can you include the top of the XML file? You should really treat that file as XML rather than just text: hard to give suggestions without knowing its structure. – Tim Williams Oct 25 '12 at 03:59
  • Here is the header:
    2012-05-11 XXXX MYYYY
    – nwdbrown Oct 25 '12 at 05:42
  • Please don't post a bunch of code in a comment. It's obviously unreadable. Just edit your question and place it there. – Jean-François Corbett Oct 25 '12 at 09:26
  • Thanks Jean-Francois for the note. I wasn't aware of XPath. This puts me in the correct direction: http://stackoverflow.com/questions/5522290/loop-through-xml-file-using-vba-and-xpath – nwdbrown Oct 25 '12 at 15:05

0 Answers0