updated xml through excel 2010 vba and calling command for use in windows command line

Question

I am in the process of trying to automate several analysis steps and having some trouble updating and xml file and calling a java command in an excel-vba. The below runs and produces the desired output up until the update bch portion. Starting there I am not sure the syntax is correct for updating the bch file which is in xml format. There are select fields in the xml I am trying to update and then a java command in run. The fields in the bch file to update are both instances of <Image> and <Destination>. The vba has the values to update the bch file I just have never done it and do not think the syntax is correct. I apologize for the lengthy post I just wanted to be complete. Thank you :).

Current VB

Private Sub CommandButton3_Click()

Dim MyBarCode   As String      ' Enter Barcode
Dim MyScan      As String      ' Enter ScanDate
Dim MyDirectory As String

MyBarCode = Application.InputBox("Please enter the barcode", "Bar Code", Type:=2)
If MyBarCode = "False" Then Exit Sub   'user canceled
Do
    MyScan = Application.InputBox("Please enter scan date", "Scan Date", Date, Type:=2)
    If MyScan = "False" Then Exit Sub   'user canceled
    If IsDate(MyScan) Then Exit Do
    MsgBox "Please enter a valid date format. ", vbExclamation, "Invalid Date Entry"
Loop

Range("B20").Value = MyBarCode
Range("B21").Value = CDate(MyScan)

'Create nexus directory and folder
MyDirectory = "N:\1_DATA\MicroArray\NexusData\" & MyBarCode & "_" & Format(CDate(MyScan), "m-d-yyyy") & "\"
If Dir(MyDirectory, vbDirectory) = "" Then MkDir MyDirector

'Write to text file
    Open MyDirectory & "sample_descriptor.txt" For Output As #1
    Print #1, "Experiment Sample" & vbTab & "Control Sample" & vbTab & "Display Name" & vbTab & "Gender" & vbTab & "Control Gender" & vbTab & "Spikein" & vbTab & "SpikeIn Location" & vbTab & "Barcode"
    Print #1, MyBarCode & "_532Block1.txt" & vbTab & MyBarCode & "_635Block1.txt" & vbTab & ActiveSheet.Range("B8").Value & " " & ActiveSheet.Range("B9").Value & vbTab & ActiveSheet.Range("B10").Value & vbTab & ActiveSheet.Range("B5").Value & vbTab & ActiveSheet.Range("B11").Value & vbTab & ActiveSheet.Range("B12").Value & vbTab & ActiveSheet.Range("B20").Value
    Print #1, MyBarCode & "_532Block2.txt" & vbTab & MyBarCode & "_635Block2.txt" & vbTab & ActiveSheet.Range("C8").Value & " " & ActiveSheet.Range("C9").Value & vbTab & ActiveSheet.Range("C10").Value & vbTab & ActiveSheet.Range("C5").Value & vbTab & ActiveSheet.Range("C11").Value & vbTab & ActiveSheet.Range("C12").Value & vbTab & ActiveSheet.Range("B20").Value
    Print #1, MyBarCode & "_532Block3.txt" & vbTab & MyBarCode & "_635Block3.txt" & vbTab & ActiveSheet.Range("D8").Value & " " & ActiveSheet.Range("D9").Value & vbTab & ActiveSheet.Range("D10").Value & vbTab & ActiveSheet.Range("D5").Value & vbTab & ActiveSheet.Range("D11").Value & vbTab & ActiveSheet.Range("D12").Value & vbTab & ActiveSheet.Range("B20").Value
    Print #1, MyBarCode & "_532Block4.txt" & vbTab & MyBarCode & "_635Block4.txt" & vbTab & ActiveSheet.Range("E8").Value & " " & ActiveSheet.Range("E9").Value & vbTab & ActiveSheet.Range("E10").Value & vbTab & ActiveSheet.Range("E5").Value & vbTab & ActiveSheet.Range("E11").Value & vbTab & ActiveSheet.Range("E12").Value & vbTab & ActiveSheet.Range("B20").Value
    Close #1

'Open bch
    Set oXMLFile = CreateObject(“Microsoft.XMLDOM”)
    XMLFileName = “MyDirectory & MyScan.bch”
    oXMLFile.Load (MyScan.bch)

'Update bch
    Set TitleNode = oXMLFile.SelectSingleNode(“ / Batch / Entry / Image”)
        TitleNode.Text = "I:\ & MyBarCode & "_532"
    Set TitleNode = oXMLFile.SelectSingleNode(“ / Batch / Entry / Destination”)
        TitleNode.Text = "MyDirectory & MyBarCode & "_" & Format(CDate(MyScan), "m-d-yyyy") & "\"
    Set TitleNode = oXMLFile.SelectSingleNode(“ / Batch / /Entry / Entry / Image”)
        TitleNode.Text = "I:\ & MyBarCode & "_635"
    Set TitleNode = oXMLFile.SelectSingleNode(“ / Batch / /Entry / Entry / Destination”)
        TitleNode.Text = “MyDirectory & MyBarCode & "_" & Format(CDate(MyScan), "m-d-yyyy") & "\"

           If MsgBox("The project file has been created. " & _
                     "Do you want to run ImaGene?", _
           vbQuestion + vbYesNo) = vbYes Then

