Copy-Paste & save as macro - Xtreme Visual Basic Talk

01-08-2005, 07:51 PM

pool_cup

Freshman

Join Date: Sep 2004

Posts: 32

Copy-Paste & save as macro

hELLO!
Is there some way to automate a task of copying a sheet values of an Excel file, pasting them into a text file and rename it to another extension rather txt?

I have already done this task using an excel macro but it is not recognized by the program that uses the resulting file as an input, I think that maybe it gets corrupted during the process...The truth is that it does work when I do it manually, I copy the values, paste them into a text file and rename the file to .epc...weird isnt it?

here is the code, hope to have another point of view

Code:

Sub Button3_Click()

Dim Tb As Workbook, Tn As String
Dim Sb As Workbook, Sn As String
Dim SPath As String
    
    On Error GoTo NBG
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    
    Set Tb = ThisWorkbook
    Set Sb = Workbooks.Add
    
    Tb.Sheets("Sheet1").Cells.Copy 'change to your sheet name
    Sb.Sheets(1).Cells.PasteSpecial xlValues
    Application.CutCopyMode = False
    
  
    
    Sn = Left(Tb.Name, InStr(1, Tb.Name, ".xl", 1) - 1)
    SPath = Tb.Path & "\" & Sn & ".epc"
    
    Sb.SaveAs SPath, xlTextPrinter, CreateBackup:=False

   
NBG: 'Errors
    If Err.Number <> 0 Then
        MsgBox "Error number " & Err.Number & vbLf & Err.Description, vbCritical, "ERROR"
    End If
    Err.Clear
    Sb.Close False
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub

01-09-2005, 09:11 AM

italkid

Down...

Retired Moderator
* Expert *

Join Date: Dec 2002

Location: Belgium.

Posts: 6,731

You can save Excel workbooks (1 sheet at a time though) as a text file (file > saveas > textfile).
But I don't know for sure there is a way to change a file extension programmatically.
Which is your specific reason you want to do such thing?