0

I have written some code that converts my Excel file into a XML data form, however I cannot figure out a way to save the new document to a specific location with a specific name.

So far I have tried to find some that will give me the option to save the filename, however everything I have found so far will only let me put in a path name when I attempt to save it. Like Document.Save "Path" will only allow me to save to the path location and will not keep the file name that I have stored in a variable.

xslDoc.LoadXML "<?xml version=" & Chr(34) & "1.0" & Chr(34) & "?>" _
            & "<xsl:stylesheet version=" & Chr(34) & "1.0" & Chr(34) _
            & "                xmlns:xsl=" & Chr(34) & "http://www.w3.org/1999/XSL/Transform" & Chr(34) & ">" _
            & "<xsl:strip-space elements=" & Chr(34) & "*" & Chr(34) & " />" _
            & "<xsl:output method=" & Chr(34) & "xml" & Chr(34) & " indent=" & Chr(34) & "yes" & Chr(34) & "" _
            & "            encoding=" & Chr(34) & "UTF-8" & Chr(34) & "/>" _
            & " <xsl:template match=" & Chr(34) & "node() | @*" & Chr(34) & ">" _
            & "  <xsl:copy>" _
            & "   <xsl:apply-templates select=" & Chr(34) & "node() | @*" & Chr(34) & " />" _
            & "  </xsl:copy>" _
            & " </xsl:template>" _
            & "</xsl:stylesheet>"
    xslDoc.async = False
    doc.transformNodeToObject xslDoc, newDoc
    Dim Docname As String

    Docname = "VMM_" & SN.Text
    fileSaveName = GetSaveAsFilename(InitialFileName:=sItem * Docname, filefilter:="XML Files (*.xml),*xml")
    newDoc.SaveAs Filename:= fileSaveName, FileFormat:=xlTextPrinter, CreateBackup:False

I found code that helped me convert my code to XML which is the first part of it, however I cant find out how to use the SaveAs with the newDoc which is a MSXML2.DOMDocument60. I'm trying to save it with the filename Docname, in the location which is called sItem, any help would be greatly appreciated

Hakon
  • 89
  • 1
  • 9
  • I have added what I think you requested! If you would like to look at anything else let me know! @Parfait. Thank you for looking at this, I can just not figure out how to do it. – Hakon Jun 03 '19 at 20:28
  • It was actually your code that I found from three years ago and am using for the conversion! I'm sorry I'm pretty new to VBA. – Hakon Jun 03 '19 at 20:37
  • And what error do you receive? Please post exact message. Be sure to use [proper error handling](https://stackoverflow.com/questions/6028288/properly-handling-errors-in-vba-excel) to retrieve a message. Does FileDialog launch with `GetSaveAsFilename` to pick a file? – Parfait Jun 03 '19 at 20:44
  • Haha! I thought I recognized the identity transform in XSLT. It is much easier to read it from file instead of concatenated string. Remember XSLT is an XML file. – Parfait Jun 03 '19 at 20:45
  • No File Dialog does not launch, it selects GetSaveAsFilename and states Sub or Function not defined. – Hakon Jun 03 '19 at 20:46
  • Prefix it with `Application.` You should always use `Option Explicit` at top of VBA module and always `Debug \ Compile` after code changes. – Parfait Jun 03 '19 at 20:53
  • Luckily I had the Option Explicit already up there and when I added Application it continued past that line, however once it gets to the newDoc.SaveAs line it gives me a syntax error. – Hakon Jun 03 '19 at 20:58
  • Then fix the syntax error. `CreateBackup` arg should have `:=` not just colon. – Parfait Jun 03 '19 at 21:05
  • If the Method or Data member is not found for in front of the .SaveAs, I see most people are putting sheets in front of it, but since I am creating a xml should I still do that? – Hakon Jun 03 '19 at 21:36

0 Answers0