Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting to Notepad
I am trying to get excel to export a column to a .txt file is there any way that I can do this so that later on I can refrence this file later on. I know I can copy and paste the colmun but I was hoping that there was a way that I could export it..
Thank Adam Kozul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting to Notepad
I got the following code from this NG. This code assumes the data begins at
A1. I don't recall who to credit, but here's the code: Public Sub ExportToTextFile() 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 CellValue As String Dim FName As String Dim Sep As String Dim SelectionOnly As Boolean FName = "testing.txt" Sep = "," SelectionOnly = True Application.ScreenUpdating = False On Error GoTo EndMacro: FNum = FreeFile If SelectionOnly = True Then With Selection StartRow = .Cells(1).Row StartCol = .Cells(1).Column EndRow = .Cells(.Cells.Count).Row EndCol = .Cells(.Cells.Count).Column End With Else With ActiveSheet.UsedRange StartRow = .Cells(1).Row StartCol = .Cells(1).Column EndRow = .Cells(.Cells.Count).Row EndCol = .Cells(.Cells.Count).Column End With End If Open FName For Output Access Write As #FNum For RowNdx = StartRow To EndRow WholeLine = "" For ColNdx = StartCol To EndCol If Cells(RowNdx, ColNdx).Value = "" Then CellValue = Chr(34) & Chr(34) Else CellValue = _ Application.WorksheetFunction.Text _ (Cells(RowNdx, ColNdx).Value, _ Cells(RowNdx, ColNdx).NumberFormat) End If WholeLine = WholeLine & CellValue & Sep Next ColNdx WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep)) Print #FNum, WholeLine Next RowNdx EndMacro: On Error GoTo 0 Application.ScreenUpdating = True Close #FNum End Sub -- Michael J. Malinsky Pittsburgh, PA "I am a bear of very little brain, and long words bother me." -- AA Milne, Winne the Pooh "Adam Kozul" wrote in message ... I am trying to get excel to export a column to a .txt file is there any way that I can do this so that later on I can refrence this file later on. I know I can copy and paste the colmun but I was hoping that there was a way that I could export it... Thanks Adam Kozul |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting to Notepad
Here is where you got most of it:
http://www.cpearson.com/excel/imptext.htm import/export text files Chip Pearson's site. -- Regards, Tom Ogilvy "Michael Malinsky" wrote in message ... I got the following code from this NG. This code assumes the data begins at A1. I don't recall who to credit, but here's the code: Public Sub ExportToTextFile() 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 CellValue As String Dim FName As String Dim Sep As String Dim SelectionOnly As Boolean FName = "testing.txt" Sep = "," SelectionOnly = True Application.ScreenUpdating = False On Error GoTo EndMacro: FNum = FreeFile If SelectionOnly = True Then With Selection StartRow = .Cells(1).Row StartCol = .Cells(1).Column EndRow = .Cells(.Cells.Count).Row EndCol = .Cells(.Cells.Count).Column End With Else With ActiveSheet.UsedRange StartRow = .Cells(1).Row StartCol = .Cells(1).Column EndRow = .Cells(.Cells.Count).Row EndCol = .Cells(.Cells.Count).Column End With End If Open FName For Output Access Write As #FNum For RowNdx = StartRow To EndRow WholeLine = "" For ColNdx = StartCol To EndCol If Cells(RowNdx, ColNdx).Value = "" Then CellValue = Chr(34) & Chr(34) Else CellValue = _ Application.WorksheetFunction.Text _ (Cells(RowNdx, ColNdx).Value, _ Cells(RowNdx, ColNdx).NumberFormat) End If WholeLine = WholeLine & CellValue & Sep Next ColNdx WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep)) Print #FNum, WholeLine Next RowNdx EndMacro: On Error GoTo 0 Application.ScreenUpdating = True Close #FNum End Sub -- Michael J. Malinsky Pittsburgh, PA "I am a bear of very little brain, and long words bother me." -- AA Milne, Winne the Pooh "Adam Kozul" wrote in message ... I am trying to get excel to export a column to a .txt file is there any way that I can do this so that later on I can refrence this file later on. I know I can copy and paste the colmun but I was hoping that there was a way that I could export it... Thanks Adam Kozul |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting to Notepad
Adam
Here's a little easier macro to follow - not as powerful, but for your specific request, it gets the job done. Sub Col1ToText() 'copy sheet1 into a new workbook ThisWorkbook.Sheets(1).Copy 'The new workbook will be active With ActiveWorkbook 'Delete all the columns but one .Sheets(1).Columns("B:IV").Delete 'Save the new workbook as text .SaveAs "C:\Dick\MyCol1.txt", xlTextWindows 'Close the new workbook with no prompt .Close False End With End Sub -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "Adam Kozul" wrote in message ... I am trying to get excel to export a column to a .txt file is there any way that I can do this so that later on I can refrence this file later on. I know I can copy and paste the colmun but I was hoping that there was a way that I could export it... Thanks Adam Kozul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exporting to excel from NotePad | Excel Worksheet Functions | |||
Exporting Excel file into Notepad with column & row lines | Charts and Charting in Excel | |||
Pasting from Notepad | Excel Discussion (Misc queries) | |||
Output to Notepad | Excel Programming | |||
DDEInitiate with Notepad | Excel Programming |