Copy worksheet to a txt file and name it based on a cell name

[abhimaan]

Hello,
I am trying to copy content of a worksheet to a text file. What i want to know is how can i name the text file based on a given string in a particular cell. And the issue is that i do not need the complete cell content I want to drop/trim last 4 characters of the cell.
Eg: value in my cell A1 is "abhimanyu_wk2" and i want to create a text file with name only
abhimanyu.
I need script to copy content to text file from a worksheet and name the text file based on the cell A1(as in the example given above).
Any help would be highly appreciated.
Thanks,
Abhimanyu

 

[tlowry]

I think tis is what you are looking for:

Procedure

1. Copy this code below to ThisWorkbook module
2. Modify the SAVEPATH to where the text file is to be saved
3. Change SAVETYPE, if necessary

To call

SaveAsText Sheets(4)
or
SaveAsText Sheets("Invoices")
or
SaveAsText ActiveSheet

Option Explicit
Sub SaveAsText(ws As Variant)
    Const [B][COLOR=Green]SAVEPATH [/COLOR][/B]= "C:\Temp\"     'set save path here be sure to end with \
    Const [B][COLOR=Green]SAVETYPE [/COLOR][/B]= xlText         ' set save type here xlcvs or others
    Const APP_TITLE = "Save Sheet to text."
    Dim sName As String
    Application.ScreenUpdating = False
    On Local Error GoTo errors
    sName = ws.Range("A1").Value
    Select Case Len(sName)
        Case Is <= 4
            MsgBox ws.Name & " is too short", vbCritical, APP_TITLE
        Case Else
            sName = Left(sName, Len(sName) - 4)
            Application.DisplayAlerts = False
            ws.Cells.Copy
            Workbooks.Add
            ActiveWorkbook.Sheets(1).Range("A1").PasteSpecial Paste:=xlAll, _
                    Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            ActiveWorkbook.SaveAs Filename:=SAVEPATH & sName, FileFormat _
                :=SAVETYPE, CreateBackup:=False
            ActiveWorkbook.Close
            Application.DisplayAlerts = True
    End Select
errors:
    If Err <> 0 Then MsgBox Err.Description, vbCritical, APP_TITLE
    Application.ScreenUpdating = True
End Sub

 

[abhimaan]

Hi,

Thanks a lot for ur reply but the above code was bit complex for me to understand. Actually I have the code as below:

Sub Macro5()
'
' Macro5 Macro
'


Dim ce As Range
Dim sName As String



sName = Sheets("Staging-wk2").Range("A2")
Select Case Len(sName)
Case Is <= 4

Case Else
sName = Left(sName, Len(sName) - 4)


End Select


Open "d:\sName.dfn" For Output As #1

For Each ce In Range("A1", Range("A1").End(xlDown))

Print #1, ce.Value

Next ce

Close #1

End Sub

What i am trying to do is to capture the name of the text file into a variable(sName) by the method given by you and call that variable where it's creating the file.

The file is getting created but with the name as sName.