使用VBA创建.txt文件.如何创建可变间距的列

Question

我已经设法使用了一段代码，该代码将数据从Excel电子表格导出到.txt文档中。

然而，由于任务的性质，数据必须有一定的空格宽，即.txt文件夹中的第一列必须有8个空格宽，第二列必须有20个空格宽等。我知道如何在使用VBA导入到.txt时创建固定长度的列(如下所示(，但如何实现可变间距。

到目前为止的代码如下：

Public Sub CompileMacro()
Dim lRow As Long
Dim lCol As Long
Dim strRow As String
Dim ws As Excel.Worksheet
Dim ts As TextStream
Dim fs As FileSystemObject

Set fs = New FileSystemObject
Set ts = fs.CreateTextFile(" C: . . . ", True, False)
Set ws = Application.ActiveSheet
Row = 1
Do While Row <= ws.UsedRange.Rows.Count
strRow = ""
Col = 1
Do While Col <= ws.UsedRange.Columns.Count
strRow = strRow & ws.Cells(Row, Col) & PadSpace(8, Len(ws.Cells(Row, Col)))

Col = Col + 1
Loop
ts.WriteLine strRow
Row = Row + 1
ws.Range("A" & Row).Activate
Loop
ts.Close: Set ts = Nothing
Set fs = Nothing
End Sub

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

Answer 1

创建一个数组来保存填充设置：

padding = Array(8, 20, 10, 10, 20, 8, 10, 10)

更改字符串生成器以使用给定列的值：

strRow = strRow & ws.Cells(Row, Col) & PadSpace(padding(Col - 1), Len(ws.Cells(Row, Col)))

所以你的循环看起来像这样：

Row = 1
padding = Array(8, 20, 10, 10, 20, 8, 10, 10)
Do While Row <= ws.UsedRange.Rows.Count
strRow = ""
Col = 1
Do While Col <= ws.UsedRange.Columns.Count
strRow = strRow & ws.Cells(Row, Col) & PadSpace(padding(Col - 1), Len(ws.Cells(Row, Col)))
Col = Col + 1
Loop
ts.WriteLine strRow
Row = Row + 1
ws.Range("A" & Row).Activate

Loop

Answer 2

也许可以尝试将Space$与赋值运算符左侧的不寻常Mid$组合使用，如以下

strRow = Space$(padding(col))
Mid$(strRow, 1, Len(ws.Cells(Row, col))) = ws.Cells(Row, col)

此外，您还需要填充数组，例如像padding = Array(0, 8, 20)一样定义的数组。

Mid$(strRow, 1, Len(ws.Cells(Row, col)))用ws.Cells(Row, col)重写strRow字符串内的空格字符

Answer 3

我过去使用Excel功能Save As .prn实现了这一点。这个"；文本打印机"；将保留与图纸的间距，因此您必须调整列宽，直到它完美为止。如果您已经使用VBA设置列宽，那么这很简单。

您可以根据图纸从VBA调用此函数。看起来像这样：

Dim ws As Excel.Worksheet
Set ws = Application.ActiveSheet
ws.SaveAs fileName, xlTextPrinter