将excel中的一系列数据导出为ascii纯文本格式的表csv或txt文件使用vba

问题描述：

我需要将一系列数据从Excel或LibreOffice Calc文件导出为ASCII纯文本表格 - 文本文件。输出的格式如下：将excel中的一系列数据导出为ascii纯文本格式的表csv或txt文件使用vba

+--------+--------------+--------+ 
| Name | Place  | Phone | 
+========+==============+========+ 
| JOHN | TEXAS  | 123456 | 
+--------+--------------+--------+ 
| ROBERT | INDIANAPOLIS | 234567 | 
+--------+--------------+--------+ 
| JEANNE | CAMBODIA  | 345678 | 
+--------+--------------+--------+ 
| ANN | CANADA  | 456789 | 
+--------+--------------+--------+ 
| RICK | HAGUE  | 567890 | 
+--------+--------------+--------+

是否可以使用vba？是的，数字应该右对齐。

或者这样：

+--------+--------------+--------+ 
| Name | Place  | Phone | 
+========+==============+========+ 
| JOHN | TEXAS  | 123456 | 
| ROBERT | INDIANAPOLIS | 234567 | 
| JEANNE | CAMBODIA  | 345678 | 
| ANN | CANADA  | 456789 | 
| RICK | HAGUE  | 567890 | 
+--------+--------------+--------+

目前还不清楚您的意思是否应该完全像您展示的那样，包括边框在内？你有什么尝试 - 什么？ –

@TimWilliams，是的，我需要完全如上所示。但是如果每行之后的水平线是不可能的，那么它是可以的，但至少标题应该在它下面有一行。 – adam1969in

是的，这可以做...看到这个答案http://stackoverflow.com/questions/11503174/how-to-create-and-write-to-a-txt-file-using-vba – Rosetta

答

如果我没有记错，一个纯文本文件不会在所有的理由列。

这就是说，试试这个： http://www.excel-easy.com/vba/examples/write-data-to-text-file.html

vim那样做，为什么不是vba？ – adam1969in

链接是不是比直接保存为.csv文件 – adam1969in

@ adam1969in我求求不同，这正是你需要的。 – Rosetta

答

嗯，我懂了工作。

Sub Convert2Text() 
    Dim i As Integer, k As Integer, n As Integer, m As Integer 
    Dim Rng As Range, Dpath As String 
    Dim FSO As Scripting.FileSystemObject 
    Dim TxtFile As TextStream 
    Dim MaxLen As Integer 

     Set Rng = Application.InputBox("Please Select range.", "-Convert to Text-", , -Range("R2").Left, Range("R2").Top, , , 8) 
     If Rng Is Nothing Then 
      Exit Sub 
     End If 
     Dpath = Environ("USERPROFILE") & "\Documents\" 
     Set FSO = New FileSystemObject 
     Set TxtFile = FSO.CreateTextFile(Dpath & "Outputfile.txt", True) 

      MaxLen = 1 
      For i = 1 To Rng.Columns.Count 
       For k = 1 To Rng.Rows.Count 
       If MaxLen < Len(Rng.Cells(i, k)) Then 
        MaxLen = Len(Rng.Cells(i, k)) 
       End If 
       Next k 
      Next i 
      MaxLen = MaxLen + 4 

      For i = 1 To Rng.Columns.Count 
       TxtFile.Write "+" 
        For m = 1 To MaxLen 
         TxtFile.Write "=" 
        Next m 
      Next i 
       TxtFile.Write "+" 
       TxtFile.WriteLine 

      For n = 1 To Rng.Rows.Count 
       For i = 1 To Rng.Columns.Count 
         TxtFile.Write "|" 
         TxtFile.Write " " 
         TxtFile.Write " " 
         TxtFile.Write Rng.Cells(n, i) 

         For k = 1 To MaxLen - (Len(Rng.Cells(n, i))) - 2 
          TxtFile.Write " " 
         Next k 
       Next i 
         TxtFile.Write "|" 
         TxtFile.WriteLine 

       For i = 1 To Rng.Columns.Count 
         TxtFile.Write "+" 
          For m = 1 To MaxLen 
           TxtFile.Write "-" 
         Next m 
       Next i 
         TxtFile.Write "+" 
         TxtFile.WriteLine 
      Next n 

     MsgBox "Done!" 
     Shell "explorer.exe " & Dpath, vbNormalFocus 

End Sub

现在，我必须专注于让数字对齐。谢谢大家的建议。

将excel中的一系列数据导出为ascii纯文本格式的表csv或txt文件使用vba

相关推荐