CODEWITHSUNDEEP
excelvbaexport
Jake Burk
Jake Burk

Reputation: 1

Export Worksheets to Txt

I need to export multiple worksheets to .txt files. I want them to be saved in the same location as the workbook is. I also need the worksheetnames to be the .txt file names. The code below is what i have so far, but I'm getting a debugging error on sheets(ws.Name).Select....Any ideas? Thanks all!

Also, i have this stored in my personal workbook so i can use it on any file i have open

Sub Worksheets_to_txt() '<--Saves each worksheet as a text file with the same name

    Dim ws As Worksheet
    Dim relativePath As String
    Dim answer As VbMsgBoxResult

    relativePath = ThisWorkbook.Path

    answer = MsgBox("Are you sure you want to export worksheets?", vbYesNo, "Run Macro") '<--Pop up box to confirm export

    If answer = vbYes Then

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For Each ws In ThisWorkbook.Worksheets

        Sheets(ws.Name).Select
        Sheets(ws.Name).Copy
        ActiveWorkbook.SaveAs Filename:= _
        relativePath & "\" & ws.Name & ".txt", _
        FileFormat:=xlText, CreateBackup:=False
        ActiveWorkbook.Close
        ThisWorkbook.Activate
    Next
     End If

End Sub

Upvotes: 0

Views: 3204

Answers (2)

Francesco Giossi
Francesco Giossi

Reputation: 170

Try this

Sub Worksheets_to_txt() '<--Saves each worksheet as a text file with the same name

    Dim ws As Worksheet
    Dim relativePath As String
    Dim answer As VbMsgBoxResult

    relativePath = ActiveWorkbook.Path

    answer = MsgBox("Are you sure you want to export worksheets?", vbYesNo, "Run Macro") '<--Pop up box to confirm export

    If answer = vbYes Then

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For Each ws In ActiveWorkbook.Worksheets

        ws.Select
        ws.Copy
        ActiveWorkbook.SaveAs Filename:= _
        relativePath & "\" & ws.Name & ".txt", _
        FileFormat:=xlText, CreateBackup:=False
        ActiveWorkbook.Close
        ActiveWorkbook.Activate
    Next
     End If

End Sub

Upvotes: 1

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96791

If you have 2 or more workbooks open (personal and another) then replace:

For Each ws In ThisWorkbook.Worksheets

with

For Each ws In ActiveWorkbook.Worksheets

That way the Select will work. (You can't Select a sheet unless the proper workbook is also active)

Also fix:

ThisWorkbook.Activate

at the bottom of the loop

Upvotes: 2

Related Questions