jdilts
07-18-2012, 06:58 AM
How can I export a worksheet to a txt file? I need to do so for every worksheet I create in the script below. Also, vba newbie. If there's a better way to write this code, please let me know.
Thanks!
Option Explicit
Sub BarcodeUpload()
'Worksheet variables
Dim QIA As String
QIA = "QIAmp Extraction"
'Retrieve workbook name
Dim WBname As String
WBname = ThisWorkbook.Name
'Catch when QIAamp Extraction does not exist
Dim o
o = DoesWorkSheetExist(QIA, WBname)
'Array with entire sequence column
Dim sequenceCol(32) As String
sequenceCol(1) = "CTAAGGTAAC"
sequenceCol(2) = "TAAGGAGAAC"
sequenceCol(3) = "AAGAGGATTC"
sequenceCol(4) = "TACCAAGATC"
sequenceCol(5) = "CAGAAGGAAC"
sequenceCol(6) = "CTGCAAGTTC"
sequenceCol(7) = "TTCGTGATTC"
sequenceCol(8) = "TTCCGATAAC"
sequenceCol(9) = "TGAGCGGAAC"
sequenceCol(10) = "CTGACCGAAC"
sequenceCol(11) = "TCCTCGAATC"
sequenceCol(12) = "TAGGTGGTTC"
sequenceCol(13) = "TCTAACGGAC"
sequenceCol(14) = "TTGGAGTGTC"
sequenceCol(15) = "TCTAGAGGTC"
sequenceCol(16) = "TCTGGATGAC"
sequenceCol(17) = "TCTATTCGTC"
sequenceCol(18) = "AGGCAATTGC"
sequenceCol(19) = "TTAGTCGGAC"
sequenceCol(20) = "CAGATCCATC"
sequenceCol(21) = "TCGCAATTAC"
sequenceCol(22) = "TTCGAGACGC"
sequenceCol(23) = "TGCCACGAAC"
sequenceCol(24) = "AACCTCATTC"
sequenceCol(25) = "CCTGAGATAC"
sequenceCol(26) = "TTACAACCTC"
sequenceCol(27) = "AACCATCCGC"
sequenceCol(28) = "ATCCGGAATC"
sequenceCol(29) = "TCGACCACTC"
sequenceCol(30) = "CGAGGTTATC"
sequenceCol(31) = "TCCAAGCTGC"
sequenceCol(32) = "TCTTACACAC"
'------------------------------------------------------------------------------------------------------------------
' 33 patients or less
'------------------------------------------------------------------------------------------------------------------
'Worksheet variables
Dim BC As String
BC = "BarcodeUpload1"
'Create new worksheet Barcode Upload
Sheets.Add.Name = BC
'Format header in Barcode Upload
Sheets(BC).Cells(1, 1).Value = "id_str"
Sheets(BC).Cells(1, 2).Value = "type"
Sheets(BC).Cells(1, 3).Value = "sequence"
Sheets(BC).Cells(1, 4).Value = "floworder"
Sheets(BC).Cells(1, 5).Value = "index"
Sheets(BC).Cells(1, 6).Value = "annotation"
Sheets(BC).Cells(1, 7).Value = "adapater"
Sheets(BC).Cells(1, 8).Value = "score_mode"
Sheets(BC).Cells(1, 9).Value = "score_cutoff"
'Starting place in QIA
Dim QIArow, QIAcol As Long
QIArow = 7
'Starting place in BC
Dim BCrow, BCcol As Long
BCrow = 2
'For each sample
Dim y, FLAG32 As Long
For y = 1 To 32
If y = 32 Then
FLAG32 = 1
End If
Dim check As Long
check = Sheets(QIA).Cells(QIArow, 1) 'gets the sample order number
If check = y Then 'checks if it equals y
Dim id As String
id = Sheets(QIA).Cells(QIArow, 2) 'if so, grab the id
Sheets(BC).Cells(BCrow, 1).Value = id 'put id in Barcode Upload worksheet
Sheets(BC).Cells(BCrow, 3).Value = sequenceCol(y) 'put id in Barcode Upload worksheet
Sheets(BC).Cells(BCrow, 5).Value = y 'index
Sheets(BC).Cells(BCrow, 7).Value = "GAT" 'adaptor
Sheets(BC).Cells(BCrow, 8).Value = 1 'score_mode
Sheets(BC).Cells(BCrow, 9).Value = 2 'score_cutoff
'add one to current row positions
QIArow = QIArow + 1
BCrow = BCrow + 1
Else
Exit For
End If
Next y
'------------------------------------------------------------------------------------------------------------------
'more than 32 and under 65 patients
'------------------------------------------------------------------------------------------------------------------
'check if there is a 33 position. If so, then create a new worksheet
If 33 = Sheets(QIA).Cells(39, 1).Value Then
'Worksheet variables
BC = "BarcodeUpload2"
'Create new worksheet Barcode Upload2
Sheets.Add.Name = BC
'Format header in Barcode Upload
Sheets(BC).Cells(1, 1).Value = "id_str"
Sheets(BC).Cells(1, 2).Value = "type"
Sheets(BC).Cells(1, 3).Value = "sequence"
Sheets(BC).Cells(1, 4).Value = "floworder"
Sheets(BC).Cells(1, 5).Value = "index"
Sheets(BC).Cells(1, 6).Value = "annotation"
Sheets(BC).Cells(1, 7).Value = "adapater"
Sheets(BC).Cells(1, 8).Value = "score_mode"
Sheets(BC).Cells(1, 9).Value = "score_cutoff"
'Starting place in QIA
QIArow = 39
'Starting place in BC
BCrow = 2
'For each sample
Dim h, FLAG64 As Long
h = 33
For y = 1 To 32
If y = 32 Then
FLAG64 = 1
End If
check = Sheets(QIA).Cells(QIArow, 1) 'gets the sample order number
If check = h Then 'checks if it equals y
id = Sheets(QIA).Cells(QIArow, 2) 'if so, grab the id
Sheets(BC).Cells(BCrow, 1).Value = id 'put id in Barcode Upload worksheet
Sheets(BC).Cells(BCrow, 3).Value = sequenceCol(y) 'put id in Barcode Upload worksheet
Sheets(BC).Cells(BCrow, 5).Value = y 'index
Sheets(BC).Cells(BCrow, 7).Value = "GAT" 'adaptor
Sheets(BC).Cells(BCrow, 8).Value = 1 'score_mode
Sheets(BC).Cells(BCrow, 9).Value = 2 'score_cutoff
'add one to current row positions
QIArow = QIArow + 1
BCrow = BCrow + 1
h = h + 1
Else
Exit For
End If
Next y
End If
'------------------------------------------------------------------------------------------------------------------
'more than 64 and under 97 patients
'------------------------------------------------------------------------------------------------------------------
'check if there is a 65 position. If so, then create a new worksheet
If 65 = Sheets(QIA).Cells(71, 1).Value Then
'Worksheet variables
BC = "BarcodeUpload3"
'Create new worksheet Barcode Upload2
Sheets.Add.Name = BC
'Format header in Barcode Upload
Sheets(BC).Cells(1, 1).Value = "id_str"
Sheets(BC).Cells(1, 2).Value = "type"
Sheets(BC).Cells(1, 3).Value = "sequence"
Sheets(BC).Cells(1, 4).Value = "floworder"
Sheets(BC).Cells(1, 5).Value = "index"
Sheets(BC).Cells(1, 6).Value = "annotation"
Sheets(BC).Cells(1, 7).Value = "adapater"
Sheets(BC).Cells(1, 8).Value = "score_mode"
Sheets(BC).Cells(1, 9).Value = "score_cutoff"
'Starting place in QIA
QIArow = 71
'Starting place in BC
BCrow = 2
'For each sample
h = 65
For y = 1 To 32
check = Sheets(QIA).Cells(QIArow, 1) 'gets the sample order number
If check = h Then 'checks if it equals y
id = Sheets(QIA).Cells(QIArow, 2) 'if so, grab the id
Sheets(BC).Cells(BCrow, 1).Value = id 'put id in Barcode Upload worksheet
Sheets(BC).Cells(BCrow, 3).Value = sequenceCol(y) 'put id in Barcode Upload worksheet
Sheets(BC).Cells(BCrow, 5).Value = y 'index
Sheets(BC).Cells(BCrow, 7).Value = "GAT" 'adaptor
Sheets(BC).Cells(BCrow, 8).Value = 1 'score_mode
Sheets(BC).Cells(BCrow, 9).Value = 2 'score_cutoff
'add one to current row positions
QIArow = QIArow + 1
BCrow = BCrow + 1
h = h + 1
Else
Exit For
End If
Next y
End If
End Sub
Public Function DoesWorkSheetExist(WorkSheetName As String, WorkBookName As String)
Dim WS As Worksheet
On Error Resume Next
If WorkBookName = vbNullString Then
Set WS = Sheets(WorkSheetName)
Else
Set WS = Workbooks(WorkBookName).Sheets(WorkSheetName)
End If
On Error GoTo 0
DoesWorkSheetExist = Not WS Is Nothing
End Function
Thanks!
Option Explicit
Sub BarcodeUpload()
'Worksheet variables
Dim QIA As String
QIA = "QIAmp Extraction"
'Retrieve workbook name
Dim WBname As String
WBname = ThisWorkbook.Name
'Catch when QIAamp Extraction does not exist
Dim o
o = DoesWorkSheetExist(QIA, WBname)
'Array with entire sequence column
Dim sequenceCol(32) As String
sequenceCol(1) = "CTAAGGTAAC"
sequenceCol(2) = "TAAGGAGAAC"
sequenceCol(3) = "AAGAGGATTC"
sequenceCol(4) = "TACCAAGATC"
sequenceCol(5) = "CAGAAGGAAC"
sequenceCol(6) = "CTGCAAGTTC"
sequenceCol(7) = "TTCGTGATTC"
sequenceCol(8) = "TTCCGATAAC"
sequenceCol(9) = "TGAGCGGAAC"
sequenceCol(10) = "CTGACCGAAC"
sequenceCol(11) = "TCCTCGAATC"
sequenceCol(12) = "TAGGTGGTTC"
sequenceCol(13) = "TCTAACGGAC"
sequenceCol(14) = "TTGGAGTGTC"
sequenceCol(15) = "TCTAGAGGTC"
sequenceCol(16) = "TCTGGATGAC"
sequenceCol(17) = "TCTATTCGTC"
sequenceCol(18) = "AGGCAATTGC"
sequenceCol(19) = "TTAGTCGGAC"
sequenceCol(20) = "CAGATCCATC"
sequenceCol(21) = "TCGCAATTAC"
sequenceCol(22) = "TTCGAGACGC"
sequenceCol(23) = "TGCCACGAAC"
sequenceCol(24) = "AACCTCATTC"
sequenceCol(25) = "CCTGAGATAC"
sequenceCol(26) = "TTACAACCTC"
sequenceCol(27) = "AACCATCCGC"
sequenceCol(28) = "ATCCGGAATC"
sequenceCol(29) = "TCGACCACTC"
sequenceCol(30) = "CGAGGTTATC"
sequenceCol(31) = "TCCAAGCTGC"
sequenceCol(32) = "TCTTACACAC"
'------------------------------------------------------------------------------------------------------------------
' 33 patients or less
'------------------------------------------------------------------------------------------------------------------
'Worksheet variables
Dim BC As String
BC = "BarcodeUpload1"
'Create new worksheet Barcode Upload
Sheets.Add.Name = BC
'Format header in Barcode Upload
Sheets(BC).Cells(1, 1).Value = "id_str"
Sheets(BC).Cells(1, 2).Value = "type"
Sheets(BC).Cells(1, 3).Value = "sequence"
Sheets(BC).Cells(1, 4).Value = "floworder"
Sheets(BC).Cells(1, 5).Value = "index"
Sheets(BC).Cells(1, 6).Value = "annotation"
Sheets(BC).Cells(1, 7).Value = "adapater"
Sheets(BC).Cells(1, 8).Value = "score_mode"
Sheets(BC).Cells(1, 9).Value = "score_cutoff"
'Starting place in QIA
Dim QIArow, QIAcol As Long
QIArow = 7
'Starting place in BC
Dim BCrow, BCcol As Long
BCrow = 2
'For each sample
Dim y, FLAG32 As Long
For y = 1 To 32
If y = 32 Then
FLAG32 = 1
End If
Dim check As Long
check = Sheets(QIA).Cells(QIArow, 1) 'gets the sample order number
If check = y Then 'checks if it equals y
Dim id As String
id = Sheets(QIA).Cells(QIArow, 2) 'if so, grab the id
Sheets(BC).Cells(BCrow, 1).Value = id 'put id in Barcode Upload worksheet
Sheets(BC).Cells(BCrow, 3).Value = sequenceCol(y) 'put id in Barcode Upload worksheet
Sheets(BC).Cells(BCrow, 5).Value = y 'index
Sheets(BC).Cells(BCrow, 7).Value = "GAT" 'adaptor
Sheets(BC).Cells(BCrow, 8).Value = 1 'score_mode
Sheets(BC).Cells(BCrow, 9).Value = 2 'score_cutoff
'add one to current row positions
QIArow = QIArow + 1
BCrow = BCrow + 1
Else
Exit For
End If
Next y
'------------------------------------------------------------------------------------------------------------------
'more than 32 and under 65 patients
'------------------------------------------------------------------------------------------------------------------
'check if there is a 33 position. If so, then create a new worksheet
If 33 = Sheets(QIA).Cells(39, 1).Value Then
'Worksheet variables
BC = "BarcodeUpload2"
'Create new worksheet Barcode Upload2
Sheets.Add.Name = BC
'Format header in Barcode Upload
Sheets(BC).Cells(1, 1).Value = "id_str"
Sheets(BC).Cells(1, 2).Value = "type"
Sheets(BC).Cells(1, 3).Value = "sequence"
Sheets(BC).Cells(1, 4).Value = "floworder"
Sheets(BC).Cells(1, 5).Value = "index"
Sheets(BC).Cells(1, 6).Value = "annotation"
Sheets(BC).Cells(1, 7).Value = "adapater"
Sheets(BC).Cells(1, 8).Value = "score_mode"
Sheets(BC).Cells(1, 9).Value = "score_cutoff"
'Starting place in QIA
QIArow = 39
'Starting place in BC
BCrow = 2
'For each sample
Dim h, FLAG64 As Long
h = 33
For y = 1 To 32
If y = 32 Then
FLAG64 = 1
End If
check = Sheets(QIA).Cells(QIArow, 1) 'gets the sample order number
If check = h Then 'checks if it equals y
id = Sheets(QIA).Cells(QIArow, 2) 'if so, grab the id
Sheets(BC).Cells(BCrow, 1).Value = id 'put id in Barcode Upload worksheet
Sheets(BC).Cells(BCrow, 3).Value = sequenceCol(y) 'put id in Barcode Upload worksheet
Sheets(BC).Cells(BCrow, 5).Value = y 'index
Sheets(BC).Cells(BCrow, 7).Value = "GAT" 'adaptor
Sheets(BC).Cells(BCrow, 8).Value = 1 'score_mode
Sheets(BC).Cells(BCrow, 9).Value = 2 'score_cutoff
'add one to current row positions
QIArow = QIArow + 1
BCrow = BCrow + 1
h = h + 1
Else
Exit For
End If
Next y
End If
'------------------------------------------------------------------------------------------------------------------
'more than 64 and under 97 patients
'------------------------------------------------------------------------------------------------------------------
'check if there is a 65 position. If so, then create a new worksheet
If 65 = Sheets(QIA).Cells(71, 1).Value Then
'Worksheet variables
BC = "BarcodeUpload3"
'Create new worksheet Barcode Upload2
Sheets.Add.Name = BC
'Format header in Barcode Upload
Sheets(BC).Cells(1, 1).Value = "id_str"
Sheets(BC).Cells(1, 2).Value = "type"
Sheets(BC).Cells(1, 3).Value = "sequence"
Sheets(BC).Cells(1, 4).Value = "floworder"
Sheets(BC).Cells(1, 5).Value = "index"
Sheets(BC).Cells(1, 6).Value = "annotation"
Sheets(BC).Cells(1, 7).Value = "adapater"
Sheets(BC).Cells(1, 8).Value = "score_mode"
Sheets(BC).Cells(1, 9).Value = "score_cutoff"
'Starting place in QIA
QIArow = 71
'Starting place in BC
BCrow = 2
'For each sample
h = 65
For y = 1 To 32
check = Sheets(QIA).Cells(QIArow, 1) 'gets the sample order number
If check = h Then 'checks if it equals y
id = Sheets(QIA).Cells(QIArow, 2) 'if so, grab the id
Sheets(BC).Cells(BCrow, 1).Value = id 'put id in Barcode Upload worksheet
Sheets(BC).Cells(BCrow, 3).Value = sequenceCol(y) 'put id in Barcode Upload worksheet
Sheets(BC).Cells(BCrow, 5).Value = y 'index
Sheets(BC).Cells(BCrow, 7).Value = "GAT" 'adaptor
Sheets(BC).Cells(BCrow, 8).Value = 1 'score_mode
Sheets(BC).Cells(BCrow, 9).Value = 2 'score_cutoff
'add one to current row positions
QIArow = QIArow + 1
BCrow = BCrow + 1
h = h + 1
Else
Exit For
End If
Next y
End If
End Sub
Public Function DoesWorkSheetExist(WorkSheetName As String, WorkBookName As String)
Dim WS As Worksheet
On Error Resume Next
If WorkBookName = vbNullString Then
Set WS = Sheets(WorkSheetName)
Else
Set WS = Workbooks(WorkBookName).Sheets(WorkSheetName)
End If
On Error GoTo 0
DoesWorkSheetExist = Not WS Is Nothing
End Function