'Run imaGene java
    cd "C:\Program Files\BioDiscovery\ImaGene 9.0"
    ImaGene.exe -batch "MyDirectory & "\" & "_" & MyScan.bch"

BCH File format before updating

  <?xml version="1.0" encoding="UTF-8"?>

  <Batch>
  <Entry>
  <Image>I:\257168310011_532.tif</Image>
   <Template>C:\Users\cmccabe\Desktop\071683\LC_106Genes_071683_D_20141205.gal</Template>
   <Configuration>C:\Users\cmccabe\Desktop\EmArray\Design\Exon_Array_Parameters.xml      </Configuration>
  <Destination>N:\1_DATA\MicroArray\NexusData\12345_11-19-2015</Destination>
  <Channel>0</Channel>
  <ChannelName></ChannelName>
  <SubstituteGridImage>null</SubstituteGridImage>
  <SubstituteGridChannel>0</SubstituteGridChannel>
  <AdjustGrid>true</AdjustGrid>
  <AdjustSpots>true</AdjustSpots>
  <AlignImages>true</AlignImages>
  <Normalize>false</Normalize>
  <Analyze>false</Analyze>
  </Entry>
  <Entry>
  <Image>I:\257168310011_635.tif</Image>
<Template>C:\Users\cmccabe\Desktop\071683\LC_106Genes_071683_D_20141205.gal</Template>
<Configuration>C:\Users\cmccabe\Desktop\EmArray\Design\Exon_Array_Parameters.xml     </Configuration>
 <Destination>N:\1_DATA\MicroArray\NexusData\12345_11-19-2015</Destination>
 <Channel>0</Channel>
 <ChannelName></ChannelName>
 <SubstituteGridImage>null</SubstituteGridImage>
 <SubstituteGridChannel>0</SubstituteGridChannel>
 <AdjustGrid>true</AdjustGrid>
 <AdjustSpots>true</AdjustSpots>
 <AlignImages>true</AlignImages>
 <Normalize>false</Normalize>
 <Analyze>false</Analyze>
 </Entry>
 </Batch>

get rid of the smartquotes and spaces in your xpath queries perhaps. Otherwise (those might just be typo when you copied to StackOverflow), it looks like you're not writing the xml back to file. You've loaded in into memory and are manipulating it, but you need to write it back out to the file before you run anything against that file. — David Zemens, Nov 20 '15 at 21:11
Replace the deprecated `CreateObject("Microsoft.XMLDOM")` with the up-to-date version `CreateObject("MSXML2.DOMDocument.6.0")` — barrowc, Nov 21 '15 at 00:05

score 2 · Accepted Answer · answered Nov 21 '15 at 03:11

Consider using XPath in VBA to extract all items of certain node. Then, using VBA's Shell() to call the external Java program:

' Open bch '
    ' ADD VBA REFERENCE: MICROSOFT XML, v3.0 or v6.0 '
    Dim oXMLFile As New MSXML2.DOMDocument
    Dim imgNode As MSXML2.IXMLDOMNodeList, destNode As MSXML2.IXMLDOMNodeList
    Dim XMLFileName As String

    XMLFileName = MyDirectory & "MyScan.bch"
    oXMLFile.Load (XMLFileName)

    ' EXTRACT NODES INTO LIST AND REWRITE NODES '
    Set imgNode = oXMLFile.DocumentElement.SelectNodes("/Batch/Entry/Image")
    imgNode(0).Text = "I:\" & MyBarCode & "_532"
    imgNode(1).Text = "I:\" & MyBarCode & "_635"

    Set destNode = oXMLFile.DocumentElement.SelectNodes("/Batch/Entry/Destination")
    destNode(0).Text = MyDirectory & MyBarCode & "_" & Format(CDate(MyScan), "m-d-yyyy")
    destNode(1).Text = MyDirectory & MyBarCode & "_" & Format(CDate(MyScan), "m-d-yyyy")

    ' SAVE UPDATED XML '
    oXMLFile.Save XMLFileName

    ' CALL JAVA PROGRAM USING SHELL '
    Shell """C:\Program Files\BioDiscovery\ImaGene 9.0\ImaGene.exe"" batch " _
              & XMLFileName, vbNormalFocus

    ' UNINTIALIZE OBJECTS '    
    Set imgNode = Nothing
    Set destNode = Nothing        
    Set oXMLFile = Nothing

"MSXML2.DOMDocument" is the equivalent of "MSXML2.DOMDocument30" which doesn't use XPath for "selectNodes". It uses the older XSL Patterns instead - see [this question](http://stackoverflow.com/questions/2099880/whats-the-difference-between-xsl-pattern-and-xpath-in-syntax). Either use "MSXML2.DOMDocument60" or use `oXMLFile.setProperty "SelectionLanguage", "XPath"` - see [this article](http://blogs.msdn.com/b/xmlteam/archive/2006/10/23/using-the-right-version-of-msxml-in-internet-explorer.aspx) for more on this — barrowc, Nov 22 '15 at 22:06

updated xml through excel 2010 vba and calling command for use in windows command line

1 Answers1