将工作表导出到Txt [英] Export Worksheets to Txt

问题描述

我需要将多个工作表导出到.txt文件.我希望将它们保存在与工作簿相同的位置.我还需要工作表名称为.txt文件名.下面的代码是我到目前为止所拥有的，但是我在工作表(ws.Name)上遇到调试错误.选择....有什么想法吗?谢谢大家！

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!

此外，我将其存储在我的个人工作簿中，以便可以在打开的任何文件上使用它

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

推荐答案

如果您有 2 个或更多工作簿，请打开(个人和其他)然后替换:

For Each ws In ThisWorkbook.Worksheets

使用

For Each ws In ActiveWorkbook.Worksheets

这样， Select 将起作用.(除非相应的工作簿也处于活动状态，否则您不能选择工作表)

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

也可以解决:

ThisWorkbook.Activate

位于循环底部

这篇关于将工作表导出到Txt的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！