How to create XML without declarations

[silverback24]

Maybe I have been asking the wrong questions? Is there a way to create an XML file without the XML declaration and schema reference? Our ancient inventory management system will not accept anything that is not an XML document without those two lines. I know, I know. I am pushing them to upgrade considering this runs on superDOS.

I have tried piecing the code together and saving as a text document that looks identical. This doesn't work.

 

[jimrward]

can you give a brief example of what you mean by "without declaration and schema reference", is your data source an excel worksheet with column headers ??

 

[silverback24]

Here is a copypasta from a previous post of mine. I have this putting it together two ways. One is to have a sheet with the prefixes of the xml code in column A and the suffixes in the column C I transpose paste the data into B, concatenate them all into D, paste that into a new sheet and save as text. This way has no declarations but isn't accepted due to not being an XML file. All attempts to save this version as an XML file have added data that is not acceptable. The second is in an excel sheet with headers that match, and exporting this as XML file. This file is acceptable if i manually remove the below stated text. That defeats the purpose of the macro.

Ok, I have adapted my macro to utilize two connector pages so that the credits come out correctly. My issue now is the XML header. Our database will not accept the files if they start with:
(?xml version="1.0" encoding="UTF-8" standalone="yes"?)
(SBCPartOrderRequest xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance")

I need it to only start with:
(SBCPartOrderRequest)
Changed < and > to ( and ). Any ideas?

 

[tusharm]

How is the XML file created?

Can you modify the file after it is created?

Here is a copypasta from a previous post of mine. I have this putting it together two ways. One is to have a sheet with the prefixes of the xml code in column A and the suffixes in the column C I transpose paste the data into B, concatenate them all into D, paste that into a new sheet and save as text. This way has no declarations but isn't accepted due to not being an XML file. All attempts to save this version as an XML file have added data that is not acceptable. The second is in an excel sheet with headers that match, and exporting this as XML file. This file is acceptable if i manually remove the below stated text. That defeats the purpose of the macro.

Ok, I have adapted my macro to utilize two connector pages so that the credits come out correctly. My issue now is the XML header. Our database will not accept the files if they start with:
(?xml version="1.0" encoding="UTF-8" standalone="yes"?)
(SBCPartOrderRequest xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance")

I need it to only start with:
(SBCPartOrderRequest)
Changed < and > to ( and ). Any ideas?

 

[silverback24]

The XML version I put together just exports the data from an Excel sheet to which I have a schema attached. I have deleted the data afterward manually and once that is done they are accepted. I have tried some find and replace things I have found but they haven't worked. I do not have the actual macro at home this weekend so I can't post the code until tomorrow. Thanks for y'alls responses.

 

[tusharm]

So, it appears you have your own macro that creates the file. In that case, modify that macro so that after Excel creates the file, the macro opens it as a text file (which is what a XML file is!), delete the 1st line and in the 2nd line replace everything in "xmlns and ") with just ) -- where ) is actually >.

The XML version I put together just exports the data from an Excel sheet to which I have a schema attached. I have deleted the data afterward manually and once that is done they are accepted. I have tried some find and replace things I have found but they haven't worked. I do not have the actual macro at home this weekend so I can't post the code until tomorrow. Thanks for y'alls responses.

 

[silverback24]

The find and replace codes I have tried ran into errors with the quotes that are in the 2 lines. Do you have any pointers as far as coding goes?
I would think it would start with:

Dim File_Path As String File_path= Filename& ".xml" Open File_Path for Input as #1</pre>

Here is my current macro:
Sub Creditmemo()
'
' Macro1 Macro
'

'
Dim n As Integer
Dim r As Integer
Dim Filename As String
n = 1
r = 2
'Fname = "F:\Users\Clint\Desktop\work stuff\" & "CRDA " & Format(Date, "ddmmyyyy") & " - " & n & ".txt"

'Paste data from Credit Memo Data to Row 2 for the connector pages to work
Workbooks("Credit Memo Workbook.XLSM").Activate
Sheets("Credit Memo Data").Select
For Each Cell In Sheets("Credit Memo Data").Range("A2:A500")
If Cell.Value = "CREDIT" Then
Filename = "H:\Desktop\" & "CRDA " & Format(Date, "ddmmyyyy") & " - " & n

Sheets("Credit Memo Data").Select
Rows(r & ":" & r).Select
Selection.Copy

Sheets("Row 2 connector page").Select
Sheets("Row 2 connector page").Range("A2").PasteSpecial Paste:=xlPasteValues, Transpose:=False

'Export XML code
ActiveWorkbook.XmlMaps("SBCPartOrderRequest_Map").Export URL:=Filename & ".xml"

n = n + 1
r = r + 1

Else
Exit Sub

End If
Next
End Sub

 

[silverback24]

I am self teaching macros, this last piece is something I haven't been able to put together. So any help, as to how to implement this last section would save my company time and money so we don't have to pay our inventory management company to write something to accept it with this extra data, will be greatly appreciated.

 

[silverback24]

Nevermind, they made a change on their end so that it didn't look for the > when looking for the leading declaration.