批量转换Excel到文本分隔的文件 [英] Batch convert Excel to text-delimited files

问题描述

我想做的是创建一个宏，它可以占用所有的xls文件在一个文件夹中，并将其转换为txt文件。

目前正在

  Sub Combined（）
 
 Application.DisplayAlerts = False 
 
 Const fPath As String =C：\Users\A9993846\Desktop\ 
 Dim sh As Worksheet 
 Dim sName As String 
 Dim inputString As String 
 
应用程序
 .Calculation = xlCalculationManual 
 .EnableEvents = False 
 .ScreenUpdating = False 
 End with 
 
 sName = Dir（fPath&* .xls *）
 
 Do Until sName = 
使用GetObject（fPath& sName）
对于每个sh在.Worksheets 
中与sh 
 .SaveAs替换（sName，.xls *，.txt） ，42'更新：
结束
下一步sh 
。关闭True 
结束
 sName = Dir 
循环
 
应用程序
 .Calculation = xlAutomatic 
 .EnableEvents = True 
。 ScreenUpdating = True 
结束
结束Sub 
  

但是它没有按预期工作我对VB有0个知识。任何人愿意交出手？

解决方案

下面的代码将所有Excel工作簿（xlsx的测试文件扩展名）转换为给定文件夹成CSV文件。文件名将为[工作簿名] [sheetname] .csv，即foo.xlsx将获得foo.xlsxSheet1.scv，foo.xlsxSheet2.scv等。为了运行它，创建一个纯文本文件，将其重命名为.vbs并复制粘贴下面的代码。更改路径信息并运行它。

  Option Explicit 
 
 Dim oFSO，myFolder 
 Dim xlCSV 
 
 myFolder =C：\your\path\to\excelfiles\
 
 
设置oFSO = CreateObject（Scripting.FileSystemObject ）
 xlCSV = 6'Excel CSV格式枚举
调用ConvertAllExcelFiles（myFolder）
设置oFSO =没有
 
调用MsgBox（完成！）
 
 
 Sub ConvertAllExcelFiles（ByVal oFolder）
 Dim targetF，oFileList，oFile 
 Dim oExcel，oWB，oWSH 
 
设置oExcel = CreateObject（Excel .Application）
 oExcel.DisplayAlerts = False 
设置targetF = oFSO.GetFolder（oFolder）
设置oFileList = targetF.Files 
对于oFileList中的每个oFile 
如果（右（oFile.Name，4）=xlsx）然后
设置oWB = oExcel.Workbooks.Open（oFile.Path）
对于oWB.Sheets中的每个oWSH 
调用oWSH。 SaveAs（oFile.Path&oWSH.Name&.csv，xlCSV）
 Ne xt 
设置oWSH =没有
调用oWB.Close 
设置oWB =没有
结束如果
下一个
调用oExcel.Quit 
设置oExcel =没有
 
结束Sub 
 
  

您可以提供更好的文件如果需要，命名，错误处理等。

Hi I'm facing a problem on dealing with converting Excel spreadsheets to txt files.

What I want to do is to create a Macro which can takes all the xls files in one folder and convert them to txt files.

The code currently working on

Sub Combined()

  Application.DisplayAlerts = False

  Const fPath As String = "C:\Users\A9993846\Desktop\"
  Dim sh As Worksheet
  Dim sName As String
  Dim inputString As String

  With Application
    .Calculation = xlCalculationManual
    .EnableEvents = False
    .ScreenUpdating = False
  End With

  sName = Dir(fPath & "*.xls*")

  Do Until sName = ""
    With GetObject(fPath & sName)
      For Each sh In .Worksheets
        With sh
          .SaveAs Replace(sName, ".xls*", ".txt"), 42 'UPDATE:
        End With
      Next sh
      .Close True
    End With
    sName = Dir
  Loop

  With Application
    .Calculation = xlAutomatic
    .EnableEvents = True
    .ScreenUpdating = True
  End With
End Sub

But It's not working as expected, I have 0 knowledge on VB. Anyone willing to give a hand?

解决方案

The code below converts all Excel Workbooks (tests file extension for "xlsx") in a given folder into CSV files. File names will be [workbookname][sheetname].csv, ie "foo.xlsx" will get "foo.xlsxSheet1.scv", "foo.xlsxSheet2.scv", etc. In order to run it, create a plain text file, rename it to .vbs and copy-paste the code below. Change path info and run it.

Option Explicit

Dim oFSO, myFolder
Dim xlCSV

myFolder="C:\your\path\to\excelfiles\"


Set oFSO = CreateObject("Scripting.FileSystemObject")
xlCSV = 6 'Excel CSV format enum
Call ConvertAllExcelFiles(myFolder)
Set oFSO = Nothing

Call MsgBox ("Done!")


Sub ConvertAllExcelFiles(ByVal oFolder)
Dim targetF, oFileList, oFile
Dim oExcel, oWB, oWSH

    Set oExcel = CreateObject("Excel.Application")
    oExcel.DisplayAlerts = False
    Set targetF = oFSO.GetFolder(oFolder)
    Set oFileList = targetF.Files
    For Each oFile in oFileList
        If (Right(oFile.Name, 4) = "xlsx") Then
            Set oWB = oExcel.Workbooks.Open(oFile.Path)
            For Each oWSH in oWB.Sheets
                Call oWSH.SaveAs (oFile.Path & oWSH.Name & ".csv", xlCSV)
            Next
            Set oWSH = Nothing
            Call oWB.Close
            Set oWB = Nothing
        End If
    Next
    Call oExcel.Quit
    Set oExcel = Nothing

End Sub

You can give better file naming, error handling/etc if needed.

这篇关于批量转换Excel到文本分隔的文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！