Exporting Excel Sheet Column into a text file

[bnkolev]

Hi All,

I am trying to export the same two columns from each sheet in a workbook (for example A2:A500 and B2:B500).
I need the columns from each sheet to be exported into separate files that I will be prompted to name.
Also, the two columns have to be separated by one space in the text file and the Macro will have to skip any blanks (the columns i need to export have different sizes). I need this very urgently and would GREATLY appreciate your help!

best, Boris

 

[Meesam_ali]

Hi my method seems to be an OVER-KILL but for sure will achieve easily what you want albeit with lot of flexibility. Just change the code where required.
<code>
Sub ArrayFile()


Dim dat1() As String
Dim dat2() As String


Dim i As Integer


Sheets("data").Select 'Select the sheet containing data in current workbook.
LastRowColA = Range("D65536").End(xlUp).Row 'Find last row of data
LastRowColB = Range("E65536").End(xlUp).Row 'Find last row of data




Open "C:\Users\SYED MEESAM ALI\Desktop\excel\files\Result1.txt" For Output As #1 'Output File.
On Error Resume Next
For i = 4 To LastRowColA
ReDim dat1(i) 'Save all the descriptions in an array.
ReDim dat2(i)
dat1(i) = Range("D" & CStr(i)).Value 'From the 1st Column.
dat2(i) = Range("E" & CStr(i)).Value 'From the 2nd Column.
Write #1 , dat1(i), dat2(i)
Next i
Close #1
MsgBox "Successfully Done!"


End Sub
</code>

 

[bnkolev]

Hi my method seems to be an OVER-KILL but for sure will achieve easily what you want albeit with lot of flexibility. Just change the code where required.
<code>
Sub ArrayFile()


Dim dat1() As String
Dim dat2() As String


Dim i As Integer


Sheets("data").Select 'Select the sheet containing data in current workbook.
LastRowColA = Range("D65536").End(xlUp).Row 'Find last row of data
LastRowColB = Range("E65536").End(xlUp).Row 'Find last row of data




Open "C:\Users\SYED MEESAM ALI\Desktop\excel\files\Result1.txt" For Output As #1 'Output File.
On Error Resume Next
For i = 4 To LastRowColA
ReDim dat1(i) 'Save all the descriptions in an array.
ReDim dat2(i)
dat1(i) = Range("D" & CStr(i)).Value 'From the 1st Column.
dat2(i) = Range("E" & CStr(i)).Value 'From the 2nd Column.
Write #1 , dat1(i), dat2(i)
Next i
Close #1
MsgBox "Successfully Done!"


End Sub
</code>

Hi Meesam,

thanks for the quick reply! I have three questions regarding the code:

1) How can I make the code iterate through all the sheets in the workbook and save the data, rather than just one sheet
2) How can I make the data get copied in a text file with different name?
3) The data in the text file appears in the following format " "11","332" " and I need it in " 11 332 ". No semicolons, no comas or quotations.

best, Boris

 

[Meesam_ali]

Ok try this code. It will make separate file for each of your worksheet and the name of file will be name of worksheet. Also it won't output data with double quotes.
<code>
Sub ArrayFile()
Dim ws As Worksheet
Dim LastRowColA As Integer, count As Integer
count = 0
Dim i As Integer


Application.EnableEvents = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False
LastRowColA = Range("D65536").End(xlUp).Row


For Each ws In ThisWorkbook.Worksheets
count = count + 1
Open "C:\Users\SYED MEESAM ALI\Desktop\excel\files" & ws.Name & ".txt" For Output As #1


For i = 4 To LastRowColA
Print #1 , ws.Range("D" & CStr(i)).Value, ws.Range("E" & CStr(i)).Value
Next i
Close #1
Next ws
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.DisplayAlerts = True




MsgBox "Successfully Done!"


End Sub
</code>