01-08-2005, 07:51 PM
Join Date: Sep 2004
Copy-Paste & save as macro
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
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
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
If Err.Number <> 0 Then
MsgBox "Error number " & Err.Number & vbLf & Err.Description, vbCritical, "ERROR"
Application.ScreenUpdating = True
Application.DisplayAlerts = True