Extract Workbook Data to Text File (multiple sheets)
 

Anyone have any code that can read through a selected workbook, save each
sheet to a text file (excluding header row) the code would need to append
each sheets data to that text file so the text file has all the data from the
workbook in one data file.

(I used the merge worksheets code from another post, but the files were so
large they exceeded Excel's capability - so now I need to go text).

Thanks in advance.

Extract Workbook Data to Text File (multiple sheets)
 

The macro below will export to a comma delimited file

HTH,
Bernie
MS Excel MVP


Sub ExportAllSheetsToPRN()

Dim fName As String
Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim mySht As Worksheet

fName = "C:\Documents and Settings\ExportAllSheets.prn"

Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile

Open fName For Output Access Write As #FNum

For Each mySht In ActiveWorkbook.Worksheets
With mySht.Range("A2", mySht.Range("A65536").End(xlUp).End(xlToRight))
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With


For RowNdx = StartRow To EndRow
WholeLine = mySht.Cells(RowNdx, StartCol).Text
For ColNdx = StartCol + 1 To EndCol
WholeLine = WholeLine & ", " & mySht.Cells(RowNdx, ColNdx).Text
Next ColNdx
Print #FNum, WholeLine
Next RowNdx
Next mySht

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum

End Sub



"NYSA-HD" wrote in message
...
Anyone have any code that can read through a selected workbook, save each
sheet to a text file (excluding header row) the code would need to append
each sheets data to that text file so the text file has all the data from the
workbook in one data file.

(I used the merge worksheets code from another post, but the files were so
large they exceeded Excel's capability - so now I need to go text).

Thanks in advance.

Extract Workbook Data to Text File (multiple sheets)
 

This worked fantastic! Thank you again for your help.



"Bernie Deitrick" wrote:

The macro below will export to a comma delimited file

HTH,
Bernie
MS Excel MVP


Sub ExportAllSheetsToPRN()

Dim fName As String
Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim mySht As Worksheet

fName = "C:\Documents and Settings\ExportAllSheets.prn"

Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile

Open fName For Output Access Write As #FNum

For Each mySht In ActiveWorkbook.Worksheets
With mySht.Range("A2", mySht.Range("A65536").End(xlUp).End(xlToRight))
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With


For RowNdx = StartRow To EndRow
WholeLine = mySht.Cells(RowNdx, StartCol).Text
For ColNdx = StartCol + 1 To EndCol
WholeLine = WholeLine & ", " & mySht.Cells(RowNdx, ColNdx).Text
Next ColNdx
Print #FNum, WholeLine
Next RowNdx
Next mySht

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum

End Sub



"NYSA-HD" wrote in message
...
Anyone have any code that can read through a selected workbook, save each
sheet to a text file (excluding header row) the code would need to append
each sheets data to that text file so the text file has all the data from the
workbook in one data file.

(I used the merge worksheets code from another post, but the files were so
large they exceeded Excel's capability - so now I need to go text).

Thanks in advance.