I am trying to run and fix the below excel 2010 vba
and getting an object variable or with block variable not set
on Set imgNode = oXMLFile.DocumentElement.SelectNodes("/Batch/Entry/Image")
and can not seem to fix it. I'm sure there are other things that are not correct but this seems to be the first thing. I apologize for the long post, just wanted to include all the details. Thank you :).
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 last 5 digits of 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 'Create nexus directory and folder MyDirectory = "N:\1_DATA\MicroArray\NexusData\" & "2571683" & MyBarCode & "_" & Format(CDate(MyScan), "m-d-yyyy") & "\" If Dir(MyDirectory, vbDirectory) = "" Then MkDir MyDirectory '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, "2571683" & MyBarCode & "_532Block1.txt" & vbTab & "2571683" & 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 & "2571683" & MyBarCode Print #1, "2571683" & MyBarCode & "_532Block2.txt" & vbTab & "2571683" & 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 & "2571683" & MyBarCode Print #1, "2571683" & MyBarCode & "_532Block3.txt" & vbTab & "2571683" & 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 & "2571683" & MyBarCode Print #1, "2571683" & MyBarCode & "_532Block4.txt" & vbTab & "2571683" & 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 & "2571683" & MyBarCode Close #1 ' 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 & "C:\Users\cmccabe\Desktop\EmArray\Design\imagene.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 ' UNINTIALIZE OBJECTS ' Set imgNode = Nothing Set destNode = Nothing Set oXMLFile = Nothing MsgBox ("ImaGene is running, please wait") ' CALL JAVA PROGRAM USING SHELL ' Shell """C:\Program Files\BioDiscovery\ImaGene 9.0\ImaGene.exe"" -batch " _ & XMLFileName, vbNormalFocus 'Update perl values MyCombine = "2571683" & MyDirectory & MyBarCode & "_" & Format(CDate(MyScan), "m-d-yyyy") 'Execute perl Dim wsh As Object Set wsh = VBA.CreateObject("WScript.Shell") Dim waitOnReturn As Boolean: waitOnReturn = True Dim windowStyle As Integer: windowStyle = 1 wsh.Run "C:\Users\cmccabe\Desktop\spikein.bat MyCombine", windowStyle, waitOnReturn 'Close and Exit Application.DisplayAlerts = False Application.Quit End Sub
XML file to update
<?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>
No comments:
Post a Comment