CODEWITHSUNDEEP
excelvba
Swam
Swam

Reputation: 49

Exporting Excel Sheets as .txt files

I'm attempting to export sheets in an excel document as text files. Basically, I'm getting the output filepath from the user, skip the first sheet, and then save all of the remaining excel sheets as .txt files. I'm getting the following error when running: "Run-time error '1004': Application-defined or objected-defined error". This error seems to result from the ActiveSheet.SaveAs command.

Any help would be appreciated. Thanks in advance. 

Sub exportSheets()
    Dim ws As Worksheet
    Dim sheetName, filePath As String

    'get filepath from user input 
    filePath = Range("E6").Value

    'ask if the user is sure about exporting 
    answer = MsgBox("Export all Worksheets to text files?", vbYesNo, "Run Macro")

    If answer = vbYes Then
        'loop through every sheet in the work book
        For Each ws In ThisWorkbook.Worksheets

            'skip the first page in the excel workbook
            If ws.Index > 1 Then
                sheetName = ws.Name 'get the sheet name

                'save the active sheet
                ActiveSheet.SaveAs fileName:=filePath & "\" & sheetName & ".txt", FileFormat:=xlText, CreateBackup:=False
            End If
        Next
    End If

End Sub

Upvotes: 0

Views: 1539

Answers (1)

Storax
Storax

Reputation: 12167

Fix for your code above

Option Explicit

Sub exportSheets()
    Dim ws As Worksheet
    Dim filePath As String

    'get filepath from user input
    filePath = Range("E6").Value

    Dim answer As Variant

    'ask if the user is sure about exporting
    answer = MsgBox("Export all Worksheets to text files?", vbYesNo, "Run Macro")

    If answer = vbYes Then

        ' Turn off alerts. Be aware that will overwrite existing file without warning
        Application.DisplayAlerts = False

        'loop through every sheet in the work book
        For Each ws In ThisWorkbook.Worksheets

            With ws
                'skip the first page in the excel workbook
                If .Index > 1 Then
                    .SaveAs Filename:=filePath & "\" & .Name & ".txt", FileFormat:=xlTextMSDOS, CreateBackup:=False
                End If
            End With

        Next

        Application.DisplayAlerts = True

    End If

End Sub

Upvotes: 1

Related Questions