Results 1 to 2 of 2

Thread: VBA saving row data as xml file to directory specified in row cell

  1. #1

    VBA saving row data as xml file to directory specified in row cell



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi, I have some vba that generates an xml file from row data. The xml files are currently being saved in the same directory as the workbook.
    I now need the xml file to save to a folder/path specified in a cell in the same row. The path/folder should exist. I suppose it would be good if the code could make the directory if it did not already exist.
    Code Below. Thanks

    Sub testXLStoXML()
    sTemplateXML = _
    "<?xml version='1.0'?>" + vbNewLine + _
    "<EmailValues>" + vbNewLine + _
    "<Host>" + "</Host>" + vbNewLine + _
    "<FromEmail>" + "</FromEmail>" + vbNewLine + _
    "<FromName>" + "</FromName>" + vbNewLine + _
    "<Method>" + "</Method>" + vbNewLine + _
    "<ToEmail>" + "</ToEmail>" + vbNewLine + _
    "<FailEmail>" + "</FailEmail>" + vbNewLine + _
    "<CCAddresses>" + "</CCAddresses>" + vbNewLine + _
    "<BCCAddresses>" + "</BCCAddresses>" + vbNewLine + _
    "<ReplyTo>" + "</ReplyTo>" + vbNewLine + _
    "<Module>" + "</Module>" + vbNewLine + _
    "<Subject>" + "</Subject>" + vbNewLine + _
    "<Body>" + "</Body>" + vbNewLine + _
    "<BodyIsHTML>" + "</BodyIsHTML>" + vbNewLine + _
    "<ReceiptReqd>" + "</ReceiptReqd>" + vbNewLine + _
    "<EnableSsl>" + "</EnableSsl>" + vbNewLine + _
    "<UserEmail>" + "</UserEmail>" + vbNewLine + _
    "<Attachment>" + "</Attachment>" + vbNewLine + _
    "</EmailValues>" + vbNewLine

    Set doc = CreateObject("MSXML2.DOMDocument")
    doc.async = False
    doc.validateOnParse = False
    doc.resolveExternals = False

    With ActiveWorkbook.Worksheets(1)
    lLastRow = .UsedRange.Rows.Count

    For lRow = 2 To lLastRow
    sFile = .Cells(lRow, 1).Value
    sHost = .Cells(lRow, 2).Value
    sFromEmail = .Cells(lRow, 3).Value
    sFromName = .Cells(lRow, 4).Value
    sMethod = .Cells(lRow, 5).Value
    sToEmail = .Cells(lRow, 6).Value
    sFailEmail = .Cells(lRow, 7).Value
    sCCAddresses = .Cells(lRow, 8).Value
    sBCCAddresses = .Cells(lRow, 9).Value
    sReplyTo = .Cells(lRow, 10).Value
    sModule = .Cells(lRow, 11).Value
    sSubject = .Cells(lRow, 12).Value
    sBody = .Cells(lRow, 13).Value
    sBodyIsHTML = .Cells(lRow, 14).Value
    sReceiptReqd = .Cells(lRow, 15).Value
    sEnableSsl = .Cells(lRow, 16).Value
    sUserEmail = .Cells(lRow, 17).Value
    sAttachment = .Cells(lRow, 18).Value

    doc.LoadXML sTemplateXML
    doc.getElementsbyTagName("Host")(0).appendChild doc.createTextNode(sHost)
    doc.getElementsbyTagName("FromEmail")(0).appendChild doc.createTextNode(sFromEmail)
    doc.getElementsbyTagName("FromName")(0).appendChild doc.createTextNode(sFromName)
    doc.getElementsbyTagName("Method")(0).appendChild doc.createTextNode(sMethod)
    doc.getElementsbyTagName("ToEmail")(0).appendChild doc.createTextNode(sToEmail)
    doc.getElementsbyTagName("FailEmail")(0).appendChild doc.createTextNode(sFailEmail)
    doc.getElementsbyTagName("CCAddresses")(0).appendChild doc.createTextNode(sCCAddresses)
    doc.getElementsbyTagName("BCCAddresses")(0).appendChild doc.createTextNode(sBCCAddresses)
    doc.getElementsbyTagName("ReplyTo")(0).appendChild doc.createTextNode(sReplyTo)
    doc.getElementsbyTagName("Module")(0).appendChild doc.createTextNode(sModule)
    doc.getElementsbyTagName("Subject")(0).appendChild doc.createTextNode(sSubject)
    doc.getElementsbyTagName("Body")(0).appendChild doc.createTextNode(sBody)
    doc.getElementsbyTagName("BodyIsHTML")(0).appendChild doc.createTextNode(sBodyIsHTML)
    doc.getElementsbyTagName("ReceiptReqd")(0).appendChild doc.createTextNode(sReceiptReqd)
    doc.getElementsbyTagName("EnableSsl")(0).appendChild doc.createTextNode(sEnableSsl)
    doc.getElementsbyTagName("UserEmail")(0).appendChild doc.createTextNode(sUserEmail)
    doc.getElementsbyTagName("Attachment")(0).appendChild doc.createTextNode(sAttachment)
    doc.Save sFile
    Next

    End With
    End Sub

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,568
    Articles
    0
    Excel Version
    365
    At the moment, as your code stands, if the full path and file name is included in column A then the file gets saved at that location.
    If however, if the folder/path are in another column we need to know (a) which column and (b) whether that data includes a final (rightmost) backslash.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •