ACA Macro Breakthrough

I don't know why but tonight I'm having a lot of problems with my Internet connection. I noticed that I lost all of my tabs in Internet Explorer. Actually today has been just one long crappy day - just problems, one right after another.

Today's post will be short because I'm still tired and trying to recover my sleep after being sick a couple of weeks ago. Also, in the last 2 days I've written a long post so I want to keep this one short.

But I did want to do this post to make up for the weekend's lack of posting and I did mention last week about a breakthrough. I had done a previous post where I was trying to figure out how to get rid of empty tags because of the government's new rules about empty tags - you can't have them. So I've been trying to program them out but there was one part where I was struggling. I finally came up with a macro but the approach was to go through line by line and identify if that line needed to be eliminated. The line by line method blew up the program such that it took over an hour to do its job.

Last week I finally succeeded in finding a solution. It involves using xml's syntax of nodes. Microsoft apparently had embedded xml's concept of nodes and coding concepts into its VBA. It looks sufficiently different to feel like a different language. Last year, when I Googled on how to get rid of blank spaces, this "language" did not show up. This year it did. It might have been due to different search words or due to a new work machine. Whatever the case may be, Googling brought up different sets of choices and this time I encountered what looked like a new language.

Below is a snippet of the new macro code for getting rid of tags.

The first section starts off with the Dims and some variables such as FileName and Wks. That's not the new part. I'm showing you that part just to situate you as to where the new piece of macro will come in. Basically, getting rid of the empty tags will be the first thing to be accomplished.

The part below the line that starts with 'Dim XMLDOC As MSXML2.DOMDocument' and ends with 'Set objDOC = Nothing' is the new code. The Dims look like to set some variables as part of xml. Then we create our object variable XMLDOC and load the FileName into the xml object variable.

The next line which is key - 'Set listDOC = XMLDOC.SelectNodes("//*[not(node())]")' - sets listDOC as a variable containing all of the empty tags that is in XMLDOC object variable.

Then we go through each empty tags in listDOC and remove them: 'Item.ParentNode.RemoveChild (Item)'

Finally we save XMLDOC into FileName (which gets saved out onto wherever your xml file is located) and we clear all of the xml variables.

After running through those set of codes, a lot of empty tags will be gone but then a new set will appear, so I have these set of codes down twice to get rid of all empty tags. It takes two runs to do this. After all of the empty tags are gone, the macro then proceeds to add in the namespace prefixes and add in the required tags to satisfy the government's schemas.

Macro code

Dim FileName As String
Dim fso As Object
Dim FindValues As Variant
Dim I As Long
Dim ReplaceValues As Variant
Dim ReplaceValues2 As Variant
Dim Text As String
Dim TextFile As Object
Dim Wks As Worksheet

'Please change the directory path to find your xml.
'Also, update the "Replacement list" in this file to change the element names to the proper ones given by the government.
FileName = "C:UsersvfrizzellDocumentsVeroniqueNotepad macro testingACA test.xml"Set Wks = Worksheets("Replacement list")

[ap_divider color="#CCCCCC" style="solid" thickness="3px" width="100%" mar_top="20px" mar_bot="px"]
' Use of DOM to remove empty tags Order is important. This DOM section has to be done before the "Search and Replace" sets/searches.

Dim XMLDOC As MSXML2.DOMDocument
Dim listDOC As IXMLDOMNodeList
Dim objDOC As IXMLDOMNode

Set XMLDOC = CreateObject("MSXML2.DOMDocument")
XMLDOC.async = False: XMLDOC.validateOnParse = False
XMLDOC.Load (FileName)

Set listDOC = XMLDOC.SelectNodes("//*[not(node())]")

For Each Item In listDOC
Item.ParentNode.RemoveChild (Item)
Next

' Need to write results back out to file
XMLDOC.Save (FileName)

Set XMLDOC = Nothing
Set listDOC = Nothing
Set objDOC = Nothing

Once I know I have successfully submitted the xml files to the government, I will update my webpages to include the new codes for processing year 2017. Hopefully that will be in mid-March.