VBA Export Excel to XML format and save to specific directory

Question

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.

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.

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.

What error do you get if you use your directory, tradeid and fmt? Have you checked it crates a string correctly in debug? — aebailey
Yes thats the problem. I do not get any error and string does not get created. How should I go about the last piece? — Conor
so say if I edit : exportToXML "Directory&tradeid&fmt", op - what happens is file gets saved to the location where my Macro is saved to and filename appears as "Directory&tradeid&fmt". I have tried several different ways. — Conor

AnalystCave.com AnalystCave.com · Accepted Answer · 2015-05-11T15:04:17

You can. Simply write:

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

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

VBA Export Excel to XML format and save to specific directory

1 Answers