Solved: Export XML from Access using Excel VBA

[db342]

I am writing an Excel (2003) VBA macro to export Access (2003) tables into XML which will then be formatted/translated with an XSLT stylesheet and loaded into a simulation program.


For Each obj In dbs.AllTables
TableNames(indexTable) = obj.Name ' Put table name in array
indexTable = indexTable + 1 'index the table count
NewXMLFile = NewPath & TableNames(indexTable) & ".xml" 'create a filepath for xml file

'.....need to save table as xml here.......

Next obj


The issue is trying to export the table as xml. It can be done (laboriously) by selecting a table in Access and choosing "export" then "as xml", so I believe there must be a way to do it programmatically.

I found:
Application.ExportXML acExportTable, TableNames(indexTable), NewXMLFile
But I haven't found much supporting info on it.

Does anyone know of a way to save an Access table as an XML file using VBA in Excel?

I would be grateful for help.
Thanks!:think:

[db342]

Anyone?

[rory]

Is the Application.ExportXML not working then?

Incidentally, since this is really about Access VBA, you might get better assistance in that forum (my Access coding is a little rusty)

[db342]

No, it wasn't working. I posted in Excel forum because I'm using Excel to get to Access. The problem was that Access VBA doesn't work in Excel VBA unless you create an Access.application object. Once I figured that out I was able to use the exportXML method - but still with different syntax than the version I found. I now have it working.

(The reason for using Excel was to be able to enter the names of the Access databases on an Excel worksheet in order to open each database and create an XML representing each table from each database. Plus, the Excel worksheet gives us a place to enter destination filepaths and filenames.)

Here's part of the sub that ended up working. (Some non-essential lines deleted.)


Sub ExportXMLFile(DBFullName As String) 'sub to export db as xml
Dim appAccess As New Access.Application 'Instance of Application to Access needed to utilize application methods
Dim NewXMLFile As String, TableNames() as string
Dim TableCount As Integer, indexTable As Integer

indexTable = 0
appAccess.OpenCurrentDatabase (DBFullName)
TableCount = appAccess.CurrentData.AllTables.Count
ReDim TableNames(TableCount)

For Each obj In appAccess.CurrentData.AllTables
If Not (Left(obj.Name, 4) = "MSys") Then 'ignore MS objects that are not tables
TableNames(indexTable) = obj.Name ' When you find a table put name in array
NewXMLFile = NewFilePath & TableNames(indexTable) & ".xml" 'create a filepath for xml file
appAccess.ExportXML ObjectType:=acExportTable, DataSource:=TableNames(indexTable), _
DataTarget:=NewXMLFile, Encoding:=acUTF8
XMLArrayBucket = indexTable + XMLArrayIndex 'set XMLArray bucket index
XMLArray(XMLArrayBucket) = NewXMLFile '
XMLArrayIndex = XMLArrayIndex + 1 'Track Qty in XMLArray
indexTable = indexTable + 1
End If
Next obj
appAccess.CloseCurrentDatabase
End Sub


I'm fairly new to this, so I'm sure there were easier ways to accomplish some of it. Having found little to go by, at least I'm happy :rofl: to get it to work.

Thanks for looking at it Rory. :beerchug:

Hope it helps someone later!

[stanl]

Does anyone know of a way to save an Access table as an XML file using VBA in Excel?

I would be grateful for help.
Thanks!:think:

I started a thread about the .xsd part of that in the SQL forum. The code is a lot more direct than yours. but my aims were a little different.

Stan