尝试使用工作表的名称保存Excel工作簿的当前工作表 . 基于http://www.mcgimpsey.com/excel/textfiles.html的示例 . 我可以使用工作簿的名称保存它(使用Open(ThisWorkbook.FullName&".txt")输出为#nFileNum),但不是工作表 . 尝试了ActiveSheet.Name,它将显示在msgBox中,但它不会保存 .
'from http://www.mcgimpsey.com/excel/textfiles.html
Public Sub FixedFieldTextFile()
Const DELIMITER As String = "" 'Normally none
Const PAD As String = " " 'or other character
Dim vFieldArray As Variant
Dim myRecord As Range
Dim nFileNum As Long
Dim i As Long
Dim sOut As String
'MsgBox (ActiveSheet.Name)
'vFieldArray contains field lengths, in characters, from field 1 to N
vFieldArray = Array(30, 10, 10, 25, 25, 14, 14, 14, 14, 10, 10, 10, 10)
nFileNum = FreeFile
Open "Test.txt" For Output As #nFileNum
'Open (ThisWorkbook.FullName & ".txt") For Output As #nFileNum
'Open (ActiveSheet.Name & ".txt") For Output As #nFileNum
'Open (Worksheet.Name & ".txt") For Output As #nFileNum
For Each myRecord In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For i = 0 To UBound(vFieldArray)
sOut = sOut & DELIMITER & Left(.Offset(0, i).Text & _
String(vFieldArray(i), PAD), vFieldArray(i))
Next i
Print #nFileNum, Mid(sOut, Len(DELIMITER) + 1)
sOut = Empty
End With
Next myRecord
Close #nFileNum
End Sub