Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Export Active Cells, Comma Sep to .txt file
Hello, this is my first post on a very old subject.
There are 2 changes I'd like to make to this code. 1. I'd like it not to ask for a filename, and use the Worksheet name instead. 2. This code save a Tab separated text file, I'd like to use Comma's instead. I tried changing vbTab to vbComma, but then I get an error on the line "CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) - 1)". Any assistance would be appreciated! Sub OutputActiveSheetAsTabDelim() Dim SrcRg As Range Dim CurrRow As Range Dim CurrCell As Range Dim CurrTextStr As String Dim ListSep As String Dim FName As Variant Dim ColCount As Integer Dim CurrCol As Integer FName = Application.GetSaveAsFilename("", "Tab Delimited File (*.txt),*.txt") If FName < False Then ListSep = vbTab ''Chg to comma, etc for a different separator If Selection.Cells.Count 1 Then Set SrcRg = Selection Else Set SrcRg = ActiveSheet.UsedRange End If ColCount = SrcRg.Columns.Count Open FName For Output As #1 For Each CurrRow In SrcRg.Rows CurrCol = 0 CurrTextStr = "" For Each CurrCell In CurrRow.Cells CurrCol = CurrCol + 1 CurrTextStr = CurrTextStr & CurrCell.Value & ListSep Next While Right(CurrTextStr, 1) = ListSep CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) - 1) Wend Print #1, CurrTextStr Next Close #1 End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Export Active Cells, Comma Sep to .txt file
Sub OutputActiveSheetAsTabDelim()
Dim SrcRg As Range Dim CurrRow As Range Dim CurrCell As Range Dim CurrTextStr As String Dim ListSep As String Dim FName As Variant Dim ColCount As Integer Dim CurrCol As Integer sPath = "C:\Myfolder\" FName = sPath & Activesheet.Name & ".csv" ListSep = "," If Selection.Cells.Count 1 Then Set SrcRg = Selection Else Set SrcRg = ActiveSheet.UsedRange End If ColCount = SrcRg.Columns.Count Open FName For Output As #1 For Each CurrRow In SrcRg.Rows CurrCol = 0 CurrTextStr = "" For Each CurrCell In CurrRow.Cells CurrCol = CurrCol + 1 CurrTextStr = CurrTextStr & CurrCell.Value & ListSep Next While Right(CurrTextStr, 1) = ListSep CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) - 1) Wend Print #1, CurrTextStr Next Close #1 End If End Sub -- Regards, Tom Ogilvy "Stryves" wrote: Hello, this is my first post on a very old subject. There are 2 changes I'd like to make to this code. 1. I'd like it not to ask for a filename, and use the Worksheet name instead. 2. This code save a Tab separated text file, I'd like to use Comma's instead. I tried changing vbTab to vbComma, but then I get an error on the line "CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) - 1)". Any assistance would be appreciated! Sub OutputActiveSheetAsTabDelim() Dim SrcRg As Range Dim CurrRow As Range Dim CurrCell As Range Dim CurrTextStr As String Dim ListSep As String Dim FName As Variant Dim ColCount As Integer Dim CurrCol As Integer FName = Application.GetSaveAsFilename("", "Tab Delimited File (*.txt),*.txt") If FName < False Then ListSep = vbTab ''Chg to comma, etc for a different separator If Selection.Cells.Count 1 Then Set SrcRg = Selection Else Set SrcRg = ActiveSheet.UsedRange End If ColCount = SrcRg.Columns.Count Open FName For Output As #1 For Each CurrRow In SrcRg.Rows CurrCol = 0 CurrTextStr = "" For Each CurrCell In CurrRow.Cells CurrCol = CurrCol + 1 CurrTextStr = CurrTextStr & CurrCell.Value & ListSep Next While Right(CurrTextStr, 1) = ListSep CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) - 1) Wend Print #1, CurrTextStr Next Close #1 End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Export Active Cells, Comma Sep to .txt file
Thank you for your assistance!
Tom Ogilvy wrote: |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to export a range to a text file? | Excel Programming | |||
export to csv file (comma problem) | Excel Programming | |||
How do I export data from Excel as a series of comma-separated val | New Users to Excel | |||
Macro to export data from active .doc form to an existing .xls | Excel Programming | |||
Macro to export a worksheet and save as new file | Excel Programming |