VBA Code to Save Excel Sheet to XML

  • Thread starter Thread starter sjwopg
  • Start date Start date
S

sjwopg

I'm working on a project that uses VBA to send data to Excel from Access. The
spreadsheet has a number of tabs with charts that reflect the data. At the
end of the Access code, the spreadsheet is saved to a specific folder. The
next step would be to create an XML file for the contents of the "Data" tab.

When I create the XML file manually from Excel, I get the xml list which is
<20k. When I try to save the Excel file as xml, using VBA in Access, I get a
file that is >300k and contains the whole spreadsheet.

Can someone show me, or lead me, to VBA code that I can use in Access to
save only one tab, from a multi-tab spreadsheet into an xml file?

Thanks in advance.

Steve
 
Excel has a Worksheet.SaveAs method, where you can specify XML as the
output format using the xlXMLSpreadsheet constant. Since you're doing
this in Access, you'll need to define the constant somewhere in your
code:

Const xlXMLSpreadsheet = 46

For example, this will save the active sheet as an XML file:

ActiveSheet.SaveAs "C:\MyFile.xml", xlXMLSpreadsheet

Obviously you would need to use fully qualified Excel Objects, since
there's no ActiveSheet object in Access.

See http://blogs.technet.com/heyscripti...an-i-convert-an-excel-spreadsheet-to-xml.aspx
for code sample.

HTH,
JP
 
Back
Top