Text file creation macro

[SushiJuice]

HI there. I'm trying to:

1. Copy a range and paste it into another sheet (could I create one on the fly?)
2. Then create a temporary workbook to copy the same information (could I eliminate the step above instead?)
3. Then save the new file as a .txt file on the desktop with a specific name
4. I'd like to then delete the temporary workbood (if that's possible - can't figure out how to do that)

Here's the best I've come up with, but it keeps renaming the orginal workbook instead of the temp file. Please help.

    Range("M3:M241").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("a1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Range("A1").Select
    
Filename = ActiveWorkbook.Name
user = Environ("Username")
desktop = "C:\Users\" & user & "\Desktop\"
newfilename = "TMO_" & Format(Now, "yyyymmdd")

ThisWorkbook.SaveAs Filename:=desktop & newfilename & ".txt", FileFormat:=xlTextWindows

End Sub

 

[rpaulson]

this will dump for data to a csv text file with out the need to create any temp files.
adjust the range as needed.

Sub Create_File()
myfile = "c:\test\myfiel.txt"
If Dir(myfile) <> "" Then Kill myfile ' deletes file if it exists
data = ""

For r = 2 To 50 'change to the rows s needed
   for c = 1 to 10 'change to the columns needed
        data = data & Cells(r, c) & "," & vbCr & vbLf
  next c
Next r

Open myfile For Append As #1
Print #1, data
Close #1
End Sub

hth,
Ross

 

[SushiJuice]

this will dump for data to a csv text file with out the need to create any temp files.
adjust the range as needed.

Sub Create_File()
myfile = "c:\test\myfiel.txt"
If Dir(myfile) <> "" Then Kill myfile ' deletes file if it exists
data = ""

For r = 2 To 50 'change to the rows s needed
   for c = 1 to 10 'change to the columns needed
        data = data & Cells(r, c) & "," & vbCr & vbLf
  next c
Next r

Open myfile For Append As #1
Print #1, data
Close #1
End Sub

hth,
Ross

Thanks for the reply!

A Couple questions:

1. Can I make this save to the desktop?
2. Will it save as a dynamic name - I need to save it with this format: newfilename = "TMO_" & Format(Now, "yyyymmdd")
3. For the rows/columns section - is that from home (A1)?

I've tried running the macro and it's giving me this error:
View attachment 105245

it highlights this line of code: "Open myfile For Append As #1"

 

[rlv01]

(could I eliminate the step above instead?)

If the only data you want to export is M3:M241, then yes; but these two lines of code:

    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select

imply that after you copy a single column of data (M3:M241) to Sheet1, you are enlarging the range to include more columns from Sheet1 before you copy it to the new workbook to save as a text file. Can you clarify?

 

[SushiJuice]

If the only data you want to export is M3:M241, then yes; but these two lines of code:

    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select

imply that after you copy a single column of data (M3:M241) to Sheet1, you are enlarging the range to include more columns from Sheet1 before you copy it to the new workbook to save as a text file. Can you clarify?

Yes - I was trying to copy only the number of rows that had data in them.

The original sheet is filtered so I copy M3:M241 to capture all relavent data and paste it into sheet1. There aren't actually 238 rows of data, so then only want to copy the rows with data. That may not be the correct way to do that. Sorry for the confusion.

 

[rlv01]

I'm still not sure I understand what ranges you are tying to copy, but try this.

Sub CopyTest()
    Dim user As String, desktop As String, newfilename As String, Filename as String
    Dim WB As Workbook
    Dim rngCopy As Range
    
    'Create export file name
    Filename = ActiveWorkbook.Name
    user = Environ("Username")
    desktop = "C:\Users\" & user & "\Desktop\"
    newfilename = "TMO_" & Format(Now, "yyyymmdd") & ".txt"
    
    'Copy data from range on ActiveSheet & paste data to Sheet1
    Set rngCopy = ActiveSheet.Range("M3:M241").SpecialCells(xlCellTypeVisible)
    With Worksheets("Sheet1")
        .Columns("A").ClearContents
         rngCopy.Copy
        .Range("A1").PasteSpecial Paste:=xlPasteValues
        
        'Copy Sheet1 data
        Set rngCopy = .Range("A1", .Range("A" & .Rows.Count).End(xlUp))
        Application.Intersect(rngCopy.EntireRow, .UsedRange).Copy
    End With
    
    'Paste sheet1 range to new worksheet
    Set WB = Workbooks.Add
    ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
    
    'Save as text file.
    Application.DisplayAlerts = False
    WB.SaveAs Filename:=desktop & newfilename, FileFormat:=xlTextWindows
    WB.Close False
    Application.DisplayAlerts = True
    DoEvents
End Sub