Excel 2007 XML Export

[biz7]

Hello,

Our bank just installed Microsoft Excel 2007. When I click on the Developer
tab in the Ribbon section & export, the file exports successfully. However,
I cannot import the file into my work application directly, "unless", I open
the file with notepad & delete the 1st line item which automatically is there
when I export from Excel 2007. The line item reads as follows: <?xml
version="1.0" encoding="UTF-8" standalone="yes"?>

Is there a way to remove this automatic line item from the xml? I am not
sure what is causing the xml export to add this line item automatically.
Otherwise, I would be able to skip a step (do not open notepad) & just import
the xml file. Please advise-thanks for your assistance!

 

[Jan Karel Pieterse]

Hi Biz7,

Our bank just installed Microsoft Excel 2007. When I click on the Developer
tab in the Ribbon section & export, the file exports successfully. However,
I cannot import the file into my work application directly, "unless", I open
the file with notepad & delete the 1st line item which automatically is there
when I export from Excel 2007. The line item reads as follows: <?xml
version="1.0" encoding="UTF-8" standalone="yes"?>

Is there a way to remove this automatic line item from the xml? I am not
sure what is causing the xml export to add this line item automatically.
Otherwise, I would be able to skip a step (do not open notepad) & just import
the xml file. Please advise-thanks for your assistance!

That line *belongs* to an XML file, it tells the world that it is dealing with
an XML file. And XML file which does not have that line is in fact not properly
created in the first place.

That being said; what is the second line? WHat needs to be done with the xml
file after the export and "fix"?

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[biz7]

Hi Jan,

Following is the export xml from Microsoft Excel 2007 Developer tab (please
see below). I am importing into a SQL web based application. In testing,
the xml file will import but will not update data with the first line item
there. When the first line item is removed, the data is updated. I have
tested it many times. Please advise, thanks.

XML export:
===================
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ExportEdits>
<tblScenarioData>
<Row Key1="4-299436" Key2="" Key3="" Key4="" Key5="" Attribute="CUSIP"
TableName="tblAssets" AttributeValue="03073JAH7" DataType="8" OverWrite="1"
Active="1"/>
</tblScenarioData>
</ExportEdits>
=====================

 

[Jan Karel Pieterse]

Hi Biz7,

You could use a small macro like this one to remove the first line:

Sub RemoveFirstLine()
Dim lFreeFile As Long
Dim sNew() As String
Dim lLineCt As Long
Dim lCt As Long

ReDim sNew(0)
lFreeFile = FreeFile
Open "c:\data\test.xml" For Input As #lFreeFile
Do
ReDim Preserve sNew(lLineCt)
Input #lFreeFile, sNew(lLineCt)
lLineCt = lLineCt + 1
Loop Until EOF(lFreeFile)
Close lFreeFile
lFreeFile = FreeFile
Open "c:\data\test1.xml" For Output As lFreeFile
For lCt = LBound(sNew) + 1 To UBound(sNew)
Print #lFreeFile, sNew(lCt)
Next
Close lFreeFile
End Sub

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com
-------------------------------------------------
From: biz7 <[email protected]>
Subject: Re: Excel 2007 XML Export
Date: Fri, 10 Jul 2009 06:36:07 -0700
Newsgroups: microsoft.public.excel.crashesgpfs

Hi Jan,

Following is the export xml from Microsoft Excel 2007 Developer tab (please
see below). I am importing into a SQL web based application. In testing,
the xml file will import but will not update data with the first line item
there. When the first line item is removed, the data is updated. I have
tested it many times. Please advise, thanks.

XML export:
===================
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ExportEdits>
<tblScenarioData>
<Row Key1="4-299436" Key2="" Key3="" Key4="" Key5="" Attribute="CUSIP"
TableName="tblAssets" AttributeValue="03073JAH7" DataType="8" OverWrite="1"
Active="1"/>
</tblScenarioData>
</ExportEdits>
=====================