从.csv写入到txt文件，列之间有不同的空格

I have written the vba code below to take an excel file, and read/write it into a new text file. The main issue I had with doing this was that the spacing needed in the new text file was not uniformed. By that, I mean that if row 1 with four columns is [column a = 2 column b = 3, column c = 4, and column d = 5], the output in the text file will be:

我已经编写了下面的vba代码来获取一个excel文件，并将它读/写到一个新的文本文件中。我这样做的主要问题是，新文本文件中所需的间隔没有统一。我的意思是，如果有四列的第1行是[a = 2列b = 3, c = 4, d = 5]，那么文本文件中的输出将是:

2       3    4              5

There is a tab between the 2 and 3, four spaces between the 3 and 4, and 14 spaces between the 4 and 5. This is quite random, but formatting is due to previous files created.

在2和3之间有一个选项卡，在3和4之间有4个空格，在4和5之间有14个空格。这是非常随机的，但是格式化是由于以前创建的文件造成的。

I successfully completed one aspect of this problem, however, there are two new issues that come about.

我成功地完成了这个问题的一个方面，然而，有两个新问题即将出现。

In the third column of my excel file, not all the numbers are of the same length. Ex - Cells(3, 3).Value = 79.13 and Cells(4, 3).Value = 81.6

在我的excel文件的第三列中，并不是所有的数字都是相同的长度。Ex - cell (3,3).Value = 79.13, Cells(4,3).Value = 81.6

How do I get the following (which is what happens now):

我如何得到以下(这是现在发生的):

"302277600       19940130    79.13              18.06"
"302277600       19940131    81.6              18.06"

to turn to this:

转向:

"302277600       19940130    79.13              18.06"
"302277600       19940131    81.6               18.06"

Basically, changing the spacing between values based upon the length. Further, is there a way to get rid of the quotation marks?

基本上，根据长度改变值之间的间隔。还有，有没有办法去掉引号?

Sub excelToTxt()

Dim FilePath As String
Dim CellData As String
Dim LastCol As Long
Dim LastRow As Long

LastCol = 4
LastRow = 19954

FilePath = Application.DefaultFilePath & "\test.txt"

Open FilePath For Output As #2

For i = 1 To LastRow

    CellData = ""

    For j = 1 To LastCol
        If j = 1 Then
            CellData = CellData + Trim(ActiveCell(i, j).Value) & "       "
        ElseIf j = 2 Then
            CellData = CellData + Trim(ActiveCell(i, j).Value) & "    "
        ElseIf j = 3 Then
            CellData = CellData + Trim(ActiveCell(i, j).Value) & "              "
        ElseIf j = LastCol Then
            CellData = CellData & Trim(ActiveCell(i, j).Value)
        End If

Next j

Write #2, CellData

Next i

Close #2

End Sub

Is anyone able to help with this problem?

有人能帮助解决这个问题吗?

1 个解决方案

#1

Combine this with you if statements.

结合if语句。

Public Function PadSpace(nMaxSpace As Integer, nNumSpace As Integer) As String
    If nMaxSpace < nNumSpace Then
        PadSpace = ""
    Else
        PadSpace = Space(nMaxSpace - nNumSpace)
    End If
End Function

You call it with the Column width and the length of the value. It returns a string with the number of spaces to put between the value and the next value. The "Pad"

您可以用列的宽度和值的长度来调用它。它返回一个字符串，该字符串包含要在值和下一个值之间放置的空格数。“垫”

    If j = 1 Then
        CellData = CellData + Trim(ActiveCell(i, j).Value) & PadSpace(16, Len(Trim(ActiveCell(i, j).Value)))
    ElseIf j = 2 Then
        CellData = CellData + Trim(ActiveCell(i, j).Value) & PadSpace(12, Len(Trim(ActiveCell(i, j).Value)))
    ElseIf j = 3 Then
        CellData = CellData + Trim(ActiveCell(i, j).Value) & PadSpace(19, Len(Trim(ActiveCell(i, j).Value)))
    ElseIf j = LastCol Then
        CellData = CellData & Trim(ActiveCell(i, j).Value)
    End If

#1