PDA

View Full Version : write variable in vba to text file



cmccabe1
12-15-2015, 11:26 AM
The below excel 2010 vba runs perfectly (well used to) except I can not seem to get the variable process (time elapsed) to print from the Call Perl section to the analysis.txt, which captures some information regarding the analysis. I tried to use a function (in bold) to accomplish this, but am not using it correct? I hope the below is a good start. Thank you :).

VB


Private Sub CommandButton3_Click()

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

'GET USER INPUT '
Line1:
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 - 1, 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 VERIFY FOLDER '
MyDirectory = "N:\1_DATA\MicroArray\NexusData\" & "2571683" & MyBarCode & "_" & Format(CDate(MyScan), "m-d-yyyy") & "\"
If Dir(MyDirectory, vbDirectory) = "" Then
MkDir MyDirectory
Else
MsgBox ("Already exsists! Please enter again")
GoTo Line1
End If


'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 & "Location" & vbTab & "Barcode" & vbTab & "Medical Record" & vbTab & "Date of Birth" & vbTab & "Order Date"
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 & vbTab & ActiveSheet.Range("C201").Value & vbTab & ActiveSheet.Range("D201").Value & vbTab & ActiveSheet.Range("E201").Value
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 & vbTab & ActiveSheet.Range("C202").Value & vbTab & ActiveSheet.Range("D202").Value & vbTab & ActiveSheet.Range("E202").Value
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 & vbTab & ActiveSheet.Range("C203").Value & vbTab & ActiveSheet.Range("D203").Value & vbTab & ActiveSheet.Range("E203").Value
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 & vbTab & ActiveSheet.Range("C201").Value & vbTab & ActiveSheet.Range("D204").Value & vbTab & ActiveSheet.Range("E204").Value
Close #1


'CREATE ANALYSIS LOG '

Open MyDirectory & "analysis.txt" For Output As #2
Print #2, "Analysis done by" & " " & Environ("UserName") & " " & "on" & " " & Date & " " & "at" & " " & Time & " " & "and took" & ret & "to complete"
Close #2


'CONFIRM ENTRIES '
Dim Patient As String
Dim Barcode As String
Dim Directory As String
Dim MyFile As Variant
Dim MyFolder As String
Dim i As Long


Directory = "N:\1_DATA\MicroArray\NexusData\" & "2571683" & MyBarCode & "_" & Format(CDate(MyScan), "m-d-yyyy")

Patient = ActiveSheet.Range("B9").Value & " " & ActiveSheet.Range("C9").Value & " " & ActiveSheet.Range("D9").Value & " " & ActiveSheet.Range("E9").Value
Barcode = "2571683" & MyBarCode
MsgBox "The patients that will be analyzed are:" + Patient & "The barcode is:" + Barcode




iYesNo = MsgBox("Do the patients and barcode match the setup sheet?", vbYesNoCancel)
Select Case iYesNo
Case vbYes
GoTo Line2
Case vbNo
MsgBox ("Doesn't match! Please enter again")
Call DeleteFolder(Directory)
GoTo Line1
End Select


'ADD VBA REFERENCE: MICROSOFT XML, v3.0 or v6.0 '
Line2:
Dim oXMLFile As New MSXML2.DOMDocument
Dim imgNode As MSXML2.IXMLDOMNodeList, destNode As MSXML2.IXMLDOMNodeList
Dim XML*File*Name As String


XML*File*Name = "C:\Users\cmccabe\Desktop\EmArray\Design\imagene.bch"
oXMLFile.Load (XML*File*Name)

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


Set destNode = oXMLFile.DocumentElement.SelectNodes("/Batch/Entry/Destination")
destNode(0).Text = MyDirectory
destNode(1).Text = MyDirectory

'SAVE UPDATED XML '
oXMLFile.Save XML*File*Name

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

'CALCULATE TIME FOR PROCESS '
Function Process() As String

Dim StartTime As Double
Dim MinutesElapsed As String

StartTime = Timer ' define start time
'CALL JAVA PROGRAM USING SHELL AND COMPLETE PROCESS '
Dim wshell As Object
Set wshell = CreateObject("wscript.shell")

Dim waitOnReturn As Boolean: waitOnReturn = True
Dim windowStyle As Integer: windowStyle = 1
wshell.Run Chr(34) & "C:\Users\cmccabe\Desktop\EmArray\Design\java.bat", windowsStyle, waitOnReturn

'UPDATE PERL VARIABLES USING SHELL '
Dim PerlCommand As String, PerlParameters As String, VarDirectory As String
Dim var As String, var1 As String, var2 As String, var3 As String

MsgBox ("Verifying spike-ins, please wait")

VarDirectory = "N:\1_DATA\MicroArray\NexusData\" & "2571683" & MyBarCode & "_" & Format(CDate(MyScan), "m-d-yyyy")

var = VarDirectory
var1 = "sample_descriptor.txt"
var2 = "C:\cygwin\home\cmccabe\test_probes8.txt"
var3 = var & "\" & "output.txt"

'CALL PERL '
PerlCommand = """C:\Users\cmccabe\Desktop\EmArray\Design\perl.bat"""
PerlParameters = """" & var & """" & " " & _
"""" & var1 & """" & " " & _
"""" & var2 & """" & " " & _
"""" & var3 & """"
CreateObject("wscript.shell").Run PerlCommand & " " & PerlParameters, windowsStyle, waitOnReturn

MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss") 'elapsed seconds
Process = MinutesElapsed & " minutes"
ret = Process()
Write #1, ret
End Function

iYesNo = MsgBox("ImaGene and spike-in anlysis complete, do you want to run NxClinical?", vbYesNoCancel)


Select Case iYesNo


Case vbYes
'CALL AND EXECUTE NXCLINICAL '
Dim x As Variant
Dim Path As String


'SET INSTALLATION PATH '
Path = "C:\Program Files\BioDiscovery\NxClinical Client\NxClinical.exe"


x = Shell(Path, vbNormalFocus)

Case vbNo
'CLOSE AND EXIT '
Application.DisplayAlerts = False
Application.Quit
End Select

End Function

Leith Ross
12-15-2015, 10:01 PM
Hello cmccabe1,

Deja vu! You must be experiencing the same problem I am with the Excel Forum. I have been locked out for over 24 hours. Always seems to happen this time of year.

I don't see any bold text in your post. Maybe you can post just that section of the code.

Bob Phillips
12-16-2015, 03:24 AM
If you look Leith, you can see the markup characters around the inline function declaration


Function Process() As String

and the lines


Process = MinutesElapsed & " minutes"
ret = Process()
Write #1, ret
End Function

Leith Ross
12-16-2015, 02:38 PM
Hello xld,

Thanks for pointing that out. If I have to refer to the source code to determine what is supposed to be "highlighted" on the screen then why bother displaying the code in a window that should highlight the code's text?

cmccabe1
12-16-2015, 03:40 PM
Thank you XLD and I apologize for the confusion Leith. Yes those are lines that do not seem to work. Thank you both very much :).

Bob Phillips
12-17-2015, 01:54 AM
Hello xld,

Thanks for pointing that out. If I have to refer to the source code to determine what is supposed to be "highlighted" on the screen then why bother displaying the code in a window that should highlight the code's text?

I understand that, but the OP quite reasonably trid to highlight them with bold markups, but the VBA tags seem to override/ignore that. Anyway, ultimately you have to refer to the code, that is where it is documented :)