从excel中写入固定宽度的文本文件vba

Question

这是一个程序的输出。

我已经指定了什么应该是宽度程序中的每个单元格和我的程序显示正确的输出。

我想要做的是细胞内容应从右向左书写。例如高亮显示的数字9983.54的宽度为21，文本文件使用了前7列。但我希望它使用最后7列的文本文件。

请参阅预期的输出图像。

我没有得到任何线索如何做到这一点。我不是一个非常专业的程序员，但我喜欢编码。这个文本文件被用作一些其他程序的输入，我试图从excel VBA中自动编写文本文件。

任何人都可以提出一种方法来获得这种输出格式？

这是给我的第一输出

Option Explicit 

Sub CreateFixedWidthFile(strFile As String, ws As Worksheet, s() As Integer) 
    Dim i As Long, j As Long 
    Dim strLine As String, strCell As String 

    'get a freefile 
    Dim fNum As Long 
    fNum = FreeFile 

    'open the textfile 
    Open strFile For Output As fNum 
    'loop from first to last row 
    'use 2 rather than 1 to ignore header row 
    For i = 1 To ws.Range("a65536").End(xlUp).Row 
     'new line 
     strLine = "" 
     'loop through each field 
     For j = 0 To UBound(s) 
      'make sure we only take chars up to length of field (may want to output some sort of error if it is longer than field) 
      strCell = Left$(ws.Cells(i, j + 1).Value, s(j)) 
      'add on string of spaces with length equal to the difference in length between field length and value length 
      strLine = strLine & strCell & String$(s(j) - Len(strCell), Chr$(32)) 
     Next j 
     'write the line to the file 
     Print #fNum, strLine 
    Next i 
    'close the file 
    Close #fNum 

End Sub 

“例如代码可以使用被称为代码：

Sub CreateFile() 
    Dim sPath As String 
    sPath = Application.GetSaveAsFilename("", "Text Files,*.txt") 
    If LCase$(sPath) = "false" Then Exit Sub 
    'specify the widths of our fields 
    'the number of columns is the number specified in the line below +1 
    Dim s(6) As Integer 
    'starting at 0 specify the width of each column 
    s(0) = 21 
    s(1) = 9 
    s(2) = 15 
    s(3) = 11 
    s(4) = 12 
    s(5) = 10 
    s(6) = 186 
    'for example to use 3 columns with field of length 5, 10 and 15 you would use: 
    'dim s(2) as Integer 
    's(0)=5 
    's(1)=10 
    's(2)=15 
    'write to file the data from the activesheet 
    CreateFixedWidthFile sPath, ActiveSheet, s 
End Sub 

Answer 1

像这样的东西应该工作：

x = 9983.54 
a = Space(21-Len(CStr(x))) & CStr(x) 

然后a将是14个空格，然后是x：

a = "    9983.54" 

这里21是所需的列宽---根据需要改变。对于非数字x，可能不需要CStr。

如果你要右对齐大量不同数据的不同宽度的字段，你可以写一个通用的功能：你LeftJust(CStr(9983.54), 21)叫它

Function LeftJust(val As String, width As Integer) As String 
    LeftJust = Space(width - Len(val)) & val 
End Function 

的。

另请注意，VBA的Print #声明有一个Spc(n)参数，您可以使用该参数来生成固定宽度输出，例如Print #fNum, Spc(n); a;在此声明之前，您计算n：n = 21-Len(CStr(a))。

希望有帮助