Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automated multiple text files into multiple sheets in one workbook | Excel Discussion (Misc queries) | |||
Multiple Sheets (Need to create 500 individual sheets in one workbook, pulling DATA | Excel Worksheet Functions | |||
extract data from a text file | Excel Programming | |||
Extract data from multiple sheets | Excel Programming | |||
Extract Data From Multiple Excel Files - One File | Excel Programming |