export worksheet to a txt file

**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!

[VBA]

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
[/VBA]

**GTO** · 07-18-2012, 12:58 PM

Hi there,

The first issue I spot is:
[vba]o = DoesWorkSheetExist(QIA, WBname)[/vba]
You are checking to see if the sheet exists, but if it does not, the code continues if the sheet does not exist. It then errors out when it later tries to refer to the non-existant sheet.

Could you attach a sample workbook with a couple of sheets of data that replicate QIAmp Extraction's layout?

As to after the code runs, how do you want the data layed out in the text file? Is there only one column of data; or if several columns, do you want the data columns seperated by commas, tabs, ???

Mark

**jdilts** · 07-19-2012, 06:05 AM

The following code I threw into my vba script at the last minute to catch if the sheet wasn't there. I will look into that further. Thanks for pointing that out. (I've also attached a workbook with some example data)

As for the txt file, there are several columns and it needs to be a csv. Is that do-able?

[VBA]'Retrieve workbook name
Dim WBname As String
WBname = ThisWorkbook.Name

'Catch when QIAamp Extraction does not exist
Dim o
o = DoesWorkSheetExist(QIA, WBname)

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[/VBA]

**Kenneth Hobs** · 07-19-2012, 06:46 AM

In this thread, change the value of wbName to fit your needs. http://www.vbaexpress.com/forum/showthread.php?t=42923

Thread: export worksheet to a txt file

Thread Tools

Display

export worksheet to a txt file

Posting Permissions