XML : updated xml through excel 2010 vba and calling java command

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 2010 vb. 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 VB 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 oXML­File = CreateObject("Microsoft.XMLDOM")      XML­File­Name = "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>    

No comments:

Post a Comment