VBA 导出 Excel 范围到特定目录和文件名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13896822/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Export Excel Range to specific directory and file name
提问by user1906902
I have an Excel worksheet from which I need to export range, A:1 to last used cell in column A, to an xml file. How do you set the exported file name to be the same as the file I exported from?
我有一个 Excel 工作表，我需要从中导出范围，A:1 到 A 列中最后使用的单元格，到一个 xml 文件。你如何将导出的文件名设置为与我导出的文件相同？
Sub exportxmlfile()
Dim myrange As Range
Worksheets("xml").Activate
Set myrange = Range("A1:A20000")
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("C:\exports12\test.xml", True)
For Each c In myrange
a.WriteLine (c.Value)
Next c
a.Close
End Sub
采纳答案by chris neilsen
Use the Workbook.Nameproperty to get the file name.
使用该Workbook.Name属性获取文件名。
FWIW, there are a few opportunities to improve your code
FWIW，有一些机会可以改进您的代码
Sub exportxmlfile()
' declare all your variables
Dim myrange As Range
Dim fs As Object
Dim a As Object
Dim dat As Variant
Dim i As Long
' No need to activate sheet
With Worksheets("xml")
' get the actual last used cell
Set myrange = .Range("A1", .Cells(.Rows.Count, 1).End(xlUp))
' copy range data to a variant array - looping over an array is faster
dat = myrange.Value
Set fs = CreateObject("Scripting.FileSystemObject")
' use the excel file name
Set a = fs.CreateTextFile("C:\exports12\" & .Parent.Name & ".xml", True)
End With
For i = 1 To UBound(dat, 1)
a.WriteLine dat(i, 1)
Next
a.Close
End Sub
