Save to Desktop as a text file vba

[decadence]

Hi, Is it possible to save the Active Sheet data to the desktop as a text file using the Active Workbook Name and the file Extension .asc instead of .txt.
I need to save it as tab delimited if the workbook name starts with the Letter A if not then save as comma delimited.
Also If this can't be done Is it possible to change the extension once saved?. Many Thanks

 

[Logit]

.
Here are two macros to get you started :

Sub textSave()
Dim ws As Worksheet, wsName As String
    Application.ScreenUpdating = False
    For Each ws In ThisWorkbook.Worksheets
        wsName = ws.Name
        ws.Copy
        ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & wsName & ".txt", FileFormat:=xlTextMSDOS, CreateBackup:=False
        ActiveWorkbook.Close False
    Next ws
    Application.ScreenUpdating = True
End Sub

Sub Export_Sheets_To_Text_Files()
    Dim Ws          As Worksheet 
    Dim strFile     As String 
     
     
    With Application 
        .ScreenUpdating = False 
        For Each Ws In .ActiveWorkbook.Worksheets 
            Ws.Copy 
            strFile = ThisWorkbook.Path & "\" & Ws.Name & ".txt" 
            .ActiveWorkbook.SaveAs Filename:=strFile, FileFormat:=xlText 
            .ActiveWorkbook.Saved = True 
            .ActiveWorkbook.Close 
        Next Ws 
        .ScreenUpdating = True 
    End With 

    MsgBox "Done...", 64 
End Sub

 

[bugmonsta]

This little script will ask you where to save it and will use Excel file format to export as a csv. I have added an extra line for you to try outputting it to asc - it worked on my machine but I don't know if its in the correct format for you?

it copies the activesheet and

I used the code to ask for a file location by vaskov17 via the post:

https://www.mrexcel.com/forum/excel...apture-file-path-post3169593.html#post3169593

Sub test()
Dim TempWB As Workbook
   
ActiveSheet.Copy
Set TempWB = ActiveWorkbook

Dim fd As FileDialog
    Dim sPath As String
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    
    If fd.Show = -1 Then
        sPath = fd.SelectedItems(1)
    End If
[COLOR="#008000"]    'sPath now holds the path to the folder or nothing if the user clicked the cancel button[/COLOR]
  
With TempWB
    .SaveAs Filename:=sPath & "\CSV-Exported", FileFormat:=xlCSVWindows, CreateBackup:=False
    
[COLOR="#008000"]    'use below to try your asc format to see if it saves ok for you
    'SaveAs Filename:=sPath & "\CSV-Exported.asc",  CreateBackup:=False[/COLOR]
    .Close
    End With

End Sub

 

[decadence]

Thank you for your codes, however I have put together a simple macro that does the job

Sub SavetoDesktop()
' Saves the Activesheet to the Desktop as a text file with a different extension
    Dim strPath As String
    ActiveSheet.Copy
    strPath = Environ("HomeDrive") & Environ("HomePath") & "\Desktop\" & ActiveSheet.Name & ".asc"
    ActiveWorkbook.SaveAs filename:=strPath, FileFormat:=xlText
End Sub