Importing Values from XML Files (2003)

[grovelli]

I'm importing the attached XML files by clicking the "Load XML" button on the startup form of the attached mdb. Is there any way, during the import procedure, to append the files to the first table created when you import rather than creating a different table for each import?

[HansV]

ImportXML always imports to a table with the same name as the XML file (without the extension). If you use acAppendData instead of acStructureAndData as second argument of ImportXML, the imported data will be appended to the already existing data. You shouldn't delete the table before importing data in that case, of course.

You cannot use ImportXML to import into a differently named table - you can either rename the XML file before importing it, or you can import it "as is", then use an append query to transfer the data to another table.

[grovelli]

Thank you Hans <img src=/S/smile.gif border=0 alt=smile width=15 height=15>. Is it possibile, using VBA, to automatically launch the winzip built into Windows xp so as to unzip the attached zip file into a folder of one's choosing? I cannot install other zipping utilities. I was planning on extracting the xml files into a folder and then looping through them to import/append them in one go.

[HansV]

From Using Windows XP "Compressed Folder" shell extension to work with .zip files:

<hr>There's no API in Windows to work with .zip files<hr>

There is a download with VB.Net code to extract files from a compressed folder (.zip file), but I have no idea if it can be converted to VBA.

[HansV]

No, you've got a loose double double quote near the end, and there is no backslash at the end of the path. It should be
<code>
Shell """C:ProgrammiWinZipWZUnzip.exe"" -o """ & Me.txtXMLDocument & """ ""C:test"""</code>

[grovelli]

Thank you Hans. I'm trying now to do the whole process starting from the zip file(attached) since I've been told I can install WinZip but when I click on the "Load XML" button, no table gets created.
Is
Shell """C:ProgrammiWinZipWZUnzip.exe"" -o " & Me.txtXMLDocument & " c:test"""
the proper syntax?

[grovelli]

Thank you Hans. Now I get an error message telling me that the ImportXML method failed.
Any lead?

[HansV]

I know very little about XML, so I cannot help you with that.

[grovelli]

Found the solution: I added the path to the file name:

Application.ImportXML "c:test" & fil.Name, acAppendData

[grovelli]

The prezzi table(that I get by importing the XML files) has all the fields' data types set as text so when I export the table to Excel by clicking the "Export to XL" button on the startup form of the attached mdb, I get some values which don't match the corresponding ones in the Prezzi table, such as 38.649.457 instead of 38,649457
Is there any way to export the Prezzi table to Excel so that all the values stay as they are in the table?

[HansV]

1) Why don't you import the XML files directly into Excel?
2) The data in the Prezzi table are apparently intended for a system with a comma as decimal separator. To export the data successfully to Excel, you must either set your system to use comma as decimal separator, or you must specify this in the International tab of Tools | Options in Excel.

[grovelli]

Thank you Hans, both the system and the options in Excel are set to use the comma as decimal separator.
I don't know how to code the import/append process of the XML files in Excel VBA. I guess I'll have to make an inquiry in the Excel section of the Lounge.

[HansV]

My system uses comma as decimal separator. If I click the button, the data in the Excel sheet look the same as in the Access table, unless I specify that Excel should use the point as decimal separator. So I can't explain your problem.

[grovelli]

I use Italian settings which use comma as decimal separator.
I suppose you're using Dutch settings so I've tried using those as well but the result is the same in both languages: I get 38.649.457 instead of 38,649457 on row 8 in the attached jpg's.

[HansV]

As far as numbers are concerned, there is no difference between the Italian and Dutch settings. I have no idea why it doesn't work for you.