VBA将Excel导出为XML格式并保存到特定目录

I'm nearly finished building a large Macro but I have on efinal stumbling block. I have produced a sheet with expected values which I now need to export to XML format. I only need to export this particular sheet called "Output". There were issues when I exported to text so had to create a Sub exportToXML to remove some quotation marks that where produced on export. This worked fine. However when I export I have to manually type in the Directory and filename I want on output file which isnt good. The directory may change from time to time and also the filename.

我差不多已经完成了一个巨大的宏观建筑,但是我已经完成了绊脚石。我已经生成了一个具有预期值的工作表,我现在需要将其导出为XML格式。我只需要导出这个名为“输出”的特殊表格。我导出到文本时出现问题,因此必须创建Sub exportToXML以删除导出时生成的某些引号。这很好。但是,当我导出时,我必须在输出文件上手动输入我想要的目录和文件名,这不是很好。目录可能会不时更改,也可能会更改文件名。

Sub exportToXML(fileNAme As String, ws As Worksheet) On Error GoTo EndMacro: Dim FNum As Integer FNum = FreeFile Dim startRow As Long, endRow As Long Dim startCol As Integer, endCol As Integer With ws.UsedRange startRow = .Cells(1).Row startCol = .Cells(1).Column endRow = .Cells(.Cells.Count).Row endCol = .Cells(.Cells.Count).Column End With

Sub exportToXML(fileNAme As String,ws As Worksheet)On Error GoTo EndMacro:Dim FNum As Integer FNum = FreeFile Dim startRow As Long,endRow As Long Dim startCol As Integer,endCol As Integer with ws.UsedRange startRow = .Cells(1) .Row startCol = .Cells(1).Column endRow = .Cells(.Cells.Count).Row endCol = .Cells(.Cells.Count).Column End With

Open fileNAme For Output Access Write As #FNum
Dim rowNdx As Long, colNdx As Integer
Dim wholeLine As String, cellValue As String
For rowNdx = startRow To endRow
    wholeLine = ""
    For colNdx = startCol To endCol
        If ws.Cells(rowNdx, colNdx).Value = "" Then
            cellValue = ""
        Else
            cellValue = ws.Cells(rowNdx, colNdx).Value
            If removeCommaNums Then
                If IsNumeric(cellValue) Then
                    cellValue = Replace(cellValue, ",", "")
                End If
            End If
        End If
        wholeLine = wholeLine & cellValue & sep
    Next colNdx
    wholeLine = Left(wholeLine, Len(wholeLine) - Len(sep))
    Print #FNum, wholeLine; " "
Next rowNdx
EndMacro:
On Error GoTo 0
Close #FNum
End Sub

Sub SaveAs()
fmt As String, Directory As String, tradeid As String
fmt = ".txt"
tradeid = Worksheets("XML Instruction").Range("B16").Value
Directory = Worksheets("EUC").Range("C7").Value
Dim op As Worksheet
Set op = Sheets("Output")

exportToXML "I:\test.txt", op

End Sub

The last part of the SavAs Sub is the part I have the issue on :- exportToXML "I:\test.txt", op . I had to manually enter the location (I:) and filename (test) to get any output file.

SavAs Sub的最后一部分是我遇到的问题: - exportToXML“I:\ test.txt”,op。我不得不手动输入位置(我:)和文件名(测试)来获取任何输出文件。

Can I not reference the Directory & tradeid for example to determine where the file will export to and what the filename will be? This is why I have referenced Directory, tradeid, fmt.

我是否可以不参考Directory和tradeid来确定文件将导出到的位置以及文件名是什么?这就是我引用Directory,tradeid,fmt的原因。

1 个解决方案

#1

You can. Simply write:

您可以。只需写:

exportToXML Directory & "\" & tradeid & "\" & fmt, op

exportToXML目录&“\”&tradeid&“\”&fmt,op

Make sure Directory is a valid Directory and tradeid does not have special chars not permitted.

确保目录是有效的目录,并且tradeid没有不允许的特殊字符。

#1