请教：A:G列数据如何将其从第2行起按第一行所要求的字节长度（不足的末尾以空格补足），用VBA整表导出一个每列定长左对齐，“|”分隔的TXT文件，谢谢！

附件：用VBA整表导出TXT文件.rar 

[此贴子已经被作者于2008-6-15 11:31:01编辑过]

请高手指点、简化、解决“有中文全角字节导致错乱情况”？

Sub abc()
    Dim n, i, j, k, cd2 As Integer
    Dim str As String
    Dim str0 As String
    Dim str1 As String * 21
    Dim str2 As String * 12
    Dim str3 As String * 32
    Dim str4 As String * 60
    Dim str5 As String * 16
    Dim str6 As String * 12
    Dim str7 As String * 60
   
    Set fp = CreateObject("Scripting.FileSystemObject")
    Set f = fp.CreateTextFile("c:\1.txt", True)
    Application.ScreenUpdating = False     '关闭屏幕刷新
    p = Range("i65536").End(xlUp).Row    '读取当前列行末号
    Application.ScreenUpdating = True      '打开屏幕刷新
       
      
    i = 2
    j = 1
    Do While Cells(i, 1) <> ""
        str = ""
        For j = 1 To 7  '读取1-7列
           
            'cd2 = LenB(StrConv(Trim(Cells(i, j)), vbFromUnicode))  '读取当前单元格字节长度,计算字符串的字节数
            'MsgBox i & ":" & j & "=" & cd2
           
            If (Trim(Cells(i, j).Value)) <> "" Then
               str0 = Trim(Cells(i, j).Value)
            Else
               str0 = " "
            End If
           
            If j = 1 Then
                          str1 = str0
                          str0 = str1
            End If
            If j = 2 Then
                          str2 = str0
                          str0 = str2
            End If
            If j = 3 Then
                          str3 = str0
                          str0 = str3
            End If
            If j = 4 Then
                          str4 = str0
                          str0 = str4
            End If
            If j = 5 Then
                          str5 = str0
                          str0 = str5
            End If
            If j = 6 Then
                          str6 = str0
                          str0 = str6
            End If
            If j = 7 Then
                          str7 = str0
                          str0 = str7
            End If
           
            str = str + str0
            str = str + "|"
           
            Next j
        str = str + Trim(Cells(i, 15).Value)
        f.writeline (str)
    i = i + 1
    Loop
    f.Close
Shell "notepad.exe " & "c:\1.txt", vbNormalFocus
End Sub

[此贴子已经被作者于2008-6-15 11:43:28编辑过]

See if help you

试试下面的代码：

Sub OutDataExample()
    Dim lOutputFile As Long
    Dim rng As Range
   
    Set rng = Sheet1.Range("A2")
   
    On Error Resume Next
    Kill ("C:\WriteExample.txt")
    On Error GoTo 0
   
    '获取一个有效的文件号
    lOutputFile = FreeFile
   
    '创建一个新的文件
    Open "C:\WriteExamle.txt" For Output As #lOutputFile
   
    '遍历数据
    Do Until IsEmpty(rng)
        '将数据写入文件
        Print #lOutputFile, _
                   Trim(rng.Value) & Space(22 - Len(rng.Value)) & "|" & _
                   Trim(rng.Offset(0, 1).Value) & Space(15 - Len(rng.Offset(0, 1).Value)) & "|" & _
                   Trim(rng.Offset(0, 2).Value) & Space(32 - Len(rng.Offset(0, 2).Value)) & "|" & _
                   Trim(rng.Offset(0, 3).Value) & Space(60 - Len(rng.Offset(0, 3).Value)) & "|" & _
                   Trim(rng.Offset(0, 4).Value) & Space(16 - Len(rng.Offset(0, 4).Value)) & "|" & _
                   Trim(rng.Offset(0, 5).Value) & Space(12 - Len(rng.Offset(0, 5).Value)) & "|" & _
                   Trim(rng.Offset(0, 6).Value)
       
        '下移一行
        Set rng = rng.Offset(1, 0)
    Loop
   
    Set rng = Nothing
    Close lOutputFile
End Sub

[此贴子已经被作者于2008-6-15 12:13:10编辑过]

VBA中LEN函数计算的是字符数，LENB函数计算的按双字节字符计算的字节数，
比如字符串“公司abc”的字节数为7，但在VBA中，用LEN（"公司abc"）得到的结果是5，用LENB（"公司abc"）得到的结果是10，
请问应如何才能得到字符串的字节数？

因上述原因所致，KCFONG、fanjy 所提供的VBA程序导出的txt文件仍有错位，请两位再出手相助！（1楼附件已改）

参照 http://club.excelhome.net/viewthread.php?tid=260561&replyID=&skin=0

所提供：lenb(StrConv("公司abc",vbfromUnicode)) 字节数计算正确，但VBA如何编写使其不错位。

[此贴子已经被作者于2008-6-15 15:12:02编辑过]

see if help you