XSD File

Question

I have an xml file, but I need to be able to program this file to automatically generate an xsd file upon saving.  Does anyone know how I can do that?  Is there a way to program a macro to generate the xsd file?

Answer 1

Hi AuntDi,

Thanks for your post.

I think this is what you are looking for:

Sub Create_XSD2()
   Dim StrMyXml As String, MyMap As XmlMap
   Dim StrMySchema As String
   ' Book.xml is the file created in section one of this topic.
   StrMyXml = "C:\BookData.xml"

   ' Turn off async loading.
   Application.DisplayAlerts = False
   ' Add the string to the XmlMaps collection.
   Set MyMap = ThisWorkbook.XmlMaps.Add(StrMyXml)
   Application.DisplayAlerts = True

   ' Create an empty file and output the schema.
   StrMySchema = ThisWorkbook.XmlMaps(1).Schemas(1).XML
   Open "C:\BookData2.xsd" For Output As #1
   Print #1, StrMySchema
   Close #1
End Sub

 

For more about this, please refer to: http://msdn.microsoft.com/en-us/library/gg469857.aspx

I hope this helps.

---

Best Regards, Calvin Gao [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

Thank you for responding to my post. 

When I try to run the code provided, I receive a Microsoft Visual Basic error that states "System Error &H80041020 (-2147217376)."  I have no idea what that means.  What should I do next?

Answer 3

Hi,

Would you like to tell on which line cause the error? Which Excel version are your working with? What your XML file looks like?

I would happy to help you to resolve the problem if you can provide some details like above:-)

I look forward to hearing of you.

---

Best Regards, Calvin Gao [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 4

I experienced the same error...
Excel 2007

DEBUG:  Set MyMap = ThisWorkbook.XmlMaps.Add(StrMyXml)

Does this line need to be written differently for 2007?

 

Sub Create_XSD()
   Dim StrMyXml As String, MyMap As XmlMap
   Dim StrMySchema As String
   StrMyXml = "< BookInfo >"
   StrMyXml = StrMyXml & "<Book>"
   StrMyXml = StrMyXml & "<ISBN>Text</ISBN>"
   StrMyXml = StrMyXml & "<Title>Text</Title>"
   StrMyXml = StrMyXml & "<Author>Text</Author>"
   StrMyXml = StrMyXml & "<Quantity>999</Quantity>"
   StrMyXml = StrMyXml & "</Book>"
   StrMyXml = StrMyXml & "<Book></Book>"
   StrMyXml = StrMyXml & "</ BookInfo >"

   ' Turn off async loading.
   Application.DisplayAlerts = False
   ' Add the string to the XmlMaps collection.
   Set MyMap = ThisWorkbook.XmlMaps.Add(StrMyXml)
   Application.DisplayAlerts = True

   ' Create an empty file and output the schema.
   StrMySchema = ThisWorkbook.XmlMaps(1).Schemas(1).XML
   Open "C:\MySchema.xsd" For Output As #1
   Print #1, StrMySchema
   Close #1
End Sub

Answer 5

remove the spaces in < BookInfo > & </ BookInfo >

s/b  <BookInfo>  & </BookInfo>

 

Works now!

Answer 6

YO TENGO EL MISMO PROBLEMA Y YA MUY LOS espacion Y NADA, ME DICE QUE DEPURE Y ME SOMBREA CON AMARILLO LA SIGUIENTE SECCION 

Set MyMap = ThisWorkbook.XmlMaps.Add(StrMyXml)