CODEWITHSUNDEEP
excelvba
alphaService
alphaService

Reputation: 131

Export data from 5 worksheets into 5 text files

My workbook contains n-number of worksheets. 5 of these are containing data that should be exported to text files.

In a sub procedure I have created 5 text files named export01 to export05 The 5 worksheets that contain the data to export are named in the same way export01 to export05.

The data of worksheet export01 should be written in text file export01 and so on.

For the export of data for table export01 I have created this code but I don't know how to export the other 4 table's data.

Should I copy the code just 4 times down and change what is needed or is there a much better solution? Thank you for your help!

sub exportTxt()
Dim rng As Range
Dim myTable As String
Dim myFile As String

myTable = "export1"
myFile = "export1.txt"

Sheets(myTable).Select
Set rng = Range("a1").CurrentRegion
Open myFile For Output As #1
   For i = 1 To rng.Rows.Count
       For j = 1 To rng.columns.Count
           cellValue = rng.Cells(i, j).Value
       If j = rng.columns.Count Then
           Print #1, cellValue
       Else
       Print #1, cellValue,
       End If
       Next j
   Next i
Close #1
End Sub

Upvotes: 3

Views: 81

Answers (2)

brettdj
brettdj

Reputation: 55702

Something like this where your sheetnames are held in an array:

Sub DumpSheets()
Dim ws As Worksheet
For Each ws In Sheets(Array("yoursheetnames","test", "test 2", "Sheet4", "Sheet5"))
    ws.SaveAs "C:\temp\" & ws.Name & "txt", xlTextMSDOS
Next ws
End Sub

Upvotes: 2

hnk
hnk

Reputation: 2214

Put all your code in a giant For Loop

While this isn't the most efficient way to go about things, it will work.

For i = 1 to 5
    myTable = "export" & i
    myFile = "export" & i & ".txt"

    ' The rest of your code goes here

Next i

You might consider writing a line at a time (Writeln) or even using 

WorkBookVariableName.SaveAs myFile, xlCSV

Upvotes: 0

Related Questions