Results 1 to 15 of 15
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing Values from XML Files (2003)

    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?
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing Values from XML Files (2003)

    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.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Values from XML Files (2003)

    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.
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing Values from XML Files (2003)

    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.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing Values from XML Files (2003)

    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>

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Values from XML Files (2003)

    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?
    Attached Files Attached Files

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Values from XML Files (2003)

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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing Values from XML Files (2003)

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

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Values from XML Files (2003)

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

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

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Values from XML Files (2003)

    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?
    Attached Files Attached Files

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing Values from XML Files (2003)

    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.

  12. #12
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Values from XML Files (2003)

    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.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing Values from XML Files (2003)

    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.

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Values from XML Files (2003)

    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.
    Attached Files Attached Files

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing Values from XML Files (2003)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •